dataguard配置手冊

1、文件標(biāo)識(shí)版 本 號(hào)密 級(jí)ORACLE10G DATAGUARD PHYISCAL STANDBY配置指導(dǎo)編制蘇波日期2013-05-08審核日期批準(zhǔn)日期修訂記錄版本編號(hào)版本日期修訂人說明 目 錄1、部署環(huán)境：42、準(zhǔn)備工作43、正式開始配置53.1 設(shè)置主數(shù)據(jù)庫為 force logging 模式53.2 設(shè)置主數(shù)據(jù)庫為歸檔模式53.3 數(shù)據(jù)文件拷貝53.31、拷貝主庫的數(shù)據(jù)文件到備庫中53.32、創(chuàng)建從庫的控制文件63.33 從庫創(chuàng)建 admin 目錄63.34 在從庫上創(chuàng)建密碼文件63.35 創(chuàng)建從庫 flash_recovery_area 目錄73.4修改 spfile 文件73.41、

2、創(chuàng)建 pfile 文件73.42、創(chuàng)建 spfile 文件93.5創(chuàng)建備機(jī)redo日志103.6配置網(wǎng)絡(luò)，修改 listener.ora ,tnsnames.ora103.61 配置103.62 測試網(wǎng)絡(luò)配置143.7啟動(dòng)主備服務(wù)143.71 主庫153.72 備庫153.73 測試歸檔154、日常維護(hù)164.1 正確打開主庫和備庫164.2 正確關(guān)閉順序164.3 備庫Read-only模式打開164.4 日志傳輸狀態(tài)監(jiān)控174.5 備庫歸檔目錄維護(hù)175、主庫正常切換185.1人工干預(yù)正常切換185.2 通過運(yùn)行腳本實(shí)現(xiàn)主庫正常切換196、主庫災(zāi)難切換196.1 人工干預(yù)主庫災(zāi)難切換196

3、.2通過運(yùn)行腳本實(shí)現(xiàn)主庫災(zāi)難切換201、部署環(huán)境：Oracle 10G DataGuard主庫：HOSTNAME: primary IP: 1 SID=ccpbs備庫：HOSTNAME: standby IP: 2 SID=ccpbsoracle version:OS platform : OEL5.4VMware 運(yùn)行 主備虛擬機(jī)Data Guard 默認(rèn)是 maximize performance， 可以用以下語句來進(jìn)行模式間的切換。ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTE

4、CTION |AVAILABILITY | PERFORMANCE;修改主數(shù)據(jù)庫保護(hù)模式需要重啟數(shù)據(jù)庫才能生效。select protection_mode,protection_level from v$database;2、準(zhǔn)備工作1、在主備機(jī)分別安裝 OEL5.4 操作系統(tǒng)分別設(shè)置 HOSTNAME 為 “odd”、“even”；2、在主備機(jī)上分別安裝 oracle 10gR2 數(shù)據(jù)庫，安裝時(shí)注意選擇“高級(jí)”，只安裝“數(shù)據(jù)庫軟件；”3、只在主機(jī)上用 dbca 新建數(shù)據(jù)庫實(shí)例，選擇默認(rèn)選項(xiàng)，SID 名和 SERVICE 名設(shè)定為“ccpbs”；參數(shù)規(guī)劃：數(shù)據(jù)庫db_namesidinsta

5、nce_nameservice_namesdb_unique_namefal_serverfal_client主庫ccpbsccpbsccpbspripristdbypri備庫ccpbsccpbsccpbsStdbystdbypristdby3、正式開始配置3.1 設(shè)置主數(shù)據(jù)庫為 force logging 模式$ sqlplus / nolog;SQL> conn / as sysdba;SQL> alter database force logging;3.2 設(shè)置主數(shù)據(jù)庫為歸檔模式SQL> archive log listSQL> shutdown immedia

6、teSQL> startup mountSQL> alter database archivelog;SQL> archive log list3.3 數(shù)據(jù)文件拷貝數(shù)據(jù)文件的拷貝可以分為兩種模式：rman 和文件的直接拷貝，由于我們的系統(tǒng)可以停機(jī)，所以會(huì)采用第二種方式。注意不用拷貝 redo 文件，備用機(jī)在啟動(dòng)的時(shí)候會(huì)自動(dòng)的創(chuàng)建 redo 文件?？刂莆募膊挥每截悺?.31、拷貝主庫的數(shù)據(jù)文件到備庫中SQL> SELECT NAME FROM v$datafile;SQL> select name from v$tempfile;把主庫的以上數(shù)據(jù)文件拷貝到備庫上同

7、樣目錄3.32、創(chuàng)建從庫的控制文件在主庫上為從庫創(chuàng)建控制文件(不要和主庫控制文件重名),然后拷貝到從庫/home/oracle/oracle10g/oradata/ccpbs/ 目錄下。（記住這個(gè)目錄，在下面從庫的 spfile 配置中要用到）。SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/standby.ctl'3.33 從庫創(chuàng)建 admin 目錄將主庫的 /home/oracle/oracle10g/admin/ccpbs 目錄及文件夾拷貝到從庫的相同目錄。3.34 在從庫上創(chuàng)建密碼文件

8、缺省情況下， linux 下的格式是 orapwSID(大小寫敏感）在$ORACLE_HOME/dbs目錄下，創(chuàng)建密碼文件：orapwd file=orapwccpbs password=oracle建議直接拷貝主庫該位置的文件到備庫對應(yīng)位置。3.35 創(chuàng)建從庫 flash_recovery_area 目錄在 /home/oracle/oracle10g目錄中創(chuàng)建 flash_recover_area 目錄，該目錄參數(shù)在從庫中有設(shè)定，為*.db_recovery_file_dest 項(xiàng)的值，如果沒有會(huì)出錯(cuò)。3.4修改 spfile 文件說明：我們在這里需要先根據(jù) spfile生成 pfile

9、文件，然后修改好對應(yīng)的參數(shù)值，再依據(jù) pfile 生成 spfile 文件。3.41、創(chuàng)建 pfile 文件在主庫上創(chuàng)建 pfile 文件SQL> CREATE PFILE FROM SPFILE;生成的pfile文件，默認(rèn)格式為 initSID.ora，此處為initccpbs.ora文件路徑為/home/oracle/oracle10g/product/10.2.0/db_1/dbs將主庫生成的pfile文件，拷貝到從庫相同目錄下。修改主庫的 initccpbs.ora 文件如下，紅色部分為需要新加入的內(nèi)容，藍(lán)色部分是特別需要注意的部分：-initccpbs.ora-ccpbs._d

10、b_cache_size=188743680ccpbs._java_pool_size=4194304ccpbs._large_pool_size=4194304ccpbs._shared_pool_size=83886080ccpbs._streams_pool_size=0*.audit_file_dest='/home/oracle/oracle10g/admin/ccpbs/adump'*.background_dump_dest='/home/oracle/oracle10g/admin/ccpbs/bdump'*.compatible='10

11、.'*.control_files='/home/oracle/oracle10g/oradata/ccpbs/control01.ctl','/home/oracle/oracle10g/oradata/ccpbs/control02.ctl','/home/oracle/oracle10g/oradata/ccpbs/control03.ctl'*.core_dump_dest='/home/oracle/oracle10g/admin/ccpbs/cdump'*.db_block_size=8192*.

12、db_domain=''*.db_file_multiblock_read_count=16*.db_name='ccpbs'*.db_recovery_file_dest='/home/oracle/oracle10g/flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.dispatchers='(PROTOCOL=TCP) (SERVICE=ccpbsXDB)'*.job_queue_processes=10*.nls_language='SIMPL

13、IFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=300*.pga_aggregate_target=94371840*.processes=300*.remote_login_passwordfile='EXCLUSIVE'*.sessions=335*.sga_target=285212672*.undo_management='AUTO'*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/home/oracle/o

14、racle10g/admin/ccpbs/udump'*.db_unique_name='pri'*.fal_server='stdby'*.fal_client='pri'*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,stdby)'*.log_archive_dest_1='LOCATION=/home/oracle/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'*.LOG_ARCHIVE_DEST_2

15、='SERVICE=stdby LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby'*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.standby_archive_dest='/home/oracle/arch1/'*.standby_file_management='AUTO'-修改從庫的 initccpbs.or

16、a文件加入如下：-initccpbs.ora-ccpbs._db_cache_size=188743680ccpbs._java_pool_size=4194304ccpbs._large_pool_size=4194304ccpbs._shared_pool_size=83886080ccpbs._streams_pool_size=0*.audit_file_dest='/home/oracle/oracle10g/admin/ccpbs/adump'*.background_dump_dest='/home/oracle/oracle10g/admin/ccpbs

17、/bdump'*.compatible='.0'*.control_files='/home/oracle/oracle10g/oradata/ccpbs/stdby.ctl'*.core_dump_dest='/home/oracle/oracle10g/admin/ccpbs/cdump'*.db_block_size=8192*.db_domain=''*.db_file_multiblock_read_count=16*.db_name='ccpbs'*.db_recovery_fi

18、le_dest='/home/oracle/oracle10g/flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.dispatchers='(PROTOCOL=TCP) (SERVICE=ccpbsXDB)'*.job_queue_processes=10*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=300*.pga_aggregate_target=94371

19、840*.processes=300*.remote_login_passwordfile='EXCLUSIVE'*.sessions=335*.sga_target=285212672*.undo_management='AUTO'*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/home/oracle/oracle10g/admin/ccpbs/udump'*.db_unique_name='stdby'*.fal_server='pri'*.fal_

20、client='stdby'*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,stdby)'*.log_archive_dest_1='LOCATION=/home/oracle/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'*.LOG_ARCHIVE_DEST_2='SERVICE=pri LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=

21、pri'*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_file_name_convert='/home/oracle/arch1','/home/oracle/arch1'*.standby_archive_dest='/home/oracle/arch1/'*.standby_file_management='AUTO' -3.42、創(chuàng)建 spfile 文件從庫：備份原spfile文件

22、，將修改完成的 pfile 重新生成spfile文件SQL> CREATE SPFILE FROM PFILE;從庫：SQL>CREATE SPFILE FROM PFILE;啟動(dòng)從庫到mount狀態(tài)SQL>startup mount;3.5創(chuàng)建備機(jī)redo日志因?yàn)槲覀兪褂?LGWR 所以我們要?jiǎng)?chuàng)建 standby redo 日志。一定要日志注意的大小，要和主庫一樣。萬一加錯(cuò)了日志可以使用下面的語句進(jìn)行刪除。ALTER DATABASE DROP LOGFILE GROUP 8;先查看日志文件位置：SQL>select * from v$logfile;再添加：SQL&

23、gt; alter database add standby logfile group 4 /home/oracle/oracle10g/oradata/ccpbs/redo04.log size 50m;SQL> alter database add standby logfile group 5 /home/oracle/oracle10g/oradata/ccpbs/redo05.log size 50m;SQL> alter database add standby logfile group 6 /home/oracle/oracle10g/oradata/ccpbs/

24、redo06.log size 50m;SQL> alter database add standby logfile group 7 /home/oracle/oracle10g/oradata/ccpbs/redo07.log size 50m;3.6配置網(wǎng)絡(luò)，修改 listener.ora ,tnsnames.ora3.61 配置先在主從庫上分別用 netca 命令配置監(jiān)聽和本地服務(wù)名，再分別編輯主、從庫的 listener.ora,tnsnames.ora，參數(shù)以下主庫的 listener.ora 文件如下：# listener.ora Network Configuration

25、 File: /home/oracle/oracle10g/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/oracle10g/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC= (GLOBAL_DBNAME=ccpbs)

26、(ORACLE_HOME=/home/oracle/oracle10g/product/10.2.0/db_1) (SID_NAME=ccpbs) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = odd)(PORT = 1521) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) ) #主庫的 tnsnames.ora 文件如下：# tnsnames.ora Network Configuration File: /home/orac

27、le/oracle10g/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.CCPBS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ccpbs) ) )pri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCO

28、L = TCP)(HOST = 1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ccpbs) ) )stdby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 2)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ccpbs) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PRO

29、TOCOL = IPC)(KEY = EXTPROC0) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) #從庫的 listeners.ora 如下# listener.ora Network Configuration File: /home/oracle/oracle10g/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SI

30、D_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/oracle10g/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC= (GLOBAL_DBNAME=ccpbs) (ORACLE_HOME=/home/oracle/oracle10g/product/10.2.0/db_1) (SID_NAME=ccpbs) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HO

31、ST = even)(PORT = 1521) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) ) #從庫的 tnsnames.ora 文件如下：# tnsnames.ora Network Configuration File: /home/oracle/oracle10g/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.CCPBS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (

32、PROTOCOL = TCP)(HOST = 2)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ccpbs) ) )pri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ccpbs) ) )stdby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(

33、HOST = 2)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ccpbs) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) #在上面配置完成之后，需要在主、備機(jī)上重啟監(jiān)聽服務(wù)$ Lsnrctl stop$ lsnrctl start可以使用 lsn

34、rctl status命令查看監(jiān)聽器狀態(tài)3.62 測試網(wǎng)絡(luò)配置如果配置成功，在主機(jī)上測試$ tnsping pri$ tnsping stdby 也能得到相似結(jié)果$ sqlplus sys/oraclepri as sysdba;$ sqlplus sys/oraclestdby as sysdba; 也能得到相似結(jié)果在備機(jī)上執(zhí)行上面四條指令也能得到正確的結(jié)果，則說明網(wǎng)絡(luò)配置成功。3.7啟動(dòng)主備服務(wù)上面配置完成后先將主、備數(shù)據(jù)庫關(guān)閉SQL> shutdown immediate;然后分別在主、備機(jī)上重啟監(jiān)聽器CMD> lsnrctl stopCMD> lsnrctl star

35、t然后依次打開主庫和備庫3.71 主庫SQL> CONN / AS SYSDBASQL> STARTUP3.72 備庫SQL> STARTUP MOUNT;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECTFROM SESSION;到次為止，最關(guān)鍵的時(shí)刻來了，我們要測試看是否配置成功，如果成功的話在主機(jī)上的歸檔就能順利傳到3.73 測試歸檔1. 測試主庫產(chǎn)生的歸檔日志是否能正常傳送到歸檔日志在主機(jī)上SQL>archive log list;SQL>alter system archi

36、ve log current;SQL>archive log list;主庫進(jìn)行日志切換:SQL>Alter system switch logfile;然后分別查看主庫和備庫的 /home/oracle/arch1 目錄下是否產(chǎn)生了同樣的歸檔日志文件。select max(sequence#) from v$archived_log;select max(sequence#) from v$log_history;select group#,sequence#,archived,status from v$log;select name,sequence#,applied fro

37、m v$archived_log;select sequence#,applied from v$archived_log;若不同步，1. 看 log 日志， archive 是否有丟失2. 可以在備庫做如下操作：alter database recover managed standby database cancel;alter database recover managed standby database disconnect from session;4、日常維護(hù)4.1 正確打開主庫和備庫主庫:SQL> CONN / AS SYSDBASQL> STARTUP備庫:SQ

38、L> STARTUP MOUNT;SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;4.2 正確關(guān)閉順序備庫:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL>SHUTDOWN IMMEDIATE;主庫SQL>SHUTDOWN IMMEDIATE;4.3 備庫Read-only模式打開當(dāng)前主庫正常 OPEN 狀態(tài)、備庫處于日志傳送狀態(tài).1 在備庫停止日志傳送SQL> recover

39、 managed standby database cancel;2 備庫 Read-only 模式打開SQL> alter database open read only;3 備庫回到日志傳送模式SQL> recover managed standby database disconnect from session;SQL> select status from v$instance;4.4 日志傳輸狀態(tài)監(jiān)控1 主庫察看當(dāng)前日志狀況SQL> select sequence#,status from v$log;2 備庫察看 RFS(Remote File Servi

40、ce)接收日志情況和 MRP 應(yīng)用日志同步主庫情況SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKSFROM V$MANAGED_STANDBY;3 察看備庫是否和主庫同步SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;4 察看備庫已經(jīng)歸檔的 redoSQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#

41、, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;5 察看備庫已經(jīng)應(yīng)用的 redoSQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#FROM V$LOG_HISTORY;6 察看備庫接收,應(yīng)用 redo 數(shù)據(jù)過程.SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;4.5 備庫歸檔目錄維護(hù)1 找到備庫歸檔目錄SQL> show parameter log_archive_dest_12 維護(hù)策略每周 2,4,7 刪除已經(jīng)應(yīng)用的歸檔文件5、主庫正常切換5.1人工干預(yù)正常切換1 在主庫端檢驗(yàn)數(shù)據(jù)庫可切換狀態(tài)SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS:TO STANDBY 表示可以正常切換.如果 SWITCHOVER_STATUS 的值為 SESSIONS ACTIVE,表示當(dāng)前有會(huì)話處于 ACTIVE