1、Dataguard 配置實(shí)施方案目錄1.DataGuard配置條件22.實(shí)施計(jì)劃23.判斷DataGuard是否安裝22. 網(wǎng)絡(luò)配置：主庫和備庫24.配置hosts文件：主庫和備庫35.監(jiān)聽配置：主庫和備庫36.修改主庫備庫到歸檔模式45. 創(chuàng)建主庫口令文件57.修改主庫初始化參數(shù)pfile58.創(chuàng)建備份庫需要的控制文件79.創(chuàng)建主庫的spfile710.備份生產(chǎn)數(shù)據(jù)庫711.修改備庫初始化參數(shù)pfile812.復(fù)制備庫控制文件913.在備庫上創(chuàng)建口令文件914.創(chuàng)建備庫的spfile915.啟動(dòng)物理備用數(shù)據(jù)庫916.配置Standby Redo Log917.Redo Apply的啟動(dòng)和停止

2、101. DataGuard配置條件DataGuard的備庫有物理standby和邏輯standby兩種，由于邏輯standby的局限性，生產(chǎn)環(huán)境選擇物理standby作為DataGuard的備庫。實(shí)現(xiàn)物理standby需要具備以下條件：1、 oralce數(shù)據(jù)庫必須是企業(yè)版，企業(yè)版才支持DataGuard；2、 DataGuard中的數(shù)據(jù)庫運(yùn)行的平臺(tái)必須相同；3、 DataGuard中的主庫和備庫必須運(yùn)行于歸檔模式；4、 DataGuard中的主庫和備庫的db_name必須相同、db_unique_name必須不同；5、 使用具有相關(guān)sysdba權(quán)限的用戶管理primary和standby數(shù)據(jù)

3、庫。2. 實(shí)施計(jì)劃對(duì)于在DataGuard實(shí)施的第一步的實(shí)施計(jì)劃如下：日期工作內(nèi)容備注2013-11-18檢查數(shù)據(jù)庫是否滿足配置要求2013-11-19配置DataGuard，并進(jìn)行相關(guān)驗(yàn)證2013-11-20進(jìn)行DataGuard管理，驗(yàn)證主備庫切換3. 判斷DataGuard是否安裝Sql>select * from v$option where parameter = 'Oracle Data Guard'返回結(jié)果為空，DG沒有配置。2. 網(wǎng)絡(luò)配置：主庫和備庫主庫操作系統(tǒng)版本：centos5.4數(shù)據(jù)庫版本: Database 11g Enterprise Editi

4、on Release 11.2.0.1.0 Ip地址：172.34.21.204Db_name：standbydDb_unique_name：standbydinstance_name：primaryservice_names：standbyd備庫操作系統(tǒng)版本：centos5.4數(shù)據(jù)庫版本: Database 11g Enterprise Edition Release 11.2.0.1.0 Ip地址：172.34.21.205Db_name：standbydDb_unique_name：standbydbinstance_name：standbyservice_names：standbydb

5、4. 配置hosts文件：主庫和備庫vi /etc/hosts# Do not remove the following line, or various programs# that require network functionality will fail.127.0.0.1 hlht-db1 localhost.localdomain localhost:1 localhost6.localdomain6 localhost610.52.151.102 hlht-db110.52.151.103 hlht-db25. 監(jiān)聽配置：主庫和備庫主庫：配置主庫監(jiān)聽和tns:oracle hl

6、ht-db1$ cd $ORACLE_HOME/network/adminoracle hlht-db1admin$ cat listener.ora# listener.ora Network Configuration File: /opt/oracle/112/network/admin/listener.ora# Generated by Oracle configuration tools.#addSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle

7、/112) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /opt/oracle/112) (SID_NAME = primary) ) )#addLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.132.204)(PORT = 1561) #(ADDRESS = (PROTO

8、COL = TCP)(HOST = 172.34.21.204)(PORT = 1561) ) )oraclehlht-db1 admin$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /opt/oracle/112/network/admin/tnsnames.ora# Generated by Oracle configuration tools.STANDBYD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.132.204)(PORT = 11

9、731) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.34.21.204)(PORT = 11731) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) )STANDBYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.132.205)(PORT = 11731) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.34.21.205)(PORT = 11731) (CONNECT_

10、DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) )備庫：配置備庫監(jiān)聽和tns:oracle hlht-db2$ cd $ORACLE_HOME/network/adminoraclehlht-db2 admin$ cat listener.ora # listener.ora Network Configuration File: /opt/oracle/112/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER

11、= (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/112) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /opt/oracle/112) (SID_NAME = standby) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (ADDRESS

12、= (PROTOCOL = TCP)(HOST = 10.53.132.205)(PORT = 11731) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.34.21.205)(PORT = 11731)oraclehlht-db2 admin$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /opt/oracle/112/network/admin/tnsnames.ora# Generated by Oracle configuration tools.STANDBYDB = (DE

13、SCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = hlht-db2)(PORT = 1521) #(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.132.205)(PORT = 11731) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.34.21.205)(PORT = 11731) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby) ) )STANDBYD = (DESCRIPTION = #(ADDRESS

14、 = (PROTOCOL = TCP)(HOST = hlht-db2)(PORT = 1521) #(ADDRESS = (PROTOCOL = TCP)(HOST = 172.34.21.204)(PORT = 11731) (ADDRESS = (PROTOCOL = TCP)(HOST =172.34.21.205)(PORT = 11731) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) )6. 修改主庫備庫到歸檔模式檢查歸檔情況：Archive log list;修改歸檔路徑、歸檔文件夾大小、歸檔文件

15、格式：sqlplus / as sysdbaSQL> alter system set log_archive_dest_1='location=/data/archlog' alter system set db_recovery_file_dest_size = 10G; alter system set log_archive_format='archivelog_%t_%s_%r' scope=spfile;修改歸檔SQL>shutdown immediate; startup mount; alter database archivelog

16、;啟動(dòng)數(shù)據(jù)庫SQL> alter database open;設(shè)置強(qiáng)制寫日志：SQL> select FORCE_LOGGING from v$database;NOSQL> alter database force logging;SQL> select FORCE_LOGGING from v$database;YES5. 創(chuàng)建主庫口令文件orapwd file=$ORACLE_HOME/dbs/orapwprimary password=XXX entries=10注：這里的file命名規(guī)則在unix/linux下orapw<ORACLE_SID>，在

17、windows下為pwd<SID>.ora。主備庫的sys用戶密碼相同7. 修改主庫初始化參數(shù)pfile 創(chuàng)建主庫pfile sql > create pfile from spfile;使用 create pfile from spfile創(chuàng)建的都放在默認(rèn)的位置和默認(rèn)的命名：Pfile：init<sid>.ora 與spfile相同路徑。修改pfileDB_UNIQUE_NAME=standby# DB_UNIQUE_NAME主備數(shù)據(jù)庫不能相同LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,standbydb)'#D

18、ataGuard專用配置，主備數(shù)據(jù)庫相同，為兩個(gè)庫的db_unique_nameLOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= standby#主數(shù)據(jù)庫歸檔地址LOG_ARCHIVE_DEST_2= 'SERVICE=standby LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= standbydb#備用數(shù)據(jù)庫歸檔地址，由該配置指定

19、向備用數(shù)據(jù)庫寫歸檔日志# SERVICE=standby LGWR ASYNC AFFIRM：這3個(gè)參數(shù)影響dg的保護(hù)模式，一般這3個(gè)參數(shù)都配置LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc #歸檔日志文件名格式LOG_ARCHIVE_MAX_PROCESSES=30#默認(rèn)是4，可以不修改#以下是主庫作為備用數(shù)據(jù)庫的配置：用于主備切換FAL_SERVER=standby # FAL_SERVER是另一個(gè)數(shù)據(jù)庫的service_nameFAL_CLIEN

20、T=orcl #當(dāng)前數(shù)據(jù)庫的service_nameDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/orcl'# 配置主備庫的數(shù)據(jù)文件位置，遠(yuǎn)程的目錄寫在前面，LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/orcl'# 配置主備庫的在線日志和standby日志文件位置，遠(yuǎn)程的目錄寫在前面STANDBY_FI

21、LE_MANAGEMENT=AUTO REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE修改成功后進(jìn)行啟動(dòng)驗(yàn)證：1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora;2、如果可以啟動(dòng)成功，就創(chuàng)建spfile：Create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora;3、如果不成功，查看pfile是否修改錯(cuò)誤。備庫的pfile可以是主庫復(fù)制過來的，也可以由備庫是spfile生成

22、后進(jìn)行修改，但是備庫的控制文件一定是主庫創(chuàng)建的。Pfile 拷貝到備庫上：scp rp /u01/app/product/11.2.0/db_1/dbs/initorcl.ora node2:/u01/app/product/11.2.0/db_1/dbs/8. 創(chuàng)建備份庫需要的控制文件創(chuàng)建控制文件Shutdown immediateSTARTUP MOUNT; ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl.ctl' ALTER DATABASE OPEN;這個(gè)就是standby controlfile，備庫的控

23、制文件一定要使用這個(gè)控制文件。9. 創(chuàng)建主庫的spfile修改主庫pfile成功后進(jìn)行啟動(dòng)驗(yàn)證：1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora;2、如果可以啟動(dòng)成功，先進(jìn)行spfile的備份，然后創(chuàng)建spfile：Create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora;3、如果不成功，查看pfile是否修改錯(cuò)誤。4、穿件spfile成功后進(jìn)行spfile啟動(dòng)數(shù)據(jù)庫進(jìn)行驗(yàn)證。Shutdow

24、n immediatestartup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'sql> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'(先把原來的干掉)shutdown immediate;startupstartup from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'

25、10. 備份生產(chǎn)數(shù)據(jù)庫至此主庫已經(jīng)配置完畢，需要復(fù)制主庫的數(shù)據(jù)文件、standby 控制文件、歸檔日志、參數(shù)文件pfile到備庫。1、 停止數(shù)據(jù)庫2、 直接復(fù)制數(shù)據(jù)文件、歸檔日志文件、standby 控制文件、參數(shù)文件pfile到備庫相應(yīng)目錄scp -rp /u01/oradata/ORCL node2:/u01/oradata/scp rp /u01/app/admin/orcl node2:/u01/app/admin(記得在備庫創(chuàng)建admin)11. 修改備庫初始化參數(shù)pfile修改備庫參數(shù)文件pfile，這個(gè)備庫pfile參數(shù)文件可以是備庫由spfile生成。也可以是主庫復(fù)制過來的，但

26、是要修改正確。Pfile指定的控制文件路徑：修改成主庫復(fù)制過來的standby控制文件，該控制文件復(fù)制三份，在pfile文件指定路徑。control_files='E:OracleoradataDGMAINcontrol01.ctl','E:OracleoradataDGMAINcontrol02.ctl','E:OracleoradataDGMAINcontrol03.ctl'#控制文件DB_UNIQUE_NAME=standby# DB_UNIQUE_NAME主備數(shù)據(jù)庫不能相同LOG_ARCHIVE_CONFIG='DG_CONFIG

27、=(orcl,standby)'#DataGuard專用配置，主備數(shù)據(jù)庫相同LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby#主數(shù)據(jù)庫歸檔地址LOG_ARCHIVE_DEST_2= 'SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl#備用數(shù)據(jù)庫歸檔地址，由該配置指定向備用數(shù)據(jù)庫寫歸檔日志LOG_

28、ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc #歸檔日志文件名格式LOG_ARCHIVE_MAX_PROCESSES=30#默認(rèn)是4，可以不修改#以下是主庫作為備用數(shù)據(jù)庫的配置：用于主備切換FAL_SERVER=orcl # FAL_SERVER是另一個(gè)數(shù)據(jù)庫的service_nameFAL_CLIENT=standby #當(dāng)前數(shù)據(jù)庫的service_nameDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/

29、orcl','/u01/app/oracle/oradata/standby'LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/standby'#如果主庫與備庫安裝目錄不一樣需要配置CONVERT，遠(yuǎn)程的數(shù)據(jù)庫目錄寫在前面STANDBY_FILE_MANAGEMENT=AUTO REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE修改成功后進(jìn)行啟動(dòng)驗(yàn)證：1、Startup pfile='/u01/app/or

30、acle/product/10.2.0/db_1/dbs/initorcl.ora;2、如果可以啟動(dòng)成功，就創(chuàng)建spfile：Create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora;3、如果不成功，查看pfile是否修改錯(cuò)誤。12. 復(fù)制備庫控制文件備庫的控制文件是主庫的standby控制文件復(fù)制過來的，不需要修改，直接復(fù)制三份。scp -rp /tmp/dg.ctl node2:/u01/oradata/ORCL/controlfile/scp -rp /tmp/dg.ctl nod

31、e2:/u01/flash_recovery_area/orcl/controlfile/13. 在備庫上創(chuàng)建口令文件orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=XXXX entries=1014. 創(chuàng)建備庫的spfile 修改成功后進(jìn)行啟動(dòng)驗(yàn)證：1、Startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora;2、如果可以啟動(dòng)成功，就創(chuàng)建spfile：Create spfile from pfile='/u01/app/oracle/product/10

32、.2.0/db_1/dbs/initorcl.ora;3、如果不成功，查看pfile是否修改錯(cuò)誤。4、創(chuàng)建成功后進(jìn)行spfile方式啟動(dòng)數(shù)據(jù)庫進(jìn)行驗(yàn)證。Shutdown immediatestartup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'CREATE SPFILE FROM PFILE;15. 啟動(dòng)物理備用數(shù)據(jù)庫STARTUP MOUNT;16. 配置Standby Redo Log主庫和備庫都配置standby redo log，在主庫查看日志組的數(shù)量和每個(gè)日志文件的大小SQL> S

33、ELECT GROUP#, BYTES FROM V$LOG;在備庫庫查看日志組的數(shù)量和每個(gè)日志文件的大小，standby日志文件組文件大小和redo log大小一樣，需要比redo log多一組或者幾組。SQL> SELECT GROUP#, BYTES/1024/1024 FROM V$STANDBY_LOG;創(chuàng)建日志組和redo log文件SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4('/oracle/dbs/slog1.rdo') SIZE 50M; SQL> ALTER DATABASE ADD ST

34、ANDBY LOGFILE group 5 ('/oracle/dbs/slog2.rdo') SIZE 50M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/oracle/dbs/slog3.rdo') SIZE 50M;SQL> ALTER DATABASE ADD STANDBY LOGFILE group 7('/oracle/dbs/slog4.rdo') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE group 7('/

35、u01/app/oracle/oradata/standby/sredo07.log') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE group 7('/u01/app/oracle/oradata/orcl/sredo07.log') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE group 7('/u01/app/oracle/product/10.2.0/db_1/oradata/standby/sredo07.log') size 50M; 17. Redo A

36、pply的啟動(dòng)和停止啟動(dòng)日志恢復(fù)：ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Alter database recover managed standby database disconnect from session;關(guān)閉日志恢復(fù)：alter database recover managed standby database cancel;對(duì)于物理備庫(physical standby)來說，處于日志恢復(fù)模式的時(shí)候數(shù)據(jù)庫是不能打開的。如果要打開只

37、能先關(guān)閉日志恢復(fù)模式，然后以read only （只讀方式）打開。（oracle11G版本可以在read only下應(yīng)用歸檔日志）SQL>alter database recover managed standby database cancel;停止日志恢復(fù)模式SQL>alter database open read only;打開數(shù)據(jù)庫。物理備庫在只讀模式下可以增加臨時(shí)文件。SQL> alter database temp add tempfile '/opt/oradata/orcl/temp02.dbf' size 100m reuse;這個(gè)時(shí)候備庫還

38、是可以接收主庫傳輸過來的歸檔日志文件，只是不能應(yīng)用日志而已。當(dāng)用read only 方式打開備庫后只有重新啟動(dòng)才能使備庫重新回到備用模式。進(jìn)行主備庫驗(yàn)證：在備庫SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在主庫強(qiáng)制日志切換到當(dāng)前的online redo log file.ALTER SYSTEM ARCHIVE LOG CURRENT;在備庫查看新的被歸檔的redo dataSELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY

39、SEQUENCE#;在備庫查看接收到的被應(yīng)用的redoSQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;查看數(shù)據(jù)庫的角色select database_role,protection_mode,protection_level from v$database;l 主備庫切換1.查看主庫的狀態(tài)SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;2.將主庫切換至備用模式SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO

40、 PHYSICAL STANDBY WITH -> SESSION SHUTDOWN;3.關(guān)閉、裝載主數(shù)據(jù)庫SQL> SHUTDOWN ABORT;SQL> STARTUP MOUNT;4.查看備庫準(zhǔn)備向主庫模式切換SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO_PRIMARY1 row selected5.切換備庫至主庫模式SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;6.打開

41、新的主數(shù)據(jù)庫SQL> ALTER DATABASE OPEN;7. 在新的備庫服務(wù)器上啟動(dòng) REDO apply。 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -> DISCONNECT FROM SESSION;l 災(zāi)難恢復(fù)（failover）Step 1 Flush any unsent redo from the primary database to the target standbydatabaseSQL> ALTER SYSTEM FLUSH REDO

42、 TO target_db_name;Step 2 Verify that the standby database has the most recently archived redo logfile for each primary database redo thread.SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;SQL> ALTER DATABASE REGISTER PHYSICAL

43、LOGFILE 'filespec1'Step 3 Identify and resolve any archived redo log gaps.SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1'Step 4 Repeat Step 3 until all gaps are resolved.Step 5 Stop Redo Apply.Issue the