物理備庫open報錯ORA-10458: standby database requires recovery

問題展現：
機房掉電導致oracle 11g RAC+DG  所有3節點都非正常關機。

開機之後，RAC兩節點正常啟動，DG上面的資料庫例項只能啟動到mount狀態，無法open。

DG：
alter database open；
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata/system01.dbf'


RAC01的alert日誌報錯：
Thread 1 advanced to log sequence 71686 (LGWR switch)
  Current log# 2 seq# 71686 mem# 0: +DATA/scprd/onlinelog/group_2.300.926178257
Tue Dec 26 14:43:46 2017
Archived Log entry 267550 added for thread 1 sequence 71685 ID 0x350f8bcc dest 1:
Tue Dec 26 14:43:52 2017
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH SID='*';
Tue Dec 26 14:43:59 2017
Error 12169 received logging on to the standby
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';
Tue Dec 26 14:44:01 2017
Thread 1 cannot allocate new log, sequence 71687
Checkpoint not complete
  Current log# 2 seq# 71686 mem# 0: +DATA/scprd/onlinelog/group_2.300.926178257
Thread 1 advanced to log sequence 71687 (LGWR switch)
  Current log# 1 seq# 71687 mem# 0: +DATA/scprd/onlinelog/group_1.304.926178257
Tue Dec 26 14:44:07 2017
Archived Log entry 267552 added for thread 1 sequence 71686 ID 0x350f8bcc dest 1:
Tue Dec 26 14:49:14 2017
Error 12169 received logging on to the standby
Tue Dec 26 14:49:50 2017
Thread 1 advanced to log sequence 71688 (LGWR switch)
  Current log# 2 seq# 71688 mem# 0: +DATA/scprd/onlinelog/group_2.300.926178257
Tue Dec 26 14:49:50 2017
Archived Log entry 267558 added for thread 1 sequence 71687 ID 0x350f8bcc dest 1:
Tue Dec 26 14:49:50 2017
Error 12169 received logging on to the standby
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance SCPRD1 - Archival Error. Archiver continuing.
Tue Dec 26 14:51:09 2017

主從日誌同步有問題：
DG：
SQL> COL NAME FOR A13
SQL> COL VALUE FOR A20
SQL> COL UNIT FOR A30
SQL> SET LINES 122
SQL> SELECT NAME,VALUE,UNIT,TIME_COMPUTED
  2  FROM V$DATAGUARD_STATS
  3  WHERE NAME IN ('transport lag','apply lag');


NAME          VALUE                UNIT                           TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag                      day(2) to second(0) interval   12/26/2017 14:19:22
apply lag     +00 01:53:52         day(2) to second(0) interval   12/26/2017 14:19:22

apply lag有延時。

主庫：
SQL> select thread#, max(sequence#) from v$archived_log group by thread#;


   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          71710
         2          68404
DG：
SQL> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#;


   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          71634
         2          68325
DG比主庫的SEQUENCE慢，主從同步有問題。

問題解決：
檢視RAC01的tnsnames有問題：
SCPRDDG =
CPRD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wmsscan2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SCPRD)
    )
  )


    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SCPRDDG)
    )
  )
修改為：
SCPRDDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SCPRDDG)
    )
  )

重新測試同步，正常了。
apply lag沒有延時了。
主從日誌同步的SEQUENCE也一樣了。

再把DG變為open：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


Database altered.


SQL> alter database open read only;


Database altered.


SQL> select RECOVERY_MODE from v$archive_dest_status where rownum<5;


RECOVERY_MODE
-----------------------
IDLE
IDLE
IDLE
IDLE


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


Database altered.


SQL> select RECOVERY_MODE from v$archive_dest_status where rownum<5;


RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE

SQL> SELECT NAME,VALUE,UNIT,TIME_COMPUTED
FROM V$DATAGUARD_STATS
  2    3  WHERE NAME IN ('transport lag','apply lag');


NAME          VALUE                UNIT                           TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00         day(2) to second(0) interval   12/26/2017 16:31:30
apply lag     +00 00:00:00         day(2) to second(0) interval   12/26/2017 16:31:30

DG可以提供只讀服務了，一切恢復正常。