Oracle 11G DG之Duplicate方式搭建
DG搭建
1.DG之RMAN Duplicate方式
環(huán)境：
IP DB_NAME DB_UNIQUE_NAME ORACLE_SID DB PORT
主庫：192.168.1.69 hsidb hsidbpr hsidb 1525
備庫：192.168.1.70 hsidb hsidbsd hsidb 1525
#Active Database Duplicate步驟
a.根據(jù)主庫設(shè)置參數(shù)后的PFILE,備庫根據(jù)主庫的PFILE,設(shè)置參數(shù)值,生成備庫SPFILE.
b.根據(jù)主庫的密碼文件,生成備庫的密碼文件.
c.把備庫啟動到nomount狀態(tài).
d.RMAN同時(shí)連接主庫與備庫,執(zhí)行duplicate命令.
###主庫
1.1 主/備庫安裝Oracle Software及靜態(tài)監(jiān)聽及TNS,主庫DBCA建庫.
cat /etc/hosts
192.168.1.70 rrfuwu-29.beidou rrfuwu-29
192.168.1.69 rrfuwu-28.beidou rrfuwu-28
1.2 主庫查看歸檔模式
SQL> archive log list;
1.3 開啟force_logging
SQL>select NAME,FORCE_LOGGING from v$database;
SQL>shutdown immediate;
SQL>startup mount
SQL>alter database force logging;
SQL>alter database open;
1.4 密碼文件
查看主庫是否存在,密碼文件,如果存在,scp到備份,因?yàn)榇颂幹鱾鋷霴RACLE_SID相同,故備庫可以直接使用.
注:主備庫密碼文件密碼一定要相同.
[oracle@rrfuwu-28 dbs]$scp -rp orapwhsidb 192.168.1.70:/u01/app/oracle/product/11.2.0/db_1/dbs
1.5 主庫添加standby logfile
standby logfile=(1+logfile組數(shù))*thread=(1+3)*1=4組,需要加4組standby logfile.
查看主庫logfile
主庫添加standby logfile
SQL>alter database add standby logfile 'x' size 300M;
1.6 主庫創(chuàng)建pfile
SQL> create pfile from spfile;
主庫inithsidb.ora備份, 可進(jìn)行DB原參數(shù)值還原.
vim inithsidb.ora 加入如下參數(shù)
注： 主備庫數(shù)據(jù)文件與日志文件路徑相同, 做 rman duplicate時(shí)參數(shù)db_file_name_convert 與log_file_name_convert 也需要設(shè)置,如果兩個(gè)參數(shù)不設(shè)置,做duplicate時(shí)會報(bào)
"ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log N thread P:' xxxxx';" (N為log日志組號,P為thread號,xxxxx為日志路徑代替).
*.db_unique_name=hsidbpr
*.log_archive_config='DG_CONFIG=(hsidbpr,hsidbsd)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hsidbpr'
*.log_archive_dest_2='SERVICE=hsidbsd LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hsidbsd'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.FAL_SERVER=hsidbsd
*.FAL_CLIENT=hsidbpr
*.db_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.log_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.standby_file_management=AUTO
關(guān)閉實(shí)例,根據(jù)修改后的pfile,創(chuàng)建spfile.
1.7 主庫scp pfile到備庫
注: 主備庫密碼文件特權(quán)用戶密碼要相同.
[oracle@rrfuwu-28 dbs]$ scp -rp inithsidb.ora 192.168.1.70:/u01/app/oracle/product/11.2.0/db_1/dbs
###備庫
2.1 根據(jù)專到備庫的主庫pfile,進(jìn)行相關(guān)DG參數(shù)修改.
*.db_unique_name=hsidbsd
*.log_archive_config='DG_CONFIG=(hsidbpr,hsidbsd)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hsidbsd'
*.log_archive_dest_2='SERVICE=hsidbpr LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hsidbpr'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.FAL_SERVER=hsidbpr
*.FAL_CLIENT=hsidbsd
*.db_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.log_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.standby_file_management=AUTO
2.2 備庫創(chuàng)建相關(guān)目錄
根據(jù)pfile中的目錄信息,在備庫創(chuàng)建相關(guān)目錄
[oracle@rrfuwu-29 ~]$ mkdir -p /u01/app/oracle/admin/hsidb/adump
[oracle@rrfuwu-29 ~]$ mkdir -p /u01/app/oracle/oradata/hsidb/
[oracle@rrfuwu-29 ~]$ mkdir -p /u01/app/oracle/arch
2.3 備庫startup nomount
SQL> startup nomount pfile=?/dbs/inithsidb.ora;
2.4 RMAN DUPLICATE
[oracle@rrfuwu-29 ~]$ rman target sys/SIGasmlib@HSIDBPR auxiliary sys/SIGasmlib@HSIDBSD
RMAN>duplicate target database for standby from active database nofilenamecheck dorecover;
......中間過程省略................
2.5 logfile應(yīng)用
SQL>alter database open;
SQL>alter database recover managed standby database using current logfile disconnect from session;
SQL>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;
備庫啟動到open狀態(tài).
DB在進(jìn)行alter database recover managed standby database using current logfile disconnect from session后產(chǎn)生MRP進(jìn)程,進(jìn)行l(wèi)ogfile恢復(fù). RFS進(jìn)程為接受主庫日志功能.
現(xiàn)在為止查看主庫狀態(tài), 保護(hù)模式為最大性能模式, DB角色為PRIMARY
SQL>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;
查看備狀態(tài),保護(hù)模式為最大性能模式, DB角色為PHYSICAL STANDBY.
此處我們要搭建DG如果保護(hù)模式為最大可用性模式,故下面需要做DG模式轉(zhuǎn)換.
2.6 查看主備庫日志是否同步.
sql>archive log list;
sql>select unique(thread#),max(sequence#) over(partition by thread#) from v$archived_log;
切歸檔之前---主庫日志sequence
切歸檔之前----備庫日志sequence, 查詢主備庫日志sequence在切歸檔前同步.
手動切歸檔測試.
切歸檔之后-----主庫日志sequence
切歸檔之后----備庫日志sequence
主/備庫日志是同步的.
2.7 最大可用性模式
SQL>alter database set standby database to maximize availability;
主庫進(jìn)行切換.
查看備庫已經(jīng)由最大性能模式切換為最大可用性模式.
2.8 主備庫switchover切換測試.
SQL>alter database commit to switchover to physical standby;
SQL>shutdown immediate;
SQL>startup
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;
備庫切主庫.
SQL>alter database recover managed standby database cancel;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate;
SQL>startup
SQL>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;
注:
做RMAN DUPLICATE時(shí),主/備庫數(shù)據(jù)庫目錄結(jié)構(gòu)即使相同,參數(shù).db_file_name_convert與 log_file_name_convert也需要配置, 如果不配置,最后做rman duplicate時(shí)會報(bào)錯 "ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed ORA-00312: online log N thread P:' xxxxx';" (N為log日志組號,P為thread號,xxxxx為日志路徑代替).
主/備庫duplicate后, 備庫TNSNAMES.ORA中多了一個(gè)LISTENER_HSIDB主庫的監(jiān)聽信息,rrfuwu-28為主庫的HOSTNAME,故此處可以寫成主庫IP,或把rrfuwu-28的域名解析寫到備庫/etc/hosts中.此處如果忘記修改,備庫做主備庫切換,startup時(shí)會報(bào)錯. “ORA-00119: invalid specification for system parameter LOCAL_LISTENER ”
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=rrfuwu-28)(PORT=1525))'
