Oracle 11g物理備用數(shù)據(jù)庫“實況克隆”詳解
原創(chuàng)【51CTO獨家特稿】相比Oracle 8i和Oracle 9i，Oracle 11g在數(shù)據(jù)庫備份方面做出了極大的改善，特別是作為Oracle最大可用性架構(gòu)（MAA）一部分的真正應用集群（RAC）特性。Oracle 11g現(xiàn)在創(chuàng)建一個備用數(shù)據(jù)庫變得更加簡單了，因為恢復管理器（RMAN）支持直接從主數(shù)據(jù)庫使用DUPLICATE DATABASE命令集通過網(wǎng)絡克隆一個備用數(shù)據(jù)庫，只要目標數(shù)據(jù)庫是活動的即可。這意味著再也不用先生成，再傳輸，最后在備用數(shù)據(jù)庫上通過復雜的手工方式還原和恢復主數(shù)據(jù)庫的RMAN備份集了，相反，RMAN在主站點上自動生成一個轉(zhuǎn)換腳本在內(nèi)存中，然后在備用站點上使用這個腳本管理克隆操作，實際上不用DBA進行任何干預。
下文將集中精力講解備用數(shù)據(jù)庫“實況克隆”特性。筆者的硬件基本情況是：雙核AMD Athlon 64位CPU（Winchester 420），4GB內(nèi)存，主機運行的是Windows xp系統(tǒng)，運行VMWare Server 1.0.8訪問訪問虛擬數(shù)據(jù)庫服務器環(huán)境，每個虛擬機使用1個CPU，1200M內(nèi)存，我選擇Oracle Enterprise Linux (OEL) 4.5.1（Linux內(nèi)核版本2.6.9-55.0.0.0.2.ELsmp）作為虛擬機客戶端操作系統(tǒng)。
每個VMWare虛擬機配置好后，在每個虛擬機的/etc/hosts文件中添加合適的條目，讓主站點（training）和備用站點（11gStdby）之間建立起網(wǎng)絡連接，然后在每個節(jié)點上都安裝Oracle 11g數(shù)據(jù)庫，最后，在主站點上創(chuàng)建好標準的11g R1種子數(shù)據(jù)庫，包括標準的示例方案。這個數(shù)據(jù)庫的ORACLE_SID是orcl，接下來就可以開始執(zhí)行實況克隆操作了。
克隆前準備工作：調(diào)整主數(shù)據(jù)庫
在克隆主數(shù)據(jù)庫到對應的備用環(huán)境中之前，我需要對主數(shù)據(jù)庫做一些調(diào)整，下面的步驟未做特別說明沒有先后順序，只要在發(fā)出DUPLICATE DATABASE命令前這些步驟都執(zhí)行完了即可，在克隆操作過程中應該沒有什么讓人意外的東西出現(xiàn)。
強制記錄所有的交易
大多數(shù)組織實施數(shù)據(jù)衛(wèi)士配置的主要原因是保證所有交易都不丟失，但遺憾的是，默認情況下，Oracle數(shù)據(jù)庫是運行在NOFORCE LOGGING模式下的，這意味著對對象的改變可能丟失，因為他們的存儲屬性被設為NOLOGGING，為了確保所有的改變都被記錄下來，我將執(zhí)行ALTER DATABASE FORCE LOGGING命令，這個命令需要在執(zhí)行ALTER DATABASE ARCHIVELOG命令將數(shù)據(jù)庫ARCHIVELOG模式前執(zhí)行，這些命令如清單1所示。
清單1 將主數(shù)據(jù)庫切換到ARCHIVELOG模式
--為歸檔日志文件設置一個合適的格式
ALTER SYSTEM SET log_archive_format = 'log_%s_%t_%r.arc' SCOPE=SPFILE;
--設置新的DB_UNIQUE_NAME參數(shù)，它不能動態(tài)修改
ALTER SYSTEM SET db_unique_name = 'orcl' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
創(chuàng)建備用重做日志組
自從Oracle 9i R2開始支持備用重做日志（standby redo log 即SRL）組開始，Oracle就建議配置它，SRL對于實時應用（Real Time Apply）特性是需要的，或DBA想要實現(xiàn)重做日志串聯(lián)目的時也需要，除此之外，它任然是備用數(shù)據(jù)庫配置選項。Oracle 11g另一個優(yōu)點是如果SRL在主數(shù)據(jù)庫上已經(jīng)配置好，那么DUPLICATE DATABASE命令將會在備用數(shù)據(jù)庫上自動創(chuàng)建它們。清單2顯示了我在主數(shù)據(jù)庫上創(chuàng)建SRL的命令，注意我也使用了多個重SRL文件保護整個SRL組，避免數(shù)據(jù)丟失，這一點和在線重做日志組類似。
清單2 在主數(shù)據(jù)庫上創(chuàng)建備用重做日志文件
ALTER DATABASE
ADD STANDBY LOGFILE
'/u01/app/oracle/oradata/orcl/srl01.log'
SIZE 50M
REUSE;
ALTER DATABASE
ADD STANDBY LOGFILE
'/u01/app/oracle/oradata/orcl/srl02.log'
SIZE 50M
REUSE;
ALTER DATABASE
ADD STANDBY LOGFILE
'/u01/app/oracle/oradata/orcl/srl03.log'
SIZE 50M
REUSE;
文件名轉(zhuǎn)換
一般情況下，備用數(shù)據(jù)庫都是創(chuàng)建在與主數(shù)據(jù)庫不同的主機上的，否則，在災難中主備數(shù)據(jù)庫都有可能受到危害，最佳做法是將對應的備用數(shù)據(jù)庫的目錄和文件名都弄成一樣，但如果遇到掛載點不一樣時，目錄名需要修改，這個時候就需要使用DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT初始化參數(shù)進行轉(zhuǎn)換了。
修改主站點初始化參數(shù)
在主數(shù)據(jù)庫上設置下列初始化參數(shù)確保DUPLICATE DATABASE命令能夠一樣配置備用數(shù)據(jù)庫，我在清單3中詳細列出了這些初始化參數(shù)設置：
（1）DB_UNIQUE_NAME
我通過這個參數(shù)為主數(shù)據(jù)庫定義一個唯一的實例名，這個參數(shù)值使得區(qū)分“原始”主數(shù)據(jù)庫和備用數(shù)據(jù)庫變得更加簡單，因為這是一個靜態(tài)參數(shù)，我在清單1中已經(jīng)將其設置為SCOPE=SPFILE，它將在主數(shù)據(jù)庫實例啟動時生效。
（2）LOG_ARCHIVE_CONFIG
這個參數(shù)控制主或備用數(shù)據(jù)庫是否應該接受和/或發(fā)送來自遠程源的歸檔重做日志，它允許我們包含所有主備數(shù)據(jù)庫，因為它在配置中列出了所有數(shù)據(jù)庫的DB_UNIQUE_NAME值，我將其設置為目前我的數(shù)據(jù)衛(wèi)士數(shù)據(jù)庫orcl和stdby。
（3）STANDBY_FILE_MANAGEMENT
我將這個參數(shù)設置問為auto了，這樣主數(shù)據(jù)庫上發(fā)生什么操作，備用數(shù)據(jù)庫上就會跟著發(fā)生什么操作，如主數(shù)據(jù)庫上創(chuàng)建一個文件，備用數(shù)據(jù)庫上也將創(chuàng)建一個相同的文件，刪除主數(shù)據(jù)庫上一個已有文件，備用數(shù)據(jù)庫上也做對應的刪除。如新增一個在線重做日志文件組或刪除一個表空間。
（4）LOG_ARCHIVE_DEST_n
這個控制是從主數(shù)據(jù)庫上傳輸歸檔重做日志到物理備用數(shù)據(jù)庫的關(guān)鍵參數(shù)，我將設置兩個歸檔目標：
1. 目標LOG_ARCHIVE_DEST_1指定主數(shù)據(jù)庫歸檔重做日志的物理位置，注意我們使用了閃回恢復區(qū)作為目標。
2. 目標LOG_ARCHIVE_DEST_2指定了對應的備用數(shù)據(jù)庫實例（stdby）的網(wǎng)絡服務地址，這個參數(shù)確保歸檔重做日志自動傳輸?shù)絺溆谜军c。
對于這個歸檔重做日志傳輸參數(shù)我還可以指定另外兩個指令：
◆當數(shù)據(jù)庫是以特定角色激活時，指令VALID_FOR大大簡化了重做日志傳輸時的類型，當主備數(shù)據(jù)庫角色不同時，在傳輸重做日志時這是最關(guān)鍵的參數(shù)，表1列出了這個參數(shù)允許的值。
|
表1 VALID_FOR指令值 | |
|
設置 |
含義 |
|
ALL_LOGFILES |
（默認）目標使用在線或備用重做日志文件 |
|
ONLINE_LOGFILE |
目標僅適用于在線歸檔重做日志文件 |
|
STANDBY_LOGFILE |
目標僅適用于備用重做日志文件 |
|
ALL_ROLES |
當數(shù)據(jù)庫以主或備用角色運行時（默認）目標都是有效的 |
|
PRIMARY_ROLE |
當數(shù)據(jù)庫以主角色運行時目標是有效的 |
|
STANDBY_ROLE |
當數(shù)據(jù)庫以備用角色運行時目標是有效的 |
◆也可以設置合適的重做日志傳輸模式（redo transport mode）值指定歸檔重做日志從主數(shù)據(jù)庫傳輸?shù)絺溆脭?shù)據(jù)庫，表2列出了這個指令允許的值。
表2 重做日志傳輸模式 | |
|
設置 |
含義 |
|
ASYNC |
在事務提交前可能不是所有的目標都接受了傳輸?shù)闹刈鋈罩荆J值） |
|
SYNC |
在事務提交前所有目標必須接受傳輸?shù)闹刈鋈罩?/P> |
|
AFFIRM |
僅當重做數(shù)據(jù)被寫入到備用重做日志后，目標才確認已收到，含有SYNC含義 |
|
NOAFFIRM |
當重做數(shù)據(jù)寫入到備用重做日志前目標就可以確認收到，含有ASYNC含義 |
網(wǎng)絡配置修改
最后，我需要確保主數(shù)據(jù)庫和備用數(shù)據(jù)庫之間能夠通過網(wǎng)絡通信，唯一需要變化的就是在主數(shù)據(jù)庫本地命名配置文件（TNSNAMES.ORA）中加上備用數(shù)據(jù)庫的實例，備用數(shù)據(jù)庫的LISTENER.ORA配置文件也需要一個備用數(shù)據(jù)庫實例的靜態(tài)監(jiān)聽器，這些變化如清單4所示。
清單3 在主數(shù)據(jù)庫上設置合適的初始化參數(shù)值
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/ORCL/ DB_UNIQUE_NAME=orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';
ALTER SYSTEM SET log_archive_dest_state_1 = 'ENABLE';
ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=stdby ASYNC DB_UNIQUE_NAME=stdby VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
ALTER SYSTEM SET log_archive_dest_state_2 = 'ENABLE';
ALTER SYSTEM SET standby_file_management = 'AUTO';
ALTER SYSTEM SET log_archive_config = 'DG_CONFIG=(orcl,stdby)';
清單4 網(wǎng)絡配置文件修改
#在主數(shù)據(jù)庫實例上添加一個備用數(shù)據(jù)庫條目
STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gStdby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby)
)
)
#使用備用數(shù)據(jù)庫實例的靜態(tài)引用設置備用數(shù)據(jù)庫監(jiān)聽器
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = stdby)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gStdby)(PORT = 1521))
準備克?。簻蕚鋫溆谜军c
現(xiàn)在主站點已經(jīng)準備好可以克隆了，在對應的備用站點上也需要做一些對應的調(diào)整：
創(chuàng)建必要的目錄
需要為數(shù)據(jù)庫控制文件、數(shù)據(jù)文件、在線重做日志文件和備用重做日志文件創(chuàng)建必要的目錄，我還為數(shù)據(jù)庫的審核跟蹤創(chuàng)建了合適的目錄。
設置密碼文件
因為主數(shù)據(jù)庫要和備用數(shù)據(jù)庫進行通信時需要使用遠程認證，我將會使用orapwd工具創(chuàng)建一個新的密碼文件，確保SYS的密碼和主數(shù)據(jù)庫匹配（注意我可能會直接從主數(shù)據(jù)庫拷貝到備用數(shù)據(jù)庫）。
創(chuàng)建備用初始化參數(shù)文件
最后，我需要創(chuàng)建一個初始化參數(shù)文件（PFILE），僅允許我啟動備用數(shù)據(jù)庫實例，它只需要一個參數(shù)：DB_NAME。當DUPLICATE DATABASE命令腳本執(zhí)行完畢后，它將會創(chuàng)建一個服務端參數(shù)文件（SPFILE），它僅包括合適的初始化參數(shù)設置。
在清單5中我解釋了這些命令和臨時備用數(shù)據(jù)庫初始化參數(shù)，為了開啟DUPLICATE DATABASE克隆操作，我將啟動備用站點的監(jiān)聽器，然后使用前面創(chuàng)建的PFILE初始化參數(shù)文件將備用數(shù)據(jù)庫實例啟動到NOMOUNT狀態(tài)。
$> export ORACLE_SID=stdby
$> sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/init_stdby.ora';
清單5 備用數(shù)據(jù)庫臨時初始化參數(shù)文件
######
# File: initstdby.ora
# Purpose: "Dummy" PFILE to enable startup of standby database
# instance during DUPLICATE DATABASE over the network
#####
DB_NAME=stdby
#p#
通過DUPLICATE DATABASE克隆備用數(shù)據(jù)庫
從主數(shù)據(jù)庫的RMAN會話環(huán)境啟動DUPLICATE DATABASE命令，前面我已經(jīng)提到過，Oracle 11g中DUPLICATE DATABASE命令最引人注目的改變是它可以通過網(wǎng)絡直接將主數(shù)據(jù)庫克隆到備用數(shù)據(jù)庫站點。作為設置備用數(shù)據(jù)庫的一部分，我也可以為所有需要的初始化參數(shù)指定值，DUPLICATE DATABASE將會在備用數(shù)據(jù)庫上創(chuàng)建一個新的SPFILE。
清單6顯示了使用DUPLICATE DATABASE命令進行克隆的完整語句，注意我添加了下面這樣一些額外的參數(shù)，可能和主數(shù)據(jù)庫的參數(shù)稍有不同：
（1）DB_UNIQUE_NAME
我將這個參數(shù)的值設為stdby了。
（2）CONTROL_FILES
我只為備用數(shù)據(jù)庫創(chuàng)建了一個控制文件，在克隆完畢后我會復制多個。
（3）FAL_CLIENT和FAL_SERVER
這兩個參數(shù)確定哪個數(shù)據(jù)庫服務分別擔任FAL（fetch archive log）客戶端和服務器，例如，無論何時，當主數(shù)據(jù)庫和備用數(shù)據(jù)庫之間的網(wǎng)絡斷掉后，或如果備用數(shù)據(jù)庫已經(jīng)關(guān)閉相當長一段時間，歸檔重做日志可能就不會傳輸?shù)絺溆梅掌魃?。這種情況叫做歸檔日志空白（archive log gap），這兩個FAL服務名確定了由哪個服務器（FAL_SERVER）維護所有歸檔重做日志組主列表，由它為FAL_CLIENT提供可能發(fā)生的歸檔日志空白解決方案。在我們的數(shù)據(jù)衛(wèi)士設置中，將備用服務器配置為FAL_CLIENT，將主服務器配置為FAL_SERVER。
（4）LOG_FILE_NAME_CONVERT
我已經(jīng)使用這個參數(shù)將主數(shù)據(jù)庫的歸檔重做日志和備用重做日志的目標做了翻譯，確保在克隆過程中RMAN能夠自動在備用數(shù)據(jù)庫上創(chuàng)建恰當?shù)母北尽?/P>
（5）LOG_ARCHIVE_DEST_n
和主數(shù)據(jù)庫一樣，我也設置了兩個歸檔日志目標：一個主目標LOG_ARCHIVE_DEST_1和次要目標LOG_ARCHIVE_DEST_2。將來主備站點角色發(fā)生交換后，將由次要目標中的歸檔重做日志傳輸?shù)皆贾鲾?shù)據(jù)庫中。
最后，讓我們開始克隆吧！首先在主數(shù)據(jù)庫服務器上啟動一個RMAN會話，以target連接到主數(shù)據(jù)庫，以auxiliary連接到備用數(shù)據(jù)庫：
oracle@training> rman target / auxiliary sys/oracle@stdby
Recovery Manager: Release 11.1.0.6.0 - Production on Tue Apr 14 19:29:25 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1210321736)
connected to auxiliary database: STDBY (not mounted)
為了加快處理過程，我將會通過ALLOCATE CHANNEL命令創(chuàng)建兩個auxiliary通道和兩個normal通道，并在相同的RUN塊中使用DUPLICATE DATABASE開始克隆，下面是RMAN命令塊所做的事情：
1. 使用主數(shù)據(jù)庫服務器的參數(shù)文件作為模版為備用數(shù)據(jù)庫創(chuàng)建了一個新的SPFILE，但在DUPLICATE DATABASE運行塊中的SET命令中做了適當?shù)男薷摹?/P>
2. 然后關(guān)閉備用數(shù)據(jù)庫，再使用新的SPFILE啟動到NOMOUNT模式。
3. 接下來創(chuàng)建主數(shù)據(jù)庫控制文件的拷貝，修改它讓所有文件名都與備用數(shù)據(jù)庫匹配，拷貝新的控制文件到備用數(shù)據(jù)庫上，然后使用新的控制文件將數(shù)據(jù)庫啟動到MOUNT模式。
4. 然后在備用數(shù)據(jù)庫上直接創(chuàng)建主數(shù)據(jù)庫數(shù)據(jù)文件的鏡像拷貝備份。
5. 最后，使用主數(shù)據(jù)庫上當前的歸檔重做日志在備用數(shù)據(jù)庫上執(zhí)行必要的恢復，并將備用數(shù)據(jù)庫置為管理恢復模式。
我在清單7中列出了克隆操作的結(jié)果，它顯示了RMAN命令的輸出內(nèi)容，清單8列出了克隆過程中產(chǎn)生的備用數(shù)據(jù)庫的警告日志條目。
清單7 來自一個成功的備用數(shù)據(jù)庫克隆操作的輸出內(nèi)容
[oracle@training ~]$ rman target / auxiliary sys/oracle@stdby
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Apr 18 06:25:07 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1210321736)
connected to auxiliary database: STDBY (not mounted)
RMAN> RUN {
ALLOCATE CHANNEL d1 TYPE DISK;
ALLOCATE CHANNEL d2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL cnv1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL cnv2 TYPE DISK;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='stdby'
SET control_files='/u01/app/oracle/oradata/orcl/control01.ctl'
SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stdby/'
SET log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/STDBY/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby'
SET log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
SET fal_client='stdby'
SET fal_server='orcl'
SET standby_file_management='AUTO'
SET log_archive_config='dg_config=(orcl,stdby)'
NOFILENAMECHECK;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21>
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=126 device type=DISK
allocated channel: d2
channel d2: SID=120 device type=DISK
allocated channel: cnv1
channel cnv1: SID=97 device type=DISK
allocated channel: cnv2
channel cnv2: SID=96 device type=DISK
Starting Duplicate Db at 18-APR-09
contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl' auxiliary format
'/u01/app/oracle/product/11.1.0/db_1/dbs/orapwstdby' file
'/u01/app/oracle/product/11.1.0/db_1/dbs/spfileorcl.ora' auxiliary format
'/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestdby.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestdby.ora''";
}
executing Memory Script
Starting backup at 18-APR-09
Finished backup at 18-APR-09
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestdby.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''stdby'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/orcl/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u01/app/oracle/oradata/orcl/'', ''/u01/app/oracle/oradata/stdby/'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=/u01/app/oracle/flash_recovery_area/STDBY/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''stdby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''orcl'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(orcl,stdby)'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''stdby'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/orcl/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/orcl/'', ''/u01/app/oracle/oradata/stdby/'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=/u01/app/oracle/flash_recovery_area/STDBY/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''stdby'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''orcl'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(orcl,stdby)'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422678528 bytes
Fixed Size 1300324 bytes
Variable Size 121637020 bytes
Database Buffers 293601280 bytes
Redo Buffers 6139904 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcl/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting backup at 18-APR-09
channel d1: starting datafile copy
copying standby control file
output file name=/home/oracle/snapcf_orcl.f tag=TAG20090418T062548 RECID=36 STAMP=684483962
channel d1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-APR-09
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orcl/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orcl/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orcl/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orcl/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orcl/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orcl/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orcl/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-APR-09
channel d1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel d2: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20090418T062611 RECID=0 STAMP=0
channel d2: datafile copy complete, elapsed time: 00:01:15
channel d2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20090418T062611 RECID=0 STAMP=0
channel d1: datafile copy complete, elapsed time: 00:01:54
channel d1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/oradata/orcl/example01.dbf tag=TAG20090418T062611 RECID=0 STAMP=0
channel d1: datafile copy complete, elapsed time: 00:00:15
channel d1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20090418T062611 RECID=0 STAMP=0
channel d2: datafile copy complete, elapsed time: 00:01:01
output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20090418T062611 RECID=0 STAMP=0
channel d1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-APR-09
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/flash_recovery_area/ORCL/log_109_1_682541003.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc" ;
catalog clone archivelog "/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc";
switch clone datafile all;
}
executing Memory Script
Starting backup at 18-APR-09
channel d1: starting archived log copy
input archived log thread=1 sequence=109 RECID=110 STAMP=684484146
output file name=/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc RECID=0 STAMP=0
channel d1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 18-APR-09
cataloged archived log
archived log file name=/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc RECID=1 STAMP=684484135
datafile 1 switched to datafile copy
input datafile copy RECID=36 STAMP=684484136 file name=/u01/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=37 STAMP=684484136 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=38 STAMP=684484136 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=39 STAMP=684484136 file name=/u01/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=40 STAMP=684484136 file name=/u01/app/oracle/oradata/orcl/example01.dbf
contents of Memory Script:
{
set until scn 4021704;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 18-APR-09
starting media recovery
archived log for thread 1 with sequence 109 is already on disk as file /u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc
archived log file name=/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc thread=1 sequence=109
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-APR-09
Finished Duplicate Db at 18-APR-09
released channel: d1
released channel: d2
清單8 來自一個成功的備用數(shù)據(jù)庫克隆操作的警告日志
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.1.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =12
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in client-side pfile /home/oracle/initstdby.ora on machine 11gStdby
System parameters with non-default values:
db_name = "stdby"
Sat Apr 18 06:24:25 2009
PMON started with pid=2, OS id=8334
Sat Apr 18 06:24:25 2009
...
(為了簡潔，這里有所刪減）
...
Sat Apr 18 06:24:29 2009
MMON started with pid=14, OS id=8362
ORACLE_BASE from environment = /u01/app/oracle
Sat Apr 18 06:24:29 2009
MMNL started with pid=15, OS id=8364
Sat Apr 18 06:24:52 2009
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/11.1.0/db_1/dbs/arch
destination database instance is 'started' not 'mounted'
Sat Apr 18 06:25:09 2009
ALTER SYSTEM SET spfile='/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestdby.ora' SCOPE=MEMORY;
ALTER SYSTEM SET db_unique_name='stdby' SCOPE=SPFILE;
ALTER SYSTEM SET control_files='/u01/app/oracle/oradata/orcl/control01.ctl' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stdby/' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/STDBY/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl' SCOPE=SPFILE;
ALTER SYSTEM SET fal_client='stdby' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='orcl' SCOPE=SPFILE;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config='dg_config=(orcl,stdby)' SCOPE=SPFILE;
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
Shutting down instance (immediate)
License high water mark = 7
alter database close
ORA-1507 signalled during: alter database close...
alter database dismount
ORA-1507 signalled during: alter database dismount...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Apr 18 06:25:13 2009
Stopping background process VKTM:
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Apr 18 06:25:16 2009
Instance shutdown complete
Sat Apr 18 06:25:16 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilestdby.ora
System parameters with non-default values:
processes = 150
sga_target = 400M
control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"
log_file_name_convert = "/u01/app/oracle/oradata/orcl/"
log_file_name_convert = "/u01/app/oracle/oradata/stdby/"
db_block_size = 8192
compatible = "11.1.0.0.0"
log_archive_config = "dg_config=(orcl,stdby)"
log_archive_dest_1 = "location=/u01/app/oracle/flash_recovery_area/STDBY/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby"
log_archive_dest_2 = "service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl"
log_archive_dest_state_1 = "ENABLE"
log_archive_dest_state_2 = "ENABLE"
log_archive_max_processes= 4
log_archive_format = "log_%s_%t_%r.arc"
fal_client = "stdby"
fal_server = "orcl"
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 8G
standby_file_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
db_unique_name = "stdby"
open_cursors = 300
pga_aggregate_target = 150M
diagnostic_dest = "/u01/app/oracle"
Sat Apr 18 06:25:19 2009
PMON started with pid=2, OS id=8395
Sat Apr 18 06:25:19 2009
...
(為了簡潔，這里有所刪減）
...
ORACLE_BASE from environment = /u01/app/oracle
Sat Apr 18 06:25:25 2009
RFS connections have been disallowed
alter database mount standby database
Sat Apr 18 06:25:38 2009
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from orcl to stdby
Setting recovery target incarnation to 2
ARCH: STARTING ARCH PROCESSES
Sat Apr 18 06:25:38 2009
ARC1 started with pid=21, OS id=8445
Sat Apr 18 06:25:38 2009
ARC0 started with pid=20, OS id=8443
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Thread not mounted
ARC1: Becoming the heartbeat ARCH
Sat Apr 18 06:25:38 2009
ARC3 started with pid=23, OS id=8449
ARC3: Thread not mounted
Sat Apr 18 06:25:38 2009
ARC2 started with pid=22, OS id=8447
ARC2: Thread not mounted
ARC1: Thread not mounted
Sat Apr 18 06:25:39 2009
Successful mount of redo thread 1, with mount id 1212288222
Physical Standby Database mounted.
Lost write protection disabled
Completed: alter database mount standby database
Sat Apr 18 06:28:56 2009
Switch of datafile 1 complete to datafile copy
checkpoint is 4021609
Switch of datafile 2 complete to datafile copy
checkpoint is 4021608
Switch of datafile 3 complete to datafile copy
checkpoint is 4021656
Switch of datafile 4 complete to datafile copy
checkpoint is 4021701
Switch of datafile 5 complete to datafile copy
checkpoint is 4021679
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/flash_recovery_area/STDBY/
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5
alter database recover if needed
standby start until change 4021704
Media Recovery Start
Fast Parallel Media Recovery NOT enabled
Managed Standby Recovery not using Real Time Apply
ORA-279 signalled during: alter database recover if needed
standby start until change 4021704
...
alter database recover logfile '/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc'
Media Recovery Log /u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc
Incomplete Recovery applied until change 4021704 time 04/18/2009 06:29:05
Media Recovery Complete (stdby)
Completed: alter database recover logfile '/u01/app/oracle/flash_recovery_area/STDBY/log_109_1_682541003.arc'
克隆之后：清除和校驗
至此克隆操作執(zhí)行結(jié)束，我需要確保備用數(shù)據(jù)庫從主數(shù)據(jù)庫接收到歸檔重做日志，為了驗證主備數(shù)據(jù)庫確實是聯(lián)通的，我將在主數(shù)據(jù)庫上執(zhí)行一次重做日志切換。
SQL﹥ ALTER SYSTEM ARCHIVE LOG CURRENT;
下面是來自備用數(shù)據(jù)庫的警告日志，從中可以看出在線重做日志成功傳輸并應用到備用數(shù)據(jù)庫上了。
Completed: alter database clear logfile group 6
RFS connections are allowed
Sat Apr 18 06:29:58 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 8492
RFS[1]: Identified database type as 'physical standby'
RFS LogMiner: Client disabled from further notification
Sat Apr 18 06:35:39 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 8506
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u01/app/oracle/oradata/stdby/srl01.log'
Sat Apr 18 06:36:28 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 8512
RFS[3]: Identified database type as 'physical standby'
kcrrvslf: active RFS archival for log 4 thread 1 sequence 111
RFS[3]: Successfully opened standby log 5: '/u01/app/oracle/oradata/stdby/srl02.log'
Sat Apr 18 06:42:53 2009
原文：Oracle 11g Data Guard: Building a Physical Standby Database by Jim Czuprynski
【編輯推薦】
