联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-01110 ORA-17070 OSD-04006 故障恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友找到我说应用访问数据库和导出数据都报ORA-01110 ORA-17070 OSD-04006之类错误,数据库可以正常open,但是业务访问关键数据和导出报错
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
尝试mount data磁盘组
SQL> alter diskgroup DATADG mount NOTE: cache registered group DATADG number=1 incarn=0xbc43fafd NOTE: cache began mount (first) of group DATADG number=1 incarn=0xbc43fafd NOTE: Assigning number (1,0) to disk (/dev/raw/raw2) Thu Jun 02 10:14:33 2022 NOTE: GMON heartbeating for grp 1 GMON querying group 1 at 27 for pid 27, osid 3853 NOTE: Assigning number (1,1) to disk () GMON querying group 1 at 28 for pid 27, osid 3853 NOTE: cache dismounting (clean) group 1/0xBC43FAFD (DATADG) NOTE: messaging CKPT to quiesce pins Unix process pid: 3853, image: oracle@node1 (TNS V1-V3) NOTE: dbwr not being msg'd to dismount NOTE: lgwr not being msg'd to dismount NOTE: cache dismounted group 1/0xBC43FAFD (DATADG) NOTE: cache ending mount (fail) of group DATADG number=1 incarn=0xbc43fafd NOTE: cache deleting context for group DATADG 1/0xbc43fafd GMON dismounting group 1 at 29 for pid 27, osid 3853 NOTE: Disk in mode 0x8 marked for de-assignment NOTE: Disk in mode 0x8 marked for de-assignment ERROR: diskgroup DATADG was not mounted ORA-15032: not all alterations performed ORA-15040: diskgroup is incomplete ORA-15042: ASM disk "1" is missing from group number "1" ERROR: alter diskgroup DATADG mount Thu Jun 02 10:14:33 2022 ASM Health Checker found 1 new failures
报错信息比较明显 datadg的disk number 为1的磁盘丢失了。通过fdisk确认磁盘情况
Disk /dev/sdb: 42.9 GB, 42949672960 bytes 64 heads, 32 sectors/track, 40960 cylinders Units = cylinders of 2048 * 512 = 1048576 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x0006c2be Device Boot Start End Blocks Id System Disk /dev/sda: 53.7 GB, 53687091200 bytes 64 heads, 32 sectors/track, 51200 cylinders Units = cylinders of 2048 * 512 = 1048576 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x00061443 Device Boot Start End Blocks Id System /dev/sda1 * 2 2049 2097152 83 Linux Partition 1 does not end on cylinder boundary. /dev/sda2 2050 10241 8388608 82 Linux swap / Solaris Partition 2 does not end on cylinder boundary. /dev/sda3 10242 12289 2097152 83 Linux Partition 3 does not end on cylinder boundary. /dev/sda4 12290 51200 39844864 5 Extended Partition 4 does not end on cylinder boundary. /dev/sda5 12291 14338 2097152 83 Linux /dev/sda6 14340 50178 36699136 83 Linux /dev/sda7 50180 51200 1045504 83 Linux Disk /dev/sdc: 214.7 GB, 214748364800 bytes 255 heads, 63 sectors/track, 26108 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x1b3fba6b Device Boot Start End Blocks Id System /dev/sdc1 1 1045 8393931 83 Linux /dev/sdc2 1046 26108 201318547+ 83 Linux Disk /dev/sdd: 536.9 GB, 536870912000 bytes 255 heads, 63 sectors/track, 65270 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x4c63ecad Device Boot Start End Blocks Id System /dev/sdd1 1 65270 524281243+ 83 Linux Disk /dev/sde: 536.9 GB, 536870912000 bytes 255 heads, 63 sectors/track, 65270 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x00000000 Disk /dev/sdf: 536.9 GB, 536870912000 bytes 255 heads, 63 sectors/track, 65270 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x00000000
根据客户反馈,异常的应该是一个500G的磁盘,而其中sdb为分区,通过kfed命令分析,确认sdc1为ocr磁盘,sdc2为datadg的一块磁盘,另外一块磁盘应该在sdd,sde,sdf三者之中,通过kfed分析sde,sdf均不可能是asm disk(一块是文件系统,一块是彻底没有使用的空盘),如果datadg的磁盘没有丢失,那应该就是sdd这块磁盘,通过dd 磁盘100M空间,然后通过kfed进行分析确认
E:\TEMP\xff>kfed read sdd.dd
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
006648400 00000000 00000000 00000000 00000000 [................]
Repeat 26 times
0066485B0 00000000 00000000 4C63ECAD 01000000 [..........cL....]
0066485C0 FE830001 003FFFFF CB370000 00003E7F [......?...7..>..]
0066485D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0066485F0 00000000 00000000 00000000 AA550000 [..............U.]
006648600 00000000 00000000 00000000 00000000 [................]
Repeat 223 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
E:\TEMP\xff>kfed read sdd1.dd
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
006768400 00000000 00000000 00000000 00000000 [................]
Repeat 26 times
0067685B0 00000000 00000000 70D364B4 FE000000 [.........d.p....]
0067685C0 FE83FFFF D13FFFFF BB7603EB 00003A93 [......?...v..:..]
0067685D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0067685F0 00000000 00000000 00000000 AA550000 [..............U.]
006768600 02038201 00000008 80000001 826037C1 [.............7`.]
006768EA0 00000079 00800105 0000007A 00800105 [y.......z.......]
006768EB0 0000007C 00800105 0000007D 00800105 [|.......}.......]
0067693C0 0000015C 00800105 0000015D 00800105 [\.......].......]
0067693D0 0000015F 00800105 00000160 00800105 [_.......`.......]
0067693E0 00000161 00800105 00000163 00800105 [a.......c.......]
0067693F0 00000164 00800105 00000166 00800105 [d.......f.......]
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
E:\TEMP\xff>kfed read sdd.dd blkn=1|more
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 2 ; 0x002: KFBTYP_FREESPC
kfbh.datfmt: 2 ; 0x003: 0x02
kfbh.block.blk: 1 ; 0x004: blk=1
kfbh.block.obj: 2147483649 ; 0x008: disk=1
kfbh.check: 2197087544 ; 0x00c: 0x82f4e538
kfbh.fcn.base: 616391 ; 0x010: 0x000967c7
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
kfdfsb.aunum: 0 ; 0x000: 0x00000000
kfdfsb.max: 254 ; 0x004: 0x00fe
kfdfsb.cnt: 254 ; 0x006: 0x00fe
kfdfsb.bound: 0 ; 0x008: 0x0000
kfdfsb.flag: 1 ; 0x00a: B=1
kfdfsb.ub1spare: 0 ; 0x00b: 0x00
kfdfsb.spare[0]: 0 ; 0x00c: 0x00000000
kfdfsb.spare[1]: 0 ; 0x010: 0x00000000
kfdfsb.spare[2]: 0 ; 0x014: 0x00000000
通过上述信息分析,基本上可以确认sdd磁盘以前是asm disk,但是被fdisk进行了分区,基于这种情况,通过对磁盘组进行修复
E:\TEMP\xff>kfed read sdd.ok kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 2147483649 ; 0x008: disk=1 kfbh.check: 424926402 ; 0x00c: 0x1953dcc2 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 kfdhdb.driver.provstr: ORCLDISK ; 0x000: length=8 kfdhdb.driver.reserved[0]: 0 ; 0x008: 0x00000000 kfdhdb.driver.reserved[1]: 0 ; 0x00c: 0x00000000 kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000 kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000 kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000 kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000 kfdhdb.compat: 186646528 ; 0x020: 0x0b200000 kfdhdb.dsknum: 1 ; 0x024: 0x0001 kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER kfdhdb.dskname: DATADG_0001 ; 0x028: length=11 kfdhdb.grpname: DATADG ; 0x048: length=6 kfdhdb.fgname: DATADG_0001 ; 0x068: length=11 kfdhdb.capname: ; 0x088: length=0 kfdhdb.crestmp.hi: 33074858 ; 0x0a8: HOUR=0xa DAYS=0x15 MNTH=0xb YEAR=0x7e2 kfdhdb.crestmp.lo: 2375520256 ; 0x0ac: USEC=0x0 MSEC=0x1e4 SECS=0x19 MINS=0x23 kfdhdb.mntstmp.hi: 33074858 ; 0x0b0: HOUR=0xa DAYS=0x15 MNTH=0xb YEAR=0x7e2 kfdhdb.mntstmp.lo: 2375522304 ; 0x0b4: USEC=0x0 MSEC=0x1e6 SECS=0x19 MINS=0x23 kfdhdb.secsize: 512 ; 0x0b8: 0x0200 kfdhdb.blksize: 4096 ; 0x0ba: 0x1000 kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000 kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80 kfdhdb.dsksize: 512000 ; 0x0c4: 0x0007d000 kfdhdb.pmcnt: 6 ; 0x0c8: 0x00000006 kfdhdb.fstlocn: 1 ; 0x0cc: 0x00000001 kfdhdb.altlocn: 2 ; 0x0d0: 0x00000002 kfdhdb.f1b1locn: 0 ; 0x0d4: 0x00000000 kfdhdb.redomirrors[0]: 0 ; 0x0d8: 0x0000 kfdhdb.redomirrors[1]: 0 ; 0x0da: 0x0000 kfdhdb.redomirrors[2]: 0 ; 0x0dc: 0x0000 kfdhdb.redomirrors[3]: 0 ; 0x0de: 0x0000 kfdhdb.dbcompat: 168820736 ; 0x0e0: 0x0a100000 kfdhdb.grpstmp.hi: 33072461 ; 0x0e4: HOUR=0xd DAYS=0xa MNTH=0x9 YEAR=0x7e2 kfdhdb.grpstmp.lo: 3452534784 ; 0x0e8: USEC=0x0 MSEC=0x260 SECS=0x1c MINS=0x33
磁盘组mount成功,数据库open成功,实现数据0丢失
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-600 kcvent_internal_02故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库启动报ORA-00600: internal error code, arguments: [kcvent_internal_02]错,无法正常open
Reconfiguration complete parallel recovery started with 32 processes Started redo scan Completed redo scan read 22775 KB redo, 5055 data blocks need recovery Started redo application at Thread 2: logseq 166395, block 88 Recovery of Online Redo Log: Thread 2 Group 3 Seq 166395 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/group_3.283.1036687245 Mem# 1: +FLASH/orcl/onlinelog/group_3.264.1036687257 Recovery of Online Redo Log: Thread 2 Group 4 Seq 166396 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/group_4.284.1036687257 Mem# 1: +FLASH/orcl/onlinelog/group_4.265.1036687257 Completed redo application of 15.97MB Completed instance recovery at Thread 2: logseq 166396, block 15854, scn 27533037896 5055 data blocks read, 5055 data blocks written, 22775 redo k-bytes read Thread 2 advanced to log sequence 166397 (thread recovery) Redo thread 2 internally disabled at seq 166397 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_35652472.trc (incident=195549): ORA-00600: internal error code, arguments: [kcvent_internal_02], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_195549/orcl1_ora_35652472_i195549.trc
对应的trace文件信息
Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_35652472.trc
ORA-00600: internal error code, arguments: [kcvent_internal_02], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 195549 (ORA 600 [kcvent_internal_02]) ========
*** 2022-06-06 22:17:48.743
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5fmpzya54p4hf) -----
ALTER DATABASE OPEN /* db agent *//* {1:38339:2} */
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+40 bl 0000000109B1E77C 000000000 ? 000000001 ?
000000003 ? 000000000 ?
000000000 ? 000000001 ?
000000003 ? 000000000 ?
ksedst1()+112 call skdstdst() 16F60DC8B26FAB02 ?
4846284100000000 ?
FFFFFFFFFFE46D0 ?
283C6E7C6A9A6 ? 10A6B923C ?
000000000 ? 110737880 ?
2050033FFFE46D8 ?
ksedst()+40 call ksedst1() 000000000 ? 00000000A ?
07FFFFFFF ? 700000000003670 ?
000000000 ? 000000000 ?
000002004 ? 000000001 ?
dbkedDefDump()+1516 call ksedst() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 300000003 ?
ksedmp()+72 call dbkedDefDump() 310737880 ? 110000D40 ?
FFFFFFFFFFE4EE0 ? 1106AB740 ?
100124BB8 ? 000000000 ?
700011D7387FF08 ? 1106AB740 ?
ksfdmp()+100 call ksedmp() 000000002 ? 000000000 ?
000000002 ? 10AF01CA8 ?
10A041C38 ? 000000000 ?
11073C760 ? 110737880 ?
dbgexPhaseII()+1904 call ksfdmp() 000000000 ? 00000000A ?
000000002 ? 000000000 ?
000000002 ? 10A041C30 ?
000000000 ? 001050005 ?
dbgexProcessError() call dbgexPhaseII() 110737880 ? 11073A970 ?
+1556 00002FBDD ? 200000000 ?
FFFFFFFFFFE5DF8 ? 00000006C ?
200000000 ? 1000000000 ?
dbgeExecuteForError call dbgexProcessError() 110737880 ? 11073C760 ?
()+72 100000703 ? 000004000 ?
000000000 ? FFFFFFFFFFE9608 ?
000000001 ? 11073E4A8 ?
dbgePostErrorKGE()+ call dbgeExecuteForError FFFFFFFFFFE92B0 ?
2044 () 700011D61558BB8 ? 102878B5C ?
000000000 ? 000000000 ?
FFFFFFFFFFE9608 ? 000000000 ?
000000000 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 07FFFFFFF ? 700000000003670 ?
68 25800000001 ? 109E4A618 ?
000000000 ? 000000000 ?
FFFFFFFFFFEA0B0 ? 1109C0040 ?
kgeadse()+380 call dbkePostKGE_kgsf() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 069186EAB ?
kgerinv_internal()+ call kgeadse() 000000002 ? 000000002 ?
48 000000001 ? FFFFFFFFFFEAB58 ?
10A4E02F0 ? 000000002 ?
FFFFFFFFFFE9FE0 ? 000000000 ?
kgerinv()+48 call kgerinv_internal() 200000002 ? 000000002 ?
FFFFFFFFFFEA060 ? 000000000 ?
102860EB0 ? FFFFFFFFFFEA458 ?
10285CE74 ? FFFFFFFFFFEA358 ?
kgeasnmierr()+72 call kgerinv() 38400000001 ? 000000000 ?
10A4E0D20 ? 497F0A29CAE0 ?
000000001 ? FFFFFFFFFFEA1C0 ?
10A4E0D20 ? 110000D78 ?
kcvent_internal()+1 call kgeasnmierr() FFFFFFFFFFEA1C0 ? 200000002 ?
532 1F0410001F041 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000004 ?
kctenb_internal()+2 call kcvent_internal() FFFFFFFFFFEB378 ? 200000002 ?
772 FFFFFFFFFFEB448 ?
FFFFFFFFFFEB2E8 ?
41F6C57900000000 ?
000000000 ? FFFFFFFFFFEB330 ?
1106AB740 ?
kcfopd()+1508 call kctenb_internal() 07FFFFFFF ? 000000000 ?
000000018 ? FFFFFFFFFFEC380 ?
000000000 ? 110A39050 ?
FFFFFFFFFFEC390 ? 000000000 ?
adbdrv()+8028 call kcfopd() 081F0AD00 ? 00000000F ?
0FFFED4C0 ? 000000000 ?
FFFFFFFFFFED548 ? 100000000 ?
000000000 ? 1000100000000 ?
opiexe()+16048 call adbdrv() 2300000023 ? 100000001 ?
000000000 ? FFFFFFFFFFF6960 ?
000000000 ? FFFFFFFFFFF6B60 ?
FFFFFFFFFFF6A98 ? 200000002 ?
opiosq0()+3984 call opiexe() 700011E117B3B20 ? 000000000 ?
FFFFFFFFFFF7ED8 ? 110000D78 ?
000000001 ? 1109FA438 ?
FFFFFFFFFFF7E70 ?
2216414400000001 ?
kpooprx()+316 call opiosq0() 300000000 ? 000000000 ?
000000000 ? A4000000000000 ?
000000000 ? FFFFFFFFFFF87F0 ?
28104221FFFF86F0 ?
1109FAB08 ?
kpoal8()+872 call kpooprx() 1000CE68C ? 000000001 ?
FFFFFFFFFFFAD14 ? 100000001 ?
000000000 ? A40000000000A4 ?
109EB6D00 ? 000000000 ?
opiodr()+908 call kpoal8() 100000000 ? 9001000A0091108 ?
000000FFF ? 07FFFFFF8 ?
FFFFFFFFFFF8F10 ? 000000018 ?
000000000 ? 000072FFF ?
ttcpip()+1028 call opiodr() 5EFFFFA480 ? 1C00200048 ?
FFFFFFFFFFFA9F8 ? 000530058 ?
1108BEE30 ? 000000028 ?
FFFFFFFFFFFA3A0 ? 1108BEC70 ?
opitsk()+1612 call ttcpip() 110135440 ? 000002078 ?
000000000 ? 110000D78 ?
110005210 ? 000000000 ?
FFFFFFFFFFFAA20 ?
2222208009EF13C0 ?
opiino()+940 call opitsk() 110024C58 ? 000000000 ?
11079B550 ? 1107A0850 ?
110737880 ? FFFFFFFFFFFCAE0 ?
FFFFFFFFFFFEB3C ? 000000101 ?
opiodr()+908 call opiino() 3C006C787C ?
BFF0000000000000 ?
FFFFFFFFFFFEF60 ?
FFFFFFFFFFFD5E9 ?
FFFFFFFFFFFD630 ? 1106AB740 ?
FFFFFFFFFFFD650 ?
9FFFFFFF000E608 ?
opidrv()+1132 call opiodr() 3C0AFBC600 ? 410134340 ?
FFFFFFFFFFFEF60 ? 07530312F ?
108820CE4 ? 1106AB740 ?
7264626D732F6F72 ?
1106AB740 ?
sou2o()+136 call opidrv() 3C0882A9D0 ? 41170031F ?
FFFFFFFFFFFEF60 ?
110017002A0000 ? 0E0DDF00D ?
1106AB740 ?
BADC0FFEE0DDF00D ?
BADC0FFEE0DDF00D ?
opimai_real()+560 call sou2o() FFFFFFFFFFFEFD0 ?
BADC0FFEE0DDF00D ?
90000000008BE3C ?
BADC0FFEE0DDF00D ?
000000002 ? 9001000A0091108 ?
A0000000A000000 ? 10B671248 ?
ssthrdmain()+276 call opimai_real() 10B6B1D74 ? 9001000A0095260 ?
FFFFFFFFFFFF0B0 ? 10B6B1598 ?
FFFFFFFFFFFF0D0 ?
FFFFFFFFFFFF428 ?
900000000100968 ?
9001000A0091108 ?
main()+204 call ssthrdmain() 240000000 ? FFFFFFFFFFFF418 ?
8FFFFFFF0000090 ? 000000000 ?
000000000 ? 000000000 ?
BADC0FFEE0DDF00D ?
BADC0FFEE0DDF00D ?
__start()+112 call main() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
该错误在mos,互联网上没有任何信息,不过在alert日志中发现类似信息
Mon Jun 06 23:03:58 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
初步判断可能和这个错误有关系,解决相关问题后,尝试open库
SQL> recover database;
ORA-00279: change 27533037896 generated at 06/06/2022 22:17:46 needed for
thread 2
ORA-00289: suggestion :
+FLASH/orcl/archivelog/2022_06_06/thread_2_seq_166396.6532.1106691471
ORA-00280: change 27533037896 for thread 2 is in sequence #166396
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01216: thread 2 is expected to be disabled after CREATE CONTROLFILE
SQL> !oerr ora 01216
01216, 00000, "thread %s is expected to be disabled after CREATE CONTROLFILE"
// *Cause: A thread that was given during CREATE CONTROLFILE is enabled, but
// the datafiles indicate that it should be disabled. This is
// probably because the logs supplied to the CREATE CONTROLFILE
// are old (from before the disabling of the thread).
// *Action: This thread is not required to run the database. The CREATE
// CONTROLFILE statement can be reissued without the problem thread,
// and, if desired, the thread can be recreated after the database
// is open.
ORA-01216这个错误比较也比较少见,但是感觉和thread有关系,大概的意思是thread 被disable了
SQL> select thread#,STATUS FROM V$THREAD;
THREAD# STATUS
---------- ------------------
1 CLOSED
2 CLOSED
通过人工强制把thread个open,然后数据库启动成功
SQL> select thread#,status from v$thread;
THREAD# STATUS
---------- ------------------
1 OPEN
2 CLOSED
SQL> alter database open;
Database altered.
然后启动thread 2,open 第二个节点
--需要open节点 QL> startup ORACLE instance started. Total System Global Area 1.2961E+11 bytes Fixed Size 2262400 bytes Variable Size 3.3018E+10 bytes Database Buffers 9.6368E+10 bytes Redo Buffers 221818880 bytes ORA-01618: redo thread 2 is not enabled - cannot mount --已经open节点 SQL> ALTER DATABASE ENABLE THREAD 2; Database altered. --需要open节点 SQL> ALTER DATABASE MOUNT; Database altered. SQL> ALTER DATABASE OPEN; Database altered.
xifenfei1:/home/grid$crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
ora.FLASH.dg
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
ora.LISTENER.lsnr
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
ora.OCR.dg
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
ora.asm
ONLINE ONLINE xifenfei1 Started
ONLINE ONLINE xifenfei2 Started
ora.gsd
OFFLINE OFFLINE xifenfei1
OFFLINE OFFLINE xifenfei2
ora.net1.network
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
ora.ons
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
ora.registry.acfs
ONLINE ONLINE xifenfei1
ONLINE ONLINE xifenfei2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE xifenfei1
ora.cvu
1 OFFLINE OFFLINE
ora.xifenfei1.vip
1 ONLINE ONLINE xifenfei1
ora.xifenfei2.vip
1 ONLINE ONLINE xifenfei2
ora.oc4j
1 ONLINE ONLINE xifenfei2
ora.orcl.db
1 ONLINE ONLINE xifenfei1 Open
2 ONLINE ONLINE xifenfei2 Open
ora.scan1.vip
1 ONLINE ONLINE xifenfei1
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle断电故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
异常断电导致数据库异常恢复文件报ORA-00283 ORA-00742 ORA-00312
D:\check_db>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 31 00:38:42 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover datafile 1; ORA-00283: 恢复会话因错误而取消 ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况 ORA-00312: 联机日志 3 线程 1: 'D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG'
这个错误比较明显是由于异常断电引起的写丢失导致.而且这种故障在没有备份的情况下，没有什么好处理方法,只能屏蔽一致性强制拉库,尝试强制拉库报错如下
SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。
Total System Global Area 2.0310E+10 bytes
Fixed Size 2290000 bytes
Variable Size 3690991280 bytes
Database Buffers 1.6576E+10 bytes
Redo Buffers 40837120 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 18755939194213 (在 生成) 对于线程 1 是必需的
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG
ORA-00600: internal error code, arguments: [3020], [2], [78824], [8467432], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 78824, file
offset is 645726208 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 80834
ORA-01112: 未启动介质恢复
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [krsi_al_hdr_update.15], [4294967295], [], [],[], [], [], [], [], [], [], []
ORA-600 krsi_al_hdr_update.15错误,主要是由于redo异常导致无法resetlogs成功,具体参考：Alter Database Open Resetlogs returns error ORA-00600: [krsi_al_hdr_update.15], (Doc ID 2026541.1)描述,处理这个问题之后,再次resetlogs库，报ORA-600 2662错误
SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [4366], [4112122046], [4366], [4112228996], [12583040], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [4366], [4112122045], [4366], [4112228996], [12583040], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [4366], [4112122040], [4366], [4112228996], [12583040], [], [], [], [], [], [] 进程 ID: 4644 会话 ID: 1701 序列号: 3
这个问题比较简单,通过修改scn即可绕过去,之后数据库open报ORA-600 4194等错误
SQL> alter database open ; alter database open * 第 1 行出现错误: ORA-00600: 内部错误代码, 参数: [4194], [
SMON: enabling tx recovery Database Characterset is ZHS16GBK Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc (incident=322982): ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322982\orcl_smon_5112_i322982.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE replication_dependency_tracking turned off (no async multimaster replication found) LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3340.trc (incident=323030): ORA-00600: 内部错误代码, 参数: [4194], [ Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_323030\orcl_ora_3340_i323030.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Tue May 31 09:05:04 2022 Sweep [inc][322982]: completed ORACLE Instance orcl (pid = 13) - Error 600 encountered while recovering transaction (10, 33). Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc: ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], [] Checker run found 1 new persistent data failures Tue May 31 09:05:05 2022 Sweep [inc][323030]: completed Sweep [inc2][322982]: completed Tue May 31 09:05:14 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc (incident=322983): ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [0], [], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322983\orcl_smon_5112_i322983.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Tue May 31 09:05:14 2022 ORA-600 signalled during: alter database open... Block recovery stopped at EOT rba 2.61.16 Block recovery completed at rba 2.61.16, scn 4366.4112429058 Block recovery from logseq 2, block 60 to scn 18755939643393 Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_K9BSVC11_.LOG Block recovery completed at rba 2.61.16, scn 4366.4112429058 Dumping diagnostic data in directory=[cdmp_2022053],requested by(instance=1,osid=5112(SMON)),summary=[incident=322983]. Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc: ORA-01595: error freeing extent (3) of rollback segment (1)) ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [3], [], [], [], [], [], [], [], [], []
对异常undo进行处理,数据库正常open成功
SQL> shutdown immediate; ORA-00600: 内部错误代码, 参数: [4193], [ SQL> shutdown abort; ORACLE 例程已经关闭。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 2.0310E+10 bytes Fixed Size 2290000 bytes Variable Size 3690991280 bytes Database Buffers 1.6576E+10 bytes Redo Buffers 40837120 bytes 数据库装载完毕。 SQL> alter database open; 数据库已更改。
hcheck检测有一些字典不一致,建议客户逻辑导出,然后导入到新库中
HCheck Version 07MAY18 on 31-5月 -2022 09:12:22
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: ORCL
Catalog Fixed
Procedure Name Version Vs Release Timestamp Resul
t
------------------------------ ... ---------- -- ---------- -------------- -----
-
.- LobNotInObj ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- SourceNotInObj ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 05/31 09:12:23 FAIL
HCKE-0024: Orphaned Index Partition Obj# (no OBJ$) (Doc ID 1360935.1)
ORPHAN INDPART$: OBJ#=149167 BO#=6378 - no OBJ$ row
ORPHAN INDPART$: OBJ#=149168 BO#=6378 - no OBJ$ row
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- FetUet ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 05/31 09:12:23 FAIL
HCKE-0030: OBJ$ INDEX entry has no IND$ or INDPART$/INDSUBPART$ entry (Doc ID 13
60528.1)
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148278 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16462
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148920 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16678
.- BadTab$ ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadOwner ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadDepends ... 1102000400 <= *All Rel* 05/31 09:12:23 WARN
HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects (Doc ID 1361045.1)
[E] - P_OBJ#=6376 D_OBJ#=6765
.- CheckDual ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadCboHiLo ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 05/31 09:12:24 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 05/31 09:12:24 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 05/31 09:12:24 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
---------------------------------------
31-5月 -2022 09:12:24 Elapsed: 2 secs
---------------------------------------
Found 4 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL 过程已成功完成。
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：O/S-Error: (OS 23) 数据错误(循环冗余检查) 数据库恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户数据库运行过程中突然crash,检测发现ORA-27070 OSD-04016 O/S-Error: (OS 23) 等报错
Thu May 12 11:25:53 2022
KCF: write/open error block=0x19e95f online=1
file=57 H:\ORADATA\xifenfei\XFF51.DBF
error=27070 txt: 'OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。'
Thu May 12 11:25:53 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw0_3532.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
ORA-01114: 将块写入文件 57 时出现 IO 错误 (块 # 1698143)
ORA-01110: 数据文件 57: 'H:\ORADATA\xifenfei\XFF51.DBF'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。
DBW0: terminating instance due to error 1242
Thu May 12 11:25:54 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_mman_3528.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:25:54 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_lgwr_3544.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_dbw1_3536.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_psp0_3524.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_ckpt_3548.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:25:55 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_pmon_3520.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:26:06 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_q002_37468.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:26:08 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_reco_3556.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:26:08 2022
Errors in file e:\oracle\product\10.2.0\admin\xifenfei\bdump\xifenfei_smon_3552.trc:
ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式
Thu May 12 11:26:10 2022
Instance terminated by DBW0, pid = 3532
再次重启数据库报错 ORA-27070: 异步读取/写入失败 OSD-04016: 异步 I/O 请求排队时出错。类似错误
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ASM删除表空间恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
前几天刚刚恢复了一个文件系统层面drop 表空间的case(分享运气超级好的一次drop tablespace 数据恢复),又一客户删除表空间(认为是不要的表空间),结果发现业务上丢失了很多表数据,通过分析和回顾以往事件,确认由于在以前数据迁移过来的过程中,数据写入了和原库一致的表空间,而没有恢复到本该恢复的新表空间中,这次删除该空间导致很多表数据丢失.而且该客户是asm环境,drop tablespace带上了including contents and datafiles语句,导致该表空间对应的数据文件也丢失.对于这类数据的恢复,一般情况下先通过asm层面恢复出来被删除的数据文件,然后再对被删除的数据文件按照丢失system的方式恢复里面的表数据(这个客户有历史备份便于整合)
在恢复被删除的文件之前,需要先确认对应的被删除的表空间信息和对应的文件信息,通过对底层字典分析file$,ts$,结合alert日志,可以确认被删除文件的文件号,文件名称等信息
由于文件已经从asm磁盘组中删除,无法直接恢复,通过对asm磁盘组进行扫描找出对应的block信息,参考:asm磁盘组操作不当导致数据文件丢失恢复类似处理方法,分析文件是否异常
初步判断文件恢复效果应该不错,恢复出来数据文件，然后进行dbv检查
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户反馈数据库异常:两个节点rac,两个节点都启动,其中一个节点无法正常open,另外一个节点一段时间后也会挂。以下是无法正常open节点报错信息(正常open节点最终挂掉报错信息也是类似)
2022-03-30T19:19:12.870813+08:00 [84321] Successfully onlined Undo Tablespace 4. Undo initialization finished serial:0 start:1728252021 end:1728256302 diff:4281 ms (4.3 seconds) Verifying minimum file header compatibility for tablespace encryption.. Verifying file header compatibility for tablespace encryption completed for pdb 0 2022-03-30T19:19:13.953252+08:00 Database Characterset is ZHS16GBK 2022-03-30T19:19:14.538155+08:00 Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_p02o_85718.trc (incident=1093927): ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/app/oracle/diag/rdbms/xff/xff2/incident/incdir_1093927/xff2_p02o_85718_i1093927.trc 2022-03-30T19:19:15.536582+08:00 ORACLE Instance xff2 (pid = 57) - Error 607 encountered while recovering transaction (73, 12) on object 112841. 2022-03-30T19:19:33.699944+08:00 Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc: ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], [] 2022-03-30T19:19:34.673840+08:00 Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc: ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], [] 2022-03-30T19:19:34.673954+08:00 Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc: ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [ktbsdp2], [18446744073709551615], [], [], [], [], [], [], [], [], [], [] Errors in file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_smon_84007.trc (incident=1092704): ORA-607 [] [] [] [] [] [] [] [] [] [] [] [] Incident details in: /oracle/app/oracle/diag/rdbms/xff/xff2/incident/incdir_1092704/xff2_smon_84007_i1092704.trc 2022-03-30T19:19:35.422779+08:00 ***************************************************************** An internal routine has requested a dump of selected redo. This usually happens following a specific internal error, when analysis of the redo logs will help Oracle Support with the diagnosis. It is recommended that you retain all the redo logs generated (by all the instances) during the past 12 hours, in case additional redo dumps are required to help with the diagnosis. ***************************************************************** 2022-03-30T19:19:36.154689+08:00 Starting background process GTX0 2022-03-30T19:19:36.169007+08:00 GTX0 started with pid=370, OS id=87409 2022-03-30T19:19:36.645876+08:00 USER (ospid: 84007): terminating the instance due to error 607 2022-03-30T19:19:36.680109+08:00 opiodr aborting process unknown ospid (87439) as a result of ORA-1092 2022-03-30T19:19:36.681091+08:00 ORA-1092 : opitsk aborting process 2022-03-30T19:19:36.740357+08:00 System state dump requested by (instance=2, osid=84007 (SMON)), summary=[abnormal instance termination]. System State dumped to trace file /oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_diag_83895_20220330191936.trc 2022-03-30T19:19:40.135579+08:00 Instance terminated by USER, pid = 84007
对于上述报错信息分析,初步判断是由于事务异常导致,查询mos发现类似报错Bug 32208691 – After upgrade from 12.1 to 19.3 drop columns fails ORA-600[ktbsdp2] ORA-600[4512] (Doc ID 32208691.8),通过咨询客户,确认他们这边是通过plsql dev工具对id为112841表进行增加列的时候网络中断导致增加失败,后续我尝试对该表进行查询发现也报该错误,基本上可以确认由于该表事务异常导致,通过dul把该表数据恢复,然后drop 该表,数据库启动正常,未见其他报错,通过hcheck检查,数据库字典基本一致(除一些统计信息异常,原则上不影响数据库运行)
[oracle@xifenfei2 ~]$ sqlplus / as sysdba @hcheck.sql
SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 31 00:38:32 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
HCheck Version 07MAY18 on 31-MAR-2022 00:38:34
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name: xff
Is CDB?: NO
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 1202000100 <= *All Rel* 03/31 00:38:34 PASS
.- MissingOIDOnObjCol ... 1202000100 <= *All Rel* 03/31 00:38:34 PASS
.- SourceNotInObj ... 1202000100 <= *All Rel* 03/31 00:38:34 PASS
.- OversizedFiles ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- PoorDefaultStorage ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- PoorStorage ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- TabPartCountMismatch ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- OrphanedTabComPart ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- MissingSum$ ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- MissingDir$ ... 1202000100 <= *All Rel* 03/31 00:38:38 PASS
.- DuplicateDataobj ... 1202000100 <= *All Rel* 03/31 00:38:40 PASS
.- ObjSynMissing ... 1202000100 <= *All Rel* 03/31 00:38:42 PASS
.- ObjSeqMissing ... 1202000100 <= *All Rel* 03/31 00:38:42 PASS
.- OrphanedUndo ... 1202000100 <= *All Rel* 03/31 00:38:44 PASS
.- OrphanedIndex ... 1202000100 <= *All Rel* 03/31 00:38:44 PASS
.- OrphanedIndexPartition ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- OrphanedIndexSubPartition ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- OrphanedTable ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- OrphanedTablePartition ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- OrphanedTableSubPartition ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- MissingPartCol ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- OrphanedSeg$ ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- OrphanedIndPartObj# ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- DuplicateBlockUse ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- FetUet ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- Uet0Check ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- SeglessUET ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- BadInd$ ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- BadTab$ ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- BadIcolDepCnt ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- ObjIndDobj ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- TrgAfterUpgrade ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- ObjType0 ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- BadOwner ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- StmtAuditOnCommit ... 1202000100 <= *All Rel* 03/31 00:38:45 PASS
.- BadPublicObjects ... 1202000100 <= *All Rel* 03/31 00:38:46 PASS
.- BadSegFreelist ... 1202000100 <= *All Rel* 03/31 00:38:50 PASS
.- BadDepends ... 1202000100 <= *All Rel* 03/31 00:38:50 PASS
.- CheckDual ... 1202000100 <= *All Rel* 03/31 00:38:57 PASS
.- ObjectNames ... 1202000100 <= *All Rel* 03/31 00:38:57 WARN
HCKW-0018: OBJECT name clashes with SCHEMA name (Doc ID 2363142.1)
Schema=MHWZ PACKAGE=MHWZ.MHWZ
Schema=MHWZ PACKAGE BODY=MHWZ.MHWZ
.- BadCboHiLo ... 1202000100 <= *All Rel* 03/31 00:39:01 WARN
HCKW-0019: HIST_HEAD$.LOWVAL > HIVAL (Doc ID 1361047.1)
OBJ# 324163 INTCOL#=22
OBJ# 482668 INTCOL#=4
OBJ# 442865 INTCOL#=31
OBJ# 436924 INTCOL#=31
OBJ# 580529 INTCOL#=8
OBJ# 459432 INTCOL#=31
OBJ# 451260 INTCOL#=31
OBJ# 530980 INTCOL#=21
OBJ# 498442 INTCOL#=5
OBJ# 652114 INTCOL#=8
OBJ# 701695 INTCOL#=21
OBJ# 831961 INTCOL#=31
OBJ# 831962 INTCOL#=31
OBJ# 831963 INTCOL#=31
.- ChkIotTs ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- NoSegmentIndex ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- BadNextObject ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- DroppedROTS ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- FilBlkZero ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- DbmsSchemaCopy ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- OrphanedIdnseqObj ... 1202000100 > 1201000000 03/31 00:39:09 PASS
.- OrphanedIdnseqSeq ... 1202000100 > 1201000000 03/31 00:39:09 PASS
.- OrphanedObjError ... 1202000100 > 1102000000 03/31 00:39:09 PASS
.- ObjNotLob ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- MaxControlfSeq ... 1202000100 <= *All Rel* 03/31 00:39:09 PASS
.- SegNotInDeferredStg ... 1202000100 > 1102000000 03/31 00:39:13 PASS
.- SystemNotRfile1 ... 1202000100 > 902000000 03/31 00:39:13 PASS
.- DictOwnNonDefaultSYSTEM ... 1202000100 <= *All Rel* 03/31 00:39:13 PASS
.- OrphanTrigger ... 1202000100 <= *All Rel* 03/31 00:39:13 PASS
.- ObjNotTrigger ... 1202000100 <= *All Rel* 03/31 00:39:13 PASS
---------------------------------------
31-MAR-2022 00:39:13 Elapsed: 39 secs
---------------------------------------
Found 0 potential problem(s) and 16 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_ora_26887_HCHECK.trc
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户反馈数据文件无法拷贝也无法正常操作,通过查看系统日志发现ntfs文件系统异常
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
接手客户的库,已经被强制resetlogs 报ORA-600 2662错误
Sat Mar 19 20:07:48 2022 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 26228222518 Clearing online redo logfile 1 /u2/oradb/oradata/xff/redo01.log Clearing online log 1 of thread 1 sequence number 0 Clearing online redo logfile 1 complete Online log /u2/oradb/oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared Online log /u2/oradb/oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared Online log /u2/oradb/oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared Sat Mar 19 20:08:02 2022 Setting recovery target incarnation to 2 Sat Mar 19 20:08:07 2022 Assigning activation ID 2327373166 (0x8ab8e56e) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /u2/oradb/oradata/xff/redo01.log Successful open of redo thread 1 Sat Mar 19 20:08:14 2022 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sat Mar 19 20:08:14 2022 SMON: enabling cache recovery ORA-00600: internal error code, arguments: [2662], [6], [458447781], [6], [458448180], [12583056] ORA-00600: internal error code, arguments: [2662], [6], [458447780], [6], [458448180], [12583056] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [6], [458447778], [6], [458448180], [12583056] Incident details in: /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1181122/xifenfei_ora_19893_i1181122.trc Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1181122/xifenfei_ora_19893_i1181122.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [6], [458447781], [6], [458448180], [12583056] ORA-00600: internal error code, arguments: [2662], [6], [458447780], [6], [458448180], [12583056] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [6], [458447778], [6], [458448180], [12583056]
接手之后尝试获取创建控制文件脚本,报ORA-16433
Mon Mar 21 15:20:37 2022 alter database backup controlfile to trace as '/tmp/ctl' ORA-16433 signalled during: alter database backup controlfile to trace as '/tmp/ctl'...
经过一些处理之后resetlogs 成功,但是悲剧产生了(客户之前自己重建过ctl,遗漏大量数据文件,然后我参照客户的ctl进行处理)使得新的ctl中遗漏的很多数据文件,库被resetlogs打开，导致部分文件的resetlog scn不一致,另外数据库还有ORA-600 4137错误需要处理
Mon Mar 21 15:35:01 2022
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 26228222522
Clearing online redo logfile 1 /u2/oradb/oradata/xff/redo01.log
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 2327373166 (0x8ab8e56e)
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4029.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u2/oradb/oradata/xff/redo01.log'
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4029.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/u2/oradb/oradata/xff/redo02.log'
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4029.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: '/u2/oradb/oradata/xff/redo03.log'
Mon Mar 21 15:35:16 2022
Setting recovery target incarnation to 2
Mon Mar 21 15:35:23 2022
Assigning activation ID 2327514749 (0x8abb0e7d)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u2/oradb/oradata/xff/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 21 15:35:30 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:191905344 end:191905754 diff:410 (4 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'DS_POS' #9 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Mon Mar 21 15:36:00 2022
File #498 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00498' in the controlfile.
…………
File #567 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00567' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Mar 21 15:36:08 2022
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4017.trc (incident=1325274):
ORA-00600: internal error code, arguments: [4137], [28.27.4413199], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1325274/xifenfei_smon_4017_i1325274.trc
Use ADRCI or Support Workbench to package the incident.
Mon Mar 21 15:36:10 2022
db_recovery_file_dest_size of 49770 MB is 0.00% 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.
Mon Mar 21 15:36:10 2022
Starting background process CJQ0
Mon Mar 21 15:36:10 2022
CJQ0 started with pid=27, OS id=4155
Mon Mar 21 15:36:10 2022
Errors in file /u2/oradb/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4017.trc (incident=1325275):
ORA-00600: internal error code, arguments: [4137], [36.20.1072031], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u2/oradb/diag/rdbms/xifenfei/xifenfei/incident/incdir_1325275/xifenfei_smon_4017_i1325275.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Mar 21 15:36:10 2022
Completed: alter database open resetlogs
对于类似：File #567 found in data dictionary but not in controlfile.Creating OFFLINE file ‘MISSING00567′ in the controlfile.
错误处理方法:(参照bbed解决ORA-01190
1. 从操作系统中找出来所有遗漏的文件
2. 通过bbed修改文件头信息
3. 重建ctl
4. 重新打开库
Mon Mar 21 16:06:39 2022
alter database open resetlogs
RESETLOGS after complete recovery through change 28991030095
Clearing online redo logfile 1 /u2/oradb/oradata/xff/redo01.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 2327514749 (0x8abb0e7d)
Online log /u2/oradb/oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u2/oradb/oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u2/oradb/oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Mon Mar 21 16:06:53 2022
Setting recovery target incarnation to 2
Mon Mar 21 16:07:00 2022
Assigning activation ID 2327541328 (0x8abb7650)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u2/oradb/oradata/xff/redo01.log
Successful open of redo thread 1
Mon Mar 21 16:07:07 2022
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 21 16:07:07 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:193802264 end:193802294 diff:30 (0 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Mon Mar 21 16:07:38 2022
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Mar 21 16:07:38 2022
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Mar 21 16:07:40 2022
QMNC started with pid=23, OS id=5476
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Mon Mar 21 16:07:41 2022
db_recovery_file_dest_size of 49770 MB is 0.00% 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.
Completed: alter database open resetlogs
至此数据库open成功,增加temp文件,然后逻辑迁移库
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：commit_wait和commit_logging设置不当导致数据库无法正常启动
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户数据库设置以下参数，突然掉电之后，数据库无法正常启动
commit_wait = "NOWAIT" commit_logging = "BATCH"
数据库open报错
alter database open Block change tracking file is current. Beginning crash recovery of 1 threads parallel recovery started with 31 processes Started redo scan Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc (incident=8002955): ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51 ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log' Aborting crash recovery due to error 399 Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc: ORA-00399: corrupt change description in redo log ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51 ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log' Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc: ORA-00399: corrupt change description in redo log ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51 ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log' ORA-399 signalled during: alter database open...
报错信息比较明显是由于redo损坏导致,尝试强制open库
Sun Mar 20 18:32:35 2022 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 17372812227456 Resetting resetlogs activation ID 1627598093 (0x61032d0d) Sun Mar 20 18:34:08 2022 Setting recovery target incarnation to 2 Sun Mar 20 18:34:08 2022 Initializing SCN for created control file Database SCN compatibility initialized to 3 Warning - High Database SCN: Current SCN value is 17372812227459, threshold SCN value is 0 If you have not previously reported this warning on this database, please notify Oracle Support so that additional diagnosis can be performed. Sun Mar 20 18:34:08 2022 Assigning activation ID 1627615603 (0x61037173) Thread 1 opened at log sequence 1 Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Mar 20 18:34:08 2022 SMON: enabling cache recovery Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc (incident=8003142): ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc (incident=8003143): ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10 ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log' ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003143/orcl_ora_14369_i8003143.trc Errors in file /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc: ORA-00399: corrupt change description in redo log ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10 ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log' ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc (incident=8003144): ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10 ORA-00334: archived log: '/media/oracle/redolog/redo02.log' ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003144/orcl_ora_14369_i8003144.trc Sun Mar 20 18:34:11 2022 ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [4044], [3964482444], [4044], [3964488833], [12669344], ORA-00600: internal error code, arguments: [2662], [4044], [3964482443], [4044], [3964488833], [12669344], ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344],
强制open数据库报ora-600 2662错误,比较常见,通过修改scn再尝试open库
Sun Mar 20 18:38:43 2022
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 17372812227460
Resetting resetlogs activation ID 1627615603 (0x61037173)
Sun Mar 20 18:40:02 2022
Setting recovery target incarnation to 2
Sun Mar 20 18:40:02 2022
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 17372812227463, threshold SCN value is 0
If you have not previously reported this warning on this database,
please notify Oracle Support so that additional diagnosis can be performed.
Sun Mar 20 18:40:02 2022
Assigning activation ID 1627669665 (0x610444a1)
Thread 1 opened at log sequence 1
Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 20 18:40:02 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:779809538 end:779809788 diff:250 (2 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Mar 20 18:40:04 2022
QMNC started with pid=55, OS id=16232
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Mar 20 18:40:05 2022
db_recovery_file_dest_size of 3882 MB is 0.00% 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.
Sun Mar 20 18:40:05 2022
Starting background process CJQ0
Sun Mar 20 18:40:05 2022
CJQ0 started with pid=58, OS id=16251
Completed: alter database open resetlogs
后续增加temp,导出数据到新库,恢复完成