分类目录归档：数据库

log_archive_dest_1配置不当,当时standby redo无法归档引起adg不能实时同步

发表于 2025 年 8 月 13 日 由 惜分飞

oracle database dataguard无法利用standby redo实时同步

2025-08-12T18:41:57.474031+08:00
PR00 (PID:245113): Media Recovery Log /u01/app/oracle/fast_recovery_area/……/2025_08_12/o1_mf_1_34206_n9p6mmkb_.arc
PR00 (PID:245113): Media Recovery Waiting for T-1.S-34207 (in transit)

clear standby redo之后，实时同步一会儿，又无法实时同步

2025-08-12T18:43:28.183908+08:00
PR00 (PID:245113): MRP0: Background Media Recovery cancelled with status 16037
2025-08-12T18:43:28.184131+08:00
Errors in file /u01/app/oracle/diag/rdbms/dclhrdg2/DCLHR/trace/DCLHR_pr00_245113.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:245113): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Stopping change tracking
2025-08-12T18:43:28.356862+08:00
Errors in file /u01/app/oracle/diag/rdbms/dclhrdg2/DCLHR/trace/DCLHR_pr00_245113.trc:
ORA-16037: user requested cancel of managed recovery operation
2025-08-12T18:43:28.482234+08:00
Background Media Recovery process shutdown (DCLHR)
2025-08-12T18:43:29.178708+08:00
Managed Standby Recovery Canceled (DCLHR)
2025-08-12T18:43:44.724806+08:00
Clearing online log 11 of thread 1 sequence number 34111
2025-08-12T18:44:00.545234+08:00
Clearing online log 12 of thread 1 sequence number 34112
2025-08-12T18:44:10.770626+08:00
Clearing online log 13 of thread 1 sequence number 34113
2025-08-12T18:44:31.911565+08:00
Clearing online log 14 of thread 1 sequence number 34114
2025-08-12T18:44:42.111444+08:00
Clearing online log 15 of thread 1 sequence number 34108
2025-08-12T18:45:06.031673+08:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
2025-08-12T18:45:06.047722+08:00
Attempt to start background Managed Standby Recovery process (DCLHR)
Starting background process MRP0
2025-08-12T18:45:06.062512+08:00
MRP0 started with pid=219, OS id=245503
2025-08-12T18:45:06.064300+08:00
Background Managed Standby Recovery process started (DCLHR)
2025-08-12T18:45:11.086271+08:00
 Started logmerger process
2025-08-12T18:45:11.100434+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:245509): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2025-08-12T18:45:13.275444+08:00
Parallel Media Recovery started with 80 slaves
2025-08-12T18:45:13.392342+08:00
Stopping change tracking
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34207 (in transit)
2025-08-12T18:46:22.717320+08:00
 rfs (PID:245728): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:15566)
2025-08-12T18:46:31.950359+08:00
 rfs (PID:244324): Archived Log entry 99 added for B-1149239150.T-1.S-34207 ID 0x57544f44 LAD:32
2025-08-12T18:46:32.185391+08:00
 rfs (PID:245735): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:3334853)
 rfs (PID:245735): Primary database is in MAXIMUM PERFORMANCE mode
2025-08-12T18:46:32.331928+08:00
 rfs (PID:245735): Selected LNO:11 for T-1.S-34208 dbid 1299764411 branch 1149239150
2025-08-12T18:46:32.885400+08:00
PR00 (PID:245509): Media Recovery Log /u01/app/oracle/fast_recovery_area/……/2025_08_12/o1_mf_1_34207_n9p6mo8f_.arc
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34208 (in transit)
2025-08-12T18:46:33.669769+08:00
Recovery of Online Redo Log: Thread 1 Group 11 Seq 34208 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DCLHR/std_redo11.log
2025-08-12T18:50:03.011923+08:00
Expanded controlfile section 28 from 141 to 282 records
Requested to grow by 141 records; added 2 blocks of records
2025-08-12T19:16:29.515866+08:00
 rfs (PID:245735): Selected LNO:12 for T-1.S-34209 dbid 1299764411 branch 1149239150
2025-08-12T19:16:29.515943+08:00
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34209 (in transit)
2025-08-12T19:16:30.522048+08:00
Recovery of Online Redo Log: Thread 1 Group 12 Seq 34209 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DCLHR/std_redo12.log
2025-08-12T19:46:29.852369+08:00
 rfs (PID:245735): Selected LNO:13 for T-1.S-34210 dbid 1299764411 branch 1149239150
2025-08-12T19:46:29.852443+08:00
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34210 (in transit)
2025-08-12T19:46:30.858423+08:00
Recovery of Online Redo Log: Thread 1 Group 13 Seq 34210 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DCLHR/std_redo13.log
2025-08-12T20:16:29.978563+08:00
 rfs (PID:245735): Selected LNO:14 for T-1.S-34211 dbid 1299764411 branch 1149239150
2025-08-12T20:16:30.012723+08:00
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34211 (in transit)
2025-08-12T20:16:30.013045+08:00
Recovery of Online Redo Log: Thread 1 Group 14 Seq 34211 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DCLHR/std_redo14.log
2025-08-12T20:46:30.307116+08:00
 rfs (PID:245735): Selected LNO:15 for T-1.S-34212 dbid 1299764411 branch 1149239150
2025-08-12T20:46:30.339601+08:00
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34212 (in transit)
2025-08-12T20:46:30.339893+08:00
Recovery of Online Redo Log: Thread 1 Group 15 Seq 34212 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/DCLHR/std_redo15.log
2025-08-12T21:16:27.517565+08:00
 rfs (PID:245735): No SRLs available for T-1
2025-08-12T21:16:27.560282+08:00
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34213 (in transit)
2025-08-12T21:16:27.615513+08:00
 rfs (PID:245735): Opened log for T-1.S-34213 dbid 1299764411 branch 1149239150
2025-08-12T21:46:27.633777+08:00
 rfs (PID:245735): Archived Log entry 100 added for B-1149239150.T-1.S-34213 ID 0x57544f44 LAD:32
 rfs (PID:245735): No SRLs available for T-1
2025-08-12T21:46:27.787746+08:00
 rfs (PID:245735): Opened log for T-1.S-34214 dbid 1299764411 branch 1149239150
2025-08-12T21:46:28.439910+08:00
PR00 (PID:245509): Media Recovery Log /u01/app/oracle/fast_recovery_area/……/2025_08_12/o1_mf_1_34213_n9phockd_.arc
2025-08-12T21:46:32.804540+08:00
PR00 (PID:245509): Media Recovery Waiting for T-1.S-34214 (in transit)
2025-08-12T22:16:27.968668+08:00
 rfs (PID:245735): Archived Log entry 101 added for B-1149239150.T-1.S-34214 ID 0x57544f44 LAD:32
 rfs (PID:245735): No SRLs available for T-1

从报错信息看是由于无可用的standby redo导致（No SRLs available for T-1）,查询standby redo状态

[oracle@db03 trace]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 13 15:58:46 2025
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

s
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> elect group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
        11 ACTIVE
        12 ACTIVE
        13 ACTIVE
        14 ACTIVE
        15 ACTIVE

所有的standby redo都是active状态,导致无可用standby redo,出现该问题的原因,初步怀疑是standby redo无法归档导致,引起该问题的原因怀疑是由于该备库的log_archive_dest_N参数设置不当导致,检查相关参数

*.db_unique_name='DCLHRDG2'
*.fal_client='DCLHRDG2'
*.fal_server='DCLHRDG','DCLHR'
*.log_archive_config='dg_config=(DCLHR,DCLHRDG,DCLHRDG2)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=DCLHRDG'
*.log_archive_dest_2='service=DCLHR lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=DCLHR'
*.log_archive_dest_3='service=DCLHRDG2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=DCLHRDG2'

比较明显由于log_archive_dest_1和log_archive_dest_3的相关参数配置不对导致standby redo无法通过log_archive_dest_1进行归档导致,正确的配置方式为

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=DCLHRDG'
*.log_archive_dest_3='service=DCLHRDG lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=DCLHRDG'

这样配置之后,该dg库同步日志正常.

发表在 Data Guard | 标签为 | 评论关闭

RAC环境redo在各节点本地导致数据库故障恢复

发表于 2025 年 8 月 9 日 由 惜分飞

最近一个运行在win平台的rac,由于断电之后,集群两个节点均无法正常启动,客户进行了一系列尝试,结果到了ora-600 kclchkblk_4错误无法继续.
通过对数据库日志分析,回溯了故障大概的原因,启动的时候报错为:
节点1启动报错

Sun Aug 03 15:21:22 2025
alter database open
This instance was first to open
Beginning crash recovery of 2 threads
 parallel recovery started with 32 processes
Started redo scan
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_7108.trc:
ORA-00314: 日志 11 (用于线程 2) 要求的 sequence# 147717 与 147541 不匹配
ORA-00312: 联机日志 11 线程 2: 'D:\REDOLOG\REDO011.LOG'
Abort recovery for domain 0
Aborting crash recovery due to error 314
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_7108.trc:
ORA-00314: 日志 11 (用于线程 2) 要求的 sequence# 147717 与 147541 不匹配
ORA-00312: 联机日志 11 线程 2: 'D:\REDOLOG\REDO011.LOG'
Abort recovery for domain 0
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_7108.trc:
ORA-00314: 日志 11 (用于线程 2) 要求的 sequence# 147717 与 147541 不匹配
ORA-00312: 联机日志 11 线程 2: 'D:\REDOLOG\REDO011.LOG'
ORA-314 signalled during: alter database open...

节点2启动报错

Sat Aug 02 15:45:43 2025
Successful mount of redo thread 2, with mount id 1735887907
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:47460:124} */
ALTER DATABASE OPEN /* db agent *//* {1:47460:124} */
This instance was first to open
Beginning crash recovery of 2 threads
Sat Aug 02 15:45:49 2025
 parallel recovery started with 32 processes
Started redo scan
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_3444.trc:
ORA-00314: ?? 1 (???? 1) ??? sequence# 67782 ? 60818 ???
ORA-00312: ???? 1 ?? 1: 'D:\REDOLOG\REDO01.LOG'
Abort recovery for domain 0
Aborting crash recovery due to error 314
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_3444.trc:
ORA-00314: ?? 1 (???? 1) ??? sequence# 67782 ? 60818 ???
ORA-00312: ???? 1 ?? 1: 'D:\REDOLOG\REDO01.LOG'
Abort recovery for domain 0
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_3444.trc:
ORA-00314: ?? 1 (???? 1) ??? sequence# 67782 ? 60818 ???
ORA-00312: ???? 1 ?? 1: 'D:\REDOLOG\REDO01.LOG'
ORA-314 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:47460:124} */...

看到这两个报错信息得出两个结论：
1）比较明显节点1需要thead 2的 group 11 sequence为147717,但是实际group 11文件的sequence为147541；而节点2启动需要thread 1的group 1 sequence为67782，但是实际中group 1文件的sequnece为60818,这两个都相差比较多,属于非正常的情况,很可能是文件本身有问题
2）这是一套win的rac架构,理论上redo应该在共享文件系统（一般是asm中)，而这个第一感觉很可能是本地文件系统中
客户当时恢复之时查询信息截图
ORA-00314-ORA-00312

查看了两个节点的最后redo切换信息

--节点1
Sat Aug 02 10:49:31 2025
Thread 1 advanced to log sequence 67782 (LGWR switch)
  Current log# 1 seq# 67782 mem# 0: D:\REDOLOG\REDO01.LOG

--节点2（redo每组2G,节点2长时间没跑业务,之时做数据库导出操作,所以切换时间比较久远）
Sat Jul 26 16:56:42 2025
Thread 2 advanced to log sequence 147717 (LGWR switch)
  Current log# 11 seq# 147717 mem# 0: D:\REDOLOG\REDO011.LOG

并查看两个机器d:/redolog信息(客户自行resetlogs之后的,非第一现场,但是可以确认两个节点各自有一份redo文件
redo

本来这个是一个比较小的故障,只要把节点2的thread 1的redo拷贝到到节点1或者节点1的thread 2的redo拷贝到节点2,然后正常open库即可,现场恢复对rac不太熟悉,直接按照互联网上检索的处理方法,加上_allow_resetlogs_corruption然后强制拉库,结果不太幸运,拉库失败报ORA-600 kclchkblk_4错误
kclchkblk_4

Sun Aug 03 18:59:24 2025
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 21497084214
Resetting resetlogs activation ID 1543012633 (0x5bf88119)
Sun Aug 03 18:59:46 2025
Setting recovery target incarnation to 3
Sun Aug 03 18:59:46 2025
This instance was first to open
Picked broadcast on commit scheme to generate SCNs
Sun Aug 03 18:59:48 2025
Assigning activation ID 1735960667 (0x6778a85b)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\REDOLOG\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Aug 03 18:59:49 2025
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_8508.trc  (incident=728324):
ORA-00600: 内部错误代码, 参数: [kclchkblk_4], [5], [200595988], [5], [22247740], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\incident\incdir_728324\orcl1_ora_8508_i728324.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Aug 03 18:59:53 2025
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_8508.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [kclchkblk_4], [5], [200595988], [5], [22247740], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_ora_8508.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [kclchkblk_4], [5], [200595988], [5], [22247740], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 8508): terminating the instance due to error 704
Sun Aug 03 18:59:54 2025
opiodr aborting process unknown ospid (9480) as a result of ORA-1092
Sun Aug 03 19:00:09 2025
Instance terminated by USER, pid = 8508
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (8508) as a result of ORA-1092

这个故障之后,客户那边无法自行恢复,让我这边介入处理,对于这个错误以前处理比较多,一般就是scn问题,通过Patch SCN小工具快速解决
QQ20250808-185918

数据库open成功之后主要报一些ORA-600 4137,ORA-600 6006等错误

Database Characterset is ZHS16GBK
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc  (incident=1176205):
ORA-00600: 内部错误代码, 参数: [4137], [1.14.2713957], [0], [0], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\incident\incdir_1176205\orcl1_smon_8300_i1176205.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Aug 08 19:03:14 2025
ORACLE Instance orcl1 (pid = 25) - Error 600 encountered while recovering transaction (1, 14).
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc:
ORA-00600: 内部错误代码, 参数: [4137], [1.14.2713957], [0], [0], [], [], [], [], [], [], [], []
Fri Aug 08 19:03:15 2025
ORACLE Instance orcl1 (pid = 25) - Error 600 encountered while recovering transaction (5, 19).
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc:
ORA-00600: 内部错误代码, 参数: [4137], [5.19.2318502], [0], [0], [], [], [], [], [], [], [], []
Starting background process MMON
Fri Aug 08 19:03:18 2025
MMON started with pid=29, OS id=4624
Fri Aug 08 19:03:19 2025
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc  (incident=1176207):
ORA-00600: 内部错误代码, 参数: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\incident\incdir_1176207\orcl1_smon_8300_i1176207.trc
Starting background process MMNL
Fri Aug 08 19:03:19 2025
MMNL started with pid=30, OS id=8344
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance orcl1 (pid = 25) - Error 600 encountered while recovering transaction (46, 28) on object 197344.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_8300.trc:
ORA-00600: 内部错误代码, 参数: [6006], [1], [], [], [], [], [], [], [], [], [], []

通过重建undo解决该错误,数据库稳定运行,没有再crash和报明显错误,导出核心数据,完成本次恢复任务.

发表在 Oracle | 标签为 , , , , | 评论关闭

Oracle 21c 202507补丁-21.19

发表于 2025 年 8 月 9 日 由 惜分飞
 Description  Database Update  GI Update  Windows Bundle Patch
  JUL2025 (21.19.0.0.0) 38068980  38068887  37962964
  APR2025 (21.18.0.0.0) 37655430  37642955  37532378
  JAN2025 (21.17.0.0.0) 37350281  37349593  37500148
  OCT2024 (21.16.0.0.0) 36991631  36990664  36878842
  JUL2024 (21.15.0.0.0) 36696242  36696109  36521898
  APR2024 (21.14.0.0.0) 36352352  36352207  36219877
  JAN2024 (21.13.0.0.0) 36041222  36031790  35962857
  OCT2023 (21.12.0.0.0) 35740258  35738010  35681617
  JUL2023  (21.11.0.0.0) 35428978  35427907  35347974
  APR2023 (21.10.0.0.0) 35134934  35132566  35046488
  JAN2023 (21.9.0.0.0) 34839741  34838415  34750812
  Oct2022 (21.8.0.0.0) 34527084  34526142  34468137
  JUL2022 (21.7.0.0.0) 34160444  34155589  34110698
  APR2022 (21.6.0.0.0) 33843745  33859395  33829143
  JAN2022 (21.5.0.0.0) 33516412  33531909  33589769
 OCT2021 (21.4.0.0.0) 33239276  33250101  NA

参考:Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

发表在 Oracle安装升级 | 标签为 , , , | 评论关闭