DataGuard日常維護技巧

1、正確的開關(guān)機順序是： 啟動的時候，先備庫的listener,再啟動備庫,再啟動主庫的listener,再啟動主庫。 關(guān)閉的時候，先關(guān)閉主庫，再關(guān)閉備庫。 -為主數(shù)據(jù)庫或備用數(shù)據(jù)庫添加/刪除日志組 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列顯示進程信息 client_process列顯示對應(yīng)的主數(shù)據(jù)庫中的進程 sequence#列顯示歸檔redo的序列號 status列顯示的進程狀態(tài) -查詢standby庫中所有已被應(yīng)用的歸檔文件信息(不論該歸檔文件是否還存在) sql select first_time,first_change#,next_change#,sequence# from v$log_history; -最后一個被應(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#); -對于troubleshooting有用 sql select message from v$dataguard_status; m.監(jiān)控恢復(fù)操作的進程 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時只能先從primary切到standby，再從standby切到primary. 1、在主庫端 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、在備庫端 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; 然后觀察主備庫日志,如果正常的話會看到備庫會自動應(yīng)用日志. failover測試 1. 備庫上檢查是否存在歸檔中斷 sql select

8、 thread#, low_sequence#, high_sequence# from v$archive_gap; 2. 在主庫上執(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ù)庫，并注冊. sql alter database register physical logfile xxx; *其他情況(primary數(shù)據(jù)庫無法打開)： -檢查歸檔文件是否完整 分別在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. 啟動failover sql alter database recover managed standby database finish force; force關(guān)鍵字將會停止當(dāng)前活動的rfs進程，以便立刻執(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é)蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蕆羀肆薇蠆螃蒞薆螁聿芁薅襖袂膇薄薃肇肅薃蚆袀莂螞螈肅羋蟻