联系：手机/微信(+86 17813235971) QQ(107644445)
标题：IMP-00009: abnormal end of export file
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
exp导出数据正常,没有任何报错
IMP-00009: abnormal end of export file IMP-00020: long column too large for column buffer size (2) Import terminated successfully with warnings.
使用show=y进行dmp文件验证,也报IMP-00009错误,证明是dmp本身异常
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户本想truncate业务用下面所有的表,结果连接成SYS用户,并且拼接truncate 批量语句,导致sys用户下面大量表被truncate
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
由于主机异常断电，导致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错误
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; 数据库已更改。
然后逻辑导出数据,导入新库,完成数据迁移工作
联系：手机/微信(+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异常
联系：手机/微信(+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; 数据库已更改。
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有一个朋友自己想测试只用system文件open库,闲着没事给他测试了下,顺利open成功（主要还是经验比较多,规避了很多坑）
1. 准备参数文件
*.audit_file_dest='C:\app\XFF\admin\ORCL\adump' *.audit_trail='none' *.compatible='11.2.0.3.0' *.control_files='H:\TEMP\11203\control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='DBM' *.diagnostic_dest='C:\app\XFF' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=2147483648 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=170 *.sga_target=6442450944 *.undo_tablespace='UNDOTBS1' undo_management=MANUAL _corrupted_rollback_segments= _allow_resetlogs_corruption=true
2. 准备重建ctl语句
CREATE CONTROLFILE REUSE DATABASE "DBM" RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'H:\TEMP\11203\redo01.log' SIZE 50M,
GROUP 2 'H:\TEMP\11203\redo02.log' SIZE 50M,
GROUP 3 'H:\TEMP\11203\redo03.log' SIZE 50M
DATAFILE
'H:\TEMP\11203\system01.dbf'
CHARACTER SET ZHS16GBK
;
3. 重建ctl并且resetogs open库
SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 40438873410 (在 10/21/2022 14:06:16 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\ARC0000000093_1118545292.0001
ORA-00280: 更改 40438873410 (用于线程 1) 在序列 #93 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'H:\TEMP\11203\SYSTEM01.DBF'
ORA-01112: 未启动介质恢复
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01176: data dictionary has more than the 100 files allowed by the
controlfie
进程 ID: 3952
会话 ID: 14 序列号: 3
MAXDATAFILES值不对修改正确值，重建ctl，open库
SQL> RECOVER DATABASE; 完成介质恢复。 SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * 第 1 行出现错误: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number with name "" too small 进程 ID: 6916 会话 ID: 14 序列号: 1
alert日志内容
Database Characterset is ZHS16GBK Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_smon_9384.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_ora_6916.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_ora_6916.trc: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小 Error 604 happened during db open, shutting down database USER (ospid: 6916): terminating the instance due to error 604 Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_smon_9384.trc (incident=2521): ORA-00600: 内部错误代码, 参数: [2662], [9], [1784188335], [9], [1784216952], [6019273], [], [], [], [], [], [] Incident details in: C:\APP\XFF\diag\rdbms\dbm\test\incident\incdir_2521\test_smon_9384_i2521.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Non-fatal internal error happenned while SMON was doing temporary segment drop. SMON encountered 1 out of maximum 100 non-fatal internal errors. Tue Nov 01 10:17:49 2022 Instance terminated by USER, pid = 6916 ORA-1092 signalled during: ALTER DATABASE OPEN...
修改文件头scn，并正常open库
SQL> startup nomount pfile='d:/pfile.txt' ORACLE 例程已经启动。 Total System Global Area 6413680640 bytes Fixed Size 2267184 bytes Variable Size 1107298256 bytes Database Buffers 5284823040 bytes Redo Buffers 19292160 bytes SQL> alter database mount; 数据库已更改。 SQL> set numw 16 SQL> col CHECKPOINT_TIME for a40 SQL> set lines 150 SQL> set pages 1000 SQL> SELECT status, 2 to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#, 3 count(*) ROW_NUM 4 FROM v$datafile_header 5 GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy 6 ORDER BY status, checkpoint_change#, checkpoint_time; STATUS CHECKPOINT_TIME FUZ CHECKPOINT_CHANGE# ROW_NUM ------- ---------------------------------------- --- ------------------ ---------------- OFFLINE 0 121 ONLINE 2022-11-01 10:17:44 YES 40438893615 1
SQL> alter database open; 数据库已更改。 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------- H:\TEMP\11203\SYSTEM01.DBF C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00002 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00003 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00004 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00005 ……………… C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00121 C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00122 已选择122行。
恢复完成
联系：手机/微信(+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工具一键解决
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使用说明
联系：手机/微信(+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)
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：oracle open hang 等待cursor: pin S wait on X
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户19.3数据库无法在open过程hang住
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)类似.
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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,然后对异常对象进行处理(当然也可以逻辑迁移)
