联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户的mysql被人从应用层面攻击,并且删除了一些数据,导致业务无法正常使用,通过底层分析binlog确认类似恢复操作
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], []
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
再来一例客户由于断电强制拉库之后,报ORA-600 4194错误的case
Wed Jan 10 22:21:01 2024 ARC3 started with pid=39, OS id=4672 ARC1: Archival started ARC2: Archival started ARC2: Becoming the 'no FAL' ARCH ARC2: Becoming the 'no SRL' ARCH ARC1: Becoming the heartbeat ARCH SMON: enabling cache recovery Archived Log entry 4517 added for thread 1 sequence 23 ID 0xad378582 dest 1: [4796] Successfully onlined Undo Tablespace 8. Undo initialization finished serial:0 start:3480640 end:3480843 diff:203 (2 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_smon_4508.trc ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. replication_dependency_tracking turned off (no async multimaster replication found) Wed Jan 10 22:21:03 2024 Block recovery from logseq 24, block 63 to scn 42269588 Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0 Mem# 0: E:\ORADATA\xifenfei\REDO03.LOG Block recovery completed at rba 24.64.16, scn 0.42269589 Block recovery from logseq 24, block 63 to scn 42269587 Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0 Mem# 0: E:\ORADATA\xifenfei\REDO03.LOG Block recovery completed at rba 24.63.16, scn 0.42269588 Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_smon_4508.trc: ORA-01595: error freeing extent (2) of rollback segment (2)) ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Starting background process QMNC Wed Jan 10 22:21:03 2024 QMNC started with pid=40, OS id=6576 ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Wed Jan 10 22:21:04 2024 Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_mmon_6584.trc ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Starting background process SMCO Wed Jan 10 22:21:04 2024 SMCO started with pid=41, OS id=6292 Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x675E484A] [PC:0xCBBC18, kgegpa()+38] Wed Jan 10 22:21:06 2024 Errors in file E:\app\Administrator\diag\rdbms\xifenfei\xifenfei\cdump\xifenfeicore.log ORA-07445: caught exception [ACCESS_VIOLATION] at [kgegpa()+38] [0x0000000000CBBC18] Wed Jan 10 22:21:08 2024 PMON (ospid: 3212): terminating the instance due to error 397
这个比较简单屏蔽undo,启动库,然后重建undo,导出数据导入新库完成恢复
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户虚拟化平台断电，导致oracle其数据库启动ORA-01200错误
SQL> alter database open; alter database open * ERROR at line 1: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oradata/orcl/system01.dbf' ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks
对应的alert日志如下
Thu Jan 11 11:36:48 2024 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 1685778896 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Thu Jan 11 11:36:52 2024 ALTER DATABASE OPEN Read of datafile '/oradata/orcl/system01.dbf' (fno 1) header failed with ORA-01200 Rereading datafile 1 header failed with ORA-01200 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10847.trc: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oradata/orcl/system01.dbf' ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks ORA-1122 signalled during: ALTER DATABASE OPEN... Thu Jan 11 11:36:53 2024 Checker run found 1 new persistent data failures Thu Jan 11 11:41:55 2024 alter database open Read of datafile '/oradata/orcl/system01.dbf' (fno 1) header failed with ORA-01200 Rereading datafile 1 header failed with ORA-01200 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12550.trc: ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oradata/orcl/system01.dbf' ORA-01200: actual file size of 1122560 is smaller than correct size of 1131520 blocks ORA-1122 signalled during: alter database open...
报错比较明显system01.dbf文件本来大小应该为1131521个block,但是实际上只有1122561个block,因此无法正常启动,通过修改数据文件欺骗数据库
SQL> alter database datafile 1 resize 8770M; Database altered.
rman检测system文件正常
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友的asm磁盘组因为以前遗留问题(在另外一套机器上的asm disk被加入到了一个新的asm磁盘组中,导致老的dg直接dismount,新加入asm disk的磁盘组一直在使用,未听建议进行重建),昨天突然意外dismount了
Mon Dec 18 08:38:13 2023 NOTE: No asm libraries found in the system ASM Health Checker found 1 new failures Mon Dec 18 08:38:35 2023 NOTE: client his2:his registered, osid 3998514, mbr 0x1 Thu Jan 04 21:44:55 2024 WARNING: cache read a corrupt block: group=2(DATA) fn=1 blk=6743 disk=8 (DATA_0008) incarn=1428496145 au=3 blk=87 count=1 Errors in file /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4915366.trc: ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] NOTE: a corrupted block from group DATA was dumped to /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4915366.trc WARNING: cache read (retry) a corrupt block: group=2(DATA) fn=1 blk=6743 disk=8 (DATA_0008) incarn=1428496145 au=3 blk=87 count=1 Errors in file /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4915366.trc: ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ERROR: cache failed to read group=2(DATA) fn=1 blk=6743 from disk(s): 8(DATA_0008) ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] NOTE: cache initiating offline of disk 8 group DATA NOTE: process _user4915366_+asm4 (4915366) initiating offline of disk 8.1428496145 (DATA_0008) with mask 0x7e in group 2 NOTE: initiating PST update: grp = 2, dsk = 8/0x55251f11, mask = 0x6a, op = clear Thu Jan 04 21:44:55 2024 GMON updating disk modes for group 2 at 9 for pid 24, osid 4915366 ERROR: Disk 8 cannot be offlined, since diskgroup has external redundancy. ERROR: too many offline disks in PST (grp 2) Thu Jan 04 21:44:55 2024 NOTE: cache dismounting (not clean) group 2/0x7F35EE0E (DATA) WARNING: Offline for disk DATA_0008 in mode 0x7f failed. Thu Jan 04 21:44:55 2024 NOTE: halting all I/Os to diskgroup 2 (DATA) NOTE: messaging CKPT to quiesce pins Unix process pid: 3473846, image: oracle@zzzx1 (B000) Errors in file /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4915366.trc (incident=4023553): ORA-15335: ASM metadata corruption detected in disk group 'DATA' ORA-15130: diskgroup "DATA" is being dismounted ORA-15066: offlining disk "DATA_0008" in group "DATA" may result in a data loss ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] Incident details in: /u01/app/grid/diag/asm/+asm/+ASM4/incident/incdir_4023553/+ASM4_ora_4915366_i4023553.trc Thu Jan 04 21:44:57 2024 ERROR: ORA-15130 in COD recovery for diskgroup 2/0x7f35ee0e (DATA) ERROR: ORA-15130 thrown in RBAL for group number 2 Errors in file /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_rbal_2228716.trc: ORA-15130: diskgroup "DATA" is being dismounted
尝试重新mount 磁盘组,片刻之后自动dismount
Thu Jan 04 23:10:35 2024 NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 2 SUCCESS: diskgroup DATA was mounted SUCCESS: alter diskgroup data mount Thu Jan 04 23:10:42 2024 NOTE: diskgroup resource ora.DATA.dg is online Thu Jan 04 23:10:47 2024 NOTE: client his2:his registered, osid 3998052, mbr 0x1 Thu Jan 04 23:11:00 2024 WARNING: cache read a corrupt block: group=2(DATA) fn=1 blk=6743 disk=8 (DATA_0008) incarn=1428496181 au=3 blk=87 count=1 Errors in file /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4129826.trc: ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] NOTE: a corrupted block from group DATA was dumped to /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4129826.trc WARNING: cache read (retry) a corrupt block: group=2(DATA) fn=1 blk=6743 disk=8 (DATA_0008) incarn=1428496181 au=3 blk=87 count=1 Errors in file /u01/app/grid/diag/asm/+asm/+ASM4/trace/+ASM4_ora_4129826.trc: ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ERROR: cache failed to read group=2(DATA) fn=1 blk=6743 from disk(s): 8(DATA_0008) ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] ORA-15196: invalid ASM block header [kfc.c:26368] [blk_kfbl] [1] [6743] [6999 != 6743] NOTE: cache initiating offline of disk 8 group DATA NOTE: process _user4129826_+asm4 (4129826) initiating offline of disk 8.1428496181 (DATA_0008) with mask 0x7e in group 2 NOTE: initiating PST update: grp = 2, dsk = 8/0x55251f35, mask = 0x6a, op = clear Thu Jan 04 23:11:01 2024 GMON updating disk modes for group 2 at 21 for pid 35, osid 4129826 ERROR: Disk 8 cannot be offlined, since diskgroup has external redundancy. ERROR: too many offline disks in PST (grp 2) Thu Jan 04 23:11:01 2024 NOTE: cache dismounting (not clean) group 2/0x1CB5EE3B (DATA) WARNING: Offline for disk DATA_0008 in mode 0x7f failed. NOTE: messaging CKPT to quiesce pins Unix process pid: 5112822, image: oracle@zzzx1 (B000)
从报错信息看是DATA_0008磁盘的au 3 blkn 87的block异常,应该是block 6743被写成了6999导致了该问题
kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 4 ; 0x002: KFBTYP_FILEDIR kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 6999 ; 0x004: blk=6999 kfbh.block.obj: 1 ; 0x008: file=1 kfbh.check: 3317183844 ; 0x00c: 0xc5b83564 kfbh.fcn.base: 165670551 ; 0x010: 0x09dfee97 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 kfffdb.node.incarn: 1145623147 ; 0x000: A=1 NUMM=0x22246935 kfffdb.node.frlist.number: 4294967295 ; 0x004: 0xffffffff kfffdb.node.frlist.incarn: 0 ; 0x008: A=0 NUMM=0x0 kfffdb.hibytes: 0 ; 0x00c: 0x00000000 kfffdb.lobytes: 83482624 ; 0x010: 0x04f9d800
这个处理比较简单吧
kfbh.block.blk: 6999 ; 0x004: blk=6999 修改为 kfbh.block.blk: 6743; 0x004: blk=6743
然后kefd merge并且尝试mount磁盘组
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户在数据库没有关闭的情况下,直接操作系统层面mv方式把数据文件从一个分区迁移到另外一个分区,再创建ln -s(软连接)的方式实现数据文件不修改路径的方式数据文件迁移,结果数据库重启之后,库无法正常启动,报ORA-01172 ORA-01151错误
Fri Dec 29 09:49:19 2023
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 11 processes
Started redo scan
Completed redo scan
read 11591 KB redo, 1566 data blocks need recovery
Started redo application at
Thread 1: logseq 6320, block 479571
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0
Mem# 0: /data/oracle/oradata/orcl/redo04.log
Fri Dec 29 09:49:19 2023
Hex dump of (file 6, block 3598593) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc
Fri Dec 29 09:49:19 2023
Fri Dec 29 09:49:19 2023
Hex dump of (file 5, block 27832) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc
Hex dump of (file 6, block 3598208) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p010_6712.trc
Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e901 (file 6, block 3598593)
Reading datafile '/oadate/xff/xff.dbf' for corruption at rdba: 0x01406cb8 (file 5, block 27832)
Reread (file 6, block 3598593) found same corrupt data (logically corrupt)
Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e780 (file 6, block 3598208)
Reread (file 5, block 27832) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 3598593 OF FILE 6
Reread (file 6, block 3598208) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 3598208 OF FILE 6RECOVERY OF THREAD 1 STUCK AT BLOCK 27832 OF FILE 5
Fri Dec 29 09:49:32 2023
Slave exiting with ORA-1172 exception
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p010_6712.trc:
ORA-01172: recovery of thread 1 stuck at block 3598208 of file 6
ORA-01151: use media recovery to recover block, restore backup if needed
Fri Dec 29 09:49:32 2023
Fri Dec 29 09:49:32 2023
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p008_6708.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_p002_6696.trc:
ORA-10388: parallel query server interrupt (failure)
Fri Dec 29 09:49:32 2023
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 11591 KB redo, 1566 data blocks need recovery
Started redo application at
Thread 1: logseq 6320, block 479571
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0
Mem# 0: /data/oracle/oradata/orcl/redo04.log
Hex dump of (file 6, block 3598593) in trace file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc
Reading datafile '/oadate/xff/xff_01.dbf' for corruption at rdba: 0x01b6e901 (file 6, block 3598593)
Reread (file 6, block 3598593) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 3598593 OF FILE 6
Aborting crash recovery due to error 1172
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc:
ORA-01172: recovery of thread 1 stuck at block 3598593 of file 6
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6690.trc:
ORA-01172: recovery of thread 1 stuck at block 3598593 of file 6
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...
sqlplus恢复数据库报错
SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [6], [3578240], [28744064], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 6, block# 3578240, file offset is 3543138304 bytes) ORA-10564: tablespace xff ORA-01110: data file 6: '/oadate/xff/xff_01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'
alert日志报ORA-600 3020错误
Fri Dec 29 17:43:03 2023 ALTER DATABASE RECOVER datafile 6 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Fri Dec 29 17:43:42 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30140.trc (incident=462294): ORA-00600: internal error code, arguments: [3020], [6], [3578240], [28744064], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 6, block# 3578240, file offset is 3543138304 bytes) ORA-10564: tablespace XFF ORA-01110: data file 6: '/oadate/xff/xff_01.dbf' ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK' Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_462294/orcl_ora_30140_i462294.trc Fri Dec 29 17:43:42 2023 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Media Recovery failed with error 600 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 6 ...
此类故障是由于在线拷贝数据文件,可能有不少最新写入的数据都有数据文件和redo不一致的风险,引起这里的ORA-600 3020最好不要通过allow N corruption的方式跳过,因为可能导致大量数据文件坏块,这样就不光丢失了redo数据,可能数据文件中好的block中的很多数据也丢失.对于这种情况,我们为了减少客户的数据丢失,选择了最少数据丢失的方法:通过bbed修改文件头,然后直接recover 数据文件,open库
Fri Dec 29 18:05:36 2023 ALTER DATABASE RECOVER datafile 5 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Media Recovery Complete (orcl) Completed: ALTER DATABASE RECOVER datafile 5 ALTER DATABASE RECOVER datafile 6 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Media Recovery Complete (orcl) Completed: ALTER DATABASE RECOVER datafile 6 Fri Dec 29 18:07:02 2023 ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 11 processes Started redo scan Completed redo scan read 11591 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 6320, block 479571 Recovery of Online Redo Log: Thread 1 Group 4 Seq 6320 Reading mem 0 Mem# 0: /data/oracle/oradata/orcl/redo04.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 6320, block 502754, scn 2657849964 0 data blocks read, 0 data blocks written, 11591 redo k-bytes read Thread 1 advanced to log sequence 6321 (thread open) Thread 1 opened at log sequence 6321 Current log# 5 seq# 6321 mem# 0: /data/oracle/oradata/orcl/redo05.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set SMON: enabling cache recovery [2656] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:933676634 end:933676704 diff:70 (0 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Dec 29 18:07:04 2023 QMNC started with pid=31, OS id=2687 Completed: ALTER DATABASE OPEN
然后逻辑方式迁移数据到新库中,最大程度抢救客户数据
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户数据库resetlogs报错
Tue Dec 19 15:21:23 2023 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 1683789043 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Tue Dec 19 15:22:01 2023 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. ORA-1248 signalled during: alter database open resetlogs... Tue Dec 19 16:16:26 2023 alter database datafile 83 offline Completed: alter database datafile 83 offline Tue Dec 19 16:19:13 2023 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. Archived Log entry 50 added for thread 1 sequence 3657135 ID 0x5d907698 dest 1: Tue Dec 19 16:20:01 2023 Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc: ORA-00333: 重做日志读取块 8806400 计数 16384 出错 ORA-00312: 联机日志 2 线程 1: '/data/oradata/orcl/redo2.log' ORA-27072: 文件 I/O 错误 Linux-x86_64 Error: 25: Inappropriate ioctl for device Additional information: 4 Additional information: 8806400 Additional information: 4325376 Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc: ORA-00333: 重做日志读取块 8806400 计数 16384 出错 ARCH: All Archive destinations made inactive due to error 333 ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/arch/1_3657136_874715183.dbf' (error 333) (orcl) Committing creation of archivelog '/data/arch/1_3657136_874715183.dbf' (error 333) Tue Dec 19 16:20:46 2023 Archived Log entry 51 added for thread 1 sequence 3657132 ID 0x5d907698 dest 1: Tue Dec 19 16:21:28 2023 Archived Log entry 52 added for thread 1 sequence 3657133 ID 0x5d907698 dest 1: Tue Dec 19 16:22:13 2023 Archived Log entry 53 added for thread 1 sequence 3657134 ID 0x5d907698 dest 1: RESETLOGS after incomplete recovery UNTIL CHANGE 161052517347 Resetting resetlogs activation ID 1569748632 (0x5d907698) Tue Dec 19 16:23:43 2023 Setting recovery target incarnation to 3 Tue Dec 19 16:23:43 2023 Assigning activation ID 1683789043 (0x645c94f3) LGWR: STARTING ARCH PROCESSES Tue Dec 19 16:23:43 2023 ARC0 started with pid=40, OS id=5391 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Thread 1 advanced to log sequence 2 (thread open) Tue Dec 19 16:23:44 2023 ARC1 started with pid=41, OS id=5393 Tue Dec 19 16:23:44 2023 ARC2 started with pid=42, OS id=5395 ARC1: Archival started Tue Dec 19 16:23:44 2023 ARC3 started with pid=43, OS id=5397 ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Thread 1 opened at log sequence 2 Current log# 2 seq# 2 mem# 0: /data/oradata/orcl/redo2.log Successful open of redo thread 1 Tue Dec 19 16:23:44 2023 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Dec 19 16:23:44 2023 SMON: enabling cache recovery Tue Dec 19 16:23:44 2023 NSA2 started with pid=44, OS id=5399 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0025.7f7d42df): select ctime, mtime, stime from obj$ where obj# = :1 Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 27 (名称为 "_SYSSMU27_4233559991$") 过小 Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 27 (名称为 "_SYSSMU27_4233559991$") 过小 Error 704 happened during db open, shutting down database USER (ospid: 94696): terminating the instance due to error 704 Instance terminated by USER, pid = 94696 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (94696) as a result of ORA-1092
通过以上信息,可以的出来以下结论:
1. 客户的硬件或者文件系统可能有问题,通过系统日志进一步确认底层异常
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Sense Key : Medium Error [current] Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Add. Sense: Unrecovered read error Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 47 bc ff c0 00 01 00 00 Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev sdb, sector 1203568576 Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 1203568576 Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Sense Key : Medium Error [current] Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Add. Sense: Unrecovered read error Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 47 bd 00 c0 00 01 00 00 Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev sdb, sector 1203568832 Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 1203568832 Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Sense Key : Medium Error [current] Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] Add. Sense: Unrecovered read error Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 47 bd 00 80 00 00 08 00 Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev sdb, sector 1203568768 Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 1203568768 Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Sense Key : Medium Error [current] Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Add. Sense: Unrecovered read error Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 9b 1a 28 20 00 01 00 00 Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev sdb, sector 2602182688 Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 2602182688 Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Sense Key : Medium Error [current] Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Add. Sense: Unrecovered read error Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 9b 1a 29 20 00 01 00 00 Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev sdb, sector 2602182944 Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 2602182944 Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Sense Key : Medium Error [current] Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] Add. Sense: Unrecovered read error Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 9b 1a 29 00 00 00 08 00 Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev sdb, sector 2602182912 Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 2602182912
2. 数据库在强制拉库的时候,很可能是屏蔽了一致性,导致文件头scn过小
3. 在resetlogs之前,先offline了83号文件,这个将导致该文件的reseltogs scn和其他文件不一致,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)
HCheck Version 07MAY18 on 26-12月-2023 18:44:20
----------------------------------------------
Catalog Version 11.2.0.1.0 (1102000100)
db_name: ORCL
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj ... 1102000100 <= *All Rel* 12/26 18:44:20
PASS
.- MissingOIDOnObjCol ... 1102000100 <= *All Rel* 12/26 18:44:20
PASS
.- SourceNotInObj ... 1102000100 <= *All Rel* 12/26 18:44:20
PASS
.- IndIndparMismatch ... 1102000100 <= 1102000100 12/26 18:44:21
PASS
.- InvCorrAudit ... 1102000100 <= 1102000100 12/26 18:44:21
PASS
.- OversizedFiles ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- PoorDefaultStorage ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- PoorStorage ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- PartSubPartMismatch ... 1102000100 <= 1102000100 12/26 18:44:21
PASS
.- TabPartCountMismatch ... 1102000100 <= *All Rel* 12/26 18:44:21
*** 2023-12-26 18:44:21.507
PASS
.- OrphanedTabComPart ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- MissingSum$ ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- MissingDir$ ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- DuplicateDataobj ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- ObjSynMissing ... 1102000100 <= *All Rel* 12/26 18:44:21
PASS
.- ObjSeqMissing ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedUndo ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedIndex ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedIndexPartition ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedIndexSubPartition ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedTable ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedTablePartition ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedTableSubPartition ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- MissingPartCol ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedSeg$ ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- OrphanedIndPartObj# ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- DuplicateBlockUse ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- FetUet ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- Uet0Check ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- ExtentlessSeg ... 1102000100 <= 1102000100 12/26 18:44:22
PASS
.- SeglessUET ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadInd$ ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadTab$ ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadIcolDepCnt ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- ObjIndDobj ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- TrgAfterUpgrade ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- ObjType0 ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadOwner ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- StmtAuditOnCommit ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadPublicObjects ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadSegFreelist ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadDepends ... 1102000100 <= *All Rel* 12/26 18:44:22
*** 2023-12-26 18:44:22.571
PASS
.- CheckDual ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- ObjectNames ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- BadCboHiLo ... 1102000100 <= *All Rel* 12/26 18:44:22
PASS
.- ChkIotTs ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- NoSegmentIndex ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- BadNextObject ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- DroppedROTS ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- FilBlkZero ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- DbmsSchemaCopy ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- OrphanedObjError ... 1102000100 > 1102000000 12/26 18:44:23
PASS
.- ObjNotLob ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- MaxControlfSeq ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- SegNotInDeferredStg ... 1102000100 > 1102000000 12/26 18:44:23
PASS
.- SystemNotRfile1 ... 1102000100 > 902000000 12/26 18:44:23
*** 2023-12-26 18:44:23.779
PASS
.- DictOwnNonDefaultSYSTEM ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- OrphanTrigger ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
.- ObjNotTrigger ... 1102000100 <= *All Rel* 12/26 18:44:23
PASS
---------------------------------------
26-12月-2023 18:44:23 Elapsed: 3 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
然后增加temp,导出数据数据,完成本次数据库救援
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-01113 ORA-01110错误不一定都要Oracle Recovery Tools解决
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户联系我,说数据库故障经过他们一系列恢复之后,现在open库报ORA-01113 ORA-01110错误,咨询我Oracle Recovery Tools恢复工具是否可以解决该问题
Sat Dec 16 09:10:45 2023 alter database open Errors in file f:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_8948.trc: ORA-01113: 文件 1 需要介质恢复 ORA-01110: 数据文件 1: 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' ORA-1113 signalled during: alter database open...
其实这个错误不是这个库不能打开的本质,一般遇到这种错误的客户,都是强制拉过库,在拉库的过程中失败,才是导致库不能open的本质原因,比如通过查看相关日志发现拉库的时候报ORA-01555 ORA-00704错
Thu Dec 14 19:05:45 2023 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 13289960075 Resetting resetlogs activation ID 1596978603 (0x5f2ff5ab) Thu Dec 14 19:05:45 2023 Setting recovery target incarnation to 2 Thu Dec 14 19:05:45 2023 Assigning activation ID 1683369006 (0x64562c2e) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Thu Dec 14 19:05:45 2023 SMON: enabling cache recovery ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0003.1824b292): select ctime, mtime, stime from obj$ where obj# = :1 Errors in file f:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_7736.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 4 (名称为 "_SYSSMU4_1451910634$") 过小 Errors in file f:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_7736.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 4 (名称为 "_SYSSMU4_1451910634$") 过小 Error 704 happened during db open, shutting down database USER (ospid: 7736): terminating the instance due to error 704 Instance terminated by USER, pid = 7736 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (7736) as a result of ORA-1092 Thu Dec 14 19:05:51 2023 ORA-1092 : opitsk aborting process
比如还有客户咨询也是ORA-01113 ORA-01110错误,希望通过Oracle Recovery Tools工具来解决该问题,通过咨询客户确认他们其实是在前期恢复中报ORA-600 2662错误
E-Mail:dba@xifenfei.com
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
8个节点的rac,有一个节点有点问题,重装系统的时候,没有选择任何磁盘
[root@xxxrac5 oracle]# kfed read /dev/mapper/hdisk60 aun=2 aus=4096k kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 3 ; 0x002: KFBTYP_ALLOCTBL kfbh.datfmt: 2 ; 0x003: 0x02 kfbh.block.blk: 2048 ; 0x004: blk=2048 kfbh.block.obj: 2147483682 ; 0x008: disk=34 kfbh.check: 2349679586 ; 0x00c: 0x8c0d43e2 kfbh.fcn.base: 998219 ; 0x010: 0x000f3b4b kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 kfdatb.aunum: 916608 ; 0x000: 0x000dfc80 ……………… kfdate[446].free.hi: 0 ; 0xe1c: S=0 V=0 L=0 ASZM=0x0 S=0 kfdate[447].discriminator: 0 ; 0xe20: 0x00000000 kfdate[447].free.lo.next: 0 ; 0xe20: 0x0000 kfdate[447].free.lo.prev: 0 ; 0xe22: 0x0000 kfdate[447].free.hi: 0 ; 0xe24: S=0 V=0 L=0 ASZM=0x0 S=0
分析ssd磁盘的记录情况,发现磁盘中记录全部被置空
[root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=2 aus=4096k kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 000800000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=11 aus=4096k kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 002C00000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=100 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 006400000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=1000 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 03E800000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=10000 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 271000000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=100000 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 186A000000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0] [root@xxxrac5 oracle]# kfed read /dev/mapper/ssdisk30 aun=100000 kfbh.endian: 0 ; 0x000: 0x00 kfbh.hard: 0 ; 0x001: 0x00 kfbh.type: 0 ; 0x002: KFBTYP_INVALID kfbh.datfmt: 0 ; 0x003: 0x00 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 0 ; 0x008: file=0 kfbh.check: 0 ; 0x00c: 0x00000000 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 186A000000 00000000 00000000 00000000 00000000 [................] Repeat 255 times KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
dd ssd磁盘100M数据压缩前后大小比较(压缩之后只有150kb左右,除了分区和lv信息之外其他全部是0)
hdisk66 (360060e8007c550000030c55000000223) dm-66 HITACHI ,OPEN-V size=2.0T features='0' hwhandler='0' wp=rw `-+- policy='service-time 0' prio=1 status=active |- 10:0:1:4 sdfr 130:208 active ready running |- 7:0:0:4 sds 65:32 active ready running |- 8:0:1:4 sdu 65:64 active ready running `- 9:0:1:4 sdx 65:112 active ready running ssdisk58 (26e8d69b6480c854f6c9ce90091cfe58e) dm-22 Nimble ,Server size=2.0T features='1 queue_if_no_path' hwhandler='1 alua' wp=rw |-+- policy='round-robin 0' prio=50 status=active | `- 7:0:1:48 sdfu 131:0 active ready running `-+- policy='round-robin 0' prio=1 status=enabled `- 10:0:0:48 sdek 128:192 active ghost running ssdisk43 (2e0e493e08cfd75996c9ce90091cfe58e) dm-8 Nimble ,Server size=2.0T features='1 queue_if_no_path' hwhandler='1 alua' wp=rw |-+- policy='round-robin 0' prio=50 status=active | `- 7:0:1:35 sdev 129:112 active ready running `-+- policy='round-robin 0' prio=1 status=enabled `- 10:0:0:35 sddj 71:16 active ghost running ssdisk60 (2696bc3246f81fb116c9ce90091cfe58e) dm-34 Nimble ,Server size=2.0T features='1 queue_if_no_path' hwhandler='1 alua' wp=rw |-+- policy='round-robin 0' prio=50 status=active | `- 7:0:1:59 sdgq 132:96 active ready running `-+- policy='round-robin 0' prio=1 status=enabled `- 10:0:0:59 sdfg 130:32 active ghost running ssdisk08 (2629f870548db01a76c9ce90091cfe58e) dm-35 Nimble ,Server size=2.0T features='1 queue_if_no_path' hwhandler='1 alua' wp=rw |-+- policy='round-robin 0' prio=50 status=active | `- 7:0:1:6 sdcq 69:224 active ready running `-+- policy='round-robin 0' prio=1 status=enabled `- 10:0:0:6 sdz 65:144 active ghost running [root@odsrac5 oracle]# cat /sys/block/sdcq/queue/rotational 0 [root@odsrac5 oracle]# cat /sys/block/sdcu/queue/rotational 0 [root@odsrac5 oracle]# cat /sys/block/sdfr/queue/rotational 1
出现该问题是由于ssd盘的trim操作导致,相关知识补充:TRIM(SATA), Deallocate(NVMe), UNMAP(SCSI)指的是同一类指令，都是为了减少不必要的数据搬移。
原因：
在文件系统中，删除文件并没有真正的删除物理的数据，只是清空了记录表。而此时，对SSD来说，它并不知道文件已经被删除了，只有下次覆写的时候，SSD才能发现之前被删除的文件对应的page是无效的，从而启动GC。然而，如果在此之前发生了GC等数据搬移动作，无效的page仍然会被当做是有效的。重新磁盘分区,重建文件系统也可能触发类似操作
作用：
Trim 只是一个指令，它让操作系统通知 SSD 主控某个页的数据已经‘无效’后，任务就已完成，并没有更多的操作。TRIM 的先进性在于它可以让固态硬盘在进行垃圾回收的时候跳过移动无用数据的过程，从而不再用重新写入这些无用的数据，达到节省时间的目的。
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户通过kettle 插入数据，由于配置不当导致原数据丢失,希望能够恢复之前数据（mysql数据库）
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-600 3020/ORA-600 2662故障
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库重启之后报ORA-600 3020错误
Tue Dec 12 00:37:44 2023 ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /data/oracle/flash_recovery_area/ORCL/archivelog/2023_11_30/o1_mf_1_3867_lpj3krsn_.arc ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /data/oracle/flash_recovery_area/ORCL/archivelog/2023_12_02/o1_mf_1_3868_lppkmyhf_.arc Tue Dec 12 00:37:45 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr01_57992.trc (incident=31482): ORA-00600: internal error code, arguments: [3020], [1], [87806], [4282110], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 1, block# 87806, file offset is 719306752 bytes) ORA-10564: tablespace SYSTEM ORA-01110: data file 1: '/data/oracle/oradata/orcl/system01.dbf' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 5843 Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_31482/orcl_pr01_57992_i31482.trc Tue Dec 12 00:37:45 2023 Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr08_58007.trc (incident=31538): ORA-00600: internal error code, arguments: [3020], [1], [61031], [4255335], [], [], [], [], [] ORA-10567:Redo is inconsistent with data block (file# 1, block# 61031, file offset is 499965952 bytes) ORA-10564: tablespace SYSTEM ORA-01110: data file 1: '/data/oracle/oradata/orcl/system01.dbf' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 39 Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_31538/orcl_pr08_58007_i31538.trc
客户不太了解上述故障，直接使用隐含参数，强制拉库报ORA-600 2662错误
Tue Dec 12 00:45:11 2023 SMON: enabling cache recovery Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58731.trc (incident=32668): ORA-00600: internal error code, arguments: [2662], [0], [120784398], [0], [121441601], [12583040] Incident details in: /data/oracle/diag/rdbms/orcl/orcl/incident/incdir_32668/orcl_ora_58731_i32668.trc Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58731.trc: ORA-00600: internal error code, arguments: [2662], [0], [120784398], [0], [121441601], [12583040] Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58731.trc: ORA-00600: internal error code, arguments: [2662], [0], [120784398], [0], [121441601], [12583040] Error 600 happened during db open, shutting down database USER (ospid: 58731): terminating the instance due to error 600 Instance terminated by USER, pid = 58731
接手该故障之后,数据库已经被resetlogs,只能基于该现场进一步恢复,修改数据库scn,open数据库成功,导出数据,完成本次恢复.
