通过oracle的脚本研究其建库过程
时间:2014-08-30 18:58 来源:linux.it.net.cn 作者:it
要想深入地了解oracle，研究其创建过程的脚本十分有用的。
下面我们一起来研究一下oracle数据库在创建过程中用到的脚本。
oracle11g建库脚本是存放在目录/u01/app/oracle/admin/scripts下的（不同版本脚本有所不同）。
有这么几个脚本：
[oracle@localhost scripts]$ ls
cloneDBCreation.sql lockAccount.sql postScripts.sql
CloneRmanRestore.sql orcl.sh rmanRestoreDatafiles.sql
init.ora orcl.sql
initorclTemp.ora postDBCreation.sql
下面说说不同脚本的作用：
orcl.sh---创建相应的目录，同时执行orcl.sql
orcl.sql---创建密码文件，同时执行cloneRmanRestore.sql,cloneDBCreation.sql,postScritps.sql,
postDBCreation.sql
cloneRmanRestore.sql---执行rmanRestoreDatafiles.sql
rmanRestoreDatafiles.sql---使用dbms_backup_restore从Send_Database.dbf中restore数据文件
cloneDBCreation.sql---创建数据库脚本
postScripts.sql---执行dbmssml.sql,ordlib.sql脚本
postDBCreation.sql---创建spfile文件，同时编译对象。
接下来我们逐一研究这些脚本。
1.orcl.sh
[oracle@localhost scripts]$ cat orcl.sh
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/admin/orcl/dpdump
mkdir -p /u01/app/oracle/admin/orcl/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/orcl
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/flash_recovery_area/orcl
mkdir -p /u01/app/oracle/oradata/orcl
mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs
umask ${OLD_UMASK}
ORACLE_SID=orcl; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/admin/orcl/scripts/orcl.sql
可以看到在这里创建了建库需要的各个目录，最后引用了orcl.sql脚本。
2.orcl.sql
[oracle@localhost scripts]$ cat orcl.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/dbhome_1/bin/orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl force=y
@/u01/app/oracle/admin/orcl/scripts/CloneRmanRestore.sql
@/u01/app/oracle/admin/orcl/scripts/cloneDBCreation.sql
@/u01/app/oracle/admin/orcl/scripts/postScripts.sql
@/u01/app/oracle/admin/orcl/scripts/lockAccount.sql
@/u01/app/oracle/admin/orcl/scripts/postDBCreation.sql
可以看到这里创建了密码文件orapworcl,然后依次运行如下的各个脚本。
3.CloneRmanRestore.sql
[oracle@localhost scripts]$ cat CloneRmanRestore.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl/scripts/CloneRmanRestore.log append
startup nomount pfile="/u01/app/oracle/admin/orcl/scripts/init.ora";
@/u01/app/oracle/admin/orcl/scripts/rmanRestoreDatafiles.sql;
spool off
这里存在一个init.ora的文件，这个文件是根据用户创建数据库之前
定义的参数自动生成的。在这个脚本中我们局可以看到它的引用了。在这里会引用rmanRestoreDatafiles.sql.
4.rmanRestoreDatafiles.sql
[oracle@localhost scripts]$ cat 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/orcl/system01.dbf', 0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle/oradata/orcl/sysaux01.dbf', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle/oradata/orcl/undotbs01.dbf', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle/oradata/orcl/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/dbhome_1/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;
5.cloneDBCreation.sql
[oracle@localhost scripts]$ cat cloneDBCreation.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl/scripts/cloneDBCreation.log append
Create controlfile reuse set database "orcl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/orcl/scripts/initorclTemp.ora";
Create controlfile reuse set database "orcl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "orcl" open resetlogs;
exec dbms_service.delete_service('seeddata');
exec dbms_service.delete_service('seeddataXDB');
alter database rename global_name to "orcl";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/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/dbhome_1/demo/schema/mkplug.sql &&sysPassword change_on_install change_on_install change_on_install change_on_install change_on_install change_on_install /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/example.dmp /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/example01.dfb /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/admin/orcl/scripts/ "\'SYS/&&sysPassword as SYSDBA\'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup restrict pfile="/u01/app/oracle/admin/orcl/scripts/initorclTemp.ora";
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT WE8MSWIN1252;
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;
这个脚本较为复杂，我们一点点来看。首先，根据指定的数据库名称orcl创建了一个控制文件，指定了一些参数以及数据文件和日志文件组及其成员。
然后通过 dbms_backup_restore包清空dbid等信息，zerodbid是包中的一个过程，用于清空数据文件头的部分信息，新的dbid在之后控制文件创建时可以被重新计算，对于数据库克隆，这是必须的。过程zerodbid有一个输入参数，即文件号：PROCEDURE zerodbid(fno IN library_integer);当fno为0时，控制文件中包含的所有数据文件头信息都会被清零。清零之后，数据库会重新启动，控制文件被重新创建，此时新的dbid被计算并最终写入所有数据文件。
注意此时数据库启用了一个临时参数文件initorclTemp.ora,我们来看看这个文件的内容：
[oracle@localhost scripts]$ cat initorclTemp.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Archive
###########################################
log_archive_format=%t_%s_%r.dbf
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=orcl
###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/orcl/control01.ctl", "/u01/app/oracle/flash_recovery_area/orcl/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=848297984
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/orcl/adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
_no_recovery_through_resetlogs=true
注意到这个文件的最后一行_no_recovery_through_resetlogs=true,这是一个隐含参数，这个参数用于限制恢复能否跨越resetlogs,这里有必要说一下resetlogs。
我们知道，对于数据库来说，resetlogs意味着不完全恢复，在数据库resetlogs之后打开，控制文件中很多信息会被改写，在oracle10g之前，如果用resetlogs打开数据库，那么将不能再通过控制文件进行resetlogs点之前的恢复，但是从oracle10g开始，即使我们用resetlogs方式打开了数据库，oracle仍然允许进行resetlogs点之前的恢复。在clone数据库时，oracle设置这个参数为TRUE，意味着不允许再次进行跨越resetlogs时间点的恢复。
接着，oracle以受限模式打开，添加临时文件，通过内部操作强制更改字符集，国家字符集。最后修改用户口令，禁用restricted session模式。至此，这个克隆过程完毕。
6.postScripts.sql
[oracle@localhost scripts]$ cat postScripts.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl/scripts/postScripts.log append
@/u01/app/oracle/product/11.2.0/dbhome_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/dbhome_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;
7.postDBCreation.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl/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;
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup mount pfile="/u01/app/oracle/admin/orcl/scripts/init.ora";
alter database archivelog;
alter database open;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' FROM pfile='/u01/app/oracle/admin/orcl/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
host /u01/app/oracle/product/11.2.0/dbhome_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME orcl -PORT 1521 -EM_HOME /u01/app/oracle/product/11.2.0/dbhome_1 -LISTENER LISTENER -SERVICE_NAME orcl -SID orcl -ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 -HOST localhost.localdomain -LISTENER_OH /u01/app/oracle/product/11.2.0/dbhome_1 -LOG_FILE /u01/app/oracle/admin/orcl/scripts/emConfig.log;
spool off
在这个脚本中，oracle将数据库设置为归档模式，从pfile文件中创建spfile文件，最后打开数据库。
(责任编辑：IT)
| 要想深入地了解oracle，研究其创建过程的脚本十分有用的。 下面我们一起来研究一下oracle数据库在创建过程中用到的脚本。 oracle11g建库脚本是存放在目录/u01/app/oracle/admin/scripts下的（不同版本脚本有所不同）。 有这么几个脚本： [oracle@localhost scripts]$ ls cloneDBCreation.sql lockAccount.sql postScripts.sql CloneRmanRestore.sql orcl.sh rmanRestoreDatafiles.sql init.ora orcl.sql initorclTemp.ora postDBCreation.sql orcl.sh---创建相应的目录，同时执行orcl.sql orcl.sql---创建密码文件，同时执行cloneRmanRestore.sql,cloneDBCreation.sql,postScritps.sql, postDBCreation.sql cloneRmanRestore.sql---执行rmanRestoreDatafiles.sql rmanRestoreDatafiles.sql---使用dbms_backup_restore从Send_Database.dbf中restore数据文件 cloneDBCreation.sql---创建数据库脚本 postScripts.sql---执行dbmssml.sql,ordlib.sql脚本 postDBCreation.sql---创建spfile文件，同时编译对象。 接下来我们逐一研究这些脚本。 1.orcl.sh [oracle@localhost scripts]$ cat orcl.sh #!/bin/sh OLD_UMASK=`umask` umask 0027 mkdir -p /u01/app/oracle/admin/orcl/adump mkdir -p /u01/app/oracle/admin/orcl/dpdump mkdir -p /u01/app/oracle/admin/orcl/pfile mkdir -p /u01/app/oracle/cfgtoollogs/dbca/orcl mkdir -p /u01/app/oracle/flash_recovery_area mkdir -p /u01/app/oracle/flash_recovery_area/orcl mkdir -p /u01/app/oracle/oradata/orcl mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs umask ${OLD_UMASK} ORACLE_SID=orcl; export ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH; export PATH echo You should Add this entry in the /etc/oratab: orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y /u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/admin/orcl/scripts/orcl.sql 可以看到在这里创建了建库需要的各个目录，最后引用了orcl.sql脚本。 2.orcl.sql [oracle@localhost scripts]$ cat orcl.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/dbhome_1/bin/orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl force=y @/u01/app/oracle/admin/orcl/scripts/CloneRmanRestore.sql @/u01/app/oracle/admin/orcl/scripts/cloneDBCreation.sql @/u01/app/oracle/admin/orcl/scripts/postScripts.sql @/u01/app/oracle/admin/orcl/scripts/lockAccount.sql @/u01/app/oracle/admin/orcl/scripts/postDBCreation.sql
可以看到这里创建了密码文件orapworcl,然后依次运行如下的各个脚本。
定义的参数自动生成的。在这个脚本中我们局可以看到它的引用了。在这里会引用rmanRestoreDatafiles.sql.3.CloneRmanRestore.sql [oracle@localhost scripts]$ cat CloneRmanRestore.sql SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/orcl/scripts/CloneRmanRestore.log append startup nomount pfile="/u01/app/oracle/admin/orcl/scripts/init.ora"; @/u01/app/oracle/admin/orcl/scripts/rmanRestoreDatafiles.sql; spool off
这里存在一个init.ora的文件，这个文件是根据用户创建数据库之前
4.rmanRestoreDatafiles.sql [oracle@localhost scripts]$ cat 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/orcl/system01.dbf', 0, 'SYSTEM'); dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle/oradata/orcl/sysaux01.dbf', 0, 'SYSAUX'); dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle/oradata/orcl/undotbs01.dbf', 0, 'UNDOTBS1'); dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle/oradata/orcl/users01.dbf', 0, 'USERS'); dbms_output.put_line(' Restoring ... '); dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/dbhome_1/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; 5.cloneDBCreation.sql [oracle@localhost scripts]$ cat cloneDBCreation.sql SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/orcl/scripts/cloneDBCreation.log append Create controlfile reuse set database "orcl" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 Datafile '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf' LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 51200K, GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 51200K, GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 51200K RESETLOGS; exec dbms_backup_restore.zerodbid(0); shutdown immediate; startup nomount pfile="/u01/app/oracle/admin/orcl/scripts/initorclTemp.ora"; Create controlfile reuse set database "orcl" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 Datafile '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf' LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 51200K, GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 51200K, GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 51200K RESETLOGS; alter system enable restricted session; alter database "orcl" open resetlogs; exec dbms_service.delete_service('seeddata'); exec dbms_service.delete_service('seeddataXDB'); alter database rename global_name to "orcl"; ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/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/dbhome_1/demo/schema/mkplug.sql &&sysPassword change_on_install change_on_install change_on_install change_on_install change_on_install change_on_install /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/example.dmp /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/example01.dfb /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/admin/orcl/scripts/ "\'SYS/&&sysPassword as SYSDBA\'"; connect "SYS"/"&&sysPassword" as SYSDBA shutdown immediate; connect "SYS"/"&&sysPassword" as SYSDBA startup restrict pfile="/u01/app/oracle/admin/orcl/scripts/initorclTemp.ora"; select sid, program, serial#, username from v$session; alter database character set INTERNAL_CONVERT WE8MSWIN1252; 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;
这个脚本较为复杂，我们一点点来看。首先，根据指定的数据库名称orcl创建了一个控制文件，指定了一些参数以及数据文件和日志文件组及其成员。
然后通过 dbms_backup_restore包清空dbid等信息，zerodbid是包中的一个过程，用于清空数据文件头的部分信息，新的dbid在之后控制文件创建时可以被重新计算，对于数据库克隆，这是必须的。过程zerodbid有一个输入参数，即文件号：PROCEDURE zerodbid(fno IN library_integer);当fno为0时，控制文件中包含的所有数据文件头信息都会被清零。清零之后，数据库会重新启动，控制文件被重新创建，此时新的dbid被计算并最终写入所有数据文件。 注意此时数据库启用了一个临时参数文件initorclTemp.ora,我们来看看这个文件的内容： [oracle@localhost scripts]$ cat initorclTemp.ora ############################################################################## # Copyright (c) 1991, 2001, 2002 by Oracle Corporation ############################################################################## ########################################### # Archive ########################################### log_archive_format=%t_%s_%r.dbf ########################################### # Cache and I/O ########################################### db_block_size=8192 ########################################### # Cursors and Library Cache ########################################### open_cursors=300 ########################################### # Database Identification ########################################### db_domain="" db_name=orcl ########################################### # File Configuration ########################################### control_files=("/u01/app/oracle/oradata/orcl/control01.ctl", "/u01/app/oracle/flash_recovery_area/orcl/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=848297984 ########################################### # Processes and Sessions ########################################### processes=150 ########################################### # Security and Auditing ########################################### audit_file_dest=/u01/app/oracle/admin/orcl/adump audit_trail=db remote_login_passwordfile=EXCLUSIVE ########################################### # Shared Server ########################################### dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)" ########################################### # System Managed Undo and Rollback Segments ########################################### undo_tablespace=UNDOTBS1 _no_recovery_through_resetlogs=true 注意到这个文件的最后一行_no_recovery_through_resetlogs=true,这是一个隐含参数，这个参数用于限制恢复能否跨越resetlogs,这里有必要说一下resetlogs。 我们知道，对于数据库来说，resetlogs意味着不完全恢复，在数据库resetlogs之后打开，控制文件中很多信息会被改写，在oracle10g之前，如果用resetlogs打开数据库，那么将不能再通过控制文件进行resetlogs点之前的恢复，但是从oracle10g开始，即使我们用resetlogs方式打开了数据库，oracle仍然允许进行resetlogs点之前的恢复。在clone数据库时，oracle设置这个参数为TRUE，意味着不允许再次进行跨越resetlogs时间点的恢复。 接着，oracle以受限模式打开，添加临时文件，通过内部操作强制更改字符集，国家字符集。最后修改用户口令，禁用restricted session模式。至此，这个克隆过程完毕。 [oracle@localhost scripts]$ cat postScripts.sql SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/orcl/scripts/postScripts.log append @/u01/app/oracle/product/11.2.0/dbhome_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/dbhome_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; SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/orcl/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; shutdown immediate; connect "SYS"/"&&sysPassword" as SYSDBA startup mount pfile="/u01/app/oracle/admin/orcl/scripts/init.ora"; alter database archivelog; alter database open; connect "SYS"/"&&sysPassword" as SYSDBA set echo on create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' FROM pfile='/u01/app/oracle/admin/orcl/scripts/init.ora'; shutdown immediate; connect "SYS"/"&&sysPassword" as SYSDBA startup ; host /u01/app/oracle/product/11.2.0/dbhome_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME orcl -PORT 1521 -EM_HOME /u01/app/oracle/product/11.2.0/dbhome_1 -LISTENER LISTENER -SERVICE_NAME orcl -SID orcl -ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 -HOST localhost.localdomain -LISTENER_OH /u01/app/oracle/product/11.2.0/dbhome_1 -LOG_FILE /u01/app/oracle/admin/orcl/scripts/emConfig.log; spool off
在这个脚本中，oracle将数据库设置为归档模式，从pfile文件中创建spfile文件，最后打开数据库。
(责任编辑：IT) |