Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle

EBS中用于clone创建数据库的脚本auclondb.sql

做过Oracle EBS升级/迁移项目的DBA对脚本auclondb.sql应该很熟悉,会生成一个用于创建数据空库的脚本。这个脚本记录在patch16541956中,适用于包括10g,11g,12c数据库。


auclondb.sql脚本

REM $Header: auclondb.sql 120.7 2013/04/26 06:08:48 gong ship $
REM
REM dbdrv: none
REM +==========================================================================+
REM | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA    |
REM |                       All rights reserved.                               |
REM +==========================================================================+
REM | NAME
REM |   auclondb.sql
REM |
REM | DESCRIPTION
REM |
REM |   Script to create script (aucrdb.sql) that creates a database with 
REM |   tablespaces and file structures similar to the database against 
REM |   which the script is run.
REM |
REM | USAGE
REM |
REM |   sqlplus <SYSTEM/SYSTEM passwd> @auclondb.sql <10|11|12>
REM |
REM | HISTORY
REM |
REM |   120.6 - Modified to allow RDBMS 12c
REM |   120.7 - Modified NCHAR/CHAR code to accommodate UTF8/UTF8
REM |
REM +==========================================================================+
set feedback off
set trimspool on
set lines 180
set serveroutput on size 900000
set verify off
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR  EXIT FAILURE ROLLBACK;

DEFINE target_dbver="&1"

spool aucrdb.sql

declare

  DEF_MAX_DB_FILES CONSTANT number := 500;
  DEF_PATH_PREFIX  CONSTANT varchar2(200) := '/pgold/oradata/';
  L_TABCHAR        CONSTANT varchar2(1)   := '	';
  CREATE_DATABASE  CONSTANT varchar2(30)  := 'CRDB';

  -- Fixed bug 3634436: added name for the case such that exported database 
  -- version less than 10. i.e. 8i or 9i where there is no such
  -- a datafile name.

  SYSAUX_FILENAME  CONSTANT varchar2(30)  := '?/dbf/sysaux01.dbf'; 

  QUOTE_START      CONSTANT varchar2(1) := '"';
  QUOTE_END        CONSTANT varchar2(1) := '"';

  L_DB_FILES            number;
  L_CHARSET             varchar2(100);
  L_NCHARSET            varchar2(100);
  L_INSTANCE_NAME       varchar2(30);
  L_INSTANCE_VERSTR     varchar2(30);
  L_INSTANCE_MAJOR_VER  number;
  L_TARGET_DBVER        number;

  type t_tablespace is table of varchar2(30) index by binary_integer;

  rollback_tablespaces       t_tablespace;
  rollback_tbsp_initialized  boolean := FALSE;


  procedure print(msg in varchar2,
                  indent in number default 0) is
  begin
     dbms_output.put(rpad(' ', indent+1, L_TABCHAR)||nvl(msg, '  '));
  end;

  procedure println(msg in varchar2 default null,
                    indent in number default 0) is
  begin
     print(nvl(msg, L_TABCHAR), indent);
     dbms_output.put_line(L_TABCHAR);
  end;

  procedure get_instance_info(X_instance_name      out NOCOPY varchar2,
                              X_instance_version   out NOCOPY varchar2,
                              X_instance_major_ver out NOCOPY number)
  is
  begin
     select upper(instance_name),
            version,
            substr(version, 1, instr(version, '.', 1)-1)
     into   X_instance_name, X_instance_version, X_instance_major_ver
     from   v$instance
     where  rownum = 1;
  end;

  function get_name(X_name varchar2) return varchar2
  is
  begin
     return(QUOTE_START||X_name||QUOTE_END);
  end;

  procedure header_comments
  is
     l_date_time       varchar2(30);
  begin

     l_date_time := to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');

     println(rpad('REM ', 79, '='));
     println('REM ');
     println('REM NAME');
     println('REM   aucrdb.sql');
     println('REM ');
     println('REM DESCRIPTION');
     println('REM ');
     println('REM   Script to create a database with tablespaces and files');
     println('REM   similar to that of the database against which the script');
     println('REM   was generated.');
     println('REM ');
     println('REM      Database Instance : '||l_instance_name);
     println('REM       Database Version : '||l_instance_verstr);
     println('REM          Creation Date : '||l_date_time);
     println('REM ');
     println('REM NOTES');
     println('REM   BEFORE RUNNING THE SCRIPT YOU MUST REVIEW');
     println('REM   IT FOR THE FOLLOWING : ');
     println('REM       -  Database Characterset information');
     println('REM       -  Location and size of the data files');
     println('REM       -  Tablespace storage information');
     println('REM       -  Rollback Segment information');
     println('REM ');
     println(rpad('REM ', 79, '='));
  end;

  procedure section_comments(X_msg in varchar2)
  is
  begin
     println;
     println(rpad('REM ', 75, '-'));
     println('REM '||X_msg);
     println(rpad('REM ', 75, '-'));
     println;
  end;

  function get_db_param(X_param in varchar2) return varchar2
  is
    l_param_value  varchar2(255);
  begin
    select value
    into   l_param_value
    from   v$parameter
    where  name = X_param
    and    rownum = 1;

    return(l_param_value);

  exception
    when NO_DATA_FOUND then
      return(null);
  end;

  function file_name(X_file_name in varchar2) return varchar2
  is
    l_slash_pos   number;
  begin

     l_slash_pos := instr(X_file_name, '/', -1);

     if (l_slash_pos = 0) then
       l_slash_pos := instr(X_file_name, '\', -1);
     end if;

     if (l_slash_pos > 0) then
        return(''''||DEF_PATH_PREFIX||
                     substr(X_file_name, l_slash_pos+1)||'''');
     else
        return(''''||X_file_name||'''');
     end if;
  end;

  function size_str(X_sizeBytes in number) return varchar2
  is
  begin
     if (X_sizeBytes >= (1024*1024))
     then
        return(round(X_sizeBytes/(1024*1024))||'M');
     elsif (X_sizeBytes >= 1024)
     then
        return(round(X_sizeBytes/(1024))||'K');
     else
        return(X_sizeBytes);
     end if;
  end;

  function get_nls_param(X_param in varchar2) return varchar2
  is
    l_param_value  varchar2(255);
  begin
    select value
    into   l_param_value
    from   v$nls_parameters
    where  parameter = X_param
    and    rownum = 1;

    return(l_param_value);

  exception
    when NO_DATA_FOUND then
      return(null);
  end;


  procedure log_file_info
  is
    cursor c_log_group is
      select group#, bytes, members
      from   v$log
      order by group#;

    cursor c_log_file(X_group number) is
      select group#, member
      from   v$logfile
      where  group# = X_group
      order by member;

  begin
    println('LOGFILE');

    for log_grp in c_log_group
    loop
       if (c_log_group%rowcount > 1) then
         println(',');
       end if;

       println('GROUP '||log_grp.group#||' (', 1);

       for log_file in c_log_file(log_grp.group#)
       loop
          if (c_log_file%rowcount > 1) then
             println(',');
          end if;

          print('  '||file_name(log_file.member), 1);
       end loop;
       println('');

       print(') SIZE '||size_str(log_grp.bytes), 1);
    end loop;

    println('');
  end;

  procedure system_tablespace_info
  is
    cursor c_file(tbsp in varchar2) is
      select file_name, bytes
      from   dba_data_files
      where  tablespace_name = tbsp
      order by file_name;

    cursor c_file_temp(tbsp in varchar2) is
      select file_name, bytes
      from   dba_temp_files
      where  tablespace_name = tbsp
      order by file_name;

    cursor c_undo_tablespaces(def_undo in varchar2) is
      select tablespace_name, contents
      from   dba_tablespaces
      where  contents = 'UNDO' and 
      tablespace_name <> upper(def_undo);

    cursor c_temp_tablespaces is
      select tablespace_name, contents
      from   dba_tablespaces
      where  contents = 'TEMPORARY';

    cursor c_tgroup is
      select group_name, tablespace_name
      from   dba_tablespace_groups;

    default_undo_tbsp varchar2(30);
    default_temp_tbsp varchar2(30);
    default_temp_done boolean := FALSE;

  begin

/* SYSTEM tablespace */
    println('DATAFILE');


    for c_rec in c_file('SYSTEM')
    loop
       if (c_file%rowcount > 1)
       then
          println(',');
       end if;

       print(file_name(c_rec.file_name)||
             ' SIZE '||size_str(c_rec.bytes), 1);
    end loop;
    println('');

/* SYSAUX tablespace */
    println('SYSAUX DATAFILE');
    for c_rec in c_file('SYSAUX')
    loop
      if (c_file%rowcount > 1)
      then
        println(',');
      end if;


      print(file_name(c_rec.file_name)||
        ' SIZE '||size_str(c_rec.bytes), 1);
    end loop;
    println('');

/* Default undo tablespace */
    select value into default_undo_tbsp from v$parameter where name='undo_tablespace';
    println('UNDO TABLESPACE "' || default_undo_tbsp ||'"');
    println('DATAFILE');
    for c_rec in c_file(default_undo_tbsp)
    loop
      if (c_file%rowcount > 1)
      then
        println(',');
      end if;

      print(file_name(c_rec.file_name)||
        ' SIZE '||size_str(c_rec.bytes), 1);
    end loop;
    println('');
    println('EXTENT MANAGEMENT LOCAL');


/* Temporary tablespaces */
    for trec in c_temp_tablespaces
    loop
      if (default_temp_done=FALSE ) 
      then
        println('DEFAULT TEMPORARY TABLESPACE "'||trec.tablespace_name||'"');
      else
        println('CREATE TEMPORARY TABLESPACE "'||trec.tablespace_name||'"');
      end if;

      println('TEMPFILE');
      for frec in c_file_temp(trec.tablespace_name)
      loop
        if (c_file_temp%rowcount > 1)
        then
          println(',');
        end if;
        print(file_name(frec.file_name)||' SIZE '||size_str(frec.bytes), 1);
      end loop;
      println('');
      println('EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M');
      println('/');
      println('');

      default_temp_done := TRUE;
    end loop;
    
/*  other undo tablespaces */
    for trec in c_undo_tablespaces(default_undo_tbsp)
    loop
      println('CREATE UNDO TABLESPACE "'||trec.tablespace_name||'"');
      println('DATAFILE');

      for frec in c_file(trec.tablespace_name)
      loop
        if (c_file%rowcount > 1)
        then
          println(',');
        end if;

        print(file_name(frec.file_name)||' SIZE '||size_str(frec.bytes));
      end loop;

      println('');
      println('EXTENT MANAGEMENT LOCAL AUTOALLOCATE');
      println('/');
      println('');
    end loop;


/* create tablespace groups */
    for trec in c_tgroup
    loop
      println('ALTER TABLESPACE "'||trec.tablespace_name||'" TABLESPACE GROUP "'||trec.group_name||'";');
    end loop;

/* change the default temporary tablespace */
    select property_value into default_temp_tbsp from DATABASE_PROPERTIES
    where property_name='DEFAULT_TEMP_TABLESPACE';

    if (default_temp_tbsp <> 'SYSTEM')
    then
      println('ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "'||default_temp_tbsp||'";');
      println('');
      println('prompt');
      println('prompt Ignore any errors related to the setting');
      println('prompt of the default temporary tablespace');
      println('prompt');
      println('');
    end if;
  end;

  procedure tablespace_info is
    cursor c_tablespaces is
      select tablespace_name
      from   dba_tablespaces
      where  tablespace_name not in ('SYSTEM','SYSAUX')
      and    contents not in ('UNDO','TEMPORARY')
      order by tablespace_name;


    cursor c_file(X_tablespace in varchar2) is
      select file_name, bytes
      from   dba_data_files
      where  tablespace_name = X_tablespace
      order by file_name;

  begin

    section_comments('Create Tablespaces');

    for trec in c_tablespaces
    loop
      println('CREATE TABLESPACE '||
               get_name(trec.tablespace_name));
      println('DATAFILE');

      for frec in c_file(trec.tablespace_name)
      loop
         if (c_file%rowcount > 1) then
            println(',');
         end if;


         print(file_name(frec.file_name)||
               ' SIZE '||size_str(frec.bytes), 1);
      end loop;
      println('');

/* All tablespaces are converted to locally managed, auto segment */
      println('EXTENT MANAGEMENT LOCAL AUTOALLOCATE');
      println('SEGMENT SPACE MANAGEMENT AUTO');
      println('/');
      println('');
    end loop;
  end;

  procedure create_db
  is
  begin
    section_comments('Create Database');

    println('CREATE DATABASE');
    println('MAXDATAFILES '||nvl(L_DB_FILES, DEF_MAX_DB_FILES), 1);
    println('CHARACTER SET '||L_CHARSET, 1);
    println('NATIONAL CHARACTER SET '||L_NCHARSET, 1);
  
    log_file_info;
    system_tablespace_info;
  end;

/* Main procedure */
begin

  if ('&target_dbver' not in ('10','11','12')) then
    raise_application_error(-20001, 'Invalid target database version : '||
                            '&target_dbver');
  end if;

  L_TARGET_DBVER := '&target_dbver';

  get_instance_info(L_INSTANCE_NAME, L_INSTANCE_VERSTR, L_INSTANCE_MAJOR_VER);


  if (L_TARGET_DBVER < L_INSTANCE_MAJOR_VER) then
    raise_application_error(-20001, 
               'Instance database version ('|| L_INSTANCE_MAJOR_VER||
               ') cannot be higher than the target version('|| 
               L_TARGET_DBVER||')');
  end if;

  L_DB_FILES := get_db_param('db_files');
  L_CHARSET  := get_nls_param('NLS_CHARACTERSET');
  L_NCHARSET := get_nls_param('NLS_NCHAR_CHARACTERSET');

/*
  if (L_CHARSET = 'UTF8') then
     L_NCHARSET := 'UTF8';
  else
     L_NCHARSET := 'AL16UTF16';
  end if;
*/

  header_comments;
  create_db;
  tablespace_info;

end;
.
spool aucrdb.sql
/
spool off


commit;
exit;



脚本输出样例
SQL> @auclonedb.sql
Enter value for 1: 12
REM ===========================================================================
REM
REM NAME
REM   aucrdb.sql
REM
REM DESCRIPTION
REM
REM   Script to create a database with tablespaces and files
REM   similar to that of the database against which the script
REM   was generated.
REM
REM      Database Instance : CDB1
REM       Database Version : 12.1.0.2.0
REM          Creation Date : 12-JUN-2016 22:34:41
REM
REM NOTES
REM   BEFORE RUNNING THE SCRIPT YOU MUST REVIEW
REM   IT FOR THE FOLLOWING :
REM       -  Database Characterset information
REM       -  Location and size of the data files
REM       -  Tablespace storage information
REM       -  Rollback Segment information
REM
REM ===========================================================================
REM -----------------------------------------------------------------------
REM Create Database
REM -----------------------------------------------------------------------

CREATE DATABASE
        MAXDATAFILES 200
        CHARACTER SET AL32UTF8
        NATIONAL CHARACTER SET AL16UTF16
LOGFILE
        GROUP 1 (
          '/pgold/oradata/redo01.log'
        ) SIZE 50M ,
        GROUP 2 (
          '/pgold/oradata/redo02.log'
        ) SIZE 50M ,
        GROUP 3 (
          '/pgold/oradata/redo03.log'
        ) SIZE 50M
DATAFILE
        '/pgold/oradata/system01.dbf' SIZE 800M
SYSAUX DATAFILE
        '/pgold/oradata/sysaux01.dbf' SIZE 730M
UNDO TABLESPACE "UNDOTBS1"
DATAFILE
        '/pgold/oradata/undotbs01.dbf' SIZE 225M
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE "TEMP"
TEMPFILE
        '/pgold/oradata/temp01.dbf' SIZE 197M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

prompt
prompt Ignore any errors related to the setting
prompt of the default temporary tablespace
prompt


REM -----------------------------------------------------------------------
REM Create Tablespaces
REM -----------------------------------------------------------------------


CREATE TABLESPACE "USERS"
DATAFILE
        '/pgold/oradata/users01.dbf' SIZE 5M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@db1 ~]$



关键词:ebs oracle 

相关文章

Oracle数据库备份云服务
Oracle RAC and Third Party Cloud
Open Source Languages and Oracle Database
Oracle SQL Developer Command Line (SQLcl)
Oracle Database Multilingual Engine(MLE)
Database Features A-Z
Oracle JET
Do migration with Oracle SQL Developer
Getting started with Oracle JDBC
How to generate AWR on PDB and ADG(12.2 afterwards)
Oracle ZDLRA (Zero Data Lost Recovery Appliance) Features
Oracle Database 18c On Exadata

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com