258
225
技术宅 篇四：技术分享第四波之oracle ADG主备库切换
2020-03-15 14:02:51
1点赞
3收藏
0评论
一、查看gap，如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
no rows selected
二、确认主、备库可切换角色
主：select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
备：select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
主库需要注意事项：
A 如果switchover_status为TO_STANDBY说明可以直接转换
alter database commit to switchover to physical standby;
B 如果switchover_status为SESSIONS ACTIVE 则关闭会话
alter database commit to switchover to physical standby with session shutdown;
三、主库切换redo日志及归档
四、主库进行角色切换
五、主库启动并查看状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1887350784 bytes
Fixed Size 2254344 bytes
Variable Size 1157630456 bytes
Database Buffers 721420288 bytes
Redo Buffers 6045696 bytes
Database mounted.
六、备库进行角色切换
七、开启备库并查看状态
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
八、在新备库(原主库)启用实时日志应用模式
SQL> alter database recover managed standby database using current logfile disconnect from session;
九、测试并查看主备库是否同步
主库：
SQL> alter system switch logfile;
System altered.
日志：
LNS: Standby redo logfile selected for thread 1 sequence 37 for destination LOG_ARCHIVE_DEST_2
Tue Mar 10 18:10:15 2020
Thread 1 advanced to log sequence 38 (LGWR switch)
Current log# 1 seq# 38 mem# 0: /u01/app/oracle/oradata/std/redo01.log
Tue Mar 10 18:10:15 2020
Archived Log entry 30 added for thread 1 sequence 37 ID 0x5d21d44d dest 1:
Tue Mar 10 18:10:15 2020
LNS: Standby redo logfile selected for thread 1 sequence 38 for destination LOG_ARCHIVE_DEST_2
备库日志：
Tue Mar 10 18:10:15 2020
RFS[3]: Selected log 5 for thread 1 sequence 38 dbid 1561980927 branch 1034200193
Tue Mar 10 18:10:15 2020
Archived Log entry 58 added for thread 1 sequence 37 ID 0x5d21d44d dest 1:
Tue Mar 10 18:10:15 2020
Media Recovery Waiting for thread 1 sequence 38 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 38 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/orcl/standby_02.log
主库查看应用情况：
SQL> select SEQUENCE#, APPLIED, ARCHIVED from V$ARCHIVED_LOG order by SEQUENCE# desc;
SEQUENCE# APPLIED ARC
---------- --------- ---
37 NO YES
37 NO YES
36 YES YES
36 NO YES
35 YES YES
35 NO YES
34 YES YES
33 YES YES
32 YES YES
31 YES YES
30 YES YES
备库查看应用情况：
SQL> select SEQUENCE#, APPLIED, ARCHIVED from V$ARCHIVED_LOG order by SEQUENCE# desc;
SEQUENCE# APPLIED ARC
---------- --------- ---
37 IN-MEMORY YES
36 YES YES
35 YES YES
34 NO YES
34 YES YES
33 YES YES
33 NO YES
32 YES YES
32 YES YES
31 YES YES
31 YES YES
总结
正常运行情况下ADG主备库切换一般不会出现故障，但实际操作过程中还是需谨慎。平时建议也抽空测试一下主备切换演练，避免真正故障时切换不成功。
