sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】oracle报错ORA-01502 index SYS.I_DEPENDENCY1解决办法分析

时间:2017-01-08 20:35   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 10.2 11.2 DEPENDENCY$ bbed event 10046oracle研究中心案例分析:数据库报错ORA-01502 index SYS.I_DEPENDENCY1,分析原因为 10g 中DEPENDENCY$被move 数据库无法open。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 10g 中DEPENDENCY$被move 数据库无法open

51放假期间,同事问我一个问题,那就是dependency$被move后,数据库起不来了,虽然是测试环境。 他的环境是10204版本.
自己测试了,跟9i不太一样了,参考了dbsnake的文章,似乎差别很大。如下:


[ora10g@killdb ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Apr 30 07:07:36 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select index_name from dba_indexes where table_name='DEPENDENCY$';

INDEX_NAME
------------------------------
I_DEPENDENCY1
I_DEPENDENCY2

SQL> select object_id,object_name,object_type from dba_objects where object_name like '%DEPENDENCY%';

OBJECT_ID OBJECT_NAME                              OBJECT_TYPE
---------- ---------------------------------------- -------------------
        92 DEPENDENCY$                              TABLE
       122 I_DEPENDENCY1                            INDEX
       123 I_DEPENDENCY2                            INDEX
      1068 V_$OBJECT_DEPENDENCY                     VIEW
      1069 V$OBJECT_DEPENDENCY                      SYNONYM
.......

SQL> alter table DEPENDENCY$ move;

Table altered.

SQL> select owner,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$';

OWNER         INDEX_NAME                     TABLESPACE_NAME                STATUS
------------- ------------------------------ ------------------------------ --------
SYS           I_DEPENDENCY1                  SYSTEM                         UNUSABLE
SYS           I_DEPENDENCY2                  SYSTEM                         UNUSABLE

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             146801896 bytes
Database Buffers           16777216 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

此时alert  log信息如下:

Tue Apr 30 07:14:09 PDT 2013
ARC0: Becoming the heartbeat ARCH
Tue Apr 30 07:14:09 PDT 2013
SMON: enabling cache recovery
Tue Apr 30 07:14:09 PDT 2013
Errors in file /home/ora10g/admin/roger/udump/roger_ora_16755.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
Tue Apr 30 07:14:09 PDT 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 16755
ORA-1092 signalled during: alter database open...

从错误我们可以知道数据库是在bootstrap的过程中错误了,而原因就是其中一个索引实效了. 我们通过10046来看下,具体
是什么操作导致的:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
—-trace log

我们直接搜索关键字 dependency$   找到如下内容:

PARSING IN CURSOR #5 len=186 dep=1 uid=0 oct=3 lid=0 tim=1335284694131023 hv=3311157971 ad='29ad6e3c'
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname, type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
END OF STMT
PARSE #5:c=5999,e=15839,p=4,cr=35,cu=0,mis=1,r=0,dep=1,og=4,tim=1335284694131016
=====================
PARSING IN CURSOR #1 len=84 dep=2 uid=0 oct=3 lid=0 tim=1335284694132211 hv=2686874206 ad='29ad624c'
select o.name, u.name from obj$ o, user$ u  where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #1:c=1000,e=476,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1335284694132206
BINDS #1:
kkscoacd
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b72cebd0  bln=22  avl=03  flg=05
  value=122
EXEC #1:c=1000,e=795,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1335284694133103
WAIT #1: nam='db file sequential read' ela= 92 file#=1 block#=98 blocks=1 obj#=11 tim=1335284694133318
WAIT #1: nam='db file sequential read' ela= 78 file#=1 block#=90 blocks=1 obj#=22 tim=1335284694133467
FETCH #1:c=0,e=400,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1335284694133536
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=2 pw=0 time=410 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=42 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=20 us)'
STAT #1 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=2 pw=0 time=339 us)'
STAT #1 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=1 pw=0 time=185 us)'
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
EXEC #3:c=291956,e=1910965,p=48,cr=721,cu=0,mis=0,r=0,dep=0,og=1,tim=1335284695126114
ERROR #3:err=1092 tim=455833383
怪异,我这里居然10046没有打出该sql的执行计划.无所谓,我在另外一个10g的数据库跑下sql即可:

[oracle@10gasm ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 30 07:37:42 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set lines 150
SQL> set pagesize 100
SQL> set autot traceonly exp
SQL> select owner#,
2         name,
3         namespace,
4         remoteowner,
5         linkname,
6         p_timestamp,
7         p_obj#,
8         nvl(property, 0),
9         subname,
10         type#,
11         d_attrs
12    from dependency$ d, obj$ o
13   where d_obj# = 92
14     and p_obj# = obj#(+)
15   order by order#;

Execution Plan
----------------------------------------------------------
Plan hash value: 1310495014

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     3 |   312 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY                |               |     3 |   312 |     7  (15)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |               |     3 |   312 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPENDENCY$   |     3 |    75 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | I_DEPENDENCY1 |     3 |       |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| OBJ$          |     1 |    79 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | I_OBJ1        |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("D_OBJ#"=92)
6 - access("P_OBJ#"="OBJ#"(+))
从上面我们可以知道,该sql需要访问其中的一个索引,而该为unusable,索引导致bootstrap$的时候就报错了,以至于无法open.

我在自己测试的时候,也参考了dbsnake的文章,他的操作就是通过bbed来讲索引I_DEPENDENCY1和I_DEPENDENCY2从ind$中
删掉,以至于该sql在执行的时候就不知道了,只能进行full table scan。 那么也就不会报错了。

根据他的思路,我也模拟了一下,但是后面发现不行,由于10.2版本都是一样的,所以我这里也通过另外一个10201环境来看下
index cluster 删除某条记录后,其实质变化如何:

—–创建测试范例

SQL> conn roger/roger
Connected.
SQL> create cluster test_cluster(object_id number);

Cluster created.

SQL> create table test_killdb1(
  2  object_id number primary key,
  3  object_name VARCHAR2(128))
  4  cluster test_cluster(object_id number);
cluster test_cluster(object_id number)
                               *
ERROR at line 4:
ORA-00907: missing right parenthesis

SQL> create table test_killdb1(
  2  object_id number primary key,
  3  object_name VARCHAR2(128))
  4  cluster test_cluster(object_id);

Table created.

SQL> create table test_killdb2(
  2  id number primary key,
  3  object_name VARCHAR2(128),
  4  owner VARCHAR2(30),
  5  object_id number references test_killdb1)
  6  cluster test_cluster(object_id);

Table created.

SQL> insert into test_killdb1 select object_id,object_name from
  2  dba_objects where object_id < 10;
insert into test_killdb1 select object_id,object_name from
            *
ERROR at line 1:
ORA-02032: clustered tables cannot be used before the cluster index is built

SQL>
SQL> create index test_cluster_idx_id ON CLUSTER test_cluster;

Index created.

SQL> insert into test_killdb1 select object_id,object_name from
  2  dba_objects where object_id < 10;

8 rows created.

SQL> commit;

Commit complete.

SQL> select * from test_killdb1;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
         3 I_OBJ#
         4 TAB$
         6 C_TS#
         5 CLU$
         2 C_OBJ#
         7 I_TS#
         8 C_FILE#_BLOCK#
         9 I_FILE#_BLOCK#

8 rows selected.

SQL>
SQL> select a.*,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
  2  from test_killdb1 a order by object_id;

OBJECT_ID OBJECT_NAME                         FILE#       BLK#
---------- ------------------------------ ---------- ----------
         2 C_OBJ#                                  7      12688
         3 I_OBJ#                                  7      12684
         4 TAB$                                    7      12685
         5 CLU$                                    7      12687
         6 C_TS#                                   7      12686
         7 I_TS#                                   7      12724
         8 C_FILE#_BLOCK#                          7      12725
         9 I_FILE#_BLOCK#                          7      12726

8 rows selected.
SQL> insert into test_killdb2 values(1,'roger','roger',9);

1 row created.
SQL>
SQL> insert into test_killdb2 values(2,'luoluo','luoluo',8);

1 row created.

SQL> insert into test_killdb2 values(3,'daodao','daodao',7);

1 row created.

SQL> insert into test_killdb2 values(4,'dave','dave',6);

1 row created.

SQL> insert into test_killdb2 values(5,'feixue','feixue',5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_killdb2;

        ID OBJECT_NAME                    OWNER                           OBJECT_ID
---------- ------------------------------ ------------------------------ ----------
         4 dave                           dave                                    6
         5 feixue                         feixue                                  5
         3 daodao                         daodao                                  7
         2 luoluo                         luoluo                                  8
         1 roger                          roger                                   9

SQL>
SQL> select a.*,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
  2  from test_killdb2 a order by id;

        ID OBJECT_NAME                    OWNER                           OBJECT_ID      FILE#       BLK#
---------- ------------------------------ ------------------------------ ---------- ---------- ----------
         1 roger                          roger                                   9          7      12726
         2 luoluo                         luoluo                                  8          7      12725
         3 daodao                         daodao                                  7          7      12724
         4 dave                           dave                                    6          7      12686
         5 feixue                         feixue                                  5          7      12687
从上面可以看到,我的测试环境test_killdb1 的数据分别存放在8个block中。

我们的目的是要来观察下针对该表发生delete操作的变化情况:

我这里以最后一条数据为例:

--session 1
SQL> select dump(9,16) from dual;

DUMP(9,16)
-----------------
Typ=2 Len=2: c1,a

SQL> select dump('I_FILE#_BLOCK#',16) from dual;

DUMP('I_FILE#_BLOCK#',16)
--------------------------------------------------------
Typ=96 Len=14: 49,5f,46,49,4c,45,23,5f,42,4c,4f,43,4b,23
SQL> select dump('roger',16) from dual;

DUMP('ROGER',16)
----------------------------
Typ=96 Len=5: 72,6f,67,65,72

SQL> show user
USER is "SYS"
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 7 block 12726;

System altered.

SQL> oradebug tracefile_name
/home/oracle/admin/test/udump/test_ora_32339.trc

---more test_ora_32339.trc

Block header dump:  0x01c031b6
Object id on Block  Y
seg/obj: 0xcb16  csc: 0x00.cdc63  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1c03189 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.024.00000149  0x0080003d.0151.13  C---    0  scn 0x0000.000cd88e
0x02   0x0002.02a.0000017d  0x00800016.01f9.1b  --U-    1  fsc 0x0000.000cdddf

data_block_dump,data header at 0xe743464
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x0e743464
bdba: 0x01c031b6
     76543210
flag=--------
ntab=3
nrow=3
frre=-1
fsbo=0x20
fseo=0x1f5c
avsp=0x1f3c
tosp=0x1f3c
0xe:pti[0]      nrow=1  offs=0
0x12:pti[1]     nrow=1  offs=1
0x16:pti[2]     nrow=1  offs=2
0x1a:pri[0]     offs=0x1f82
0x1c:pri[1]     offs=0x1f6f
0x1e:pri[2]     offs=0x1f5c
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 2 comc: 2 pk: 0x01c031b6.0 nk: 0x01c031b6.0       --curc,表示该block内非聚簇键的数据有1条,comc表示该block内非聚簇键的已经commit的数据有1条.
col  0: [ 2]  c1 0a                                     ---第1列的聚簇键值8
tab 1, row 0, @0x1f6f
tl: 19 fb: -CH-FL-- lb: 0x0  cc: 1 cki: 0
col  0: [14]  49 5f 46 49 4c 45 23 5f 42 4c 4f 43 4b 23 ---我们那条数据的第2列
tab 2, row 0, @0x1f5c
tl: 19 fb: -CH-FL-- lb: 0x2  cc: 3 cki: 0
col  0: [ 2]  c1 02
col  1: [ 5]  72 6f 67 65 72
col  2: [ 5]  72 6f 67 65 72
end_of_block_dump
由于我这里是asm环境,所以我bbed无法施展,所以先rman copy到本地来观察下:
RMAN> copy datafile 7 to '/home/oracle/file_7.dbf';

Starting backup at 02-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DATA1/test/datafile/roger.267.806226805
output filename=/home/oracle/file_7.dbf tag=TAG20130502T235319 recid=2 stamp=814406005
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 02-MAY-13

BBED> set file 7 block 12726
        FILE#           7
        BLOCK#          12726

BBED> p kdbr
sb2 kdbr[0]                                 @126      8066
sb2 kdbr[1]                                 @128      8047
sb2 kdbr[2]                                 @130      8028

BBED> p *kdbr[0]
rowdata[38]
-----------
ub1 rowdata[38]                             @8166     0xac
BBED> d /v
File: /home/oracle/file_7.dbf (7)
Block: 12726   Offsets: 8166 to 8191  Dba:0x01c031b6
-------------------------------------------------------
ac000102 00020001 c031b600 0001c031 l ........

                                                 b6000002 c10a0106 dfdd              l ....咻

<16 bytes per line>

BBED> x /rccccccccc
rowdata[38]                                 @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    2
mref@8171:    2
hrid@8173:0x01c031b6.0
nrid@8179:0x01c031b6.0

col    0[2] @8185:
BBED> p *kdbr[1]
rowdata[19]
-----------
ub1 rowdata[19]                             @8147     0x6c

BBED> d /v
File: /home/oracle/file_7.dbf (7)
Block: 12726   Offsets: 8147 to 8191  Dba:0x01c031b6
-------------------------------------------------------
6c000100 0e495f46 494c4523 5f424c4f l l....I_FILE#_BLO
434b23ac 00010200 020001c0 31b60000 l CK#.......
01c031b6 000002c1 0a0106df dd       l .....咻

<16 bytes per line>

BBED> x /rccccccccc
rowdata[19]                                 @8147
-----------
flag@8147: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8148: 0x00
cols@8149:    1

col   0[14] @8151: I_FILE#_BLOCK#
BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0]                              @8128     0x6c

BBED> d /v
File: /home/oracle/file_7.dbf (7)
Block: 12726   Offsets: 8128 to 8191  Dba:0x01c031b6
-------------------------------------------------------
6c020300 02c10205 726f6765 7205726f l l.....roger.ro
6765726c 0001000e 495f4649 4c45235f l gerl....I_FILE#_
424c4f43 4b23ac00 01020002 0001c031 l BLOCK#......

                                                     b6000001 c031b600 0002c10a 0106dfdd l ......咻

<16 bytes per line>

BBED> x /rccccccc
rowdata[0]                                  @8128
----------
flag@8128: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8129: 0x02
cols@8130:    3

col    0[2] @8132:

                    col    1[5] @8135: roger
col    2[5] @8141: roger

BBED>

注意,我们这里看到的row 有3行,第一行是聚簇键,第2行是非聚簇键. 大家可以发现一点,那就是聚簇键值的行头是0xac,
而非聚簇键的行头呢,是0x6c。我们知道我们普通的数据块行头都是0x2c,而这里是不同的.

我们将object_id=9的记录删除,然后再来观察下。该block的变化情况:

---session 1
SQL> delete from test_killdb2 where object_id=9;

1 row deleted.

SQL> commit;

Commit complete.

---session 2
SQL> oradebug setmypid
Statement processed.
SQL>  alter system dump datafile 7 block 12726;

System altered.

SQL> oradebug tracefile_name
/home/oracle/admin/test/udump/test_ora_1887.trc

Block header dump:  0x01c031b6
Object id on Block  Y
seg/obj: 0xcb16  csc: 0x00.cdec0  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1c03189 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.024.00000149  0x0080003d.0151.13  C---    0  scn 0x0000.000cd88e
0x02   0x0005.024.00000193  0x00800767.01c5.11  --U-    1  fsc 0x000f.000cdec2

data_block_dump,data header at 0xebd3464
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x0ebd3464
bdba: 0x01c031b6
     76543210
flag=--------
ntab=3
nrow=3
frre=-1
fsbo=0x20
fseo=0x1f49
avsp=0x1f3c
tosp=0x1f4f
0xe:pti[0]      nrow=1  offs=0
0x12:pti[1]     nrow=1  offs=1
0x16:pti[2]     nrow=1  offs=2
0x1a:pri[0]     offs=0x1f82
0x1c:pri[1]     offs=0x1f49
0x1e:pri[2]     offs=0x1f5c
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 2 comc: 1 pk: 0x01c031b6.0 nk: 0x01c031b6.0   ---大家注意观察这里的变化
col  0: [ 2]  c1 0a
tab 1, row 0, @0x1f49
tl: 19 fb: -CH-FL-- lb: 0x0  cc: 1 cki: 0
col  0: [14]  49 5f 46 49 4c 45 23 5f 42 4c 4f 43 4b 23
tab 2, row 0, @0x1f5c
tl: 4 fb: -CHDFL-- lb: 0x2  cc: 0 cki: 0      ---可以看到该记录已经被删除了。
end_of_block_dump
End dump data blocks tsn: 6 file#: 7 minblk 12726 maxblk 12726
我们可以清楚的看到,curc的值不变,而comc的值减少了1.也就是等于block内的已commit实际数量条数.

我们此时仍然使用bbed来观察下该block的一些变化:

BBED> set file 7 block 12726
        FILE#           7
        BLOCK#          12726

BBED> p kdbr
sb2 kdbr[0]                                 @126      8066
sb2 kdbr[1]                                 @128      8009
sb2 kdbr[2]                                 @130      8028

BBED> p *kdbr[0]
rowdata[57]
-----------
ub1 rowdata[57]                             @8166     0xac

BBED> d /v
File: /home/oracle/file_7.dbf (7)
Block: 12726   Offsets: 8166 to 8191  Dba:0x01c031b6
-------------------------------------------------------
ac000102 00010001 c031b600 0001c031 l ........

                                                 b6000002 c10a0106 c2de              l ....罗

<16 bytes per line>
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8109     0x6c

BBED> p *kdbr[2]
rowdata[19]
-----------
ub1 rowdata[19]                             @8128     0x7c  ---大家可以发现,我们第2条记录的行头发生了变化.

BBED> x /rccccccc
rowdata[19]                                 @8128
-----------
flag@8128: 0x7c (KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC)
lock@8129: 0x02
cols@8130:    0

BBED> d /v
File: /home/oracle/file_7.dbf (7)
Block: 12726   Offsets: 8128 to 8191  Dba:0x01c031b6
-------------------------------------------------------
7c020300 02c10205 726f6765 7205726f l |.....roger.ro
6765727c 0201000e 495f4649 4c45235f l ger|....I_FILE#_
424c4f43 4b23ac00 01020001 0Oracleoracleplus.net001c031 l BLOCK#......

                                                     b6000001 c031b600 0002c10a 0106c2de l ......罗

<16 bytes per line>
现在我们来简单总结下,关于index cluster的删除,其实就是将其所在的聚簇行头所记录的comc值减少了,同时
将该记录的行头有正常情况下的0x6c改成0x7c.
现在我们回到问题上来,我的目的是要从ind$里面将I_DEPENDENCY1 和I_DEPENDENCY2的记录删除掉,这样就可以让这2个sql
无法识别到这2个索引,也就不会报错了。

在10.2版本中,这2个index的object_id如下,且在ind$中对应的位置都是一样的:

SQL> select obj#,flags from ind$ where obj#=122;

      OBJ#      FLAGS
---------- ----------
       122       2050

SQL> select obj#,flags from ind$ where obj#=123;

      OBJ#      FLAGS
---------- ----------
       123       2050

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
  2  from ind$ where obj#=122;

     FILE#       BLK#
---------- ----------
         1         32

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
  2  from ind$ where obj#=123;

     FILE#       BLK#
---------- ----------
         1         32

SQL>
SQL> select dump(122,16) from dual;

DUMP(122,16)
--------------------
Typ=2 Len=3: c2,2,17

SQL> select dump(123,16) from dual;

DUMP(123,16)
--------------------
Typ=2 Len=3: c2,2,18
知道了10gR2中index cluster删除记录的实质后,我们就可以利用bbed来进行操作了,如下是我的操作过程:

BBED> set file 1 block 32
        FILE#           1
        BLOCK#          32

BBED> f /x 03c20218
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32               Offsets:  579 to 1090           Dba:0x00400020
------------------------------------------------------------------------
03c20218 03c20218 018002c1 0203c20a 26018002 c10302c1 0b02c103 03c20338
ff02c102 03c21533 018002c1 0303c206 1304c303 382c02c1 0202c102 04c30441
0d07786f 0c0c1701 2804c30b 505704c3 0b505702 c103ffff ff02c103 ffffffff
07786e04 0f0e0f2e 6c002103 03c20217 03c20217 018002c1 0203c20a 1e018002
c10402c1 0b02c103 03c20338 ff02c102 03c21533 02c10202 c10303c2 056404c3
0b505702 c10202c1 0203c21f 4b07786f 0c0c1701 2704c30b 505704c3 0b505702
c104ffff ff02c104 ffffffff 07786e04 0f0e0f2e 6c002403 02c15d01 8002c102
03c2075a ffff02c1 0aff02c1 0b02c129 02c10203 c2033803 c2061e26 2d2d2d2d
2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
2d2d04c3 0b505703 c2064301 80018001 8002c121 01800180 07786f0c 0c170125
04c30b50 57ffff02 c10a02c1 0a06c506 25580a0d 018002c2 04ffffff ff07786e
040f0e0f 2e6c0014 0802c104 02c10402 c2290180 08434f4d 4d454e54 2402c102
02c22901 80ffff01 80ffff02 c1040180 03c20935 02c10201 80018002 c2296c00
140702c1 0402c104 02c22901 8008434f 4d4d454e 542402c1 0202c229 0180ffff
0180ffff 02c10401 8003c209 3502c102 01800180 02c2296c 00140702 c10302c1
0302c229 01800656 414c5545 2402c102 02c22901 80ffff01 80ffff02 c1030180

<32 bytes per line>
BBED> d /v count 20 offset 575
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32      Offsets:  575 to  594  Dba:0x00400020
-------------------------------------------------------
6c002103 03c20218 03c20218 018002c1 l l.!........
0203c20a                            l ..

<16 bytes per line>

BBED> modify /x 7c offset 575
Warning: contents of previous BIFILE will be lost. Proceed  (Y/N) y
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32               Offsets:  575 to  594           Dba:0x00400020
------------------------------------------------------------------------
7c002103 03c20218 03c20218 018002c1 0203c20a

<32 bytes per line>
BBED> f /x 03c20217
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32               Offsets:  687 to  706           Dba:0x00400020
------------------------------------------------------------------------
03c20217 03c20217 018002c1 0203c20a 1e018002

<32 bytes per line>

BBED> d /v count 20 offset 683
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32      Offsets:  683 to  702  Dba:0x00400020
-------------------------------------------------------
6c002103 03c20217 03c20217 018002c1 l l.!........
0203c20a                            l ..

<16 bytes per line>

BBED> modify /x 7c offset 683
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32               Offsets:  683 to  702           Dba:0x00400020
------------------------------------------------------------------------
7c002103 03c20217 03c20217 018002c1 0203c20a

<32 bytes per line>

BBED> p *kdbr[0]
rowdata[5236]
-------------
ub1 rowdata[5236]                           @8166     0xac

BBED> x /rccccccccccccccccccccccccccccccccccc
rowdata[5236]                               @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    6
mref@8171:    6
hrid@8173:0x00400020.0
nrid@8179:0x00400020.0

col    0[2] @8185: 罿

BBED> d /v count 200
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32      Offsets: 8166 to 8191  Dba:0x00400020
-------------------------------------------------------
ac000106 00060000 40002000 00004000 l ......@. ...@.
20000002 c15a0106 f860              l  ...罿..鴃

<16 bytes per line>

BBED> modify /x 05 offset 8171
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 32               Offsets: 8171 to 8191           Dba:0x00400020
------------------------------------------------------------------------
05000040 00200000 00400020 000002c1 5a0106f8 60

<32 bytes per line>

BBED> sum apply
Check value for File 1, Block 32:
current = 0x1c8c, required = 0x1c8c
操作完成之后,尝试启动数据库:

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             146801896 bytes
Database Buffers           16777216 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> conn /as sysdba
Connected.
SQL> create pfile='/tmp/a1.ora' from spfile;

File created.

SQL> delete from obj$ where obj# =123;

1 row deleted.

SQL> delete from obj$ where obj# =122;

1 row deleted.

SQL> delete from icol$ where obj#=122;
delete from icol$ where obj#=122
            *
ERROR at line 1:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kddummy_blkchk], [1], [32], [6121], [], [], [], []

SQL> commit;

Commit complete.

SQL> create unique index i_dependency1 on
  2    dependency$(d_obj#, d_timestamp, order#)
  3    storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4  /
  dependency$(d_obj#, d_timestamp, order#)
  *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index '.' or partition of such index is in unusable state
此时数据库不停的报错:
Fri May 03 01:26:58 PDT 2013
Errors in file /home/ora10g/admin/roger/bdump/roger_smon_25401.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘.’ or partition of such index is in unusable state
Fri May 03 01:27:08 PDT 2013

按理说这里应该是可以delete 进行相关对象的删除完毕,然后就可以重建这2个索引了,但是发现情况不是这样,或许是我这里
操作有些问题。当时报错后,后来我重启了一下,就起不来了,比较遗憾:

Fri May 03 03:07:30 PDT 2013
SMON: enabling cache recovery
Fri May 03 03:07:34 PDT 2013
Errors in file /home/ora10g/admin/roger/udump/roger_ora_28635.trc:
ORA-00600: internal error code, arguments: [kkdlusr1], [123], [], [], [], [], [], []
Fri May 03 03:07:36 PDT 2013
Errors in file /home/ora10g/admin/roger/udump/roger_ora_28635.trc:
ORA-00600: internal error code, arguments: [kkdlusr1], [123], [], [], [], [], [], []
Fri May 03 03:07:36 PDT 2013
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 28635
ORA-1092 signalled during: alter database open..

经过多次方法尝试,都无法绕过这个[kkdlusr1] 错误,极度郁闷。

无奈之下,我建该库直接rm -rf删掉,利用上次的rman 备份重新恢复了一个,然后再次模拟,通过如下方式顺利解决了这个问题:

SQL> alter table DEPENDENCY$ move;

Table altered.

SQL> show user
USER is "SYS"
SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
SQL> conn /as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
使用隐含参数_db_always_check_system_ts 直接打开数据库.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             146801896 bytes
Database Buffers           16777216 bytes
Redo Buffers                2920448 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

SQL> conn /as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> show parameter system

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_db_always_check_system_ts           boolean     FALSE
filesystemio_options                 string      none
SQL> delete from obj$ where obj# =122;

1 row deleted.

SQL>  delete from obj$ where obj# =123;

1 row deleted.

SQL>  delete from icol$ where obj#=122;

3 rows deleted.

SQL>  delete from icol$ where obj#=123;

2 rows deleted.

SQL> delete from seg$ where ts#=0 and file#=1 and block#=929;

1 row deleted.

SQL> delete from seg$ where ts#=0 and file#=1 and block#=937;

1 row deleted.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from ind$ where obj#=122;

  COUNT(*)
----------
         1

SQL> select count(*) from ind$ where obj#=123;

  COUNT(*)
----------
         1

SQL> delete from ind$ where obj#=122;

1 row deleted.

SQL> delete from ind$ where obj#=123;

1 row deleted.

SQL> commit;

Commit complete.

SQL> create unique index i_dependency1 on
  2    dependency$(d_obj#, d_timestamp, order#)
  3    storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4  /
  dependency$(d_obj#, d_timestamp, order#)
  *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index '.' or partition of such index is in unusable state

SQL> create index i_dependency2 on
  2    dependency$(p_obj#, p_timestamp)
  3    storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4  /
  dependency$(p_obj#, p_timestamp)
  *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index '.' or partition of such index is in unusable state

SQL> alter system switch logfile;

System altered.

SQL> create index i_dependency2 on
  2    dependency$(p_obj#, p_timestamp)
  3    storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4  /
  dependency$(p_obj#, p_timestamp)
  *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index '.' or partition of such index is in unusable state

SQL> shutdown immediate
ORA-00604: error occurred at recursive SQL level 2
ORA-01502: index '.' or partition of such index is in unusable state
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             146801896 bytes
Database Buffers           16777216 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL> create index i_dependency2 on
  2    dependency$(p_obj#, p_timestamp)
  3    storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4  /

Index created.

SQL> create unique index i_dependency1 on
  2    dependency$(d_obj#, d_timestamp, order#)
  3    storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4  /

Index created.

SQL>
这里有几个地方需要注意:

1. drop index的实质就是操作几个数据字典表:obj$,icol$,seg$,ind$. 这一点,大家可以通过10046 event
去跟踪drop index的过程可以发现。

2. 上面关于删除seg$的记录,需要知道对象的段头,经过我检查发现,10gR2版本中段头block号都是一样的,所以
根本不需要通过其他工具去获取。

3. 数据字典维护操作完成之后,进行索引重建,仍然报错,需要将数据库重启一下,否则是不行的。

4. 重建这2个索引的脚本可以在$ORACLE_HOME/rdbms/admin/sql.bsq中找到.



补充:在11gR2版本中,这个发生变化了

[ora11g@11gR2test ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.2.0 Production on Fri May 3 08:02:21 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> set lines 200
SQL> select owner,object_name,object_id from dba_objects where object_name=’DEPENDENCY$’;

OWNER OBJECT_NAME OBJECT_ID
—————————— —————————— ———-
SYS DEPENDENCY$ 104

SQL> select owner,index_name,index_type from dba_indexes
2 where table_name=’DEPENDENCY$’;

OWNER INDEX_NAME INDEX_TYPE
—————————— —————————— —————————
SYS I_DEPENDENCY2 NORMAL
SYS I_DEPENDENCY1 NORMAL

SQL> select owner,object_name,object_id from dba_objects where object_name like ‘%I_DEPENDENCY%’;

OWNER OBJECT_NAME OBJECT_ID
—————————— —————————— ———-
SYS I_DEPENDENCY1 106
SYS I_DEPENDENCY2 107
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> alter database open;

Database altered.

SQL> oradebug close_trace
Statement processed.
SQL> oradebug tracefile_name
/home/ora11g/diag/rdbms/roger/roger/trace/roger_ora_25330.trc
SQL>
SQL>

通过分析trace文件,我发现在open的过程中,根本不涉及DEPENDENCY$相关的对象了,而且即使是索引失效了,smon也不会去检测了,如下:

SQL> alter table DEPENDENCY$ move;

Table altered.

SQL> col index_name for a30
SQL> col tablespace_name for a25
SQL> select owner,index_name,tablespace_name,status from dba_indexes where table_name=’DEPENDENCY$’;

OWNER INDEX_NAME TABLESPACE_NAME STATUS
—————————— —————————— ————————- ——–
SYS I_DEPENDENCY2 SYSTEM UNUSABLE
SYS I_DEPENDENCY1 SYSTEM UNUSABLE

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1343888 bytes
Variable Size 192941680 bytes
Database Buffers 113246208 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL>
SQL> select owner,index_name,tablespace_name,status from dba_indexes where table_name=’DEPENDENCY$’;

OWNER INDEX_NAME TABLESPACE_NAME STATUS
—————————— —————————— ————————- ——–
SYS I_DEPENDENCY2 SYSTEM UNUSABLE
SYS I_DEPENDENCY1 SYSTEM UNUSABLE

SQL> alter index I_DEPENDENCY2 rebuild;

Index altered.

SQL> alter index I_DEPENDENCY1 rebuild;

Index altered.

换句话说,11gR2版本中这个表你可以随便move,哈哈~~~~ 不过这个测试实际上没有什么价值,大家就当随便玩玩了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】oracle报错ORA-01502 index SYS.I_DEPENDENCY1解决办法分析

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1448.html

Oracle研究中心

关键词:

oracle报错ORA-01502

SYS.I_DEPENDENCY1解决办法分析