技术分享第四波之oracle ADG主备库切换

一、查看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主备库切换一般不会出现故障，但实际操作过程中还是需谨慎。平时建议也抽空测试一下主备切换演练，避免真正故障时切换不成功。