朋友的一套数据库断电后出现异常，能够open，但是不能进行exp导出，发现是obj$表索引不一致。
大家都知道出现这些bootstrap$中的部分index是无法通过设置event38003进行重建,从而导致数据库无法正常使用,最常见异常index 有:I_ICOL1, I_TS1, I_CDEF1, I_CDEF2, I_CDEF3, I_CDEF4, I_PROXY_DATA$, I_IND1, I_TS#, I_UNDO1, I_UNDO2, I_COBJ#, I_USER1, I_USER2, I_CON1, I_CON2, I_FILE1, I_FILE2, I_FILE#_BLOCK#, I_USER#, I_OBJ#, I_PROXY_ROLE_DATA$_1, I_PROXY_ROLE_DATA$_2, I_CCOL1, I_CCOL2, I_TAB1, I_COL1, I_COL2, I_COL3, I_OBJ1, I_OBJ2, I_OBJ3, I_OBJ4, I_OBJ5。
因为obj$和obj$的索引都是obj# 56号对象之前，不能正常进行rebuild索引。
数据库环境是windows x64 11.2.0.1 我这里cp到linux进行操作
╭─oracle@enmotech ~ ╰─➤ export ORACLE_SID=orcl SQL> startup nomount ORACLE instance started. Total System Global Area 839282688 bytes Fixed SIZE 2217992 bytes Variable SIZE 494929912 bytes DATABASE Buffers 335544320 bytes Redo Buffers 6590464 bytes SQL> SHOW parameter control NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ control_file_record_keep_time INTEGER 7 control_files string /oradata/orcl/control01.ctl, / oradata/orcl/control02.ctl control_management_pack_access string DIAGNOSTIC+TUNING SQL> ALTER system SET control_files='/oradata/orcl/CONTROL01.CTL' scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: DATABASE NOT mounted ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 839282688 bytes Fixed SIZE 2217992 bytes Variable SIZE 494929912 bytes DATABASE Buffers 335544320 bytes Redo Buffers 6590464 bytes SQL> ORACLE instance started. SQL> SHOW parameter control NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ control_file_record_keep_time INTEGER 7 control_files string /oradata/orcl/CONTROL01.CTL control_management_pack_access string DIAGNOSTIC+TUNING SQL> ALTER DATABASE mount 2 ; DATABASE altered. SQL> SELECT name FROM v$datafile; NAME -------------------------------------------------------------------------------- D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSTEM01.DBF D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSAUX01.DBF D:\APP\ADMINISTRATOR\ORADATA\orcl\USERS01.DBF D:\APP\ADMINISTRATOR\ORADATA\orcl\CWBASE001_1.DBF D:\APP\ADMINISTRATOR\ORADATA\orcl\CWBASE008_1.DBF D:\APP\ADMINISTRATOR\ORADATA\orcl\UNDOTBS2.DBF |
这里进行rename file文件
SQL> ALTER DATABASE RENAME file 'D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSTEM01.DBF' TO '/oradata/orcl/SYSTEM01.DBF'; ALTER DATABASE RENAME file 'D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSTEM01.DBF' TO '/oradata/orcl/SYSTEM01.DBF' * ERROR at line 1: ORA-01511: error IN renaming log/DATA files ORA-01516: nonexistent log file, DATA file, OR TEMPORARY file "D:\APP\ADMINISTRATOR\ORADATA\orcl\SYSTEM01.DBF" 不能正常进行RENAME，直接重新创建控制文件 SQL> ALTER DATABASE backup controlfile TO trace AS '/tmp/ctl.sql' noresetlogs; DATABASE altered. SQL> SHOW parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time INTEGER 7 control_files string /oradata/orcl/CONTROL01.CTL control_management_pack_access string DIAGNOSTIC+TUNING SQL> ALTER system SET control_files='/oradata/orcl/control01.ctl' scope=spfile; System altered. SQL> shutdown immediate; ORA-01109: DATABASE NOT OPEN DATABASE dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 1168 7 LOGFILE 8 GROUP 1 '/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/oradata/orcl/system01.dbf', 14 '/oradata/orcl/sysaux01.dbf', 15 '/u01/undo/UNDOTBS2.DBF' --这里因为有些文件包含用户没有，没有提供数据文件 16 CHARACTER SET ZHS16GBK 17 ; Control file created. SQL> ALTER system SET undo_tablespace=UNDOTBS2 scope=spfile; System altered. SQL> shutdown immediate; ORA-01109: DATABASE NOT OPEN DATABASE dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 839282688 bytes Fixed SIZE 2217992 bytes Variable SIZE 494929912 bytes DATABASE Buffers 335544320 bytes Redo Buffers 6590464 bytes DATABASE mounted. SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: DATA file 1: '/oradata/orcl/system01.dbf' SQL> recover DATABASE; Media recovery complete. SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * ERROR at line 1: ORA-03113: end-of-file ON communication channel Process ID: 9729 SESSION ID: 125 Serial NUMBER: 5 |
这里数据已经启动，但是会话终端，查看alert日志
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' #4 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'CWBASE001' #6 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'CWBASE008' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile.
File #5 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00005' in the controlfile.
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the 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 ZHS16GBK
Sun Nov 09 20:32:53 2014
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9729.trc (incident=9753):
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_9753/orcl_ora_9729_i9753.trc
Starting background process QMNC
Sun Nov 09 20:32:56 2014
QMNC started with pid=22, OS id=9747
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Nov 09 20:32:56 2014
Trace dumping is performing id=[cdmp_20141109203256] |
alert日志提示了文件丢失信息。并出现ORA-00600: internal error code, arguments: [kdsgrp1]错误，继续查看trace文件
因为前期知道主要原因是表obj$和索引I_OBJ4 存在不一致现象
对比下相差的数据发现，存在很多数据不一致现象
SQL> SELECT /*+ FULL(t1) */ DATAOBJ#, TYPE#, OWNER#, rowid 2 FROM obj$ t1 3 MINUS 4 SELECT /*+ index(t I_OBJ4) */ DATAOBJ#, TYPE#, OWNER#, rowid 5 FROM obj$ t; DATAOBJ# TYPE# OWNER# ROWID ---------- ---------- ---------- ------------------ 1714978 2 85 AAAAASAABAAAX3MAAE 1716145 2 85 AAAAASAABAAAX22AAp 1716146 2 85 AAAAASAABAAAX22AAx 1716149 2 85 AAAAASAABAAAX22AAC 1716176 1 85 AAAAASAABAAAX22AA+ 1716177 2 85 AAAAASAABAAAX22AA/ 1716180 1 85 AAAAASAABAAAX22ABF 1716181 2 85 AAAAASAABAAAX22ABH 1716184 1 85 AAAAASAABAAAX22ABM 1716342 1 85 AAAAASAABAAAX20AAA 1716343 2 85 AAAAASAABAAAX20AAB 1716346 2 85 AAAAASAABAAAX20AAF 1716351 1 85 AAAAASAABAAAX20AAU 1716352 2 85 AAAAASAABAAAX20AAV 1716355 2 85 AAAAASAABAAAX20AAd 1716358 1 85 AAAAASAABAAAX20AAg 1716359 2 85 AAAAASAABAAAX20AAh 1716363 1 85 AAAAASAABAAAX20AAp 1716364 2 85 AAAAASAABAAAX20AAs 1716365 2 85 AAAAASAABAAAX20AAo 1716368 1 85 AAAAASAABAAAX20AAw 1716369 2 85 AAAAASAABAAAX20AAy 1716430 2 85 AAAAASAABAAAX3MAAK 1716435 1 85 AAAAASAABAAAX3MAAR 1716436 2 85 AAAAASAABAAAX3MAAS 1716442 1 85 AAAAASAABAAAX3MAAQ 1716443 2 85 AAAAASAABAAAX3MAAa 1716448 1 85 AAAAASAABAAAX3MAAe 1716449 2 85 AAAAASAABAAAX3MAAh 1716459 1 85 AAAAASAABAAAX3MAAv 1716460 2 85 AAAAASAABAAAX3MAAx 1716462 1 85 AAAAASAABAAAX3MAAc 1716463 2 85 AAAAASAABAAAX3MAAd 1716464 1 85 AAAAASAABAAAX3MAA1 1716465 2 85 AAAAASAABAAAX3MAA2 1716473 1 85 AAAAASAABAAAX3MABC 1716474 2 85 AAAAASAABAAAX3MABD 1716479 1 85 AAAAASAABAAAX3MABG 1716480 2 85 AAAAASAABAAAWBTAAA 1716487 1 85 AAAAASAABAAAWBTAAJ 1716488 2 85 AAAAASAABAAAWBTAAK 1716489 2 85 AAAAASAABAAAWBTAAL 1716492 1 85 AAAAASAABAAAWBTAAO 1716493 2 85 AAAAASAABAAAWBTAAP 1716495 1 85 AAAAASAABAAAWBTAAR 1716496 2 85 AAAAASAABAAAWBTAAH 1716499 1 85 AAAAASAABAAAWBTAAU 1716500 2 85 AAAAASAABAAAWBTAAN 1716504 1 85 AAAAASAABAAAWBTAAX 1716505 2 85 AAAAASAABAAAWBTAAa 1716510 1 85 AAAAASAABAAAWBTAAl 1716511 2 85 AAAAASAABAAAWBTAAm 1716524 1 85 AAAAASAABAAAWBTAA6 1716525 2 85 AAAAASAABAAAWBTAA7 1716528 2 85 AAAAASAABAAAWBTAA+ 1716531 1 85 AAAAASAABAAAWBTABD 1966560 2 85 AAAAASAABAAA85TAAI 1966561 2 85 AAAAASAABAAA85TAAG 1966887 2 85 AAAAASAABAAA85TAAV 1966888 1 85 AAAAASAABAAA85TAAS 1966896 2 85 AAAAASAABAAA85TAAZ 1966897 1 85 AAAAASAABAAA85TAAY 1966900 2 85 AAAAASAABAAA85TAAe 1966901 1 85 AAAAASAABAAA85TAAb 1971868 2 85 AAAAASAABAAA84dABA 1971869 1 85 AAAAASAABAAA84dABB 1971872 2 85 AAAAASAABAAA84dABG 1971873 1 85 AAAAASAABAAA84dABH 1972126 2 85 AAAAASAABAAAWA/ABH 1972362 2 85 AAAAASAABAABX31AAO 1972363 1 85 AAAAASAABAABX31AAA 1972366 2 85 AAAAASAABAABX31AAD 1972367 1 85 AAAAASAABAABX31AAP 1972436 2 85 AAAAASAABAABX3+AAz 1972437 1 85 AAAAASAABAABX3+AA0 1972698 2 85 AAAAASAABAAAW/IAAb 1972699 1 85 AAAAASAABAAAW/IAAV 1972700 2 85 AAAAASAABAAAW/IAAW 1972701 2 85 AAAAASAABAAAW/IAAc 1972702 2 85 AAAAASAABAAAW/IAAf 1972703 1 85 AAAAASAABAAAW/IAAg 1972704 2 85 AAAAASAABAAAW/IAAk 1972705 2 85 AAAAASAABAAAW/IAAm 1972706 1 85 AAAAASAABAAAW/IAAs 1972707 2 85 AAAAASAABAAAW/IAAu 1972708 2 85 AAAAASAABAAAW/IAAv 1972709 1 85 AAAAASAABAAAW/IAAw 1972710 2 85 AAAAASAABAAAW/IAAx 1972711 1 85 AAAAASAABAAAW/IAAz 1972712 2 85 AAAAASAABAAAW/IAA0 1972713 1 85 AAAAASAABAAAW/IAA2 1972714 2 85 AAAAASAABAAAW/IAA3 1972715 1 85 AAAAASAABAAAW/IAA4 1972716 2 85 AAAAASAABAAAW/IAA6 1972717 1 85 AAAAASAABAAAW/IAA7 1972718 2 85 AAAAASAABAAAW/IAA8 1972719 1 85 AAAAASAABAAAW/IAA9 1972726 2 85 AAAAASAABAAAW/IABO 1972727 1 85 AAAAASAABAAAW/IAAG 1972733 2 85 AAAAASAABAAAW/PAAG 1972734 1 85 AAAAASAABAAAW/PAAJ 1972750 2 85 AAAAASAABAAAW/PAAb 1972757 2 85 AAAAASAABAAAW/PAAp 1972758 1 85 AAAAASAABAAAW/PAAq 1972761 2 85 AAAAASAABAAAW/PAAy 1972762 1 85 AAAAASAABAAAW/PAAz 1972769 2 85 AAAAASAABAAAW/PAA8 1972774 2 85 AAAAASAABAAAW/PABH 1973012 1 85 AAAAASAABAAAW/PAA5 1973013 1 85 AAAAASAABAAAW/PAA2 1973014 2 85 AAAAASAABAAAW/PAA1 1973089 0 0 AAAAASAABAAAADxAAb 6 85 AAAAASAABAAA85TAAh 6 85 AAAAASAABAAA85TAAl 6 85 AAAAASAABAAA85TAAn 12 85 AAAAASAABAAAW/IAAB 12 85 AAAAASAABAAAW/IAAC 12 85 AAAAASAABAAAW/PAAE 12 85 AAAAASAABAAAW/PAAn 12 85 AAAAASAABAAAW/PAAw 12 85 AAAAASAABAAAW/PAA+ 12 85 AAAAASAABAAAW/PABC 12 85 AAAAASAABAAAW/PABE 12 85 AAAAASAABAAAX22AAj 12 85 AAAAASAABAAAX22AAr 12 85 AAAAASAABAAAX22AAy 12 85 AAAAASAABAAAX22AA3 12 85 AAAAASAABAAAX22ABN 12 85 AAAAASAABAAA84dAA8 12 85 AAAAASAABAAA85TAAL 12 85 AAAAASAABAAA85TAAT 12 85 AAAAASAABAAA85TAAc 12 85 AAAAASAABAAA85TAAk 12 85 AAAAASAABAAA85TAAo 12 85 AAAAASAABAAA85TAAv 12 85 AAAAASAABAABNFCAAs 反过来在看下， SQL> SELECT /*+ index(t I_OBJ4) */ DATAOBJ#, TYPE#, OWNER#, rowid 2 FROM obj$ t 3 MINUS 4 SELECT /*+ FULL(t1) */ DATAOBJ#, TYPE#, OWNER#, rowid 5 FROM obj$ t1; DATAOBJ# TYPE# OWNER# ROWID ---------- ---------- ---------- ------------------ 1704321 2 85 AAAAASAABAAAW+aAAC 1970227 2 85 AAAAASAABAABNFEAAO 1970323 2 85 AAAAASAABAABNFFAAx 1970324 1 85 AAAAASAABAABNFFAAy 1970337 2 85 AAAAASAABAABNFFABJ 1971730 2 85 AAAAASAABAAAmZxAAA 1971739 2 85 AAAAASAABAAAmZxAAJ 1971745 2 85 AAAAASAABAAAmZxAAS 1971868 2 85 AAAAASAABAAAW/hAAo 1971869 1 85 AAAAASAABAAAW/hAAn 1971872 2 85 AAAAASAABAAAW/hAAs 1971873 1 85 AAAAASAABAAAW/hAAp 1972126 2 85 AAAAASAABAABNFbAAh 1972366 2 85 AAAAASAABAAAW/hABJ 1972367 1 85 AAAAASAABAAAW/hABG 1972463 2 85 AAAAASAABAABNFbAAy 1972464 1 85 AAAAASAABAABNFbAAs 1972514 2 85 AAAAASAABAABNFbAAK 1972518 2 85 AAAAASAABAABNFbAAS 1972520 2 85 AAAAASAABAABNFbAAY 1972521 1 85 AAAAASAABAABNFbAAU 1972676 2 85 AAAAASAABAAAmZxAAb 1972698 2 85 AAAAASAABAAAmZxAAf 1972699 1 85 AAAAASAABAAAmZxAAd 1972700 2 85 AAAAASAABAAAmZxAAc 1972701 2 85 AAAAASAABAAAmZxAAg 1972702 2 85 AAAAASAABAAAmZxAAk 1972703 1 85 AAAAASAABAAAmZxAAj 1972704 2 85 AAAAASAABAAAmZxAAl 1972705 2 85 AAAAASAABAAAmZxAAn 1972706 1 85 AAAAASAABAAAmZxAAm 1972707 2 85 AAAAASAABAAAmZxAAo 1972708 2 85 AAAAASAABAAAmZxAAq 1972709 1 85 AAAAASAABAAAmZxAAp 1972710 2 85 AAAAASAABAAAmZxAAv 1972711 1 85 AAAAASAABAAAmZxAAr 1972712 2 85 AAAAASAABAAAmZxAAx 1972713 1 85 AAAAASAABAAAmZxAAw 1972714 2 85 AAAAASAABAAAmZxAA1 1972715 1 85 AAAAASAABAAAmZxAAz 1972716 2 85 AAAAASAABAAAmZxAA4 1972717 1 85 AAAAASAABAAAmZxAA3 1972718 2 85 AAAAASAABAAAmZxAA6 1972719 1 85 AAAAASAABAAAmZxAA5 1972726 2 85 AAAAASAABAAAmZxABA 1972727 1 85 AAAAASAABAAAmZxAA7 1972733 2 85 AAAAASAABAAAmZxABG 1972734 1 85 AAAAASAABAAAmZxABB 1972757 2 85 AAAAASAABAAAmZxABM 1972758 1 85 AAAAASAABAAAmZxABH 1972761 2 85 AAAAASAABAAAW/hAAD 1972762 1 85 AAAAASAABAAAW/hAAA 1972769 2 85 AAAAASAABAAAW/hAAJ 1972774 2 85 AAAAASAABAAAW/hAAQ 1973005 2 85 AAAAASAABAAAW/hAAY 1973012 1 85 AAAAASAABAAAW/hAAF 1973013 1 85 AAAAASAABAAAW/hAAE 1973014 2 85 AAAAASAABAAAW/hAAG 1973017 2 85 AAAAASAABAAAW/hAAg 1973018 1 85 AAAAASAABAAAW/hAAb 1973019 2 85 AAAAASAABAAAW/hAAT 1973040 2 85 AAAAASAABAABNFbAAA 1973041 2 85 AAAAASAABAABNFbAAB 1973042 1 85 AAAAASAABAABNFbAAD 1973099 0 0 AAAAASAABAAAADxAAb 10 85 AAAAASAABAAAW/hAAC 10 85 AAAAASAABAAAW/hAAI 10 85 AAAAASAABAAAW/hAAN 10 85 AAAAASAABAAAW/hAAP 10 85 AAAAASAABAAAW/hAAS 10 85 AAAAASAABAAAW/hAAW 10 85 AAAAASAABAAAW/hAAk 10 85 AAAAASAABAAAW/hAAr 10 85 AAAAASAABAAAW/hAAu 10 85 AAAAASAABAAAW/hAAx 10 85 AAAAASAABAAAW/hAA1 10 85 AAAAASAABAAAW/hAA5 10 85 AAAAASAABAAAW/hAA8 10 85 AAAAASAABAAAW/hAA+ 10 85 AAAAASAABAAAW/hABI 10 85 AAAAASAABAAAX22AAj 10 85 AAAAASAABAAAX22AAr 10 85 AAAAASAABAAAX22AAy 10 85 AAAAASAABAAAX22AA3 10 85 AAAAASAABAAAX22ABN 10 85 AAAAASAABAAAX3MAAf 10 85 AAAAASAABAAAX3MAAn 10 85 AAAAASAABAAAX3MAA8 10 85 AAAAASAABAAAX3MABH 10 85 AAAAASAABAAAmZxAAC 10 85 AAAAASAABAAAmZxAAG 10 85 AAAAASAABAAAmZxAAL 10 85 AAAAASAABAAAmZxAAP 10 85 AAAAASAABAAAmZxAAU 10 85 AAAAASAABAAAmZxAAZ 10 85 AAAAASAABAAAmZxABD 10 85 AAAAASAABAAAmZxABL 12 85 AAAAASAABAAAWBnAAX 12 85 AAAAASAABAAAW+uAAE 12 85 AAAAASAABAAAW+uAAT 12 85 AAAAASAABAAAW+uAAV 12 85 AAAAASAABAAAW+uAAm 12 85 AAAAASAABAAAW+uAAn 12 85 AAAAASAABAAAW/hAAB 12 85 AAAAASAABAAAW/hAAH 12 85 AAAAASAABAAAW/hAAM 12 85 AAAAASAABAAAW/hAAO 12 85 AAAAASAABAAAW/hAAR 12 85 AAAAASAABAAAW/hAAV 12 85 AAAAASAABAAAW/hAAj 12 85 AAAAASAABAAAW/hAAq 12 85 AAAAASAABAAAW/hAAt 12 85 AAAAASAABAAAW/hAAw 12 85 AAAAASAABAAAW/hAAz 12 85 AAAAASAABAAAW/hAA4 12 85 AAAAASAABAAAW/hAA7 12 85 AAAAASAABAAAW/hAA9 12 85 AAAAASAABAAAW/hABH 118 ROWS selected. |
正反对比 发先上面有存在重复的rowid
1973089 0 0 AAAAASAABAAAADxAAb 1973099 0 0 AAAAASAABAAAADxAAb |
SQL> @lookup_rowid AAAAASAABAAAADxAAb
+————————————————————————+
| Report : lookup_rowid.sql |
| Instance : orcl |
| User : SYS |
+————————————————————————+
ROWID: AAAAASAABAAAADxAAb
Object#: 18
RelFile#: 1
Block#: 241
Row#: 27
在分布从索引和表看下最大值是多少
SQL> SELECT /*+ index(t I_OBJ4) */ MAX(DATAOBJ#) 2 FROM obj$ t ; MAX(DATAOBJ#) ------------- 1973099 索引上的最大值为 1973099,此值为索引上的，查看下下1973089在表上的对象吧，发现是_NEXT_OBJECT对象 SQL> SELECT obj#,dataobj# FROM obj$ WHERE name='_NEXT_OBJECT'; OBJ# DATAOBJ# ---------- ---------- 1 1973089 _NEXT_OBJECT 对象是在创建对象需到的,索引和表上数据不一致创建对象的时候就出现问题，先解决此问题 进行UPDATE操作看下报错信息 SQL> UPDATE obj$ SET dataobj#=1973100 WHERE name LIKE '_NEXT%'; UPDATE obj$ SET dataobj#=1973100 WHERE name LIKE '_NEXT%' * ERROR at line 1: ORA-08102: INDEX KEY NOT found, obj# 39, file 1, block 402689 (2) |
出现ORA-08102:,查看trace文件
oer 8102.2 - obj# 39, rdba: 0x00462501(afn 1, blk# 402689) kdk KEY 8102.2: ncol: 4, len: 17 KEY: (17): 05 c4 02 62 1f 5a 01 80 01 80 06 00 40 00 f1 00 1b mask: (4096): 如果熟悉索引在块上怎么存储的你可以把05 c4 02 62 1f 5a 01 80 01 80 06 00 40 00 f1 00 1b进行转换 05 c4 02 62 1f 5a --col1 --1973089 01 80 --col1 01 80 --col1 06 00 40 00 f1 00 1b --rowid 这里把rowid进行转换下 SQL> SELECT idx_rowid('00 40 00 f1 00 1b') FROM dual; IDX_ROWID('004000F1001B') ------------------------------------------- File# = 1, Block# = 241, ROW# = 27 发现和上面的AAAAASAABAAAADxAAb这个值一样 在trace文件里进行全局查找06 00 40 00 f1 00 1b,发现一个所以块信息 Object id ON Block? Y seg/obj: 0x27 csc: 0x00.d4fcdb3 itc: 11 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000e.00c.00003110 0x00c04472.09f5.01 CBU- 0 scn 0x0000.0d4ddfb8 0x02 0x000c.013.0000452b 0x00c057b2.0dd1.1d --U- 2 fsc 0x0015.0d4fcdce 0x03 0x000f.01a.00002228 0x00c04f9c.072d.0f --U- 1 fsc 0x0000.0d4fcde3 0x04 0x0005.005.00021a96 0x00c09d57.6809.39 --U- 1 fsc 0x0000.0d4fcdeb 0x05 0x0008.000.00022f64 0x00c005c3.6e28.3a C--- 0 scn 0x0000.0d4fca0b 0x06 0x000d.010.0000381d 0x00c07417.0b37.14 C--- 0 scn 0x0000.0d4fca12 0x07 0x0002.00d.00020434 0x00c07fb1.66d5.0f C--- 0 scn 0x0000.0d4fcd25 0x08 0x000e.010.00003131 0x00c035e4.09fc.61 --U- 2 fsc 0x0017.0d4fcdbb 0x09 0x000a.01d.000265f6 0x00c03df7.75ea.0d C--- 0 scn 0x0000.0d4fcd18 0x0a 0x0001.00b.0001f1cc 0x00c02da9.6477.08 C--- 0 scn 0x0000.0d4fcd64 0x0b 0x000f.00b.00002227 0x00c04f9d.072d.04 C--- 0 scn 0x0000.0d4fcdab ROW#277[1074] flag: ------, lock: 2, len=19 col 0; len 5; (5): c4 02 62 1f 64 col 1; len 1; (1): 80 col 2; len 1; (1): 80 col 3; len 6; (6): 00 40 00 f1 00 1b --rowid 相同 查看下col 1的值 SQL> SELECT f_get_from_dump(REPLACE('c4 02 62 1f 64',' ',','),'NUMBER') FROM dual; F_GET_FROM_DUMP(REPLACE('C402621F64','',','),'NUMBER') ------------------------------------------------------------- 1973099 这里就发现了 上面的1973099和1973089两个值的来源了，是_NEXT_OBJECT对象的dataobj#的值在索引和表上面的数据不一致现象 使用bbed修改成一直看下 BBED> x /r rowdata[0] @1086 ---------- flag@1086: 0x2c (KDRHFL, KDRHFF, KDRHFH) LOCK@1087: 0x00 cols@1088: 18 col 0[2] @1089: 0xc1 0x02 col 1[5] @1092: 0xc4 0x02 0x62 0x1f 0x5a --1973089 col 2[1] @1098: 0x80 col 3[12] @1100: 0x5f 0x4e 0x45 0x58 0x54 0x5f 0x4f 0x42 0x4a 0x45 0x43 0x54 col 4[2] @1113: 0xc1 0x02 col 5[0] @1116: *NULL* col 6[1] @1117: 0x80 col 7[7] @1119: 0x78 0x6e 0x03 0x1e 0x0b 0x08 0x31 col 8[7] @1127: 0x78 0x72 0x0a 0x02 0x12 0x31 0x01 col 9[7] @1135: 0x78 0x6e 0x03 0x1e 0x0b 0x08 0x31 col 10[1] @1143: 0x80 col 11[0] @1145: *NULL* col 12[0] @1146: *NULL* col 13[1] @1147: 0x80 col 14[0] @1149: *NULL* col 15[1] @1150: 0x80 col 16[4] @1152: 0xc3 0x07 0x38 0x24 col 17[1] @1157: 0x80 BBED> SET offset +11 OFFSET 1097 BBED> m /x 64 File: /oradata/orcl/system01.dbf (1) Block: 241 Offsets: 1097 TO 1608 Dba:0x004000f1 ------------------------------------------------------------------------ 6401800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 6e031e0b 08310778 720a0212 31010778 6e031e0b 08310180 ffff0180 ff018004 c3073824 01802c01 1202c102 04c40262 2001800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 6e031e0b 08310778 720a0212 31010778 6e031e0b 08310180 ffff0180 ff018004 BBED> p *kdbr[27] rowdata[0] ---------- ub1 rowdata[0] @1086 0x2c BBED> x /rnnnc rowdata[0] @1086 ---------- flag@1086: 0x2c (KDRHFL, KDRHFF, KDRHFH) LOCK@1087: 0x00 cols@1088: 18 col 0[2] @1089: 1 col 1[5] @1092: 1973099 col 2[1] @1098: 0 col 3[12] @1100: _NEXT_OBJECT col 4[2] @1113: .. col 5[0] @1116: *NULL* col 6[1] @1117: . col 7[7] @1119: xn....1 col 8[7] @1127: xr...1. col 9[7] @1135: xn....1 col 10[1] @1143: . col 11[0] @1145: *NULL* col 12[0] @1146: *NULL* col 13[1] @1147: . col 14[0] @1149: *NULL* col 15[1] @1150: . col 16[4] @1152: ..8$ col 17[1] @1157: . BBED> SUM apply CHECK VALUE FOR File 1, Block 241: CURRENT = 0xf78f, required = 0xf78f BBED> v DBVERIFY - Verification starting FILE = /oradata/orcl/system01.dbf BLOCK = 241 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (DATA) : 1 Total Blocks Failing (DATA) : 0 Total Blocks Processed (INDEX): 0 Total Blocks Failing (INDEX): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 NOT found; product=RDBMS; facility=BBED BBED> 尝试UPDATE操作 SQL> UPDATE obj$ SET dataobj#=1973105 WHERE name='_NEXT_OBJECT'; 1 ROW updated. SQL> commit; Commit complete. SQL> UPDATE obj$ SET dataobj#=1973107 WHERE name='_NEXT_OBJECT'; 1 ROW updated. SQL> commit 这里可以UPDATE操作 对整个表进行插入记录试试 SQL> INSERT INTO OBJ$ (obj#,owner#,name,namespace,TYPE#,ctime,mtime,stime,STATUS) VALUES (1973104,0,'A1',1,2,sysdate,sysdate,sysdate,0); 1 ROW created. SQL> commit; Commit complete. INSERT成功 创建TABLE 失败 SQL> CREATE TABLE a1 (id NUMBER); CREATE TABLE a1 (id NUMBER) * ERROR at line 1: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] SQL> ALTER SESSION SET events '10046 trace name context forever, level 12'; SESSION altered. SQL> CREATE TABLE a1 (id NUMBER); CREATE TABLE a1 (id NUMBER) * ERROR at line 1: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] SQL> SQL> col trace_file_name FOR a100 SQL> SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' || 2 p.spid || '.trc' trace_file_name 3 FROM (SELECT p.spid 4 FROM v$mystat m, v$session s, v$process p 5 WHERE m.statistic# = 1 6 AND s.SID = m.SID 7 AND p.addr = s.paddr) p, 8 (SELECT t.INSTANCE 9 FROM v$thread t, v$parameter v 10 WHERE v.NAME = 'thread' 11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i, 12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d; TRACE_FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10815.trc 打开trace文件发现在一下SQL处失败 SELECT o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname FROM obj$ o WHERE o.obj#=:1 带入绑定变量进行尝试 SQL> SELECT o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname FROM obj$ o WHERE o.obj#=1973107; SELECT o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname FROM obj$ o WHERE o.obj#=1973107 * ERROR at line 1: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] SQL> SELECT o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname FROM obj$ o WHERE o.obj#=1973104; ERROR: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] SQL> SELECT o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname FROM obj$ o WHERE o.obj#=1973100; no ROWS selected SQL> SELECT o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname FROM obj$ o WHERE o.obj#=1973101; no ROWS selected 进而发现obj$和I_OBJ1索引也不一致 查看i_obj1 索引的最大值 SQL> SELECT /*+ index(t i_obj1) */ MAX(obj#) FROM obj$ t; MAX(OBJ#) ---------- 1973065 dump 这个索引 查找这个键值,知道这个值存在索引的最后一个块 大家都知道索引结构分为根、分枝、叶子块，知道这个块有问题想法不让oracle访问就行，可以尝试一下方法 1、修改索引的统计信息，让oracle评估是走全表扫 2、profile固话执行计划 3、提高分枝块的数据 我这里用了提供分枝快的数据方法，批量往obj$插入数据 DECLARE l_number NUMBER; test varchar2(30); BEGIN FOR i IN 1974002 .. 1974100 loop test := 'travel' || i; INSERT INTO obj$( OBJ# , DATAOBJ# , OWNER# , NAME , NAMESPACE , SUBNAME , TYPE# , CTIME , MTIME , STIME , STATUS , REMOTEOWNER , LINKNAME , FLAGS , OID$ , SPARE1 , SPARE2 , SPARE3 , SPARE4 , SPARE5 , SPARE6) SELECT i, i, OWNER# , test , NAMESPACE , SUBNAME , TYPE# , CTIME , MTIME , STIME , STATUS , REMOTEOWNER , LINKNAME , FLAGS , OID$ , SPARE1 , SPARE2 , SPARE3 , SPARE4 , SPARE5 , SPARE6 FROM obj$ WHERE name='_NEXT_OBJECT' ; END loop; commit; END; / 这里修改下 _NEXT_OBJECT的数据，要不然创建表会报错 ORA-00600: internal error code, arguments: [kkdlcob-objn-EXISTS], [1974099], [], [], [], [], [], [], [], [], [], SQL> UPDATE obj$ SET dataobj#=1974001 WHERE name='_NEXT_OBJECT'; 1 ROW updated. SQL> commit; Commit complete. SQL> CREATE TABLE t1 (id NUMBER); TABLE created. 可以正常创建表了 下面就是在upgrade模式中提供obj$表和索引，参考 <a href="http://www.xifenfei.com/5566.html">惜分飞的bootstrap$核心INDEX(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决</a> 重建玩这个EXP数据正常，但是这个库还存在con$、cdef$ 等基表数据不一致现象。后面就不折腾了 |
