物理备库open报错ORA-10458怎么办

这篇文章给大家分享的是有关物理备库open报错ORA-10458怎么办的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

问题展现：
机房掉电导致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可以提供只读服务了，一切恢复正常。

感谢各位的阅读！关于“物理备库open报错ORA-10458怎么办”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！