DG:有多个备库如何切换

问题描述：有一数据库准备进行主备switchover切换，但是有两个备库，其中最早一个备库状态已经出现GAP，第二个备库状态正常

SQL> show parameter log_archive_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest             string
log_archive_dest_1             string     LOCATION=+archdg VALID_FOR=(AL
                         L_LOGFILES,ALL_ROLES) DB_UNIQU
                         E_NAME=bjccora

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     SERVICE=bjccoradg LGWR ASYNC C
                         OMPRESSION=ENABLE NOAFFIRM VAL
                         ID_FOR=(ONLINE_LOGFILES,PRIMAR
                         Y_ROLE) DB_UNIQUE_NAME=bjccora
                         dg

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3             string     service=bjccorastb lgwr async
                         valid_for=(online_logfiles,pri
                         mary_role) db_unique_name=bjcc
                         orastb

log_archive_dest_state_1 string ENABLE
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable

1.查询主库切换状态为GAP，未做过测试，猜测如果是当前状态可能无法完成switchover操作，因为现在归档路径指向的是log_archive_dest_state_2

SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

2.怎样把当前的切换备库指向正常的一个呢，下面我把出现GAP的备库归档dest_2给禁掉

alter system set log_archive_dest_state_2=defer scope=memory;

3.查看归档路径状态

SQL> show parameter log_archive_dest_state_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2         string     defer
log_archive_dest_state_20         string     enable
log_archive_dest_state_21         string     enable
log_archive_dest_state_22         string     enable
log_archive_dest_state_23         string     enable
log_archive_dest_state_24         string     enable
log_archive_dest_state_25         string     enable
log_archive_dest_state_26         string     enable
log_archive_dest_state_27         string     enable
log_archive_dest_state_28         string     enable
log_archive_dest_state_29         string     enable

4.查看主库状态正常

5.切换成为备库成功

alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;