10g 中DEPENDENCY$被move 数据库无法open
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 10g 中DEPENDENCY$被move 数据库无法open
51放假期间，同事问我一个问题，那就是dependency$被move后，数据库起不来了，虽然是测试环境。 他的环境是10204版本.
自己测试了，跟9i不太一样了，参考了dbsnake的文章,似乎差别很大。如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
我们直接搜索关键字 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即可:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
[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 删除某条记录后，其实质变化如何：
—–创建测试范例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
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操作的变化情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
我这里以最后一条数据为例： --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 |
|
1 |
由于我这里是asm环境，所以我bbed无法施展，所以先rman copy到本地来观察下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
<pre class="brush:php">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的变化情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
---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的一些变化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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 0001c031 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$中对应的位置都是一样的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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来进行操作了，如下是我的操作过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
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 |
操作完成之后，尝试启动数据库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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 备份重新恢复了一个，然后再次模拟，通过如下方式顺利解决了这个问题：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 直接打开数据库.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 |
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，哈哈~~~~ 不过这个测试实际上没有什么价值，大家就当随便玩玩了。
5 Responses to “10g 中DEPENDENCY$被move 数据库无法open”
[…] 详见原文博客链接地址：10g 中DEPENDENCY$被move 数据库无法open var jiathis_config={ data_track_clickback:true, summary:"", hideMore:false } 0 本文链接: 10g 中DEPENDENCY$被move 数据库无法open 版权所有: 非特殊声明均为本站原创文章，转载请注明出处：iew3c 订阅更新: 您可以通过RSS订阅我们的内容更新 […]
先看再顶，大神
bbed 可以修改成功，你的应该是还有很多使用bbed 删除ind$的记录时候，还有一些块的验证部分没有修改完全，具体可以看看我的blog，详细的东西，就不说了，你懂的
发现一种不需要bbed的方法，很easy。
[…] 51放假期间，同事问我一个问题，那就是dependency$被move后，数据库起不来了，虽然是测试环境。 他的环境是10204版本. 自己测试了，跟9i不太一样了，参考了dbsnake的文章,似乎差别很大。如下： ? […]
Leave a Reply
You must be logged in to post a comment.