**導讀**
> 作者:楊漆
> 16年關系型資料庫管理,從oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB獲得3個OCP、2個OCM;運維路上不平坦,跌過不少坑、熬過許多夜。把工作筆記整理出來分享給大夥兒,希望幫到大家少走彎路、少熬夜。
最近總是踩到搜遍全網都也找不到解決方案,僅原廠才有解藥的坑裡
Dg告警日志中大量出現Error
2021-07-13T17:00:23.984655+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trc:
ORA-01110: 資料檔案 155: '/u01/oradata/datafile/efsw_dat.980.1072178937'
2021-07-13T17:00:24.077126+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trc:
ORA-01110: 資料檔案 156: '/u01/oradata/datafile/efsw_dat.982.1072179003'
2021-07-13T17:00:24.168845+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trc:
ORA-01110: 資料檔案 157: '/u01/oradata/datafile/efsw_dat.979.1072179087'
2021-07-13T17:00:24.261303+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trc:
ORA-01110: 資料檔案 158: '/u01/oradata/datafile/loan_dat.978.1072179227'
2021-07-13T17:00:24.353301+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trc:
ORA-01110: 資料檔案 159: '/u01/oradata/datafile/loan_index.977.1072179343'
2021-07-13T17:00:24.448821+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trc:
ORA-01110: 資料檔案 160: '/u01/oradata/datafile/efs_dat.976.1072179459'
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_tt00_8566.trc:
ORA-00314: 日志 16 (用于線程 1) 要求的 sequence# 291926 與 291553 不比對
ORA-00312: 聯機日志 16 線程 1: '/u01/oradata/onlinelogstb1_redo16.log'
2021-07-13T17:15:09.529144+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_tt00_8566.trc:
ORA-00314: 日志 25 (用于線程 3) 要求的 sequence# 194861 與 194733 不比對
ORA-00312: 聯機日志 25 線程 3: '/u01/oradata/onlinelogstb3_redo25.log'
2021-07-13T17:15:09.573349+08:00
進一步打開trace檔案:
fd: 7
----- END ADS Stream Desc Dump -----
File Name Fragment: /orcl/trace/orcl_m000_9167.trc
################ Open Stream File: 1 ################
PathFile: /u01/app/oracle/diag/rdbms/orcldg3/orcl/trace/orcl_m000_9167.trm
OpFlag: 136, Status: 1, MagicBeg: 2153609765, MagicNum: 3593058129
Stream Access
----- ADS Stream Desc Dump -----
fd: 8
----- END ADS Stream Desc Dump -----
File Name Fragment: /orcl/trace/orcl_m000_9167.trm
----- END ADS Open Files Dump -----
----- END Diag Diagnostic DUMP -----
DDE encountered the following error:
ORA-49204: 遞歸 DDE 調用處于階段 I
ORA-01110: 資料檔案 160: '/u01/oradata/datafile/efs_dat.976.1072179459'
dbkh_create_finding: BEGIN
dbkhu_prepare_default_msgobj: BEGIN
dbkhu_prepare_default_msgobj:; name_id=71, type=2, flags=1
dbkhu_get_default_msg_def: BEGIN
dbkhu_get_default_msg_def: END
dbkhu_prepare_default_msgobj:: MSG PARAMS-1; i=0
dbkhu_prepare_default_msgobj: END
dbkhu_prepare_default_msgobj: BEGIN
dbkhu_prepare_default_msgobj:; name_id=71, type=2, flags=2
dbkhu_get_default_msg_def: BEGIN
dbkhu_get_default_msg_def: END
dbkhu_prepare_default_msgobj:: MSG PARAMS-2; i=0
dbkhu_prepare_default_msgobj: END
dbkh_create_finding: END
cross-check executed
dbkh_post_process_run: BEGIN
dbkh_post_process_run: NEW FAILURE COUNT: 0; DBKH_NUM_NEW_FAILURES_CTX(ctxp)=dbkh_post_process_run: END
dbkh_run_check_internal: END
dbkh_reactive_run_check: END
最近總是踩到搜遍全網都也找不到解決方案,僅原廠才有解藥的坑裡。
唯一有用的一篇文章指出:
ora-49204: recursive dde invocation at phase i This error is followed by ora-01110. This happens for all data files
這個是12c的一個BUG。 ORA-01110 For All Files In Standby Database
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIwIjNx8CX39CXy8CXycXZpZVZnFWbp9zZlBnauEmMlhDNlhTO3UWM1UjM1ImZ3IWYhZ2NzITY1QjMlZTYvwVO5QDNwIDOtUGall3LcVmdhNXLwRHdo9CXt92YucWbpRWdvx2Yx5yazF2Lc9CX6MHc0RHaiojIsJye.jpeg)
MOS上給出的解決方案:下載下傳并安裝更新檔包:p24844841_122010_Linux-x86-64.zip
續費問題商務組還在談判,下載下傳不了Bug更新檔包,隻能自己想辦法。
琢磨一會兒,發現問題應該出在standby log上。
解決方案:
1.停止備庫的恢複管理模式
2.清空standby日志
3.重新開機備庫
4.開啟備庫應用日志
5.檢查
## standby日志:
ALTER DATABASE clear LOGFILE group 15;
ALTER DATABASE clear LOGFILE group 16;
ALTER DATABASE clear LOGFILE group 17;
ALTER DATABASE clear LOGFILE group 18;
ALTER DATABASE clear LOGFILE group 19;
ALTER DATABASE clear LOGFILE group 20;
ALTER DATABASE clear LOGFILE group 21;
ALTER DATABASE clear LOGFILE group 22;
ALTER DATABASE clear LOGFILE group 23;
ALTER DATABASE clear LOGFILE group 24;
ALTER DATABASE clear LOGFILE group 25;
ALTER DATABASE clear LOGFILE group 26;
ALTER DATABASE clear LOGFILE group 27;
ALTER DATABASE clear LOGFILE group 28;
ALTER DATABASE clear LOGFILE group 29;
ALTER DATABASE clear LOGFILE group 30;
ALTER DATABASE clear LOGFILE group 31;
ALTER DATABASE clear LOGFILE group 32;
ALTER DATABASE clear LOGFILE group 33;
ALTER DATABASE clear LOGFILE group 34;
ALTER DATABASE clear LOGFILE group 35;
ALTER DATABASE clear LOGFILE group 36;
ALTER DATABASE clear LOGFILE group 37;
ALTER DATABASE clear LOGFILE group 38;
ALTER DATABASE clear LOGFILE group 39;
重新開啟備庫應用日志後一切正常,經過一晚上的運作,今早到公司後再次檢查告警日志,一切正常!
2021-07-14T09:50:40.195428+08:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[298]: Assigned to RFS process (PID:26345)
RFS[298]: Selected log 15 for T-1.S-292565 dbid 1513741333 branch 985960599
2021-07-14T09:50:46.238535+08:00
Recovery of Online Redo Log: Thread 1 Group 15 Seq 292565 Reading mem 0
Mem# 0: /u01/oradata/onlinelogstb1_redo15.log
2021-07-14T09:51:46.053469+08:00
RFS[297]: Selected log 26 for T-3.S-195400 dbid 1513741333 branch 985960599
2021-07-14T09:51:46.096309+08:00
Media Recovery Waiting for thread 3 sequence 195400 (in transit)
2021-07-14T09:51:46.097183+08:00
Recovery of Online Redo Log: Thread 3 Group 26 Seq 195400 Reading mem 0
Mem# 0: /u01/oradata/onlinelogstb3_redo26.log
2021-07-14T09:51:46.783207+08:00
Archived Log entry 1899 added for T-3.S-195399 ID 0x5a3a0712 LAD:1
2021-07-14T10:00:26.290941+08:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[299]: Assigned to RFS process (PID:26766)
RFS[299]: Selected log 15 for T-1.S-292565 dbid 1513741333 branch 985960599
2021-07-14T10:04:18.028763+08:00
Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 21 T-2.S-229169
RFS[300]: Assigned to RFS process (PID:26859)
RFS[300]: Selected log 20 for T-2.S-229170 dbid 1513741333 branch 985960599
2021-07-14T10:04:18.381307+08:00
Media Recovery Waiting for thread 2 sequence 229170 (in transit)
2021-07-14T10:04:18.382131+08:00
Recovery of Online Redo Log: Thread 2 Group 20 Seq 229170 Reading mem 0
Mem# 0: /u01/oradata/onlinelogstb2_redo20.log
2021-07-14T10:04:18.387233+08:00
Archived Log entry 1900 added for T-2.S-229169 ID 0x5a3a0712 LAD:1
## 登陸備庫二檢查(昨天出現ora-49204的DB):
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL>
SQL> select count(*),to_char(min(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='NO';
COUNT(*) TO_CHAR(MIN(FIRST
---------- -----------------
SQL>
SQL> select count(*),to_char(max(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='YES';
COUNT(*) TO_CHAR(MAX(FIRST
---------- -----------------
1879 20210714 10:03:18
## 登陸備庫一比對(一直正常運作的):
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 14 10:04:27 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL>
SQL> select count(*),to_char(max(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='YES';
COUNT(*) TO_CHAR(MAX(FIRST
---------- -----------------
14711 20210714 10:03:18
一切正常,問題解決!