DataGuard日常維護(hù)技巧

1、正確的開關(guān)機(jī)順序是： 啟動(dòng)的時(shí)候，先備庫(kù)的listener,再啟動(dòng)備庫(kù),再啟動(dòng)主庫(kù)的listener,再啟動(dòng)主庫(kù)。 關(guān)閉的時(shí)候，先關(guān)閉主庫(kù)，再關(guān)閉備庫(kù)。 -為主數(shù)據(jù)庫(kù)或備用數(shù)據(jù)庫(kù)添加/刪除日志組 sql alter database add standby logfile group 5 /oracle/oradata/orcl/standbyredo05.log size 100m; sql alter database drop standby logfile group 5; -查詢dataguard當(dāng)前處于哪種日志傳輸方式： sql select process,client_proc

2、ess,sequence#,status from v$managed_standby; process列顯示進(jìn)程信息 client_process列顯示對(duì)應(yīng)的主數(shù)據(jù)庫(kù)中的進(jìn)程 sequence#列顯示歸檔redo的序列號(hào) status列顯示的進(jìn)程狀態(tài) -查詢standby庫(kù)中所有已被應(yīng)用的歸檔文件信息(不論該歸檔文件是否還存在) sql select first_time,first_change#,next_change#,sequence# from v$log_history; -最后一個(gè)被應(yīng)用的log sql select thread#, max(sequence#) as las

3、t_applied_log from v$log_history group by thread#; -在primary server上查詢有哪些日志沒有被傳輸?shù)絪tandby sql select local.thread#, local.sequence# from (select thread#, sequence# from v$archived_log where dest_id=1) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread#

4、 = local.thread#); -對(duì)于troubleshooting有用 sql select message from v$dataguard_status; m.監(jiān)控恢復(fù)操作的進(jìn)程 select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status; -從正在恢復(fù)狀態(tài)只讀打開; sql alter database recover managed standby database cancel; sql alter database open read onl

5、y; -切換回到恢復(fù)狀態(tài); sql alter database recover managed standby database disconnect from session; 切換角色 注意：swithover時(shí)只能先從primary切到standby，再?gòu)膕tandby切到primary. 1、在主庫(kù)端 select database_role,switchover_status from v$database; 如果是to standby 表可以正常切換. 直接執(zhí)行 alter database commit to switchover to physical standby; 否則

6、執(zhí)行: alter database commit to switchover to physical standby with session shutdown; sql shutdown immediate; sql startup nomount; sql alter database mount standby database; sql select database_role from v$database; sql alter database recover managed standby database disconnect from session; 2、在備庫(kù)端 sel

7、ect database_role,switchover_status from v$database; 如果是to_primary 表可以正常切換. 執(zhí)行:alter database commit to switchover to primary; 否則執(zhí)行: alter database commit to switchover to primary with session shutdown; shutdown immediate; startup; 然后觀察主備庫(kù)日志,如果正常的話會(huì)看到備庫(kù)會(huì)自動(dòng)應(yīng)用日志. failover測(cè)試 1. 備庫(kù)上檢查是否存在歸檔中斷 sql select

8、 thread#, low_sequence#, high_sequence# from v$archive_gap; 2. 在主庫(kù)上執(zhí)行語句并找出歸檔文件 sql select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between xx and xx; -如果存在拷貝相應(yīng)的歸檔到standby數(shù)據(jù)庫(kù)，并注冊(cè). sql alter database register physical logfile xxx; *其他情況(primary數(shù)據(jù)庫(kù)無法打開)： -檢查歸檔文件是否完整 分別在prima

9、ry/standby執(zhí)行下列語句： sql select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log; 把相差的歸檔復(fù)制到待轉(zhuǎn)換的standby服務(wù)器 3. 啟動(dòng)failover sql alter database recover managed standby database finish force; force關(guān)鍵字將會(huì)停止當(dāng)前活動(dòng)的rfs進(jìn)程，以便立刻執(zhí)行failover。 或 sql alter database recover managed standby d

10、atabase finish skip standby logfile; 4. 切換物理standby角色為primary sql alter database commit to switchover to primary; sql shutdown immediate sql startup本篇文章來源于：開發(fā)學(xué)院 原文鏈接： 袁艿莇袂肆膅蒆薂衿肁蒅蚄肄羇蒄螆袇莆蒃薆膃節(jié)蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蕆羀肆薇蠆螃蒞薆螁聿芁薅襖袂膇薄薃肇肅薃蚆袀莂螞螈肅羋蟻袀袈膄蟻薀肄膀芇螂袆肆芆裊膂莄芅薄羅芀芅蚇膀膆芄蝿羃肂莃袁螆莁莂薁羈芇莁蚃螄芃莀袆肀腿荿薅袂肅荿蚇肈莃莈螀袁艿莇袂肆膅蒆薂衿肁蒅蚄肄羇蒄螆袇莆蒃薆膃節(jié)蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蕆羀肆薇蠆螃蒞薆螁聿芁薅襖袂膇薄薃肇肅薃蚆袀莂螞螈肅羋蟻袀袈膄蟻薀肄膀芇螂袆肆芆裊膂莄芅薄羅芀芅蚇膀膆芄蝿羃肂莃袁螆莁莂薁羈芇莁蚃螄芃莀袆肀腿荿薅袂肅荿蚇肈莃莈螀袁艿莇袂肆膅蒆薂衿肁蒅蚄肄羇蒄螆袇莆蒃薆膃節(jié)蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蕆羀肆薇蠆螃蒞薆螁聿芁薅襖袂膇薄薃肇肅薃蚆袀莂螞螈肅羋蟻