物理備庫open報錯ORA

173次閱讀

共計 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 怎么辦”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

正文完