DataGuard日常维护技巧

1、正确的开关机顺序是： 启动的时候，先备库的listener,再启动备库,再启动主库的listener,再启动主库。 关闭的时候，先关闭主库，再关闭备库。 -为主数据库或备用数据库添加/删除日志组 sql alter database add standby logfile group 5 /oracle/oradata/orcl/standbyredo05.log size 100m; sql alter database drop standby logfile group 5; -查询dataguard当前处于哪种日志传输方式： sql select process,client_proc

2、ess,sequence#,status from v$managed_standby; process列显示进程信息 client_process列显示对应的主数据库中的进程 sequence#列显示归档redo的序列号 status列显示的进程状态 -查询standby库中所有已被应用的归档文件信息(不论该归档文件是否还存在) sql select first_time,first_change#,next_change#,sequence# from v$log_history; -最后一个被应用的log sql select thread#, max(sequence#) as las

3、t_applied_log from v$log_history group by thread#; -在primary server上查询有哪些日志没有被传输到standby 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.监控恢复操作的进程 select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status; -从正在恢复状态只读打开; sql alter database recover managed standby database cancel; sql alter database open read onl

5、y; -切换回到恢复状态; 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 表可以正常切换. 直接执行 alter database commit to switchover to physical standby; 否则

6、执行: 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 表可以正常切换. 执行:alter database commit to switchover to primary; 否则执行: alter database commit to switchover to primary with session shutdown; shutdown immediate; startup; 然后观察主备库日志,如果正常的话会看到备库会自动应用日志. failover测试 1. 备库上检查是否存在归档中断 sql select

8、 thread#, low_sequence#, high_sequence# from v$archive_gap; 2. 在主库上执行语句并找出归档文件 sql select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between xx and xx; -如果存在拷贝相应的归档到standby数据库，并注册. sql alter database register physical logfile xxx; *其他情况(primary数据库无法打开)： -检查归档文件是否完整 分别在prima

9、ry/standby执行下列语句： sql select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log; 把相差的归档复制到待转换的standby服务器 3. 启动failover sql alter database recover managed standby database finish force; force关键字将会停止当前活动的rfs进程，以便立刻执行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本篇文章来源于：开发学院 原文链接： 袁艿莇袂肆膅蒆薂衿肁蒅蚄肄羇蒄螆袇莆蒃薆膃节蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蒇羀肆薇虿螃莅薆螁聿芁薅袄袂膇薄薃肇肃薃蚆袀莂蚂螈肅芈蚁袀袈膄蚁薀肄膀芇螂袆肆芆袅膂莄芅薄羅芀芅蚇膀膆芄蝿羃肂莃袁螆莁莂薁羁芇莁蚃螄芃莀袆肀腿荿薅袂肅荿蚇肈莃莈螀袁艿莇袂肆膅蒆薂衿肁蒅蚄肄羇蒄螆袇莆蒃薆膃节蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蒇羀肆薇虿螃莅薆螁聿芁薅袄袂膇薄薃肇肃薃蚆袀莂蚂螈肅芈蚁袀袈膄蚁薀肄膀芇螂袆肆芆袅膂莄芅薄羅芀芅蚇膀膆芄蝿羃肂莃袁螆莁莂薁羁芇莁蚃螄芃莀袆肀腿荿薅袂肅荿蚇肈莃莈螀袁艿莇袂肆膅蒆薂衿肁蒅蚄肄羇蒄螆袇莆蒃薆膃节蒃蚈羆膈蒂螁膁肄蒁袃羄莃蒀薃螇艿蕿蚅羂膄薈螇螅肀薇蒇羀肆薇虿螃莅薆螁聿芁薅袄袂膇薄薃肇肃薃蚆袀莂蚂螈肅芈蚁