**导读**
> 作者:杨漆
> 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
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
---------- -----------------
0
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
一切正常,问题解决!
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。