一次obj$、obj$索引不一致的恢复

作者：又未命名 | 来源：互联网 | 2018-06-12 12:42

朋友的一套数据库断电后出现异常，能够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 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

+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;+
| Report : lookup_rowid.sql |
| Instance : orcl |
| User : SYS |
+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;+

ROWID: AAAAASAABAAAADxAAb
Object#: 18
RelFile#: 1
Block#: 241
Row#: 27

在分布从索引和表看下最大值是多少

SQL>   SELECT /*+ index(t I_OBJ4) */ MAX&＃40;DATAOBJ#&＃41;
  2   FROM obj$ t ;
 
MAX&＃40;DATAOBJ#&＃41;
-------------
      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 &＃40;2&＃41;

出现ORA-08102:,查看trace文件

oer 8102.2 - obj# 39, rdba: 0x00462501&＃40;afn 1, blk# 402689&＃41;
kdk KEY 8102.2:
  ncol: 4, len: 17
  KEY: &＃40;17&＃41;:  05 c4 02 62 1f 5a 01 80 01 80 06 00 40 00 f1 00 1b
  mask: &＃40;4096&＃41;:
如果熟悉索引在块上怎么存储的你可以把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&＃40;'00 40 00 f1 00 1b'&＃41; FROM dual;
IDX_ROWID&＃40;'004000F1001B'&＃41;
-------------------------------------------
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&＃91;1074&＃93; flag: ------, lock: 2, len=19
col 0; len 5; &＃40;5&＃41;:  c4 02 62 1f 64
col 1; len 1; &＃40;1&＃41;:  80
col 2; len 1; &＃40;1&＃41;:  80
col 3; len 6; &＃40;6&＃41;:  00 40 00 f1 00 1b   --rowid 相同 
查看下col 1的值
 
 
SQL> SELECT f_get_from_dump&＃40;REPLACE&＃40;'c4 02 62 1f 64',' ',','&＃41;,'NUMBER'&＃41; FROM dual;
 
F_GET_FROM_DUMP&＃40;REPLACE&＃40;'C402621F64','',','&＃41;,'NUMBER'&＃41;
-------------------------------------------------------------
1973099
 
 
这里就发现了 上面的1973099和1973089两个值的来源了，是_NEXT_OBJECT对象的dataobj#的值在索引和表上面的数据不一致现象
 
使用bbed修改成一直看下
 
BBED> x /r
rowdata&＃91;0&＃93;                                  @1086
----------
flag@1086: 0x2c &＃40;KDRHFL, KDRHFF, KDRHFH&＃41;
LOCK@1087: 0x00
cols@1088:   18
 
col    0&＃91;2&＃93; @1089:  0xc1  0x02
col    1&＃91;5&＃93; @1092:  0xc4  0x02  0x62  0x1f  0x5a  --1973089
col    2&＃91;1&＃93; @1098:  0x80
col   3&＃91;12&＃93; @1100:  0x5f  0x4e  0x45  0x58  0x54  0x5f  0x4f  0x42  0x4a  0x45
 0x43  0x54
col    4&＃91;2&＃93; @1113:  0xc1  0x02
col    5&＃91;0&＃93; @1116: *NULL*
col    6&＃91;1&＃93; @1117:  0x80
col    7&＃91;7&＃93; @1119:  0x78  0x6e  0x03  0x1e  0x0b  0x08  0x31
col    8&＃91;7&＃93; @1127:  0x78  0x72  0x0a  0x02  0x12  0x31  0x01
col    9&＃91;7&＃93; @1135:  0x78  0x6e  0x03  0x1e  0x0b  0x08  0x31
col   10&＃91;1&＃93; @1143:  0x80
col   11&＃91;0&＃93; @1145: *NULL*
col   12&＃91;0&＃93; @1146: *NULL*
col   13&＃91;1&＃93; @1147:  0x80
col   14&＃91;0&＃93; @1149: *NULL*
col   15&＃91;1&＃93; @1150:  0x80
col   16&＃91;4&＃93; @1152:  0xc3  0x07  0x38  0x24
col   17&＃91;1&＃93; @1157:  0x80
 
 
BBED> SET offset +11
        OFFSET          1097
 
 
BBED> m /x 64
 File: /oradata/orcl/system01.dbf &＃40;1&＃41;
 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&＃91;27&＃93;
rowdata&＃91;0&＃93;
----------
ub1 rowdata&＃91;0&＃93;                              @1086     0x2c
 
BBED> x /rnnnc
rowdata&＃91;0&＃93;                                  @1086
----------
flag@1086: 0x2c &＃40;KDRHFL, KDRHFF, KDRHFH&＃41;
LOCK@1087: 0x00
cols@1088:   18
 
col    0&＃91;2&＃93; @1089: 1
col    1&＃91;5&＃93; @1092: 1973099
col    2&＃91;1&＃93; @1098: 0
col   3&＃91;12&＃93; @1100: _NEXT_OBJECT
col    4&＃91;2&＃93; @1113: ..
col    5&＃91;0&＃93; @1116: *NULL*
col    6&＃91;1&＃93; @1117: .
col    7&＃91;7&＃93; @1119: xn....1
col    8&＃91;7&＃93; @1127: xr...1.
col    9&＃91;7&＃93; @1135: xn....1
col   10&＃91;1&＃93; @1143: .
col   11&＃91;0&＃93; @1145: *NULL*
col   12&＃91;0&＃93; @1146: *NULL*
col   13&＃91;1&＃93; @1147: .
col   14&＃91;0&＃93; @1149: *NULL*
col   15&＃91;1&＃93; @1150: .
col   16&＃91;4&＃93; @1152: ..8$
col   17&＃91;1&＃93; @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 &＃40;DATA&＃41; : 1
Total Blocks Failing   &＃40;DATA&＃41; : 0
Total Blocks Processed &＃40;INDEX&＃41;: 0
Total Blocks Failing   &＃40;INDEX&＃41;: 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$ &＃40;obj#,owner#,name,namespace,TYPE#,ctime,mtime,stime,STATUS&＃41; VALUES &＃40;1973104,0,'A1',1,2,sysdate,sysdate,sysdate,0&＃41;;
 
1 ROW created.
 
SQL> commit;
 
Commit complete.
 
INSERT成功 
 
创建TABLE 失败
 
SQL> CREATE TABLE a1 &＃40;id NUMBER&＃41;;
CREATE TABLE a1 &＃40;id NUMBER&＃41;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: &＃91;kdsgrp1&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;,
&＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;
 
 
SQL> ALTER SESSION SET events '10046 trace name context forever, level 12';
 
SESSION altered.
 
SQL> CREATE TABLE a1 &＃40;id NUMBER&＃41;;
CREATE TABLE a1 &＃40;id NUMBER&＃41;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: &＃91;kdsgrp1&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;,
&＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;
 
 
SQL> 
SQL> col trace_file_name FOR a100
SQL> SELECT d.VALUE || '/' || LOWER&＃40;RTRIM&＃40;i.INSTANCE, CHR&＃40;0&＃41;&＃41;&＃41; || '_ora_' ||
  2         p.spid || '.trc' trace_file_name
  3    FROM &＃40;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&＃41; p,
  8         &＃40;SELECT t.INSTANCE
  9            FROM v$thread t, v$parameter v
 10           WHERE v.NAME = 'thread'
 11             AND &＃40;v.VALUE = 0 OR t.thread# = TO_NUMBER&＃40;v.VALUE&＃41;&＃41;&＃41; i,
 12         &＃40;SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest'&＃41; 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: &＃91;kdsgrp1&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;,
&＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;
 
 
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: &＃91;kdsgrp1&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;,
&＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;
 
 
 
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&＃40;obj#&＃41; FROM obj$ t;
 
 MAX&＃40;OBJ#&＃41;
----------
   1973065
 
dump 这个索引 查找这个键值,知道这个值存在索引的最后一个块
 
大家都知道索引结构分为根、分枝、叶子块，知道这个块有问题想法不让oracle访问就行，可以尝试一下方法
1、修改索引的统计信息，让oracle评估是走全表扫
2、profile固话执行计划
3、提高分枝块的数据
 
我这里用了提供分枝快的数据方法，批量往obj$插入数据
 
DECLARE
   l_number NUMBER;
   test     varchar2&＃40;30&＃41;;
 BEGIN
   FOR i IN 1974002 ..  1974100 loop
     test     := 'travel' || i;
 
      INSERT INTO obj$&＃40; OBJ# ,
                        DATAOBJ# ,
                        OWNER# , 
                        NAME ,
                        NAMESPACE ,
                        SUBNAME ,
                        TYPE# , 
                        CTIME ,
                        MTIME ,
                        STIME ,
                        STATUS ,
                        REMOTEOWNER ,
                        LINKNAME ,
                        FLAGS ,
                        OID$ , 
                        SPARE1 ,
                        SPARE2 ,
                        SPARE3 ,
                        SPARE4 ,
                        SPARE5 ,
                        SPARE6&＃41;
            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: &＃91;kkdlcob-objn-EXISTS&＃93;, &＃91;1974099&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;, &＃91;&＃93;,
 
SQL> UPDATE obj$ SET dataobj#=1974001 WHERE name='_NEXT_OBJECT';
 
1 ROW updated.
 
SQL> commit;
 
Commit complete.
 
SQL> CREATE TABLE t1 &＃40;id NUMBER&＃41;;
 
TABLE created.
 
可以正常创建表了
 
下面就是在upgrade模式中提供obj$表和索引，参考 惜分飞的bootstrap$核心INDEX&＃40;I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等&＃41;异常恢复—ORA-00701错误解决
 
重建玩这个EXP数据正常，但是这个库还存在con$、cdef$ 等基表数据不一致现象。后面就不折腾了

原文地址：一次obj$、obj$索引不一致的恢复, 感谢原作者分享。