物理备库open报错ORA

51次阅读

共计 3872 个字符，预计需要花费 10 分钟才能阅读完成。

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

问题展现：
机房掉电导致 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

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

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 怎么办”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

正文完