通過oracle的指令碼研究其建庫過程

jane_pop發表於2014-08-15
Oracle指令碼
要想深入地瞭解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檔案，最後開啟資料庫。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29800581/viewspace-1252100/，如需轉載，請註明出處，否則將追究法律責任。

相關文章