详解dbca建库生成的脚本的运行内容

我们在使用dbca使用general purpose创建数据库的时候，会创建一个scripts的文件夹，文件夹中包含了如下的一堆脚本：
cloneDBCreation.sql
initocpTemp.ora  
lockAccount.sql  
ocp.sql             
postScripts.sql
CloneRmanRestore.sql  
init.ora         
ocp.sh           
postDBCreation.sql  
rmanRestoreDatafiles.sql
今天我们的目的就是研究下这些脚本就是做了哪些事情，或者说，Oracle是如何使用这堆脚本来创建数据库的
这对脚本中，首先执行的是：

1、ocp.sh
[code lang="shell"]
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/ocp/adump
mkdir -p /u01/app/oracle/admin/ocp/dpdump
mkdir -p /u01/app/oracle/admin/ocp/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/ocp
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/fast_recovery_area/ocp
mkdir -p /u01/app/oracle/product/11.2.0/db/dbs
mkdir -p /u01/oradata/ocp
umask ${OLD_UMASK}
ORACLE_SID=ocp; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: ocp:/u01/app/oracle/product/11.2.0/db:Y
/u01/app/oracle/product/11.2.0/db/bin/sqlplus /nolog @/home/oracle/scripts/ocp.sql
[/code]

 （1）Oracle首先会创建与数据库相关的所有文件夹 
 （2）设定Oracle的环境变量，SID，PATH
 （3）提示我们需要在/etc/oratab中添加
  ocp:/u01/app/oracle/product/11.2.0/db:Y
 这步操作的作用是：只有在/etc/oratab中有记录的数据库，后期才能够被dbca这个oui工作所管理。简单来讲，如果你添加这段话，那么后期我们可以通过dbca来删除我们的数据库。
 （4）调用/home/oracle/scripts/ocp.sql脚本

2、ocp.sql脚本
[code lang="sql"]
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT sysmanPassword CHAR PROMPT 'Enter new password for SYSMAN: ' HIDE
ACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDE
host /u01/app/oracle/product/11.2.0/db/bin/orapwd file=/u01/app/oracle/product/11.2.0/db/dbs/orapwocp force=y
@/home/oracle/scripts/CloneRmanRestore.sql
@/home/oracle/scripts/cloneDBCreation.sql
[/code]
 （1）设定Oracle的4个特殊用户的密码：sys，system，sysman，dbsnmp
 （2）创建密码文件
 （3）调用CloneRmanRestore.sql脚本
 （4）调用cloneDBCreation.sql

3、CloneRmanRestore.sql
[code lang="sql"]
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/scripts/CloneRmanRestore.log append
startup nomount pfile="/home/oracle/scripts/init.ora";
@/home/oracle/scripts/rmanRestoreDatafiles.sql;
spool off
[/code]
 （1）通过调用init.ora参数文件，将数据库启动nomount状态
 （2）调用rmanRestoreDatafiles.sql脚本

4、rmanRestoreDatafiles.sql
[code lang="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/oradata/ocp/system01.dbf', 0, 'SYSTEM');
      dbms_backup_restore.restoreDataFileTo(2, '/u01/oradata/ocp/sysaux01.dbf', 0, 'SYSAUX');
      dbms_backup_restore.restoreDataFileTo(3, '/u01/oradata/ocp/undotbs01.dbf', 0, 'UNDOTBS1');
      dbms_backup_restore.restoreDataFileTo(4, '/u01/oradata/ocp/users01.dbf', 0, 'USERS');
    dbms_output.put_line(' Restoring ... ');
    dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/db/assistants/dbca/templates/Seed_Database.dfb', 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; 
[/code]
 （1）这个脚本就只有一个作用，通过读取/u01/app/oracle/product/11.2.0/db/assistants/dbca/templates/Seed_Database.dfb这个备份片，使用rman恢复的方式来创建数据库
 （2）需要注意！restore出来的数据库，此时处于mount的状态

5、cloneDBCreation.sql
[code lang="sql"]
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/scripts/cloneDBCreation.log append
Create controlfile reuse set database "ocp"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile 
'/u01/oradata/ocp/system01.dbf',
'/u01/oradata/ocp/sysaux01.dbf',
'/u01/oradata/ocp/undotbs01.dbf',
'/u01/oradata/ocp/users01.dbf'
LOGFILE GROUP 1 ('/u01/oradata/ocp/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/oradata/ocp/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/oradata/ocp/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/home/oracle/scripts/initocpTemp.ora";
Create controlfile reuse set database "ocp"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile 
'/u01/oradata/ocp/system01.dbf',
'/u01/oradata/ocp/sysaux01.dbf',
'/u01/oradata/ocp/undotbs01.dbf',
'/u01/oradata/ocp/users01.dbf'
LOGFILE GROUP 1 ('/u01/oradata/ocp/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/oradata/ocp/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/oradata/ocp/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "ocp" open resetlogs;
exec dbms_service.delete_service('seeddata');
exec dbms_service.delete_service('seeddataXDB');
alter database rename global_name to "ocp";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ocp/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
alter system disable restricted session;
connect "SYS"/"&&sysPassword" as SYSDBA
@/u01/app/oracle/product/11.2.0/db/demo/schema/mkplug.sql &&sysPassword change_on_install change_on_install change_on_install change_on_install change_on_install change_on_install example.dmp example01.dfb /u01/oradata/ocp/example01.dbf /home/oracle/scripts/ /u01/app/oracle/product/11.2.0/db/assistants/dbca/templates/ "\'SYS/&&sysPassword as SYSDBA\'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup restrict pfile="/home/oracle/scripts/initocpTemp.ora";
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter database national character set INTERNAL_CONVERT UTF8;
alter user sys account unlock identified by "&&sysPassword";
alter user system account unlock identified by "&&systemPassword";
alter system disable restricted session;
[/code]
 （1）通过Create controlfile语句重新创建控制文件
 （2）通过dbms_backup_restore.zerodbid(0)重新设定dbid
 （3）通过initocpTemp.ora参数文件重启数据库到mount状态
 （4）通过Create controlfile语句重新创建控制文件，并以open resetlogs的方式打开数据库
 （5）删除seeddata，seeddataXDB两个service
 （6）设定数据的global_name
 （7）添加temp表空间
 （8）通过example01.dbf导入sample schema
 （9）根据initocpTemp.ora重启数据库
 （10）修改数据库的字符集与国家字符集
 （11）解锁sys，system用户并重新设定其密码

转自http://blog.csdn.net/thunderstorm_/article/details/69941789