dbca建库脚本——11g
dbca的建库脚本是使用模板来创建数据库，其优点是使用模板创建数据库通常速度都会很快，原因就在于数据文件是从种子数据库中恢复出来的，而不需要创建文件及字典对象等信息。
在是用dbca建库时可以保存建库的脚本，看下这些脚本。
[oracle@sharon sharon]$ cd scripts
[oracle@sharon scripts]$ ll
total 40
-rw-r----- 1 oracle oinstall 2856 Sep 29 04:30 cloneDBCreation.sql
-rw-r----- 1 oracle oinstall 283Sep 29 04:30 CloneRmanRestore.sql
-rw-r----- 1 oracle oinstall 1793 Sep 29 04:30 init.ora
-rw-r----- 1 oracle oinstall 1829 Sep 29 04:30 initsharonTemp.ora
-rw-r----- 1 oracle oinstall 509Sep 29 04:30 lockAccount.sql
-rw-r----- 1 oracle oinstall 654Sep 29 04:30 postDBCreation.sql
-rw-r----- 1 oracle oinstall 570Sep 29 04:30 postScripts.sql
-rw-r----- 1 oracle oinstall 1372 Sep 29 04:30 rmanRestoreDatafiles.sql
-rwxr-xr-x 1 oracle oinstall 705Sep 29 04:30 sharon.sh
-rwxr-xr-x 1 oracle oinstall 554Sep 29 04:30 sharon.sql
主要是运行shell脚本来掉sql脚本来建立数据库。
1. sharon.sh
[oracle@sharon scripts]$ more sharon.sh
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/sharon/adump
mkdir -p /u01/app/oracle/admin/sharon/dpdump
mkdir -p /u01/app/oracle/admin/sharon/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/sharon
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/flash_recovery_area/sharon
mkdir -p /u01/app/oracle/oradata/sharon
mkdir -p /u01/app/oracle/product/11.2.0/db_1/dbs
umask ${OLD_UMASK}
ORACLE_SID=sharon; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab:sharon:/u01/app/oracle/product/11.2.0/db_1:Y
/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus /nolog
@/u01/app/oracle/admin/sharon/scripts/sharon.sql
2. sharon.sql
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/11.2.0/db_1/bin/orapwdfile=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsharon force=y
@/u01/app/oracle/admin/sharon/scripts/CloneRmanRestore.sql
@/u01/app/oracle/admin/sharon/scripts/cloneDBCreation.sql
@/u01/app/oracle/admin/sharon/scripts/postScripts.sql
@/u01/app/oracle/admin/sharon/scripts/lockAccount.sql
@/u01/app/oracle/admin/sharon/scripts/postDBCreation.sql
3. CloneRmanRestore.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/sharon/scripts/CloneRmanRestore.log append
startup nomountpfile="/u01/app/oracle/admin/sharon/scripts/init.ora";
@/u01/app/oracle/admin/sharon/scripts/rmanRestoreDatafiles.sql;
spool off
init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=sharon
###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/sharon/control01.ctl","/u01/app/oracle/flash_recovery_area/sharon/control02.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=4039114752
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
memory_target=422576128
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/sharon/adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=sharonXDB)"
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
rmanRestoreDatafiles.sql
set verify off;
set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
dbms_output.put_line(' ');
dbms_output.put_line('Allocating device.... ');
dbms_output.put_line('Specifying datafiles... ');
:devicename :=dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, '/u01/app/oracle/oradata/sharon/system01.dbf',0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2,'/u01/app/oracle/oradata/sharon/sysaux01.dbf', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(3,'/u01/app/oracle/oradata/sharon/undotbs01.dbf', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(4,'/u01/app/oracle/oradata/sharon/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/', done);
if done then
dbms_output.put_line('Restore done.');
else
dbms_output.put_line('ORA-XXXX: Restore failed ');
end if;
dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
4. cloneDBCreation.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/sharon/scripts/cloneDBCreation.log append
execdbms_backup_restore.zerodbid(0);
shutdownimmediate;
startupnomountpfile="/u01/app/oracle/admin/sharon/scripts/initsharonTemp.ora";
altersystem enable restricted session;
alterdatabase "sharon" open resetlogs;
execdbms_service.delete_service('seeddata');
execdbms_service.delete_service('seeddataXDB');
alterdatabase rename global_name to "sharon";
ALTERTABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/sharon/temp01.dbf' SIZE20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
selecttablespace_name from dba_tablespaces where tablespace_name='USERS';
altersystem disable restricted session;
connect "SYS"/"&&sysPassword" as SYSDBA
@/u01/app/oracle/product/11.2.0/db_1/demo/schema/mkplug.sql&&sysPassword change_on_install change_on_install change_on_installchange_on_install change_on_install change_on_install/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example.dmp /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example01.dfb/u01/app/oracle/oradata/sharon/example01.dbf/u01/app/oracle/admin/sharon/scripts/ "\'SYS/&&sysPassword asSYSDBA\'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup restrictpfile="/u01/app/oracle/admin/sharon/scripts/initsharonTemp.ora";
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter database national character set INTERNAL_CONVERT AL16UTF16;
alter user sys account unlock identified by"&&sysPassword";
alter user system account unlock identified by"&&systemPassword";
alter system disable restricted session;
initsharonTemp.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=sharon
###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/sharon/control01.ctl","/u01/app/oracle/flash_recovery_area/sharon/control02.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=4039114752
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
memory_target=422576128
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/sharon/adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=sharonXDB)"
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
_no_recovery_through_resetlogs=true
5. postScripts.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/sharon/scripts/postScripts.log append
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set current_schema=ORDSYS;
@/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"&&sysPassword" as SYSDBA
connect "SYS"/"&&sysPassword" as SYSDBA
execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
6. lockAccount.sql
SET VERIFY OFF
set echo on
spool /u01/app/oracle/admin/sharon/scripts/lockAccount.log append
BEGIN
FOR item IN ( SELECT USERNAME FROMDBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAMENOT IN (
'SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking andExpiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expireaccount lock' ;
END LOOP;
END;
/
spool off
7. postDBCreation.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/sharon/scripts/postDBCreation.log append
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfilesharon.ora'FROM pfile='/u01/app/oracle/admin/sharon/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
spool off
参考文档：http://blog.chinaunix.net/uid-23177306-id-2531023.html