ORA-01561 & ora-00600 [ktadrprc-1]
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
|
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 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 |
SQL> drop tablespace roger including contents and datafiles; drop tablespace roger including contents and datafiles * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified SQL> select owner,segment_name,segment_type 2 from dba_segments 3 where tablespace_name='ROGER'; OWNER SEGMENT_NAME SEGMENT_TYPE ------------ ------------------ ------------------ ROGER 5.155 TEMPORARY ROGER 5.163 TEMPORARY ROGER 5.171 TEMPORARY ROGER 5.179 TEMPORARY ROGER 5.187 TEMPORARY SQL> select owner,segment_name,segment_type,HEADER_FILE,HEADER_BLOCK,BYTES/1024/1024 2 from dba_segments 3 where owner='ROGER'; OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES/1024/1024 ------- --------------- -------------- ----------- ------------ --------------- ROGER 5.155 TEMPORARY 5 155 2 ROGER 5.163 TEMPORARY 5 163 2 ROGER 5.171 TEMPORARY 5 171 2 ROGER 5.179 TEMPORARY 5 179 .75 ROGER 5.187 TEMPORARY 5 187 .75 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> select ts# from ts$ where name='ROGER'; TS# ---------- 6 SQL> update seg$ set type# = 3 where ts#=6; 7 rows updated. SQL> commit; Commit complete. SQL> select file#,block#,TYPE# from seg$ where ts#=6; FILE# BLOCK# TYPE# ---------- ---------- ---------- 5 155 3 5 163 3 5 171 3 5 179 3 5 187 3 5 1371 3 5 1379 3 7 rows selected. SQL> drop tablespace roger including contents and datafiles; Tablespace dropped. SQL> select ts#,name,BLOCKSIZE,FLAGS from ts$ order by 1; TS# NAME BLOCKSIZE FLAGS ---------- ------------------------------ ---------- ---------- 0 SYSTEM 8192 1 1 UNDOTBS1 8192 17 2 SYSAUX 8192 33 3 TEMP 8192 2 4 USERS 8192 33 5 UNDOTBS2 8192 17 6 ROGER 8192 33 7 rows selected. SQL> select file#,block#,TYPE# from seg$ where ts#=6; no rows selected SQL> delete from ts$ where ts#=6; 1 row deleted. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ---------------------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> delete from ts$ where ts#=5; 1 row deleted. SQL> commit; Commit complete. SQL> select ts#,name,BLOCKSIZE,FLAGS from ts$ order by 1; TS# NAME BLOCKSIZE FLAGS ---------- ------------------------------ ---------- ---------- 0 SYSTEM 8192 1 1 UNDOTBS1 8192 17 2 SYSAUX 8192 33 3 TEMP 8192 2 4 USERS 8192 33 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 230686720 bytes Fixed Size 1266776 bytes Variable Size 150997928 bytes Database Buffers 75497472 bytes Redo Buffers 2924544 bytes Database mounted. Database opened. SQL> select file#,STATUS,CHECKPOINT_CHANGE#,ONLINE_CHANGE# from v$datafile; FILE# STATUS CHECKPOINT_CHANGE# ONLINE_CHANGE# ---------- ------- ------------------ -------------- 1 SYSTEM 708092 464631 2 ONLINE 708092 464631 3 ONLINE 708092 464631 4 ONLINE 708092 464631 SQL> select username,account_status,default_tablespace 2 from dba_users 3 where username='ROGER'; no rows selected SQL> select username from dba_users where username like '%ROGER%'; no rows selected ###### 我们发现roger表空间删除一行，roger用户也跟着被删除了。###### 关于drop表后，会变成临时表，这个应该是11gR2的新功能，在去年的一篇文章中，我写过了。 大家可以参考这里， http://hi.baidu.com/xu521huan/blog/item/29cd893db302f7fe3d6d97ba.html 但是我这里的环境是10204，居然会有这个情况，让我有点费解，，这个问题回头还需要研究一下。 最后再用oracle提供的检查数据字典的脚本检查一下，看看是否有数据字典不一致的情况 SQL> show user USER is "SYS" SQL> execute hcheck.full PL/SQL procedure successfully completed. ###### 看来是ok了 ###### SQL> create tablespace roger datafile '/oracle/product/oradata/roger/rogre01.dbf' size 200m autoextend off; Tablespace created. SQL> create user roger identified by roger default tablespace roger; create user roger identified by roger default tablespace roger * ERROR at line 1: ORA-01920: user name 'ROGER' conflicts with another user or role name SQL> drop user ROGER cascade; drop user ROGER cascade * ERROR at line 1: ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], [] |
看了下alert log，对于的600错误trace内容如下：
|
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 |
*** ACTION NAME:() 2011-07-03 13:48:26.295 *** MODULE NAME:(sqlplus@roger (TNS V1-V3)) 2011-07-03 13:48:26.295 *** SERVICE NAME:(SYS$USERS) 2011-07-03 13:48:26.295 *** SESSION ID:(159.3) 2011-07-03 13:48:26.295 kwqmnich: current time:: 5: 48: 26 kwqmnich: instance no 0 check_only flag 1 kwqmnich: initialized job cache structure *** 2011-07-03 13:51:25.349 +++++++++++++++++++++++++++++++++++++++++++++++++ HCheck Version 8i-11/2.00 Problem: OBJ$ INDEX entry has no IND$ entry INDEX has no IND$ entry: Obj=51829 ROGER.IDX_A Problem: OBJ$ TABLE entry has no TAB$ entry TABLE has no TAB$ entry: Obj=51828 ROGER.TEST1 +++++++++++++++++++++++++++++++++++++++++++++++++ Found 2 potential problems and 0 warnings Contact Oracle Support with the output to check if the above needs attention or not *** 2011-07-03 13:54:52.872 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], [] Current SQL statement for this session: drop table "ROGER"."TEST1" cascade constraints purge force ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst()+27 call ksedst1() 0 ? 1 ? ksedmp()+557 call ksedst() 0 ? CE67960 ? 1F ? B712C6FC ? BFFF5B7C ? 8D02CED ? ksfdmp()+19 call ksedmp() 3 ? BFFF5CE0 ? ADC338D ? CE67960 ? 3 ? CE15B3C ? kgerinv()+177 call 00000000 CE67960 ? 3 ? kgeasnmierr()+40 call kgerinv() CE67960 ? B7120020 ? C38DEE0 ? 0 ? BFFF5D18 ? ktadrprc()+235 call kgeasnmierr() CE67960 ? B7120020 ? C38DEE0 ? 0 ? ktssdrp_segment()+1 call ktadrprc() BFFF7004 ? 0 ? 583 dtbdrp()+1462 call ktssdrp_segment() BFFF7004 ? 0 ? 0 ? 0 ? CE6871C ? 0 ? dtbdrv()+2235 call dtbdrp() B70EFE44 ? 2A2A05B8 ? 2A2A01D8 ? BFFF72D0 ? 0 ? BFFF72EC ? 2A4455A0 ? 3 ? opiexe()+11173 call dtbdrv() B70F0C84 ? 0 ? C ? ADA884D ? B70F0C84 ? 0 ? opiosq0()+2701 call opiexe() 4 ? 0 ? BFFF7FA0 ? opiosq()+19 call opiosq0() 3 ? F ? BFFF8DF4 ? 0 ? opiodr()+976 call 00000000 4A ? F ? BFFF8DF4 ? __PGOSF112_rpidrus( call opiodr() 4A ? F ? BFFF8DF4 ? 1F ? )+159 skgmstack()+139 call 00000000 BFFF8850 ? BFFF8534 ? CE67A5C ? BFFF8D64 ? BFFF8834 ? F618 ? rpidru()+98 call skgmstack() BFFF8834 ? CE67720 ? F618 ? 99B163E ? BFFF8850 ? rpiswu2()+334 call 00000000 BFFF8D64 ? CE67A5C ? BFFF8DE0 ? BFFF88B0 ? F618 ? BFFF88CC ? rpidrv()+1199 call rpiswu2() 2DB0AAC8 ? 0 ? BFFF8D44 ? 2 ? BFFF8D90 ? 0 ? BFFF8D44 ? 0 ? 99B195A ? 99B1A48 ? BFFF8D64 ? 8 ? rpisplu()+286 call rpidrv() 1F ? 4A ? BFFF8DF4 ? 8 ? 1 ? 1F ? rpispl()+28 call rpisplu() 1F ? 0 ? 0 ? BFFFA858 ? 3A ? 0 ? 0 ? kzdukl()+10458 call rpispl() 1F ? 0 ? BFFFA858 ? 3A ? 0 ? 0 ? kzudrp()+921 call kzdukl() B6F9EB70 ? BFFFBAF0 ? B7136A44 ? FFFF0041 ? B6F9EB4C ? B7127680 ? opiexe()+13175 call kzudrp() B6F9EB68 ? B6F9FFEC ? 0 ? C ? ADA884D ? B6F9FFEC ? opiosq0()+2701 call opiexe() 4 ? 0 ? BFFFC090 ? kpooprx()+215 call opiosq0() 3 ? E ? BFFFC18C ? A4 ? kpoal8()+673 call kpooprx() BFFFEC74 ? BFFFCF00 ? 17 ? 1 ? 0 ? A4 ? opiodr()+976 call 00000000 5E ? 17 ? BFFFEC70 ? ttcpip()+1085 call 00000000 5E ? 17 ? BFFFEC70 ? 0 ? opitsk()+1054 call ttcpip() CE6F180 ? 5E ? BFFFEC70 ? 0 ? BFFFE950 ? BFFFED80 ? opiino()+821 call opitsk() 0 ? 0 ? opiodr()+976 call 00000000 3C ? 4 ? BFFFF840 ? opidrv()+466 call opiodr() 3C ? 4 ? BFFFF840 ? 0 ? sou2o()+91 call opidrv() 3C ? 4 ? BFFFF840 ? opimai_real()+117 call sou2o() BFFFF824 ? 3C ? 4 ? BFFFF840 ? main()+111 call opimai_real() 2 ? BFFFF870 ? __libc_start_main() call 00000000 2 ? BFFFF934 ? BFFFF940 ? +211 252C66 ? B742CFF4 ? 0 ? ###### 这里非常的关键 ###### HCheck Version 8i-11/2.00 Problem: OBJ$ INDEX entry has no IND$ entry INDEX has no IND$ entry: Obj=51829 ROGER.IDX_A Problem: OBJ$ TABLE entry has no TAB$ entry TABLE has no TAB$ entry: Obj=51828 ROGER.TEST1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select * from IND$ where obj#=51829; no rows selected SQL> select count(*) from obj$ where obj#=51829; COUNT(*) ---------- 1 SQL> select count(*) from TAB$ where obj#=51828; COUNT(*) ---------- 0 SQL> select count(*) from obj$ where obj#=51828; COUNT(*) ---------- 1 |
|
1 2 3 4 5 6 7 8 |
看来是数据字典不一致了，obj 51829 在obj$中存在，但是在ind$不存在，而obj 51828在obj$存在，在tab$中不存在。 这里处理的方式就有2种： 1. 由于是测试库，我完全可以把obj$中的51829 51828 这2条进行给删除就行了。 2. 由于是ind$和tab$中的数据没有，而在obj$存在，那么我们可以手工补齐ind$,tab$中的信息。 关于第1种方法，比较简单，我就不多说了；由于前面我想办法把原来的roger表空间给drop了， 这里就很难再重构了。不过总的来说，其实要想手工补齐ind$,tab$并不困难的，因为其结构如下： |
|
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 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 |
create table ind$ /* index table */ ( obj# number not null, /* object number */ /* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE * TRANSACTION DURING TRUNCATE */ dataobj# number, /* data layer object number */ ts# number not null, /* tablespace number */ file# number not null, /* segment header file number */ block# number not null, /* segment header block number */ bo# number not null, /* object number of base table */ indmethod# number not null, /* object # for cooperative index method */ cols number not null, /* number of columns */ pctfree$ number not null, /* minimum free space percentage in a block */ initrans number not null, /* initial number of transaction */ maxtrans number not null, /* maximum number of transaction */ pctthres$ number, /* iot overflow threshold, null if not iot */ type# number not null, /* what kind of index is this? */ /* normal : 1 */ /* bitmap : 2 */ /* cluster : 3 */ /* iot - top : 4 */ /* iot - nested : 5 */ /* secondary : 6 */ /* ansi : 7 */ /* lob : 8 */ /* cooperative index method : 9 */ flags number not null, /* mutable flags: anything permanent should go into property */ /* unusable (dls) : 0x01 */ /* analyzed : 0x02 */ /* no logging : 0x04 */ /* index is currently being built : 0x08 */ /* index creation was incomplete : 0x10 */ /* key compression enabled : 0x20 */ /* user-specified stats : 0x40 */ /* secondary index on IOT : 0x80 */ /* index is being online built : 0x100 */ /* index is being online rebuilt : 0x200 */ /* index is disabled : 0x400 */ /* global stats : 0x800 */ /* fake index(internal) : 0x1000 */ /* index on UROWID column(s) : 0x2000 */ /* index with large key : 0x4000 */ /* move partitioned rows in base table : 0x8000 */ /* index usage monitoring enabled : 0x10000 */ /* 4 bits reserved for bitmap index version : 0x1E0000 */ property number not null, /* immutable flags for life of the index */ /* unique : 0x01 */ /* partitioned : 0x02 */ /* reverse : 0x04 */ /* compressed : 0x08 */ /* functional : 0x10 */ /* temporary table index: 0x20 */ /* session-specific temporary table index: 0x40 */ /* index on embedded adt: 0x80 */ /* user said to check max length at runtime: 0x0100 */ /* domain index on IOT: 0x0200 */ /* join index : 0x0400 */ /* functional index expr contains a PL/SQL function : 0x0800 */ /* The index was created by a constraint : 0x1000 */ /* The index was created by create MV : 0x2000 */ /* The following columns are used for index statistics such * as # btree levels, # btree leaf blocks, # distinct keys, * # distinct values of first key column, average # leaf blocks per key, * clustering info, and # blocks in index segment. */ blevel number, /* btree level */ leafcnt number, /* # of leaf blocks */ distkey number, /* # distinct keys */ lblkkey number, /* avg # of leaf blocks/key */ dblkkey number, /* avg # of data blocks/key */ clufac number, /* clustering factor */ analyzetime date, /* timestamp when last analyzed */ samplesize number, /* number of rows sampled by Analyze */ rowcnt number, /* number of rows in the index */ intcols number not null, /* number of internal columns */ /* The following two columns are only valid for partitioned indexes */ /* * Legal values for degree, instances: * NULL (used to represent 1 on disk/dictionary and implies noparallel), or * 2 thru EB2MAXVAL-1 (user supplied values), or * EB2MAXVAL (implies use default value) */ degree number, /* number of parallel query slaves per instance */ instances number, /* number of OPS instances for parallel query */ trunccnt number, /* re-used for iots 'inclcol' */ spare1 number, /* number of columns depended on, >= intcols */ spare2 number, /* number of key columns in compressed prefix */ spare3 number, spare4 varchar2(1000), /* used for parameter str for domain idx */ spare5 varchar2(1000), spare6 date /* flashback timestamp */ ) create table tab$ /* table table */ ( obj# number not null, /* object number */ /* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE * TRANSACTION DURING TRUNCATE */ dataobj# number, /* data layer object number */ ts# number not null, /* tablespace number */ file# number not null, /* segment header file number */ block# number not null, /* segment header block number */ bobj# number, /* base object number (cluster / iot) */ tab# number, /* table number in cluster, NULL if not clustered */ cols number not null, /* number of columns */ clucols number,/* number of clustered columns, NULL if not clustered */ pctfree$ number not null, /* minimum free space percentage in a block */ pctused$ number not null, /* minimum used space percentage in a block */ initrans number not null, /* initial number of transaction */ maxtrans number not null, /* maximum number of transaction */ flags number not null, /* 0x00 = unmodified since last backup 0x01 = modified since then 0x02 = DML locks restricted to <= SX 0x04 = DML locks <= SX not acquired 0x08 = CACHE 0x10 = table has been analyzed 0x20 = table has no logging 0x40 = 7.3 -> 8.0 data object migration required 0x0080 = current summary dependency 0x0100 = user-specified stats 0x0200 = global stats 0x0800 = table has security policy 0x020000 = Move Partitioned Rows 0x0400000 = table has sub tables 0x00800000 = row dependencies enabled */ /* 0x10000000 = this IOT has a physical rowid mapping table */ /* 0x20000000 = mapping table of an IOT(with physical rowid) */ audit$ varchar2("S_OPFL") not null, /* auditing options */ rowcnt number, /* number of rows */ blkcnt number, /* number of blocks */ empcnt number, /* number of empty blocks */ avgspc number, /* average available free space/iot ovfl stats */ chncnt number, /* number of chained rows */ avgrln number, /* average row length */ avgspc_flb number, /* avg avail free space of blocks on free list */ flbcnt number, /* free list block count */ analyzetime date, /* timestamp when last analyzed */ samplesize number, /* number of rows sampled by Analyze */ /* * Legal values for degree, instances: * NULL (used to represent 1 on disk/dictionary and implies noparallel), or * 2 thru EB2MAXVAL-1 (user supplied values), or * EB2MAXVAL (implies use default value) */ degree number, /* number of parallel query slaves per instance */ instances number, /* number of OPS instances for parallel query */ /* <intcols> => the number of dictionary columns => the number of columns * that have dictionary meta-data associated with them. This is a superset of * <usercols> and <kernelcols>. * <intcols> = <kernelcols> + <number_of_virtual_columns> */ intcols number not null, /* number of internal columns */ /* <kernelcols> => the number of REAL columns (ie) columns that actually * store data. */ kernelcols number not null, /* number of REAL (kernel) columns */ property number not null, /* table properties (bit flags): */ /* 0x01 = typed table, 0x02 = has ADT columns, */ /* 0x04 = has nested-TABLE columns, 0x08 = has REF columns, */ /* 0x10 = has array columns, 0x20 = partitioned table, */ /* 0x40 = index-only table (IOT), 0x80 = IOT w/ row OVerflow, */ /* 0x100 = IOT w/ row CLustering, 0x200 = IOT OVeRflow segment, */ /* 0x400 = clustered table, 0x800 = has internal LOB columns, */ /* 0x1000 = has primary key-based OID$ column, 0x2000 = nested table */ /* 0x4000 = View is Read Only, 0x8000 = has FILE columns */ /* 0x10000 = obj view's OID is system-gen, 0x20000 = used as AQ table */ /* 0x40000 = has user-defined lob columns */ /* 0x00080000 = table contains unused columns */ /* 0x100000 = has an on-commit materialized view */ /* 0x200000 = has system-generated column names */ /* 0x00400000 = global temporary table */ /* 0x00800000 = session-specific temporary table */ /* 0x08000000 = table is a sub table */ /* 0x20000000 = pdml itl invariant */ /* 0x80000000 = table is external */ trigflag number, /* first two bytes for trigger flags, the rest for */ /* general use, check tflags_kqldtvc in kqld.h for detail */ /* 0x00000001 deferred RPC Queue */ /* 0x00000002 snapshot log */ /* 0x00000004 updatable snapshot log */ /* 0x00000008 = context trigger */ /* 0x00000010 = synchronous change table */ /* 0x00000080 = audit vault trigger */ /* 0x00010000 = server-held key encrypted columns exist */ /* 0x00020000 = user-held key encrypted columns exist */ /* 0x00200000 = table had rowmovement */ /* 0x00400000 = lobs use shared segment */ /* 0x00800000 = queue table */ /* 0x10000000 = streams unsupported table */ /* enabled at some point in past */ spare1 number, /* used to store hakan_kqldtvc */ spare2 number, /* committed partition # used by drop column */ spare3 number, /* summary sequence number */ spare4 varchar2(1000), /* committed RID used by drop column */ spare5 varchar2(1000), /* summary related information on table */ spare6 date /* flashback timestamp */ ) |
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> delete from obj$ where obj# in('51828','51829'); 2 rows deleted. SQL> commit; Commit complete. SQL> drop user ROGER cascade; User dropped. |
|
1 2 |
ok了，这个小case就先到这，如果是生产库的话，我们可以还可以借助ODU来进行处理， 回头会再写一篇更加详细文章来模拟一下关于数据字典不一致的处理，多谢大家的关注！ |
6 Responses to “ORA-01561 & ora-00600 [ktadrprc-1]”
roger写的很详细，希望以后多写点这样的帖子
ok 感谢支持，回头会多写一些原创性的文章，欢迎大家指正
I consider something truly interesting about your blog i really bookmarked .
Your article is truly informative. More importantly, it??s engaging, compelling and well-written. I will enjoy see even more of such a great writing.
Cool article it’s. Friend on mine continues to be awaiting with this content.
neat blog I??m greatful to visit your blog
Leave a Reply
You must be logged in to post a comment.