做过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 ~]$