Oracle 11g物理備用數(shù)據(jù)庫“實(shí)況克隆”詳解
原創(chuàng)【51CTO獨(dú)家特稿】相比Oracle 8i和Oracle 9i，Oracle 11g在數(shù)據(jù)庫備份方面做出了極大的改善，特別是作為Oracle最大可用性架構(gòu)（MAA）一部分的真正應(yīng)用集群（RAC）特性。Oracle 11g現(xiàn)在創(chuàng)建一個(gè)備用數(shù)據(jù)庫變得更加簡單了，因?yàn)榛謴?fù)管理器（RMAN）支持直接從主數(shù)據(jù)庫使用DUPLICATE DATABASE命令集通過網(wǎng)絡(luò)克隆一個(gè)備用數(shù)據(jù)庫，只要目標(biāo)數(shù)據(jù)庫是活動(dòng)的即可。這意味著再也不用先生成，再傳輸，最后在備用數(shù)據(jù)庫上通過復(fù)雜的手工方式還原和恢復(fù)主數(shù)據(jù)庫的RMAN備份集了，相反，RMAN在主站點(diǎn)上自動(dòng)生成一個(gè)轉(zhuǎn)換腳本在內(nèi)存中，然后在備用站點(diǎn)上使用這個(gè)腳本管理克隆操作，實(shí)際上不用DBA進(jìn)行任何干預(yù)。
下文將集中精力講解備用數(shù)據(jù)庫“實(shí)況克隆”特性。筆者的硬件基本情況是：雙核AMD Athlon 64位CPU（Winchester 420），4GB內(nèi)存，主機(jī)運(yùn)行的是Windows xp系統(tǒng)，運(yùn)行VMWare Server 1.0.8訪問訪問虛擬數(shù)據(jù)庫服務(wù)器環(huán)境，每個(gè)虛擬機(jī)使用1個(gè)CPU，1200M內(nèi)存，我選擇Oracle Enterprise Linux (OEL) 4.5.1（Linux內(nèi)核版本2.6.9-55.0.0.0.2.ELsmp）作為虛擬機(jī)客戶端操作系統(tǒng)。
每個(gè)VMWare虛擬機(jī)配置好后，在每個(gè)虛擬機(jī)的/etc/hosts文件中添加合適的條目，讓主站點(diǎn)（training）和備用站點(diǎn)（11gStdby）之間建立起網(wǎng)絡(luò)連接，然后在每個(gè)節(jié)點(diǎn)上都安裝Oracle 11g數(shù)據(jù)庫，最后，在主站點(diǎn)上創(chuàng)建好標(biāo)準(zhǔn)的11g R1種子數(shù)據(jù)庫，包括標(biāo)準(zhǔn)的示例方案。這個(gè)數(shù)據(jù)庫的ORACLE_SID是orcl，接下來就可以開始執(zhí)行實(shí)況克隆操作了。
克隆前準(zhǔn)備工作：調(diào)整主數(shù)據(jù)庫
在克隆主數(shù)據(jù)庫到對應(yīng)的備用環(huán)境中之前，我需要對主數(shù)據(jù)庫做一些調(diào)整，下面的步驟未做特別說明沒有先后順序，只要在發(fā)出DUPLICATE DATABASE命令前這些步驟都執(zhí)行完了即可，在克隆操作過程中應(yīng)該沒有什么讓人意外的東西出現(xiàn)。
強(qiáng)制記錄所有的交易
大多數(shù)組織實(shí)施數(shù)據(jù)衛(wèi)士配置的主要原因是保證所有交易都不丟失，但遺憾的是，默認(rèn)情況下，Oracle數(shù)據(jù)庫是運(yùn)行在NOFORCE LOGGING模式下的，這意味著對對象的改變可能丟失，因?yàn)樗麄兊拇鎯傩员辉O(shè)為NOLOGGING，為了確保所有的改變都被記錄下來，我將執(zhí)行ALTER DATABASE FORCE LOGGING命令，這個(gè)命令需要在執(zhí)行ALTER DATABASE ARCHIVELOG命令將數(shù)據(jù)庫ARCHIVELOG模式前執(zhí)行，這些命令如清單1所示。
清單1 將主數(shù)據(jù)庫切換到ARCHIVELOG模式
--為歸檔日志文件設(shè)置一個(gè)合適的格式
ALTER SYSTEM SET log_archive_format = 'log_%s_%t_%r.arc' SCOPE=SPFILE;
--設(shè)置新的DB_UNIQUE_NAME參數(shù)，它不能動(dòng)態(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對于實(shí)時(shí)應(yīng)用（Real Time Apply）特性是需要的，或DBA想要實(shí)現(xiàn)重做日志串聯(lián)目的時(shí)也需要，除此之外，它任然是備用數(shù)據(jù)庫配置選項(xiàng)。Oracle 11g另一個(gè)優(yōu)點(diǎn)是如果SRL在主數(shù)據(jù)庫上已經(jīng)配置好，那么DUPLICATE DATABASE命令將會在備用數(shù)據(jù)庫上自動(dòng)創(chuàng)建它們。清單2顯示了我在主數(shù)據(jù)庫上創(chuàng)建SRL的命令，注意我也使用了多個(gè)重SRL文件保護(hù)整個(gè)SRL組，避免數(shù)據(jù)丟失，這一點(diǎn)和在線重做日志組類似。
清單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ù)庫不同的主機(jī)上的，否則，在災(zāi)難中主備數(shù)據(jù)庫都有可能受到危害，最佳做法是將對應(yīng)的備用數(shù)據(jù)庫的目錄和文件名都弄成一樣，但如果遇到掛載點(diǎn)不一樣時(shí)，目錄名需要修改，這個(gè)時(shí)候就需要使用DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT初始化參數(shù)進(jìn)行轉(zhuǎn)換了。
修改主站點(diǎn)初始化參數(shù)
在主數(shù)據(jù)庫上設(shè)置下列初始化參數(shù)確保DUPLICATE DATABASE命令能夠一樣配置備用數(shù)據(jù)庫，我在清單3中詳細(xì)列出了這些初始化參數(shù)設(shè)置：
（1）DB_UNIQUE_NAME
我通過這個(gè)參數(shù)為主數(shù)據(jù)庫定義一個(gè)唯一的實(shí)例名，這個(gè)參數(shù)值使得區(qū)分“原始”主數(shù)據(jù)庫和備用數(shù)據(jù)庫變得更加簡單，因?yàn)檫@是一個(gè)靜態(tài)參數(shù)，我在清單1中已經(jīng)將其設(shè)置為SCOPE=SPFILE，它將在主數(shù)據(jù)庫實(shí)例啟動(dòng)時(shí)生效。
（2）LOG_ARCHIVE_CONFIG
這個(gè)參數(shù)控制主或備用數(shù)據(jù)庫是否應(yīng)該接受和/或發(fā)送來自遠(yuǎn)程源的歸檔重做日志，它允許我們包含所有主備數(shù)據(jù)庫，因?yàn)樗谂渲弥辛谐隽怂袛?shù)據(jù)庫的DB_UNIQUE_NAME值，我將其設(shè)置為目前我的數(shù)據(jù)衛(wèi)士數(shù)據(jù)庫orcl和stdby。
（3）STANDBY_FILE_MANAGEMENT
我將這個(gè)參數(shù)設(shè)置問為auto了，這樣主數(shù)據(jù)庫上發(fā)生什么操作，備用數(shù)據(jù)庫上就會跟著發(fā)生什么操作，如主數(shù)據(jù)庫上創(chuàng)建一個(gè)文件，備用數(shù)據(jù)庫上也將創(chuàng)建一個(gè)相同的文件，刪除主數(shù)據(jù)庫上一個(gè)已有文件，備用數(shù)據(jù)庫上也做對應(yīng)的刪除。如新增一個(gè)在線重做日志文件組或刪除一個(gè)表空間。
（4）LOG_ARCHIVE_DEST_n
這個(gè)控制是從主數(shù)據(jù)庫上傳輸歸檔重做日志到物理備用數(shù)據(jù)庫的關(guān)鍵參數(shù)，我將設(shè)置兩個(gè)歸檔目標(biāo)：
1. 目標(biāo)LOG_ARCHIVE_DEST_1指定主數(shù)據(jù)庫歸檔重做日志的物理位置，注意我們使用了閃回恢復(fù)區(qū)作為目標(biāo)。
2. 目標(biāo)LOG_ARCHIVE_DEST_2指定了對應(yīng)的備用數(shù)據(jù)庫實(shí)例（stdby）的網(wǎng)絡(luò)服務(wù)地址，這個(gè)參數(shù)確保歸檔重做日志自動(dòng)傳輸?shù)絺溆谜军c(diǎn)。
對于這個(gè)歸檔重做日志傳輸參數(shù)我還可以指定另外兩個(gè)指令：
◆當(dāng)數(shù)據(jù)庫是以特定角色激活時(shí)，指令VALID_FOR大大簡化了重做日志傳輸時(shí)的類型，當(dāng)主備數(shù)據(jù)庫角色不同時(shí)，在傳輸重做日志時(shí)這是最關(guān)鍵的參數(shù)，表1列出了這個(gè)參數(shù)允許的值。
|
表1 VALID_FOR指令值 | |
|
設(shè)置 |
含義 |
|
ALL_LOGFILES |
（默認(rèn)）目標(biāo)使用在線或備用重做日志文件 |
|
ONLINE_LOGFILE |
目標(biāo)僅適用于在線歸檔重做日志文件 |
|
STANDBY_LOGFILE |
目標(biāo)僅適用于備用重做日志文件 |
|
ALL_ROLES |
當(dāng)數(shù)據(jù)庫以主或備用角色運(yùn)行時(shí)（默認(rèn)）目標(biāo)都是有效的 |
|
PRIMARY_ROLE |
當(dāng)數(shù)據(jù)庫以主角色運(yùn)行時(shí)目標(biāo)是有效的 |
|
STANDBY_ROLE |
當(dāng)數(shù)據(jù)庫以備用角色運(yùn)行時(shí)目標(biāo)是有效的 |
◆也可以設(shè)置合適的重做日志傳輸模式（redo transport mode）值指定歸檔重做日志從主數(shù)據(jù)庫傳輸?shù)絺溆脭?shù)據(jù)庫，表2列出了這個(gè)指令允許的值。
表2 重做日志傳輸模式 | |
|
設(shè)置 |
含義 |
|
ASYNC |
在事務(wù)提交前可能不是所有的目標(biāo)都接受了傳輸?shù)闹刈鋈罩荆J(rèn)值） |
|
SYNC |
在事務(wù)提交前所有目標(biāo)必須接受傳輸?shù)闹刈鋈罩?/P> |
|
AFFIRM |
僅當(dāng)重做數(shù)據(jù)被寫入到備用重做日志后，目標(biāo)才確認(rèn)已收到，含有SYNC含義 |
|
NOAFFIRM |
當(dāng)重做數(shù)據(jù)寫入到備用重做日志前目標(biāo)就可以確認(rèn)收到，含有ASYNC含義 |
網(wǎng)絡(luò)配置修改
最后，我需要確保主數(shù)據(jù)庫和備用數(shù)據(jù)庫之間能夠通過網(wǎng)絡(luò)通信，唯一需要變化的就是在主數(shù)據(jù)庫本地命名配置文件（TNSNAMES.ORA）中加上備用數(shù)據(jù)庫的實(shí)例，備用數(shù)據(jù)庫的LISTENER.ORA配置文件也需要一個(gè)備用數(shù)據(jù)庫實(shí)例的靜態(tài)監(jiān)聽器，這些變化如清單4所示。
清單3 在主數(shù)據(jù)庫上設(shè)置合適的初始化參數(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)絡(luò)配置文件修改
#在主數(shù)據(jù)庫實(shí)例上添加一個(gè)備用數(shù)據(jù)庫條目
STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gStdby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby)
)
)
#使用備用數(shù)據(jù)庫實(shí)例的靜態(tài)引用設(shè)置備用數(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))
準(zhǔn)備克?。簻?zhǔn)備備用站點(diǎn)
現(xiàn)在主站點(diǎn)已經(jīng)準(zhǔn)備好可以克隆了，在對應(yīng)的備用站點(diǎn)上也需要做一些對應(yīng)的調(diào)整：
創(chuàng)建必要的目錄
需要為數(shù)據(jù)庫控制文件、數(shù)據(jù)文件、在線重做日志文件和備用重做日志文件創(chuàng)建必要的目錄，我還為數(shù)據(jù)庫的審核跟蹤創(chuàng)建了合適的目錄。
設(shè)置密碼文件
因?yàn)橹鲾?shù)據(jù)庫要和備用數(shù)據(jù)庫進(jìn)行通信時(shí)需要使用遠(yuǎn)程認(rèn)證，我將會使用orapwd工具創(chuàng)建一個(gè)新的密碼文件，確保SYS的密碼和主數(shù)據(jù)庫匹配（注意我可能會直接從主數(shù)據(jù)庫拷貝到備用數(shù)據(jù)庫）。
創(chuàng)建備用初始化參數(shù)文件
最后，我需要?jiǎng)?chuàng)建一個(gè)初始化參數(shù)文件（PFILE），僅允許我啟動(dòng)備用數(shù)據(jù)庫實(shí)例，它只需要一個(gè)參數(shù)：DB_NAME。當(dāng)DUPLICATE DATABASE命令腳本執(zhí)行完畢后，它將會創(chuàng)建一個(gè)服務(wù)端參數(shù)文件（SPFILE），它僅包括合適的初始化參數(shù)設(shè)置。
在清單5中我解釋了這些命令和臨時(shí)備用數(shù)據(jù)庫初始化參數(shù)，為了開啟DUPLICATE DATABASE克隆操作，我將啟動(dòng)備用站點(diǎn)的監(jiān)聽器，然后使用前面創(chuàng)建的PFILE初始化參數(shù)文件將備用數(shù)據(jù)庫實(shí)例啟動(dòng)到NOMOUNT狀態(tài)。
$> export ORACLE_SID=stdby
$> sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/init_stdby.ora';
清單5 備用數(shù)據(jù)庫臨時(shí)初始化參數(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)境啟動(dòng)DUPLICATE DATABASE命令，前面我已經(jīng)提到過，Oracle 11g中DUPLICATE DATABASE命令最引人注目的改變是它可以通過網(wǎng)絡(luò)直接將主數(shù)據(jù)庫克隆到備用數(shù)據(jù)庫站點(diǎn)。作為設(shè)置備用數(shù)據(jù)庫的一部分，我也可以為所有需要的初始化參數(shù)指定值，DUPLICATE DATABASE將會在備用數(shù)據(jù)庫上創(chuàng)建一個(gè)新的SPFILE。
清單6顯示了使用DUPLICATE DATABASE命令進(jìn)行克隆的完整語句，注意我添加了下面這樣一些額外的參數(shù)，可能和主數(shù)據(jù)庫的參數(shù)稍有不同：
（1）DB_UNIQUE_NAME
我將這個(gè)參數(shù)的值設(shè)為stdby了。
（2）CONTROL_FILES
我只為備用數(shù)據(jù)庫創(chuàng)建了一個(gè)控制文件，在克隆完畢后我會復(fù)制多個(gè)。
（3）FAL_CLIENT和FAL_SERVER
這兩個(gè)參數(shù)確定哪個(gè)數(shù)據(jù)庫服務(wù)分別擔(dān)任FAL（fetch archive log）客戶端和服務(wù)器，例如，無論何時(shí)，當(dāng)主數(shù)據(jù)庫和備用數(shù)據(jù)庫之間的網(wǎng)絡(luò)斷掉后，或如果備用數(shù)據(jù)庫已經(jīng)關(guān)閉相當(dāng)長一段時(shí)間，歸檔重做日志可能就不會傳輸?shù)絺溆梅?wù)器上。這種情況叫做歸檔日志空白（archive log gap），這兩個(gè)FAL服務(wù)名確定了由哪個(gè)服務(wù)器（FAL_SERVER）維護(hù)所有歸檔重做日志組主列表，由它為FAL_CLIENT提供可能發(fā)生的歸檔日志空白解決方案。在我們的數(shù)據(jù)衛(wèi)士設(shè)置中，將備用服務(wù)器配置為FAL_CLIENT，將主服務(wù)器配置為FAL_SERVER。
（4）LOG_FILE_NAME_CONVERT
我已經(jīng)使用這個(gè)參數(shù)將主數(shù)據(jù)庫的歸檔重做日志和備用重做日志的目標(biāo)做了翻譯，確保在克隆過程中RMAN能夠自動(dòng)在備用數(shù)據(jù)庫上創(chuàng)建恰當(dāng)?shù)母北尽?/P>
（5）LOG_ARCHIVE_DEST_n
和主數(shù)據(jù)庫一樣，我也設(shè)置了兩個(gè)歸檔日志目標(biāo)：一個(gè)主目標(biāo)LOG_ARCHIVE_DEST_1和次要目標(biāo)LOG_ARCHIVE_DEST_2。將來主備站點(diǎn)角色發(fā)生交換后，將由次要目標(biāo)中的歸檔重做日志傳輸?shù)皆贾鲾?shù)據(jù)庫中。
最后，讓我們開始克隆吧！首先在主數(shù)據(jù)庫服務(wù)器上啟動(dòng)一個(gè)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)建兩個(gè)auxiliary通道和兩個(gè)normal通道，并在相同的RUN塊中使用DUPLICATE DATABASE開始克隆，下面是RMAN命令塊所做的事情：
1. 使用主數(shù)據(jù)庫服務(wù)器的參數(shù)文件作為模版為備用數(shù)據(jù)庫創(chuàng)建了一個(gè)新的SPFILE，但在DUPLICATE DATABASE運(yùn)行塊中的SET命令中做了適當(dāng)?shù)男薷摹?/P>
2. 然后關(guān)閉備用數(shù)據(jù)庫，再使用新的SPFILE啟動(dòng)到NOMOUNT模式。
3. 接下來創(chuàng)建主數(shù)據(jù)庫控制文件的拷貝，修改它讓所有文件名都與備用數(shù)據(jù)庫匹配，拷貝新的控制文件到備用數(shù)據(jù)庫上，然后使用新的控制文件將數(shù)據(jù)庫啟動(dòng)到MOUNT模式。
4. 然后在備用數(shù)據(jù)庫上直接創(chuàng)建主數(shù)據(jù)庫數(shù)據(jù)文件的鏡像拷貝備份。
5. 最后，使用主數(shù)據(jù)庫上當(dāng)前的歸檔重做日志在備用數(shù)據(jù)庫上執(zhí)行必要的恢復(fù)，并將備用數(shù)據(jù)庫置為管理恢復(fù)模式。
我在清單7中列出了克隆操作的結(jié)果，它顯示了RMAN命令的輸出內(nèi)容，清單8列出了克隆過程中產(chǎn)生的備用數(shù)據(jù)庫的警告日志條目。
清單7 來自一個(gè)成功的備用數(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 來自一個(gè)成功的備用數(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'
克隆之后：清除和校驗(yàn)
至此克隆操作執(zhí)行結(jié)束，我需要確保備用數(shù)據(jù)庫從主數(shù)據(jù)庫接收到歸檔重做日志，為了驗(yàn)證主備數(shù)據(jù)庫確實(shí)是聯(lián)通的，我將在主數(shù)據(jù)庫上執(zhí)行一次重做日志切換。
SQL﹥ ALTER SYSTEM ARCHIVE LOG CURRENT;
下面是來自備用數(shù)據(jù)庫的警告日志，從中可以看出在線重做日志成功傳輸并應(yīng)用到備用數(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
【編輯推薦】
