[20160325]ORA-08102 index key not found
[20160325]ORA-08102 index key not found.txt
-- 一直沒機會測試如果表與索引不一致會出現什麼情況,今天測試看看。做複雜一點,選擇1個系統表con$。
$ oerr ora 8102
08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"
// *Cause: Internal error: possible inconsistency in index
// *Action: Send trace file to your customer support representative, along
// with information on reproducing the error
1.測試環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> SELECT ROWID, owner#,name,con# FROM sys.con$ where name='_NEXT_CONSTRAINT';
ROWID OWNER# NAME CON#
------------------ ---------- -------------------- ----------
AAAAAcAABAAAAEhAAM 0 _NEXT_CONSTRAINT 11940
SCOTT@book> @ &r/rowid AAAAAcAABAAAAEhAAM
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
28 1 289 12 1,289 alter system dump datafile 1 block 289 ;
SCOTT@book> select owner,index_name,table_owner,table_name from dba_indexes where table_name='CON$';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
------ ------------------------------ ------------------------------ ----------
SYS I_CON1 SYS CON$
SYS I_CON2 SYS CON$
SCOTT@book> select obj#,dataobj#,owner#,name,type# from sys.obj$ where name in ('CON$','I_CON1','I_CON2');
OBJ# DATAOBJ# OWNER# NAME TYPE#
---------- ---------- ---------- -------------------- ----------
28 28 0 CON$ 2
51 51 0 I_CON1 1
52 52 0 I_CON2 1
--其中索引I_CON2,包含欄位con#。如果破壞這個NAME='_NEXT_CONSTRAINT',讓CON#=11941,這樣索引與表存在不一致,這樣
--如果建立約束時,會報錯。另外注意這些物件obj#<59.不能透過重建來修復。
2.使用bbed修改:
SCOTT@book> select dump(11940,16),dump(11941,16) from dual ;
DUMP(11940,16) DUMP(11941,16)
----------------------- -----------------------
Typ=2 Len=4: c3,2,14,29 Typ=2 Len=4: c3,2,14,2a
BBED> set dba 1,289
DBA 0x00400121 (4194593 1,289)
BBED> p * kdbr[12]
rowdata[0]
----------
ub1 rowdata[0] @806 0x2c
BBED> x /rncnnnncct
rowdata[0] @806
----------
flag@806: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@807: 0x02
cols@808: 4
col 0[1] @809: 0
col 1[16] @811: _NEXT_CONSTRAINT
col 2[4] @828: 11940
col 3[1] @833: 0
SCOTT@book> @ &r/bbvi 1 289
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 2367488 -s 8192 /mnt/ramdisk/book/system01.dbf
--尋找c3,2,14,29,換成c3,2,14,2a。
BBED> set dba 1,289
DBA 0x00400121 (4194593 1,289)
BBED> p * kdbr[12]
rowdata[0]
----------
ub1 rowdata[0] @806 0x2c
BBED> x /rncnnnncct
rowdata[0] @806
----------
flag@806: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@807: 0x02
cols@808: 4
col 0[1] @809: 0
col 1[16] @811: _NEXT_CONSTRAINT
col 2[4] @828: 11941
col 3[1] @833: 0
BBED> sum
Check value for File 1, Block 289:
current = 0x5cbb, required = 0x5cb8
BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 1, Block 289:
current = 0x5cb8, required = 0x5cb8
3.測試:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter table emp modify(ename not null);
alter table emp modify(ename not null)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 52, file 1, block 77798 (2)
--已經提示錯誤。obj#=52 就是索引I_CON2。
4.修復:
--因為是系統物件不能重建。
SYS@book> alter index sys.i_con2 rebuild ;
alter index sys.i_con2 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SYS@book> alter index sys.i_con2 unusable;
alter index sys.i_con2 unusable
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
--不知道是否在upgrade模式可以rebuild,以後測試。
--先確定是那條記錄:
select /*+ full(a) */ con# from sys.con$ a
minus
select /*+ index(b i_con2) */ con# from sys.con$ b
CON#
----------
11941
select /*+ index(b i_con2) */ con# from sys.con$ b
minus
select /*+ full(a) */ con# from sys.con$ a
CON#
----------
11940
--從以上查詢可以確定表裡面記錄的CON#=11941,索引記錄的是11940.我選擇修改索引看看。
5.bbed修改一致:
SCOTT@book> @ &r/bbvi 1 77798
BVI_COMMAND
-------------------------------------------------------
bvi -b 637321216 -s 8192 /mnt/ramdisk/book/system01.dbf
檢索c3 02 14 29 ,修改為c3 02 14 2a。
BBED> sum
Check value for File 1, Block 77798:
current = 0x39fe, required = 0x39fd
BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 1, Block 77798:
current = 0x39fd, required = 0x39fd
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter table emp modify(ename not null);
Table altered.
SCOTT@book> alter table emp modify(ename null);
Table altered.
--OK現在現在修復了。
SCOTT@book> SELECT ROWID, owner#,name,con# FROM sys.con$ where name='_NEXT_CONSTRAINT';
ROWID OWNER# NAME CON#
------------------ ---------- -------------------- ----------
AAAAAcAABAAAAEhAAM 0 _NEXT_CONSTRAINT 11942
--已經向前推進+1.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2063785/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql中key 、primary key 、unique key 與index區別MySqlIndex
- mysql RSA private key file not foundMySql
- vue v-for中key的作用,使用index作為key會怎麼樣?VueIndex
- This relative module was not found: * ./_import_dev in ./src/router/index.js報錯ImportdevIndexJS
- [20190918]shrink space與ORA-08102錯誤.txt
- Vue 中為何不可以使用Index 作為Dom的key?VueIndex
- percona-toolkit之pt-index-usage和pt-duplicate-key-checker詳解Index
- MySQL 5.6複製報錯Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;MySqlError
- 【YashanDB資料庫】自關聯外來鍵插入資料時報錯:YAS-02033 foreign key constraint violated parent key not found資料庫AI
- 為什麼 Vue 中不要用 index 作為 key?(diff 演算法詳解)VueIndex演算法
- mysql specified key was too long與Index column size too large. The maximum column size is 767 bytes.MySqlIndex
- -bash: id: command not found -bash: tty: command not found
- Redis熱點key大keyRedis
- kubeadm not found
- Index of /virtualboxIndex
- PostgreSQL:INDEXSQLIndex
- oracle invisible index與unusable index的區別OracleIndex
- Unable to negotiate with xx.xxx.xxxx port 22: no matching host key type found. Their offer: ssh-rsa(解決的兩種方式)Go
- nodemon: command not found
- pip: command not found
- found an invalid color
- 大Key
- 熱Key
- Python, pandas: how to sort dataframe by index// Merge two dataframes by indexPythonIndex
- Specified key was too long; max key length is 1000 bytes
- index.jspIndexJS
- null與indexNullIndex
- create index .. onlineIndex
- 小程式報錯Invoke event bindViewTap in page: pages/index/indexViewIndex
- Artificial Intelligence Index:2018年AI Index報告出爐IntelIndexAI
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex
- 藍橋杯搭建環境解決Kile 的Error: A9932E: MISSING DEVICE (R003: SECURITY KEY NOT FOUND) Target not created問題Errordev
- zsh:command not found: xxx
- mac mysql: command not foundMacMySql
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- sessionStorage.key()Session
- python sorted keyPython
- Android:Unexpected lock protocol found in lock file. Expected 3, found 0.AndroidProtocol
- 如何處理redis叢集中hot key和big keyRedis