从建库脚本中看到oracle实例的创建过程

系统：redhat 5.8
数据库版本：11.2.0.4

 

建库脚本目录在

$ORACLE_BASE/admin/<SID>/scripts/

 

执行orcl.sh

#!/bin/sh   OLD_UMASK=`umask` umask 0027 mkdir -p /oracle/admin/orcl/adump mkdir -p /oracle/admin/orcl/dpdump mkdir -p /oracle/admin/orcl/pfile mkdir -p /oracle/cfgtoollogs/dbca/orcl mkdir -p /oracle/db_1/dbs mkdir -p /oracle/oradata/orcl 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:/oracle/db_1:Y #这一行要手动写到/etc/oratab中 /oracle/db_1/bin/sqlplus /nolog @/oracle/admin/orcl/scripts/orcl.sql

 

这个脚本创建了必要目录并且执行了一个sql脚本：orcl.sql

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 /oracle/db_1/bin/orapwd file=/oracle/db_1/dbs/orapworcl force=y @/oracle/admin/orcl/scripts/CloneRmanRestore.sql @/oracle/admin/orcl/scripts/cloneDBCreation.sql @/oracle/admin/orcl/scripts/postScripts.sql @/oracle/admin/orcl/scripts/lockAccount.sql @/oracle/admin/orcl/scripts/postDBCreation.sql

 

根据这个脚本看到，首先设置几个重要用户的密码，然后创建实例的密码文件orapwSID。

紧接着 执行了五个个脚本：1.CloneRmanRestore.sql，2.cloneDBCreation.sql，3.postScripts.sql

4. lockAccount.sql，5. postDBCreation.sql

 

现在来看看这几个脚本都做了什么：

1.     CloneRmanRestore.sql

SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/orcl/scripts/CloneRmanRestore.log append startup nomount pfile="/oracle/admin/orcl/scripts/init.ora"; @/oracle/admin/orcl/scripts/rmanRestoreDatafiles.sql; spool off

可以看到这个脚本使用默认参数的init.ora将数据库启动到nomount状态，并运行脚本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, '/oracle/oradata/orcl/system01.dbf', 0, 'SYSTEM');       dbms_backup_restore.restoreDataFileTo(2, '/oracle/oradata/orcl/sysaux01.dbf', 0, 'SYSAUX');       dbms_backup_restore.restoreDataFileTo(3, '/oracle/oradata/orcl/undotbs01.dbf', 0, 'UNDOTBS1');       dbms_backup_restore.restoreDataFileTo(4, '/oracle/oradata/orcl/users01.dbf', 0, 'USERS');     dbms_output.put_line(' Restoring ... ');     dbms_backup_restore.restoreBackupPiece('/oracle/db_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;

 

这个脚本使用dbms_backup_restore包，从种子数据库取出数据文件，用还原的方式来创建数据文件

 

 

2.     cloneDBCreation.sql

SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/orcl/scripts/cloneDBCreation.log append Create controlfile reuse set database "orcl" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 Datafile '/oracle/oradata/orcl/system01.dbf', '/oracle/oradata/orcl/sysaux01.dbf', '/oracle/oradata/orcl/undotbs01.dbf', '/oracle/oradata/orcl/users01.dbf' LOGFILE GROUP 1 ('/oracle/oradata/orcl/redo01.log') SIZE 51200K, GROUP 2 ('/oracle/oradata/orcl/redo02.log') SIZE 51200K, GROUP 3 ('/oracle/oradata/orcl/redo03.log') SIZE 51200K RESETLOGS; exec dbms_backup_restore.zerodbid(0); shutdown immediate; startup nomount pfile="/oracle/admin/orcl/scripts/initorclTemp.ora"; Create controlfile reuse set database "orcl" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 Datafile '/oracle/oradata/orcl/system01.dbf', '/oracle/oradata/orcl/sysaux01.dbf', '/oracle/oradata/orcl/undotbs01.dbf', '/oracle/oradata/orcl/users01.dbf' LOGFILE GROUP 1 ('/oracle/oradata/orcl/redo01.log') SIZE 51200K, GROUP 2 ('/oracle/oradata/orcl/redo02.log') SIZE 51200K, GROUP 3 ('/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 '/oracle/oradata/orcl/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; select tablespace_name from dba_tablespaces where tablespace_name='USERS'; select sid, program, serial#, username from v$session; alter database character set INTERNAL_CONVERT ZHS16GBK; 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

initorclTemp.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="orcl"   ########################################### # File Configuration ########################################### control_files=("/oracle/oradata/orcl/control01.ctl", "/oracle/oradata/orcl/control02.ctl")   ########################################### # Miscellaneous ########################################### compatible=11.2.0.4.0 diagnostic_dest=/oracle memory_target=1244659712   ########################################### # Processes and Sessions ########################################### processes=1500 sessions=1655   ########################################### # Security and Auditing ########################################### audit_file_dest="/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 _enable_automatic_maintenance=0 _diag_hm_rc_enabled=false   #关闭health monitor

除了最后的那行_no_recovery_through_resetlogs=true其余都是常见参数，这个参数的作用是限制恢复能否跨越resetlogs。在clone数据库时，设置这个参数为TRUE，意味着不允许再次进行跨越resetlogs时间点的恢复。

 

接着，oracle以受限模式打开，添加临时文件，通过内部操作强制更改字符集，国家字符集。最后修改用户口令，禁用restricted session模式。至此，这个克隆过程完毕。

 

 

 

3.     postScripts.sql

SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/orcl/scripts/postScripts.log append @/oracle/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;  # ORDSYS为Oracle interMedia 管理员账户 @/oracle/db_1/ord/im/admin/ordlib.sql; alter session set current_schema=SYS; create or replace directory XMLDIR as '/oracle/db_1/rdbms/xml'; connect "SYS"/"&&sysPassword" as SYSDBA connect "SYS"/"&&sysPassword" as SYSDBA execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;

 

其中dbmssml.sql

CREATE OR REPLACE LIBRARY dbms_sumadv_lib AS '/oracle/db_1/lib/libqsmashr.so'; /

ordlib.sql

create or replace library ORDIMLIBS as '/oracle/db_1/lib/libordim11.so'; / create or replace library ORDIMLIBT trusted as static; /

 

 

4.     lockAccount.sql

 

SET VERIFY OFF set echo on spool /oracle/admin/orcl/scripts/lockAccount.log append BEGIN  FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ( 'SYS','SYSTEM') )  LOOP   dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);   execute immediate 'alter user ' ||          sys.dbms_assert.enquote_name(          sys.dbms_assert.schema_name(          item.USERNAME),false) || ' password expire account lock' ;  END LOOP; END; / spool off

 

 

5.     postDBCreation.sql

SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/orcl/scripts/postDBCreation.log append @/oracle/db_1/rdbms/admin/catbundleapply.sql; 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='/oracle/db_1/dbs/spfileorcl.ora' FROM pfile='/oracle/admin/orcl/scripts/init.ora'; shutdown immediate; connect "SYS"/"&&sysPassword" as SYSDBA startup ; host /oracle/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME orcl -PORT 1521 -EM_HOME /oracle/db_1 -LISTENER LISTENER -SERVICE_NAME orcl -SID orcl -ORACLE_HOME /oracle/db_1 -HOST testdb -LISTENER_OH /oracle/db_1 -LOG_FILE /oracle/admin/orcl/scripts/emConfig.log; spool off

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28719622/viewspace-1853426/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28719622/viewspace-1853426/