时间：2021-07-01 10:21:17 帮助过：17人阅读
朋友的一套数据库断电后出现异常，能够open，但是不能进行exp导出，发现是obj$表索引不一致。 大家都知道出现这些bootstrap$中的部分index是无法通过设置event38003进行重建,从而导致数据库无法正常使用,最常见异常index 有:I_ICOL1, I_TS1, I_CDEF1, I_CDEF
朋友的一套数据库断电后出现异常，能够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
+————————————————————————+
| 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$表和索引，参考 惜分飞的bootstrap$核心INDEX(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决
重建玩这个EXP数据正常，但是这个库还存在con$、cdef$ 等基表数据不一致现象。后面就不折腾了
原文地址：一次obj$、obj$索引不一致的恢复, 感谢原作者分享。