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 

相关文章

基于PDB的Active Data Guard(Oracle 21.7+)
在Oracle数据库中使用REST
OGG from MySQL to Oracle
Oracle数据库容灾之两地三中心实践
低代码开发用Oracle Apex,看这篇就够了
Oracle Database 20c之SQL宏
Oracle EBS支持19c容器数据库
Java beginner for Oracle DBA
Oracle Database 20c之区块链表
Oracle Database 20c的一些微妙变化
关于Oracle的Sequence,你需要知道的
Oracle数据库优化方面资料
Top