Oracle Adg 備庫 ORA-10458 ORA-01196 ORA-01110: ORA-10458 故障處理
一 環境：
主庫：Oracle 雙節點RAC 19.6C（Linux）
備庫：Oracle 雙節點RAC 19.6C（Linux）
二 詳細報錯：
Errors with log +ORA19CB_ARCH/ORA19CB/ARCHIVELOG/2021_02_07/thread_1_seq_809.315.1063907805
2021-02-16T11:07:34.513934+08:00
Standby Crash Recovery aborted due to error 16016.
2021-02-16T11:07:34.621378+08:00
Errors in file /oracle/app/oracle/diag/rdbms/ora19cb/ora19cb1/trace/ora19cb1_ora_9831.trc:
ORA-16016: archived log for thread 2 sequence# 809 unavailable
2021-02-16T11:07:37.026592+08:00
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
stopping change tracking
2021-02-16T11:07:38.154299+08:00
Completed Standby Crash Recovery.
2021-02-16T11:07:44.616632+08:00
Errors in file /oracle/app/oracle/diag/rdbms/ora19cb/ora19cb1/trace/ora19cb1_ora_9831.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+ORA19CB_DATA/ORA19CB/DATAFILE/system.262.1046531145'
ORA-10458 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:27996:2} */...
2021-02-16T11:07:47.129461+08:00
故障原因：
由於測試環境主機頻繁重啟，導致資料不一致（猜測）
處理方法：
分析原因後，找了好多處理思路，都是建議重新duplicate，但Oracle ADG 歸檔丟失後備庫無法應用的場景下有一種處理方法，我們透過此思路繼續一步一步處理，首先在備庫查詢當前的SCN 然後在主庫基於SCN 進行備份資料和控制檔案，在備庫進行恢復，處理報錯：
1）主庫透過Broker 停止日誌傳輸和日誌應用：
edit database 'ora19cb' set STATE='APPLY-OFF' ;
edit database 'ora19c' set STATE='TRANSPORT-OFF' ;
2）備庫確認SCN：
SELECT CURRENT_SCN FROM V$DATABASE;
select min(checkpoint_change#) from v$datafile_header
3）主庫備份：
BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
4） 備庫恢復：
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/oracle/software/ForStandbyCTRL.bck';
Starting restore at 2021-02-20 16:04:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 instance=ora19cb1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+ORA19CB_DATA/ORA19CB/CONTROLFILE/current.265.1046531113
Finished restore at 2021-02-20 16:04:17
RMAN> ALTER DATABASE MOUNT;
released channel: ORA_DISK_1
Statement processed
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA19C 1068822584 PARENT 1 2019-04-17 00:55:59
2 2 ORA19C 1068822584 CURRENT 1920977 2019-08-07 00:42:40
此時後臺會有大量的檔案rename 告警：
*****************************************
WARNING: The converted filename '+ORA19CB_DATA/ora19c/aaf3729430b47162e0531e38a8c01874/datafile/ts_swing.294.1046413467'
is an ASM fully qualified filename.
Changing the filename to '+ORA19CB_DATA/MUST_RENAME_THIS_DATAFILE_20.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
2020-07-23T15:05:44.180520+08:00
*****************************************
WARNING: The converted filename '+ORA19CB_DATA/ora19c/datafile/system.258.1015634281'
is an ASM fully qualified filename.
Changing the filename to '+ORA19CB_DATA/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
繼續
RMAN> SWITCH DATABASE TO COPY;
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/system.262.1046531145"
datafile 3 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/sysaux.261.1046531261"
datafile 4 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/undotbs1.260.1046531357"
datafile 5 switched to datafile copy "+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/DATAFILE/system.259.1046531373"
datafile 6 switched to datafile copy "+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/DATAFILE/sysaux.257.1046531389"
datafile 7 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/users.271.1046531405"
datafile 8 switched to datafile copy "+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/DATAFILE/undotbs1.270.1046531407"
datafile 9 switched to datafile copy "+ORA19CB_DATA/ORA19CB/DATAFILE/undotbs2.269.1046531417"
datafile 24 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/system.284.1063557913"
datafile 25 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/sysaux.263.1063557935"
datafile 26 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/undotbs1.285.1063557951"
datafile 27 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/system.286.1063557971"
datafile 28 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/sysaux.272.1063557981"
datafile 29 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/undotbs1.288.1063557993"
datafile 30 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F914FF6E0531E38A8C0072D/DATAFILE/undo_2.289.1063557997"
datafile 31 switched to datafile copy "+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/DATAFILE/undo_2.290.1063558003
如果有報錯可以使用 switch datafile file# to copy;
5） 處理報錯
資料庫open 後臺還有大量的 WARNING，主要是redo 的路徑和temp。我透過adg 備庫調好redo 的方法把redo 和temp 替換了：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database open;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL>
SQL>
SQL>
SQL> alter database add logfile thread 1 group 2 ('+ORA19CB_DATA') size 209715200;
alter database add logfile thread 2 group 3 ('+ORA19CB_DATA') size 209715200;
alter database add logfile thread 2 group 4 ('+ORA19CB_DATA') size 209715200;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------------------
+ORA19CB_DATA/ORA19CB/TEMPFILE/temp.304.1065025445
+ORA19CB_DATA/ORA19CB/8F8000E513704E63E0531E38A8C0B3E8/TEMPFILE/temp.305.1065025455
+ORA19CB_DATA/MUST_RENAME_THIS_TEMPFILE_3.4294967295.4294967295
+ORA19CB_DATA/MUST_RENAME_THIS_TEMPFILE_4.4294967295.4294967295
SQL> alter tablespace temp add tempfile '+ORA19CB_DATA' size 50M;
Tablespace altered.
SQL> alter tablespace temp drop tempfile 3;
Tablespace altered.
SQL> conn / as sysdba
Connected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBTS1 READ ONLY NO
4 PDBTS2 READ ONLY NO
SQL> alter session set container=&PDBNAME ;
Enter value for pdbname: PDBTS2
old 1: alter session set container=&PDBNAME
new 1: alter session set container=PDBTS2
Session altered.
SQL> alter tablespace temp add tempfile '+ORA19CB_DATA' size 50M;
Tablespace altered.
SQL> alter tablespace temp drop tempfile 4;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+ORA19CB_DATA/ORA19CB/B359E4EB6F944FF6E0531E38A8C0072D/TEMPFILE/temp.309.1065026937
6） 至此資料恢復完成，繼續ADG 關係恢復：
備庫：
SQL> alter system set standby_file_management=auto;
System altered.
主庫：
edit database 'ora19cb' set STATE='APPLY-ON' ;
edit database 'ora19c' set STATE='TRANSPORT-ON' ;
oracle@ora19c1:[/home/oracle]$dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Feb 20 16:15:14 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ora19c"
Connected as SYSDG.
DGMGRL> edit database 'ora19cb' set STATE='APPLY-ON' ;
Succeeded.
DGMGRL> show database verbose ora19cb;
Database - ora19cb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ora19cb1 (apply instance)
ora19cb2
Properties:
DGConnectIdentifier = 'yydbb_s'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
DGMGRL> show database verbose ora19c;
Database - ora19c
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
ora19c1
Error: ORA-16739: redo transport service for member "ora19cb" is running
ora19c2
Error: ORA-16739: redo transport service for member "ora19cb" is running
Properties:
DGConnectIdentifier = 'yydb_p'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
ERROR
DGMGRL> edit database 'ora19c' set STATE='TRANSPORT-ON' ;
Succeeded.
DGMGRL> show database verbose ora19c;
Database - ora19c
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ora19c1
ora19c2
Properties:
DGConnectIdentifier = 'yydb_p'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
7） 重啟備庫叢集，檢視資料庫正常傳輸應用，至此問題解決！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26342786/viewspace-2758026/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle ADG 備庫新增備庫Oracle
- ORACLE ORA-01110: ORA-27072: 處理Oracle
- Oracle更新Opatch故障處理Oracle
- oracle adg備庫歸檔滿了無法同步Oracle
- Oracle 10g RAC故障處理Oracle 10g
- Oracle 11.2.0.4 Dataguard兩則故障處理Oracle
- oracle rac+adg調整redo日誌組導致adg備庫ogg抽取程式abendOracle
- Oracle DG同步失敗故障處理(二)Oracle
- Oracle client安裝the jre is 0故障處理Oracleclient
- 資料庫故障處理優質文章彙總（含Oracle、MySQL、MogDB等）資料庫OracleMySql
- Oracle搭建rac到單庫的adgOracle
- 【故障處理】ORA-600:[13013],[5001]故障處理
- Oracle ADG環境下的RMAN備份策略Oracle
- 【CHECKPOINT】Oracle檢查點優化與故障處理Oracle優化
- 不停機處理oracle超過最大processes數故障Oracle
- 【問題處理】ORA-01157 ORA-01110
- linux故障處理Linux
- 故障分析 | Greenplum Segment 故障處理
- GPON網路故障如何處理？GPON網路故障處理流程
- Oracle RAC+ADG新增資料檔案失敗處理(db_create_file_dest)Oracle
- Oracle 19c adg全庫遷移資料Oracle
- Oracle、MySQL等資料庫故障處理優質文章分享 | 10月文章彙總OracleMySql資料庫
- Oracle impdp遷移資料後主鍵丟失故障處理Oracle
- MySQL show processlist故障處理MySql
- 微服務的故障處理微服務
- teams登入故障處理
- 11g ADG級聯備庫基礎測試環境準備
- Oracle SQL處理OracleSQL
- 線上故障處理手冊
- oracle adg主庫通過rman無法刪除歸檔Oracle
- 如何在12.2版本ADG備庫生成AWR報告
- 記一次一波三折的Oracle RAC故障處理Oracle
- oracle ADG與DG的區別Oracle
- 【故障處理】TNS-04610問題
- GaussDB(分散式)例項故障處理分散式
- ORA-01591錯誤故障處理
- 如何處理HTTP 503故障問題？HTTP
- Oracle壞塊處理Oracle