ORA-600_16703比特币攻击案例分析(下)
可以看到这里需要替换38个数据块,替换后可以成功open数据库
BBED> copy file 2 block 156 to file 1 block 156 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1) Block: 156 Offsets: 0 to 511 Dba:0x0040009c------------------------------------------------------------------------ 06a20000 9c004000 13000300 00000106 c8120000 01000000 02000000 12000300 00000000 02000200 00000000 00006100 0f000000 81004000 18003000 00800000 970a0000 07000d00 99000000 8d59c000 1b001700 02200000 13000300 00076300 fffff000 50020f08 0f080000 0a000a00 0a001400 00001400 0b001f00 0e002d00 35006200 0100891f 511d0219 6217c215 6b14a012 ea10730f 610d8c08 64044105 89030a09 580aaa02 da097c06 b7070f1e 3c080d04 e6042e03 50028a09 1f06c505 5b070107 c41da71d 75195819 d517b817 35161816 3f11c90f 140eb70d 6e0b120b ea1eb61e 831e4d1e b31c7b1c 401c071c c91b921b 571b1e1b e21aa81a 701a391a 021ac919 92196318 2b18f217 c3168b16 52162815 ef14ba14 8214d113 98135d13 2413ef12 b7120112 c8119111 5c114b10 1810e60f cb0e940e 630e310e c20c8e0c 540c210c f00bc00b 8b0be11d 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> set count 1 COUNT 1BBED> copy file 2 block 156 to file 1 block 156 File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1) Block: 156 Offsets: 0 to 0 Dba:0x0040009c------------------------------------------------------------------------ 06 <32 bytes per line> BBED> copy file 2 block 160 to file 1 block 160......省略
尝试open数据库:SQL> conn / as sysdbaConnected to an idle instance.SQL> startupORACLE instance started. Total System Global Area 1269366784 bytesFixed Size 2252864 bytesVariable Size 1191186368 bytesDatabase Buffers 67108864 bytesRedo Buffers 8818688 bytesDatabase mounted.Database opened.
从alert日志可以看到此时数据库open伴随着ora-7445,并且5分钟后就会crash掉,所以要抓紧着5分钟的操作时间
Thu Feb 14 03:52:16 2019
QMNC started with pid=20, OS id=2696
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0xC1B4BF, kauxalo()+355] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/lxy/lxy/trace/lxy_mmon_2682.trc (incident=230658):
ORA-07445: exception encountered: core dump [kauxalo()+355] [SIGSEGV] [ADDR:0x0] [PC:0xC1B4BF] [SI_KERNEL(general_protection)] []
Incident details in: /u01/app/oracle/diag/rdbms/lxy/lxy/incident/incdir_230658/lxy_mmon_2682_i230658.trcUse ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Completed: ALTER DATABASE OPENThu Feb 14 03:52:20 2019Dumping diagnostic data in directory=[cdmp_20190214035220], requested by (instance=1, osid=2682 (MMON)), summary=[incident=230658].
Thu Feb 14 03:57:17 2019Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x8F] [PC:0x960E760, qkaRemoveNodeCB()+36] [flags: 0x0, count: 1]Errors in file /u01/app/oracle/diag/rdbms/lxy/lxy/trace/lxy_smon_2678.trc (incident=230642):
ORA-07445: exception encountered: core dump [qkaRemoveNodeCB()+36] [SIGSEGV] [ADDR:0x8F] [PC:0x960E760] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/lxy/lxy/incident/incdir_230642/lxy_smon_2678_i230642.trcUse ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Feb 14 03:57:20 2019System state dump requested by (instance=1, osid=2654 (PMON)), summary=[abnormal instance termination].System State dumped to trace file /u01/app/oracle/diag/rdbms/lxy/lxy/trace/lxy_diag_2664_20190214035720.trc
Thu Feb 14 03:57:20 2019PMON (ospid: 2654): terminating the instance due to error 474Dumping diagnostic data in directory=[cdmp_20190214035720], requested by (instance=1, osid=2654 (PMON)), summary=[abnormal instance termination].Instance terminated by PMON, pid = 2654
如何将备份的tab$insert回tab$?
由于tab$的备份表在tab$中并没有恢复所以无法查询,下面需要根据redodump去确定tab$的备份表t_bak的rdba
SQL> desc t_bak ERROR: ORA-03113: end-of-file on communication channel Process ID: 2812Session ID: 125 Serial number: 7
通过对logdump搜索OBJ:2(C_OBJ#的dataobj#)、OP:11.2(insert操作)、tabn:1(C_OBJ#中tab$的tab#),以及查出来的t_bak的obj#,不难找到create table as t_bak对tab$的redo日志:
CHANGE #2 TYP:2 CLS:1 AFN:1 DBA:0x00407b2c OBJ:2 SCN:0x0000.000f5a8b SEQ:1 OP:11.2 ENC:0 RBL:0KTB Redo op: 0x11 ver: 0x01 compat bit: 4 (post-11) padding: 1op: F xid: 0x0004.00f.0000011e uba: 0x00c00630.0050.37Block cleanout record, scn: 0x0000.000f5a8b ver: 0x01 opt: 0x02, entries follow... itli: 1 flg: 2 scn: 0x0000.000f5a8b KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00407b2c hdba: 0x00400090itli: 2 ispac: 0 maxfr: 4863tabn: 1 slot: 2(0x2) size/delt: 123fb: -CH-FL-- lb: 0x2 cc: 36 cki: 0null:01234567890123456789012345678901234567890123456789012345678901234567890123456789----NN-N------NNNNNNNNNNNN-----NNNN-col 0: [ 4] c3 02 30 34 --obj#col 1: [ 1] 80col 2: [ 2] c1 02col 3: [ 4] c3 05 61 29col 4: *NULL*col 5: *NULL*col 6: [ 2] c1 26col 7: *NULL*col 8: [ 2] c1 0bcol 9: [ 2] c1 29col 10: [ 2] c1 02col 11: [ 3] c2 03 38col 12: [ 2] c1 02col 13: [38] 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2dcol 14: *NULL*col 15: *NULL*col 16: *NULL*col 17: *NULL*col 18: *NULL*col 19: *NULL*col 20: *NULL*col 21: *NULL*col 22: *NULL*col 23: *NULL*col 24: *NULL*col 25: *NULL*col 26: [ 2] c1 26col 27: [ 2] c1 26col 28: [ 6] c5 06 25 58 0a 0dcol 29: [ 1] 80col 30: [ 3] c2 02 4dcol 31: *NULL*col 32: *NULL*col 33: *NULL*col 34: *NULL*col 35: [ 7] 78 77 01 1f 0e 25 01
可以看到t_bak在tab$的rdba地址为0x00407b2c(file 1 block 31532),cki为0即cluster key为kdbr[0]
BBED> set count 16
COUNT 16BBED> set file 1 block 31532
FILE# 1
BLOCK# 31532BBED> p *kdbr[0]
rowdata[2060]-------------ub1 rowdata[2060] @2601 0xacBBED> x /rx
rowdata[2060] @2601 -------------flag@2601: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)lock@2602: 0x00cols@2603: 1kref@2604: 38mref@2606: 37hrid@2608:0x00407b2c.0nrid@2614:0x00407b2c.0col 0[4] @2620: 0xc3 0x02 0x30 0x34
与redo dump一致,下面开始恢复tab$中t_bak的记录,由于是cluster block所以过程有点繁琐
BBED> p *kdbr[5]
rowdata[1937]-------------ub1 rowdata[1937] @2478 0x7c
BBED> x /rn
rowdata[1937] @2478
-------------flag@2478: 0x7c (KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC)lock@2479: 0x02cols@2480: 0BBED> m /x 6c offset 2478
File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1) Block: 31532 Offsets: 2478 to 2493 Dba:0x00407b2c------------------------------------------------------------------------
6c022400 04c30230 34018002 c10204c3
<32 bytes per line>
BBED> sum applyCheck value for File 1, Block 31532:current = 0x1e1f, required = 0x1e1fBBED> v
DBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbfBLOCK = 31532Block Checking: DBA = 4225836, Block Type = KTB-managed data blockdata header at 0x7f1cebc1d25ckdbchk: key comref count wrong
keyslot=0Block 31532 failed with check code 6121DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED
BBED> m /x 26 offset 2606
File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1)
Block: 31532 Offsets: 2606 to 2621 Dba:0x00407b2c------------------------------------------------------------------------
26000040 7b2c0000 00407b2c 000004c3
<32 bytes per line>
BBED> sum applyCheck value for File 1, Block 31532:current = 0x1e1c, required = 0x1e1cBBED> v
DBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbfBLOCK = 31532Block Checking: DBA = 4225836, Block Type = KTB-managed data blockdata header at 0x11e5a5ckdbchk: the amount of space used is not equal to block size
used=3398 fsc=385 avsp=4432 dtl=8096Block 31532 failed with check code 6110DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 7
sb2 kdbhnrow @94 127
sb2 kdbhfrre @96 6
sb2 kdbhfsbo @98 296
sb2 kdbhfseo @100 449
sb2 kdbhavsp @102 4432
sb2 kdbhtosp @104 4829
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0008
ub2 kxidslt @46 0x0005
ub4 kxidsqn @48 0x00000182
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c00d2f
ub2 kubaseq @56 0x0086
ub1 kubarec @58 0x14
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x00102cd7
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0009
ub2 kxidslt @70 0x0009
ub4 kxidsqn @72 0x0000018f
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00499
ub2 kubaseq @80 0x00f9
ub1 kubarec @82 0x09
ub2 ktbitflg @84 0x2003 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 385
ub2 _ktbitwrp @86 0x0181
ub4 ktbitbas @88 0x00102ce3
BBED> m /x 0000 offset 86
File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1)
Block: 31532 Offsets: 86 to 101 Dba:0x00407b2c------------------------------------------------------------------------
0000e32c 10000007 7f000600 2801c101
<32 bytes per line>
BBED> m /x 5a12 offset 102
File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1)
Block: 31532 Offsets: 102 to 117 Dba:0x00407b2c------------------------------------------------------------------------
5a1210d9 00000300 03000300 06000000
<32 bytes per line>
BBED> m /x 5a12 offset 104
File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1)
Block: 31532 Offsets: 104 to 119 Dba:0x00407b2c------------------------------------------------------------------------
5a120000 03000300 03000600 00000600
<32 bytes per line>
BBED> sum applyCheck value for File 1, Block 31532:current = 0x1c10, required = 0x1c10BBED> v
DBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbfBLOCK = 31532Block Checking: DBA = 4225836, Block Type = KTB-managed data blockdata header at 0x1207a5ckdbchk: space available on commit is incorrect
tosp=4698 fsc=0 stb=8 avsp=4698Block 31532 failed with check code 6111DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED
BBED> m /x 6212 offset 104
File: /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbf (1)
Block: 31532 Offsets: 104 to 119 Dba:0x00407b2c------------------------------------------------------------------------
62120000 03000300 03000600 00000600
<32 bytes per line>
BBED> sum applyCheck value for File 1, Block 31532:current = 0x1c28, required = 0x1c28BBED> v
DBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/LXY/datafile/o1_mf_system_fzvzmcmw_.dbfBLOCK = 31532DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED
SQL> select count(*) from t_bak;
COUNT(*)----------
1250
t_bak已经恢复完成,下面insert回tab$
SQL> insert into tab$ select * from (select * from t_bak where obj# in (select obj# from t_bak where obj#<>14751 minus select obj# from tab$));
982 rows created.
SQL> commit;Commit complete.SQL> select count(*) from t;
COUNT(*)----------
13982
至此数据库基本恢复完成。
但是通过hcheck脚本检查数据字典一致性发现还是有一些问题存在:
SQL> @hcheck
HCheck Version 07MAY18 on 14-FEB-2019 22:49:53----------------------------------------------Catalog Version 11.2.0.4.0 (1102000400)
db_name: LXY
Catalog Fixed
Procedure Name Version Vs Release TimestampResult------------------------------ ... ---------- -- ---------- --------------------.- LobNotInObj ... 1102000400 <= *All Rel* 02/14 22:49:53 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 02/14 22:49:53 FAIL
HCKE-0002: Object type column with missing OID$ (Doc ID 1360268.1)
OBJ#=12946 Name=SYS.AQ$SCHEDULER$_EVENT_QTAB IntCol#=20=USER_DATA TabProp=
OBJ#=12953 Name=SYS.SCHEDULER$_REMDB_JOBQTAB IntCol#=28=USER_DATATabProp=539101206OBJ#=12953 Name=SYS.SCHEDULER$_REMDB_JOBQTAB IntCol#=31=SYS_NC00031$
TabProp=539101206OBJ#=12953 Name=SYS.SCHEDULER$_REMDB_JOBQTAB IntCol#=46=SYS_NC00046$
TabProp=539101206OBJ#=12987 Name=SYS.SCHEDULER_FILEWATCHER_QT IntCol#=28=USER_DATATabProp=539101190OBJ#=12987 Name=SYS.SCHEDULER_FILEWATCHER_QT IntCol#=35=SYS_NC00035$
TabProp=539101190OBJ#=13273 Name=SYS.AQ_EVENT_TABLE IntCol#=25=USER_DATA TabProp=539363346OBJ#=13281 Name=SYS.AQ$AQ_EVENT_TABLE IntCol#=20=USER_DATA TabProp=
OBJ#=13282 Name=SYS.AQ$_AQ_EVENT_TABLE_F IntCol#=24=USER_DATA TabProp=
OBJ#=13285 Name=SYS.AQ_PROP_TABLE IntCol#=28=USER_DATA TabProp=539101186OBJ#=13591 Name=SYS.SYS$SERVICE_METRICS_TAB IntCol#=28=USER_DATATabProp=539101186.- SourceNotInObj ... 1102000400 <= *All Rel* 02/14 22:49:53 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- FetUet ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadTab$ ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadOwner ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- BadDepends ... 1102000400 <= *All Rel* 02/14 22:49:54 PASS
.- CheckDual ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- BadCboHiLo ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 02/14 22:49:55 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 02/14 22:49:55 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 02/14 22:49:55 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 02/14 22:49:55 PASS---------------------------------------14-FEB-2019 22:49:55 Elapsed: 2 secs---------------------------------------Found 11 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace fileto check if the above needs attention or notPL/SQL procedure successfully completed.Statement processed.Complete output is in trace file:
/u01/app/oracle/diag/rdbms/lxy/lxy/trace/lxy_ora_1808_HCHECK.trc
发现了11处问题,都是HCKE-0002: Object type column with missing OID$。 这是什么意思呢?
分析hcheck脚本的MissingOIDOnObjCol存储过程:
Procedure MissingOIDOnObjCol (nF In Number Default 0, VerChk In Number Default 5, Verbose In Boolean Default FALSE) Is nFr Number ; Cursor sCur1 Is Select o.obj# , o.type#, o.owner#, o.name, c.col#, c.intcol#, c.name cname, t.property From obj$ o, col$ c, coltype$ ct, oid$ oi, tab$ t Where o.obj# = ct.obj# And ct.obj# = c.obj# And ct.col# = c.col# And ct.intcol# = c.intcol# And oi.oid$(+) = ct.toid And o.obj# = t.obj#(+) And oi.oid$ is null; ps1 Varchar2(10) := 'HCKE-0002'; ps1a Varchar2(65) := 'Object type column with missing OID ps1n Varchar2(40) := '(Doc ID 1360268.1)';
CursorRun Boolean := FALSE;
Begin
If ( nF = 0) Then
nFr := FindFname('MissingOIDOnObjCol') ; Else nFr := nF;
End If ;
If ChecknCatVnFR (nCatV, nFr, VerChk) = FALSE Then Return; End If;
For c1 In sCur1 Loop
If (not CursorRun) Then
report_failure('FAIL',ps1,ps1a,ps1n,CursorRun);
End If;
put_line(' OBJ#='||c1.obj#||' Name='||Owner(c1.owner#)||'.'
||c1.name||' IntCol#='||c1.intcol#||'='||c1.cname
||' TabProp='||c1.property);
Fatal := Fatal + 1 ;
End Loop ;
If (CursorRun) Then put(chr(10)) ; else put_line('PASS'); End If ;
End ;
仔细对脚本进行分析,推测是当表的字段类型为type类型的对象时,coltype$的toid和oid$的oid$不匹配导致的,应该是之前为了open数据库替换块的时候造成的。
以OBJ#=12946 Name=SYS.AQ$SCHEDULER$_EVENT_QTAB IntCol#=20=USER_DATA为例继续分析:
由于数据字典不一致,该表是不可以正常访问:
SQL> desc SYS.AQ$SCHEDULER$_EVENT_QTAB
ERROR:
ORA-00600: internal error code, arguments: [16687], [12946], [20], [], [], [], [], [], [], [], [], []
SQL> select * from SYS.AQ$SCHEDULER$_EVENT_QTAB;select * from SYS.AQ$SCHEDULER$_EVENT_QTAB
*ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
对正常的数据库查询可以看的SYS.AQ$SCHEDULER$_EVENT_QTAB的字段名为USER_DATA的字段类型为SCHEDULER$_EVENT_INFO,通过下面的查询可以发现确实不匹配(以oid$的为准,因为之前替换的是C_OBJ#,而coltype$是C_OBJ#中的一个表):
SQL> select oid$ from oid$ where obj# in (select obj# from obj$ where name='SCHEDULER$_EVENT_INFO'); OID$--------------------------------7BB17EE961D00845E0536438A8C00848 SQL> select toid from coltype$ where obj# in (select obj# from obj$ where name='AQ$SCHEDULER$_EVENT_QTAB') and intcol#=20; TOID--------------------------------81673B4EDDF5111FE0536438A8C02F5D 通过下面的查询也可以推出以oid$的为准 SQL> select toid from type$ where toid in ('7BB17EE961D00845E0536438A8C00848','81673B4EDDF5111FE0536438A8C02F5D'); TOID--------------------------------7BB17EE961D00845E0536438A8C00848
修改coltype$后恢复正常:
SQL> update coltype$ set toid='7BB17EE961D00845E0536438A8C00848' where obj# in (select obj# from obj$ where name='AQ$SCHEDULER$_EVENT_QTAB') and intcol#=20; 1 row updated. SQL> commit;Commit complete.SQL> @flcSystem altered.System altered.SQL> select * from AQ$SCHEDULER$_EVENT_QTAB;no rows selected
逐一修改后,再次执行hcheck:
SQL> @hcheck
HCheck Version 07MAY18 on 15-FEB-2019 01:56:00----------------------------------------------Catalog Version 11.2.0.4.0 (1102000400)
db_name: LXY
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result------------------------------ ... ---------- -- ---------- -------------- ------.- LobNotInObj ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- SourceNotInObj ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- FetUet ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadTab$ ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadOwner ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadDepends ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- CheckDual ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 02/15 01:56:00 PASS
.- BadCboHiLo ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 02/15 01:56:01 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 02/15 01:56:01 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 02/15 01:56:01 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 02/15 01:56:01 PASS---------------------------------------15-FEB-2019 01:56:01 Elapsed: 1 secs---------------------------------------Found 0 potential problem(s) and 0 warning(s)
PL/SQL procedure successfully completed.Statement processed.
至此整个数据库比较完整的恢复完毕。