Oracle备份恢复 – 第9页

由于主机异常断电，导致oracle数据库无法正常启动,数据库启动报错ORA-07445 kdxlin,ORA-01172,ORA-00312,ORA-00742等错误

Fri Nov 25 11:24:53 2022
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Completed redo application of 0.46MB
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c000d0 (file 3, block 208)
Reread (file 3, block 208) found valid data
Hex dump of (file 3, block 208) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Repaired corruption at (file 3, block 208)
Hex dump of (file 3, block 152) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c00098 (file 3, block 152)
Reread (file 3, block 152) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 152 OF FILE 3
Fri Nov 25 11:25:02 2022
Hex dump of (file 3, block 6859) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01acb (file 3, block 6859)
Reread (file 3, block 6859) found same corrupt data (logically corrupt)
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224379):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224379\XFF_p010_7024_i224379.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:13 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224343):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224343\XFF_p005_12036_i224343.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Nov 25 11:25:18 2022
Sweep [inc][224379]: completed
Sweep [inc][224343]: completed
Sweep [inc2][224379]: completed
Sweep [inc2][224343]: completed
RECOVERY OF THREAD 1 STUCK AT BLOCK 6859 OF FILE 3
Fri Nov 25 11:25:33 2022
Slave exiting with ORA-1172 exception
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p004_1988.trc:
ORA-01172: 线程 1 的恢复停止在块 152 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri Nov 25 11:25:34 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p001_19268.trc:
ORA-10388: parallel query server interrupt (failure)
Fri Nov 25 11:25:38 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:41 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc  (incident=224344):
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224344\XFF_p005_12036_i224344.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p005_12036.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 27, 块号 520567)
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10564: tablespace POWERMPP
ORA-01110: 数据文件 27: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMPP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 89776
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc  (incident=224380):
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:51 2022
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\incident\incdir_224380\XFF_p010_7024_i224380.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_p010_7024.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 26, 块号 227101)
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10564: tablespace POWERMSP
ORA-01110: 数据文件 26: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\POWERMSP.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 99375
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4432] [ACCESS_VIOLATION] [ADDR:0xC] [PC:0x14306B54A] [UNABLE_TO_READ] []
Fri Nov 25 11:25:54 2022
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 900 KB redo, 386 data blocks need recovery
Started redo application at
 Thread 1: logseq 93214, block 60163
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93214 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Hex dump of (file 3, block 6743) in trace file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc
Reading datafile 'D:\APP\ADMINISTRATOR\ORADATA\XFF\UNDOTBS01.DBF' for corruption at rdba: 0x00c01a57 (file 3, block 6743)
Reread (file 3, block 6743) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 6743 OF FILE 3
Fri Nov 25 11:26:09 2022
Aborting crash recovery due to error 1172
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_4172.trc:
ORA-01172: 线程 1 的恢复停止在块 6743 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
ORA-1172 signalled during: alter database open...

尝试人工recover恢复,报ORA-00283 ORA-00742 ORA-00312错误

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'

通过特殊这里之后recover库成功

SQL> recover database until cancel;
ORA-00279: 更改 47073228694 (在 11/25/2022 08:11:15 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\XFF\ARCHIVELOG\2022_11_25\O1_MF_1_932
14_%U_.ARC
ORA-00280: 更改 47073228694 (用于线程 1) 在序列 #93214 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
已应用的日志。
完成介质恢复。

打开数据库报ORA-600 2662错误

使用oracle patch scn工具快速修改 open库成功

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area       1603411968 bytes
Fixed Size                        2281656 bytes
Variable Size                  1191186248 bytes
Database Buffers                402653184 bytes
Redo Buffers                      7290880 bytes
数据库装载完毕。
SQL> ALTER DATABASE OPEN;

数据库已更改。

然后逻辑导出数据,导入新库,完成数据迁移工作

ora-600 kccpb_sanity_check_2故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ora-600 kccpb_sanity_check_2故障处理

数据库启动报ORA-600 kccpb_sanity_check_2

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 1258291200 bytes
Fixed Size                  1250548 bytes
Variable Size             243272460 bytes
Database Buffers         1006632960 bytes
Redo Buffers                7135232 bytes
ORA-00600: ??????, ??: [kccpb_sanity_check_2], [66014], [66011], [0x0], [], [],[], []

重建控制文件报错ora-600 kccsga_update_amx_1

SQL> CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'd:\zs\redo01.log'  SIZE 50M,
  9    GROUP 2 'd:\zs\redo02.log'  SIZE 50M,
 10    GROUP 3 'd:\zs\redo03.log'  SIZE 50M
 11  DATAFILE
 12  'd:\zs\SYSAUX01.DBF',
………………
 22  'd:\zs\SYSTEM01.DBF',
 23  'd:\zs\UNDOTBS01.DBF',
 24  'd:\zs\USERS01.DBF'
 25  CHARACTER SET zhs16gbk
 26  ;
CREATE CONTROLFILE REUSE DATABASE "zs" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-00600: ??????, ??: [kccsga_update_amx_1], [9], [2920], [292], [], [], [],[]

重启实例，重建ctl成功.尝试恢复库提示需要很久之前的日志，因为有两个数据文件scn异常

通过oracle recovery tools修改文件头

再次recover数据库成功顺利open库导出客户需要数据

ORA-600 3417和ORA-600 3005故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 3417和ORA-600 3005故障处理

运行中的数据库突然报ORA-600 3417错误,lgwr进程异常数据库crash

Thu Nov 17 03:00:14 2022
Archived Log entry 23860 added for thread 2 sequence 1958 ID 0x6200e2f5 dest 1:
Thu Nov 17 03:13:11 2022
Auto-tuning: Shutting down background process GTX1
Thu Nov 17 04:00:02 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_1740.trc  (incident=672186):
ORA-00600: 内部错误代码, 参数: [3417], [2], [0], [0], [0], [1], [2], [], [], [], [], []
Thu Nov 17 04:00:04 2022
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_1740.trc:
ORA-00600: 内部错误代码, 参数: [3417], [2], [0], [0], [0], [1], [2], [], [], [], [], []
LGWR (ospid: 1740): terminating the instance due to error 470

重启之后报ORA-600 3005错误,数据库启动失败

Thu Nov 17 04:03:09 2022
Successful mount of redo thread 2, with mount id 1648753015
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {0:1:38} */
ALTER DATABASE OPEN /* db agent *//* {0:1:38} */
This instance was first to open
Beginning crash recovery of 1 threads
 parallel recovery started with 31 processes
Thu Nov 17 04:03:14 2022
Started redo scan
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
Thu Nov 17 04:03:15 2022
Reconfiguration started (old inc 14, new inc 16)
List of instances:
 1 2 (myinst: 2) 
 Global Resource Directory frozen
Thu Nov 17 04:03:15 2022
 Communication channels reestablished
Thu Nov 17 04:03:16 2022
 * domain 0 valid = 0 according to instance 1 
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Thu Nov 17 04:03:16 2022
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Nov 17 04:03:16 2022
Sweep [inc][688298]: completed
Sweep [inc2][688298]: completed
Thu Nov 17 04:03:16 2022
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Thu Nov 17 04:03:16 2022
 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info 
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Abort recovery for domain 0
Aborting crash recovery due to error 600
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_15352.trc:
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
Abort recovery for domain 0
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_ora_15352.trc:
ORA-00600: ??????, ??: [3005], [1], [706], [10374], [0], [0], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:1:38} */...

尝试人工进行recover恢复库

SQL> recover database;
ORA-00279: 更改 310644203 (在 11/17/2022 01:00:05 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+DATA/orcl/archivelog/2022_11_17/thread_2_seq_1956.22763.1120960801
ORA-00280: 更改 310644203 (用于线程 2) 在序列 #1956 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 310663747 (在 11/17/2022 02:00:01 生成) 对于线程 2 是必需的
ORA-00289: 建议:
+DATA/orcl/archivelog/2022_11_17/thread_2_seq_1957.22764.1120962585
ORA-00280: 更改 310663747 (用于线程 2) 在序列 #1957 中


ORA-10877: error signaled in parallel recovery slave


ORA-01112: 未启动介质恢复

通过查看alert日志确认由于ORA-00353错误导致recover database失败

Thu Nov 17 08:07:39 2022
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 32 slaves
Thu Nov 17 08:07:41 2022
Recovery of Online Redo Log: Thread 1 Group 1 Seq 705 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_1.261.1116409583
ORA-279 signalled during: ALTER DATABASE RECOVER  database  ...
Thu Nov 17 08:08:07 2022
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log +DATA/orcl/archivelog/2022_11_17/thread_2_seq_1956.22763.1120960801
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log +DATA/orcl/archivelog/2022_11_17/thread_2_seq_1957.22764.1120962585
Thu Nov 17 08:08:14 2022
Recovery of Online Redo Log: Thread 2 Group 4 Seq 1958 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_4.266.1116409589
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_pr00_7116.trc  (incident=704315):
ORA-00353: 日志损坏接近块 20866 更改 310761542 时间 11/17/2022 03:00:04
ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.1116409583'
Thu Nov 17 08:08:26 2022
Sweep [inc][704315]: completed
Thu Nov 17 08:08:27 2022
Media Recovery failed with error 354
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_pr00_7116.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 20866 更改 310761542 时间 11/17/2022 03:00:04
ORA-00312: 联机日志 1 线程 1: '+DATA/orcl/onlinelog/group_1.261.1116409583'
Thu Nov 17 08:08:27 2022
ORA-10877 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过对redo进行处理顺利recover成功并完美open库

SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

Patch SCN工具快速解决ORA-600 2662问题

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Patch SCN工具快速解决ORA-600 2662问题

有一个数据库由于redo异常，强制拉库启动的时候报ORA-600 2662

Sun Oct 23 06:51:13 2022
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\trace\xff01_ora_5016.trc  (incident=264609):
ORA-00600: ??????, ??: [2662], [9], [1784167754], [9], [1784229886], [12583040], [], [], [], [], [], []
Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\incident\incdir_264609\xff01_ora_5016_i264609.trc
Sun Oct 23 06:51:17 2022
Dumping diagnostic data in directory=[cdmp_20221023065117],requested by (instance=1,osid=5016),summary=[incident=264609].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\trace\xff01_ora_5016.trc:
ORA-00600: ??????, ??: [2662], [9], [1784167754], [9], [1784229886], [12583040], [], [], [], [], [], []
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\trace\xff01_ora_5016.trc:
ORA-00600: ??????, ??: [2662], [9], [1784167754], [9], [1784229886], [12583040], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 5016): terminating the instance due to error 600
Sun Oct 23 06:51:22 2022
Instance terminated by USER, pid = 5016
ORA-1092 signalled during: alter database open resetlogs...

报错比较明显由于scn问题导致,对于这个问题通过以前研发的Patch_SCN工具一键解决

解决给问题之后,open数据库遭遇ora-600 4194错误

Database Characterset is ZHS16GBK
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\trace\xff01_smon_4388.trc  (incident=296569):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\incident\incdir_296569\xff01_smon_4388_i296569.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
No Resource Manager plan active
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\trace\xff01_ora_1628.trc  (incident=296617):
ORA-00600: 内部错误代码, 参数: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\incident\incdir_296617\xff01_ora_1628_i296617.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun Oct 23 08:23:02 2022
Block recovery from logseq 1, block 568 to scn 41438874500
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: E:\ORADATA\ONLINELOG\GROUP_1.261.840661629
  Mem# 1: E:\ORADATA\ONLINELOG\GROUP_1.269.840661631
Block recovery stopped at EOT rba 1.570.16
Block recovery completed at rba 1.570.16, scn 9.2784168835
Block recovery from logseq 1, block 568 to scn 41438874497
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: E:\ORADATA\ONLINELOG\GROUP_1.261.840661629
  Mem# 1: E:\ORADATA\ONLINELOG\GROUP_1.269.840661631
Block recovery completed at rba 1.568.16, scn 9.2784168834
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\dbm\xff01\trace\xff01_smon_4388.trc:
ORA-01595: 释放区 (2) 回退段 (1) 时出错
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []

处理异常undo问题,数据库open成功，建议业务安排导出数据导入新库,完成本次恢复
Patch_SCN下载:Patch_SCN下载
Patch_SCN使用说明:Patch_SCN使用说明

Controlfile sequence number in file header is different from the one in memory

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Controlfile sequence number in file header is different from the one in memory

最近一段时间遇到多个客户类似如下错误,由于某种原因导致rac两个节点oracle实例一段时间之后就crash,然后重启,系统非常不稳定,严重影响业务使用,一个节点alert日志有类似日志

Sat Oct 22 13:01:59 2022
Instance recovery: looking for dead threads
********************* ATTENTION: ******************** 
 The controlfile header block returned by the OS
 has a sequence number that is too old. 
 The controlfile might be corrupted.
 PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE 
 without following the steps below.
 RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE 
 TO THE DATABASE, if the controlfile is truly corrupted.
 In order to re-start the instance safely, 
 please do the following:
 (1) Save all copies of the controlfile for later 
     analysis and contact your OS vendor and Oracle support.
 (2) Mount the instance and issue: 
     ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 (3) Unmount the instance. 
 (4) Use the script in the trace file to
     RE-CREATE THE CONTROLFILE and open the database. 
*****************************************************
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
SMON (ospid: 31905): terminating the instance
Sat Oct 22 13:02:03 2022
System state dump requested by (instance=1, osid=31905 (SMON)), summary=[abnormal instance termination].
System State dumped to trace file 
     /u02/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_diag_31863_20221022130203.trc
Sat Oct 22 13:02:04 2022
ORA-1092 : opitsk aborting process
Instance terminated by SMON, pid = 31905

另外的一个节点错误日志如下:

Sat Oct 22 13:02:41 2022
[24610] Successfully onlined Undo Tablespace 5.
Undo initialization finished serial:0 start:152928776 end:152930636 diff:1860 (18 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Sat Oct 22 13:03:10 2022
Error: Controlfile sequence number in file header is different from the one in memory
       Please check that the correct mount options are used if controlfile is located on NFS
USER (ospid: 24610): terminating the instance
Sat Oct 22 13:03:10 2022
System state dump requested by (instance=2, osid=24610), summary=[abnormal instance termination].
System State dumped to trace file 
         /u02/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_diag_24486_20221022130310.trc
Dumping diagnostic data in directory=[cdmp_20221022130310], 
requested by (instance=2, osid=24610), summary=[abnormal instance termination].
Instance terminated by USER, pid = 24610

通过节点的日志基本上可以确定是由于Controlfile sequence number异常导致,官方也有类似的文档描述:
Instance Crashed With “Controlfile sequence number in file header is different from the onein memory” (Doc ID 2884958.1)

oracle open hang 等待cursor: pin S wait on X

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：oracle open hang 等待cursor: pin S wait on X

客户19.3数据库无法在open过程hang住

分析alert日志

2022-10-18T15:04:57.374918+08:00
db_recovery_file_dest_size of 102400 MB is 9.58% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2022-10-18T15:09:55.535116+08:00
ORCLPDB(4):>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=40
ORCLPDB(4):System State dumped to trace file /data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_31225.trc
2022-10-18T15:19:33.374783+08:00
ORCLPDB(4):Undo initialization recovery: err:1013 start: 1911760 end: 2790176 diff: 878416 ms (878.4 seconds)
Pdb ORCLPDB hit error 1013 during open read write (1) and will be closed.

这里比较明显,cdb本身open正常,但是其中的ORCLPDB这个pdb无法open,从而显示hang的情况.查询数据库会话情况

SQL> select event,sql_id from v$session where wait_class#<>6;

EVENT								 SQL_ID
---------------------------------------------------------------- -------------
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh

EVENT								 SQL_ID
---------------------------------------------------------------- -------------
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
row cache lock							 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
SQL*Net message to client					 1dhc13tspcmys
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh

EVENT								 SQL_ID
---------------------------------------------------------------- -------------
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh
cursor: pin S wait on X 					 8vyjutx6hg3wh

33 rows selected.

SQL> select sql_text from v$sql where sql_id='8vyjutx6hg3wh';

SQL_TEXT
--------------------------------------------------------------------------------
update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undo
sqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1

SQL> col machine for a30
SQL> /

   INST_ID	  SID PADDR	       SQL_ID	     EVENT			    MACHINE	 PROGRAM
---------- ---------- ---------------- ------------- ------------------------------ ------------ ------------------------
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)

   INST_ID	  SID PADDR	       SQL_ID	     EVENT			    MACHINE	 PROGRAM
---------- ---------- ---------------- ------------- ------------------------------ ------------ -----------------------
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 4517 00000001907FEC50 8vyjutx6hg3wh row cache lock		    xifenfei	 oracle@xifenfei (P000)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)

   INST_ID	  SID PADDR	       SQL_ID	     EVENT			    MACHINE	 PROGRAM
---------- ---------- ---------------- ------------- ------------------------------ ------------ -----------------------
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)
	 1	 5645 000000018095AF40 8vyjutx6hg3wh cursor: pin S wait on X	    xifenfei	 oracle@xifenfei (P002)

31 rows selected.

SQL> l
  1  select b.INST_ID,b.sid,b.paddr,b.sql_id,b.event,b.MACHINE,b.PROGRAM from gv$session a,gv$session b
  2* where a.event='cursor: pin S wait on X'  and a.FINAL_BLOCKING_INSTANCE=b.INST_ID and
  3* a.FINAL_BLOCKING_SESSION=b.sid
SQL>

通过上述分析,可以确认是在open pdb的过程中被cursor: pin S wait on X等待事件阻塞,而被阻塞的sql是update /*+ rule */ undo$ set …………，基于这样的情况.大概率可以确认是由于bug导致.通过查询mos,确认和Bug 30931981 – Open Reset Logs Hangs With ‘row cache lock’ and ‘cursor: pin s wait for x’ Waits (Doc ID 30931981.8)类似.

不过由于客户的版本是19.3,没有对应的小patch发布.通过对相关恢复事务和恢复方式进行处理,在没有对数据库版本进行任何调整的情况下,顺利打开数据库以最快的速度恢复业务

ORA-600 ktubko_1 恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 ktubko_1 恢复

oracle 12.2的rac库,pdb在open成功之后,没过多久会自动crash掉,主要报错ORA-600 ktubko_1

2022-10-08T16:00:17.874444+08:00
XFF(5):Endian type of dictionary set to little
2022-10-08T16:00:18.602483+08:00
XFF(5):[218515] Successfully onlined Undo Tablespace 26.
XFF(5):Undo initialization finished serial:0 start:73483625 end:73484200 diff:575 ms (0.6 seconds)
XFF(5):Database Characterset for XFF is ZHS16GBK
2022-10-08T16:00:19.340271+08:00
Buffer Cache Full DB Caching mode changing from FULL CACHING ENABLED to FULL CACHING DISABLED 
Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 1394670 MBs bigger than current size. 
2022-10-08T16:00:21.308122+08:00
XFF(5):Opening pdb with no Resource Manager plan active
2022-10-08T16:00:22.655433+08:00
Pluggable database XFF opened read write
Completed:  ALTER PLUGGABLE DATABASE ALL OPEN
2022-10-08T16:00:36.419719+08:00
XFF(5):Setting Resource Manager plan SCHEDULER[0x4AC8]:DEFAULT_MAINTENANCE_PLAN via scheduler window
XFF(5):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2022-10-08T16:00:57.054295+08:00
XFF(5):minact-scn: got error during useg scan e:1555 usn:57
XFF(5):minact-scn: useg scan erroring out with error e:1555
2022-10-08T16:01:41.527943+08:00
Errors in file /u01/app/db/diag/rdbms/orcl/orcl1/trace/orcl1_smon_218039.trc  (incident=737693) (PDBNAME=XFF):
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
XFF(5):Incident details in: /u01/app/db/diag/rdbms/orcl/orcl1/incident/incdir_737693/orcl1_smon_218039_i737693.trc
XFF(5):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-10-08T16:01:41.530481+08:00
XFF(5):*****************************************************************
XFF(5):An internal routine has requested a dump of selected redo.
XFF(5):This usually happens following a specific internal error, when
XFF(5):analysis of the redo logs will help Oracle Support with the
XFF(5):diagnosis.
XFF(5):It is recommended that you retain all the redo logs generated (by
XFF(5):all the instances) during the past 12 hours, in case additional
XFF(5):redo dumps are required to help with the diagnosis.
XFF(5):*****************************************************************
2022-10-08T16:01:42.611317+08:00
XFF(5):*****************************************************************
XFF(5):An internal routine has requested a dump of selected redo.
XFF(5):This usually happens following a specific internal error, when
XFF(5):analysis of the redo logs will help Oracle Support with the
XFF(5):diagnosis.
XFF(5):It is recommended that you retain all the redo logs generated (by
XFF(5):all the instances) during the past 12 hours, in case additional
XFF(5):redo dumps are required to help with the diagnosis.
XFF(5):*****************************************************************
XFF(5):ORACLE Instance orcl1 (pid = 44) - Error 600 encountered while recovering transaction (12, 1) on object 50.
2022-10-08T16:01:42.611961+08:00
XFF(5):Errors in file /u01/app/db/diag/rdbms/orcl/orcl1/trace/orcl1_smon_218039.trc:
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
2022-10-08T16:01:42.849438+08:00
Errors in file /u01/app/db/diag/rdbms/orcl/orcl1/trace/orcl1_smon_218039.trc  (incident=737694) (PDBNAME=XFF):
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
XFF(5):Incident details in: /u01/app/db/diag/rdbms/orcl/orcl1/incident/incdir_737694/orcl1_smon_218039_i737694.trc
…………
2022-10-08T16:01:55.212368+08:00
Instance Critical Process (pid: 44, ospid: 218039, SMON) died unexpectedly
PMON (ospid: 217933): terminating the instance due to error 474
2022-10-08T16:01:55.379857+08:00
System state dump requested by (instance=1, osid=217933 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/db/diag/rdbms/orcl/orcl1/trace/orcl1_diag_217966_20221008160155.trc
2022-10-08T16:01:56.417514+08:00
ORA-1092 : opitsk aborting process

因为有smon报的ORACLE Instance orcl1 (pid = 44) – Error 600 encountered while recovering transaction (12, 1) on object xxx这种比较明显错误,基本上可以定位是undo问题.对undo异常事务进行处理,数据库顺利open,并且稳定不再crash,然后对异常对象进行处理(当然也可以逻辑迁移)

在oracle 12.2到18.14的rac环境的cdb库中,如果节点sga大小不一致,而且有一个节点sga大于128G,就可能出现该问题,敬请注意

Bug 32347014: ORA-600[4506], ORA-600[KTUBKO_1] OCCUR AND INSTANCE CRASHES

存储强制拉lun导致数据库异常恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：存储强制拉lun导致数据库异常恢复

客户那边反馈有几个lun异常,无法正常online

通过存储工程师强制拉起来lun(清除掉了cache),但是数据库无法正常mount

Fri Sep 30 17:22:57 BEIST 2022
ALTER DATABASE   MOUNT
Fri Sep 30 17:22:57 BEIST 2022
This instance was first to mount
Fri Sep 30 17:22:58 BEIST 2022
Starting background process ASMB
ASMB started with pid=25, OS id=12976304
Starting background process RBAL
RBAL started with pid=26, OS id=12779520
Fri Sep 30 17:23:02 BEIST 2022
SUCCESS: diskgroup DATA was mounted
Fri Sep 30 17:23:06 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/udump/xifenfei2_ora_14549110.trc:
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [423012], [422765], [0x000000000], [], [], [], []
Fri Sep 30 17:23:07 BEIST 2022
ORA-600 signalled during: ALTER DATABASE   MOUNT...
Fri Sep 30 17:23:07 BEIST 2022
Trace dumping is performing id=[cdmp_20220930172307]
Fri Sep 30 17:23:09 BEIST 2022
Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 9175148

可以要求保护第一现场,把asm中的数据恢复到文件系统中,然后进行恢复,由于客户是10g的环境,无法直接使用asmcmd中的cp实现此项操作,数据库也没有mount成功(无法使用rman的copy),考虑使用oracle的amdu实现此项操作需求.在拷贝过程中报AMDU-00204报错

root@xifenfei2:/recover/amduo#./amdu -diskstring '/dev/rhdiskpower*' -extract data.298 -noreport
amdu_2022_10_01_14_25_31/
AMDU-00204: file not found; arguments: [3] [DATA]
LEM-00031: Error encountered in lempgmh after calling lmserr.

通过dbv校验拷贝出来的数据文件

racle@xifenfei2:/recover#dbv file=/recover/amduo/amdu_2022_10_01_14_25_31/DATA_298.f

DBVERIFY: Release 10.2.0.5.0 - Production on Sat Oct 1 14:36:50 2022

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

DBVERIFY - Verification starting : FILE = /recover/amduo/amdu_2022_10_01_14_33_26/DATA_298.f


DBVERIFY - Verification complete

Total Pages Examined         : 262144
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 262143
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 121293100 (0.121293100)

确认此报错（AMDU-00204 LEM-00031）对于拷贝出来的数据文件无直接影响，可以忽略,拷贝出来所有文件进行重建ctl，报ORA-01159错误

SQL> CREATE CONTROLFILE REUSE DATABASE "xifenfei" NORESETLOGS  NOARCHIVELOG  
  2      MAXLOGFILES 50  
    MAXLOGMEMBERS 5  
    MAXDATAFILES 100  
    MAXINSTANCES 8  
    MAXLOGHISTORY 226  
LOGFILE  
group 5 '/recover/df/DATA_262.f'  size 200M ,
group 2 '/recover/df/DATA_266.f'  size 200M ,
group 1 '/recover/df/DATA_267.f'  size 200M ,
group 3 '/recover/df/DATA_281.f'  size 200M ,
group 4 '/recover/df/DATA_282.f'  size 200M ,
group 6 '/recover/df/DATA_283.f'  size 200M
DATAFILE  
'/recover/df/DATA_295.f',
'/recover/df/DATA_298.f',
'/recover/df/DATA_272.f',
'/recover/df/DATA_273.f',
'/recover/df/DATA_296.f',
'/recover/df/DATA_274.f',
'/recover/df/DATA_276.f',
'/recover/df/DATA_277.f',
'/recover/df/DATA_275.f',
'/recover/df/DATA_279.f',
'/recover/df/DATA_278.f',
'/recover/df/DATA_288.f',
'/recover/df/DATA_269.f',
'/recover/df/DATA_300.f',
'/recover/df/DATA_264.f',
'/recover/df/DATA_287.f',
'/recover/df/DATA_280.f',
'/recover/df/DATA_286.f',
'/recover/df/DATA_268.f',
'/recover/df/DATA_285.f',
'/recover/df/DATA_297.f'
CHARACTER SET UTF8  
;  
 37  CREATE CONTROLFILE REUSE DATABASE "xifenfei" NORESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files - wrong database id
ORA-01110: data file 3: '/recover/df/DATA_288.f'

由于部分文件不是该库的,通过进一步分析,除掉不是该库的文件,重建ctl文件成功.尝试recover数据库，报大量ORA-07445错误，由于cache丢失redo损坏导致,此类操作可能导致数据文件损坏【恢复需要谨慎,最好对数据文件做一次备份】

Sat Oct 01 16:23:16 BEIST 2022
ALTER DATABASE RECOVER  database  
Media Recovery Start
 parallel recovery started with 15 processes
Sat Oct 01 16:23:16 BEIST 2022
Recovery of Online Redo Log: Thread 1 Group 5 Seq 2202 Reading mem 0
  Mem# 0: /recover/df/DATA_262.f
Sat Oct 01 16:23:16 BEIST 2022
Recovery of Online Redo Log: Thread 2 Group 6 Seq 2394 Reading mem 0
  Mem# 0: /recover/df/DATA_283.f
Sat Oct 01 16:23:28 BEIST 2022
Recovery of Online Redo Log: Thread 2 Group 3 Seq 2395 Reading mem 0
  Mem# 0: /recover/df/DATA_281.f
Sat Oct 01 16:23:34 BEIST 2022
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2203 Reading mem 0
  Mem# 0: /recover/df/DATA_267.f
Sat Oct 01 16:23:35 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p009_13107402.trc:
ORA-07445: exception encountered: core dump [kcbzfc+00dc] [SIGSEGV] 
Sat Oct 01 16:23:35 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p014_7929944.trc:
ORA-07445: exception encountered: core dump [kcbzfc+00dc] [SIGSEGV]
Sat Oct 01 16:23:35 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p011_10092678.trc:
ORA-07445: exception encountered: core dump [kcbzfc+00dc] [SIGSEGV] 
Sat Oct 01 16:23:35 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_dbw1_12189898.trc:
ORA-07445: exception encountered: core dump [kcbzdh+0324] [SIGSEGV] 
Sat Oct 01 16:23:35 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p005_14549014.trc:
ORA-07445: exception encountered: core dump [kcbbufaddr2hdr+00d8] [SIGSEGV] 
Sat Oct 01 16:23:35 BEIST 2022
Hex dump of (file 6, block 10) in trace file /home/oracle/admin/xifenfei/bdump/xifenfei2_dbw0_13565988.trc
Corrupt block relative dba: 0x0180000a (file 6, block 10)
Bad header found during buffer corrupt after write
Data in bad block:
 type: 2 format: 1 rdba: 0x00000180
 last change scn: 0xa0c3.000a6eeb seq: 0x0 flg: 0x00
 spare1: 0x2 spare2: 0xa2 spare3: 0x3486
 consistency value in tail: 0x0000a0c3
 check value in block header: 0x204
 block checksum disabled
Reread of rdba: 0x0180000a (file 6, block 10) found different data
Sat Oct 01 16:23:35 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_dbw0_13565988.trc:
ORA-07445: exception encountered: core dump [kcbbiop+01b8] [SIGSEGV] [Invalid permissions for mapped object] 
Sat Oct 01 16:23:36 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p014_7929944.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbzfc+00dc] [SIGSEGV] [Address not mapped to object]
Sat Oct 01 16:23:36 BEIST 2022
Trace dumping is performing id=[cdmp_20221001162336]
Sat Oct 01 16:23:37 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p011_10092678.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbzfc+00dc] [SIGSEGV] [Address not mapped to object] 
Sat Oct 01 16:23:37 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p009_13107402.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbzfc+00dc] [SIGSEGV] [Address not mapped to object]
Sat Oct 01 16:23:37 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_dbw1_12189898.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbzdh+0324] [SIGSEGV] [Address not mapped to object]
Sat Oct 01 16:23:37 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p005_14549014.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbbufaddr2hdr+00d8] [SIGSEGV] [Address not mapped to object] 
Sat Oct 01 16:23:37 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_dbw0_13565988.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbbiop+01b8] [SIGSEGV] [Invalid permissions for mapped object]
Sat Oct 01 16:23:37 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p005_14549014.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbbufaddr2hdr+00d8] [SIGSEGV] [Address not mapped to object] 
Sat Oct 01 16:23:38 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p010_9502918.trc:
ORA-00600: internal error code, arguments: [3020], [3], [31913], [2], [2395], [210418], [16], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 31913)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: '/recover/df/DATA_278.f'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Sat Oct 01 16:23:39 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p010_9502918.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-00600: internal error code, arguments: [3020], [3], [31913], [2], [2395], [210418], [16], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 31913)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: '/recover/df/DATA_278.f'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Sat Oct 01 16:23:39 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_pmon_14418166.trc:
ORA-00471: DBWR process terminated with error
Sat Oct 01 16:23:39 BEIST 2022
PMON: terminating instance due to error 471
Sat Oct 01 16:23:40 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/bdump/xifenfei2_p010_9502918.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state+027c] [SIGSEGV] 
ORA-00600: internal error code, arguments: [3020], [3], [31913], [2], [2395], [210418], [16], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 31913)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 3: '/recover/df/DATA_278.f'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
Sat Oct 01 16:23:46 BEIST 2022
Dump system state for local instance only
System State dumped to trace file /home/oracle/admin/xifenfei/bdump/xifenfei2_diag_15401212.trc
Sat Oct 01 16:23:46 BEIST 2022
Trace dumping is performing id=[cdmp_20221001162346]
Sat Oct 01 16:23:49 BEIST 2022
Instance terminated by PMON, pid = 14418166

绕过redo,直接强制启动库,报ORA-01092错误

QL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area      10737418240 bytes
Fixed Size                        2114208 bytes
Variable Size                  1560284512 bytes
Database Buffers               9160359936 bytes
Redo Buffers                     14659584 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

分析alert日志,确认是由于undo异常导致

Additional information: 3
Sat Oct 01 17:25:21 BEIST 2022
Setting recovery target incarnation to 2
Sat Oct 01 17:25:21 BEIST 2022
Assigning activation ID 1094862311 (0x414245e7)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /recover/df/DATA_267.f1
Successful open of redo thread 1
Sat Oct 01 17:25:21 BEIST 2022
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Oct 01 17:25:21 BEIST 2022
SMON: enabling cache recovery
Sat Oct 01 17:25:23 BEIST 2022
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.6ee0bde5):
Sat Oct 01 17:25:23 BEIST 2022
select ctime, mtime, stime from obj$ where obj# = :1
Sat Oct 01 17:25:23 BEIST 2022
Errors in file /home/oracle/admin/xifenfei/udump/xifenfei2_ora_19726450.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 19726450
ORA-1092 signalled during: alter database open resetlogs...

类似此类错误的解决方案,以前写过参考:
在数据库open过程中常遇到ORA-01555汇总
 数据库open过程遭遇ORA-1555对应sql语句补充
 Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障
 使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
解决该问题,数据库启动正常,逻辑导出数据，导入数据完成此次恢复任务,实现绝大部分数据恢复

ORA-00316 ORA-00312故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00316 ORA-00312故障处理

数据库启动报ORA-00316,ORA-00312,无法正常启动

通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)分析,确认是当前redo损坏

对于这种情况,只能是屏蔽一致性,强制拉库,结果在拉库过程中报ORA-600 2662错误

这个错误相对比较简单,修改下相关scn即可,数据库open成功

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 3.8482E+10 bytes
Fixed Size                  2261368 bytes
Variable Size            8187285128 bytes
Database Buffers         3.0199E+10 bytes
Redo Buffers               93593600 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "xifenfei" NORESETLOGS  NOARCHIVELOG
  2          MAXLOGFILES 50
  3         MAXLOGMEMBERS 5
  4         MAXDATAFILES 1000
  5         MAXINSTANCES 8
  6         MAXLOGHISTORY 2920
  7  LOGFILE
  8         group 1   '/u01/oracle/app/oradata/xifenfei/redo01.log' size 500M,
  9         group 2   '/u01/oracle/app/oradata/xifenfei/redo02.log' size 500M,
 10         group 6   '/u01/oracle/app/oradata/xifenfei/redo06.log' size 500M,
 11         group 4   '/u01/oracle/app/oradata/xifenfei/redo04.log' size 500M,
 12         group 5   '/u01/oracle/app/oradata/xifenfei/redo05.log' size 500M,
 13         group 3   '/u01/oracle/app/oradata/xifenfei/redo03.log' size 500M
 14  DATAFILE
 15          '/u01/oracle/app/oradata/xifenfei/system01.dbf',
 16          '/u01/oracle/app/oradata/xifenfei/sysaux01.dbf',
 17          '/u01/oracle/app/oradata/xifenfei/undotbs01.dbf',
 18          '/u01/oracle/app/oradata/xifenfei/users01.dbf',
………………
 49          '/u01/oracle/app/oradata/xifenfei/XIFENFEI.dbf'
 50  CHARACTER SET  ZHS16GBK ;

Control file created.

SQL> recover database;
ORA-10877: error signaled in parallel recovery slave


SQL> recover database until cancel;
ORA-00279: change 2290050101 generated at 09/30/2022 23:18:22 needed for thread
1
ORA-00289: suggestion : /u02/oracle/arch/1_2_1116803861.dbf
ORA-00280: change 2290050101 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/app/oradata/xifenfei/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;

Database altered.

检查数据库字典一致性

SQL> @1
HCheck Version 07MAY18 on 01-OCT-2022 01:07:48
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: XIFENFEI

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- MissingOIDOnObjCol          ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- SourceNotInObj              ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- OversizedFiles              ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- PoorDefaultStorage          ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- PoorStorage                 ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- TabPartCountMismatch        ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- OrphanedTabComPart          ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- MissingSum$                 ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- MissingDir$                 ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- DuplicateDataobj            ... 1102000400 <=  *All Rel* 10/01 01:07:48 PASS
.- ObjSynMissing               ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- ObjSeqMissing               ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedUndo                ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedIndex               ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedIndexPartition      ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedIndexSubPartition   ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedTable               ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedTablePartition      ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedTableSubPartition   ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- MissingPartCol              ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedSeg$                ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- OrphanedIndPartObj#         ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- DuplicateBlockUse           ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- FetUet                      ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- Uet0Check                   ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- SeglessUET                  ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadInd$                     ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadTab$                     ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadIcolDepCnt               ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- ObjIndDobj                  ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- TrgAfterUpgrade             ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- ObjType0                    ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadOwner                    ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- StmtAuditOnCommit           ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadPublicObjects            ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadSegFreelist              ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- BadDepends                  ... 1102000400 <=  *All Rel* 10/01 01:07:49 PASS
.- CheckDual                   ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- ObjectNames                 ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- BadCboHiLo                  ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- ChkIotTs                    ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- NoSegmentIndex              ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- BadNextObject               ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- DroppedROTS                 ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- FilBlkZero                  ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- DbmsSchemaCopy              ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- OrphanedObjError            ... 1102000400 >  1102000000 10/01 01:07:50 PASS
.- ObjNotLob                   ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- MaxControlfSeq              ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- SegNotInDeferredStg         ... 1102000400 >  1102000000 10/01 01:07:50 PASS
.- SystemNotRfile1             ... 1102000400 >   902000000 10/01 01:07:50 PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- OrphanTrigger               ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
.- ObjNotTrigger               ... 1102000400 <=  *All Rel* 10/01 01:07:50 PASS
---------------------------------------
01-OCT-2022 01:07:50  Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)

PL/SQL procedure successfully completed.

Statement processed.

数据库字典本身没有大问题,但是为了排除潜在风险,建议逻辑迁移到新库

分类目录归档：Oracle备份恢复