当遭遇ORA-00600 [kkdlron-max-objid], [4254950911] 怎么办
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
今天同事反馈有客户环境遇到ORA-00600: [kkdlron-max-objid], [4254950911] 报错；该环境超过30tb大小；处理起来比较麻烦。
从错误来看，应该就是应用不断drop重建对象，导致object_id达到Oracle最大值了。虽然Oracle Mos提供了一个Bug，如下：
但实际上即使打上Patch，能用的object_id 范围也很小了，也就几千万。不足以支撑3天。
在Oracle数据库中关于object/cosntraint/users的最大数量限制，可以参考这篇文档。Internal Database Limits on Number of Objects, Constraints, and Users (Doc ID 2660231.1)
言归正传；很明显这个问题，根本原因在于应用架构设计问题，不应该不断drop重建对象，其实可以使用temporary table来解决这个问题。
就这个问题而言，同事查询该数据object 仅仅不到20万个对象。那么针对这个问题，能否有一些方法可以绕过呢？
我们知道oracle创建对象是通过_next_object来获取id的，该对象属于sys；如下：
|
1 2 3 4 5 |
SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT'; DATAOBJ# ---------- 87365 |
下面我们来进行简单的测试：
|
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 |
SQL> conn /as sysdba Connected. SQL> create user roger identified by roger; User created. SQL> grant connect,resource,dba to roger; Grant succeeded. SQL> conn roger/roger Connected. SQL> create table t1 as select * from sys.dba_objects where rownum < 100; Table created. SQL> c/t1/t2 1* create table t2 as select * from sys.dba_objects where rownum < 100 SQL> / Table created. SQL> c/t2/t3 1* create table t3 as select * from sys.dba_objects where rownum < 100 SQL> / Table created. SQL> c/t3/t4 1* create table t4 as select * from sys.dba_objects where rownum < 100 SQL> / Table created. SQL> c/t4/t5 1* create table t5 as select * from sys.dba_objects where rownum < 100 SQL> / Table created. SQL> SQL> SELECT dataobj# FROM SYS.obj$ WHERE NAME = '_NEXT_OBJECT'; DATAOBJ# ---------- 87370 SQL> drop table t1; Table dropped. SQL> c/t1/t2 1* drop table t2 SQL> / Table dropped. SQL> c/t2/t3 1* drop table t3 SQL> / Table dropped. SQL> c/t3/t4 1* drop table t4 SQL> / Table dropped. SQL> c/t4/t5 1* drop table t5 SQL> / Table dropped. SQL> conn /as sysdba Connected. SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE dataobj# > 87364 order by 2; OBJ# DATAOBJ# NAME ---------- ---------- ------------------------------ 1 87375 _NEXT_OBJECT SQL> update SYS.obj$ set DATAOBJ#=87365 where NAME='_NEXT_OBJECT'; 1 row updated. SQL> commit; Commit complete. SQL> conn roger/roger Connected. SQL> create table t1 as select * from sys.dba_objects where rownum < 100; Table created. SQL> c/t1/t2 1* create table t2 as select * from sys.dba_objects where rownum < 100 SQL> / Table created. SQL> c/t2/t3 1* create table t3 as select * from sys.dba_objects where rownum < 100 SQL> / Table created. SQL> select obj#,dataobj# ,name FROM SYS.obj$ WHERE dataobj# > 87364 order by 2; OBJ# DATAOBJ# NAME ---------- ---------- ------------------------------ 87373 87373 T1 87374 87374 T2 87375 87375 T3 1 87380 _NEXT_OBJECT SQL> SQL> SELECT CASE 2 WHEN (nextobjnum - maxobjnum) > 0 3 THEN 'GOOD' ELSE 'BAD' 4 5 END "OBJ_NUM_STATE" 6 FROM (SELECT (SELECT dataobj# 7 FROM SYS.obj$ 8 WHERE NAME = '_NEXT_OBJECT') nextobjnum, 9 (SELECT MAX (obj#) 10 FROM SYS.obj$) maxobjnum 11 FROM DUAL); OBJ_NUM_STAT ------------ GOOD |
我们可以看到，其实完全可以重用object_id。但是这里需要注意的是，直接修改数据字典，存在一定风险。需要慎重评估。
另外对于这个临时处理方案，主要是要寻找到object id到空洞范围，最好是范围够大。
当然，如果担心数据字典有异常，可以使用Oracle提供到脚本check一次。
|
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 |
SQL> @/tmp/hcheck.sql HCheck Version 07MAY18 on 07-DEC-2020 11:48:40 ---------------------------------------------- Catalog Version 11.2.0.4.0 (1102000400) db_name: KILLDB Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- LobNotInObj ... 1102000400 <= *All Rel* 12/07 11:48:41 PASS .- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 12/07 11:48:41 PASS .- SourceNotInObj ... 1102000400 <= *All Rel* 12/07 11:48:41 PASS .- OversizedFiles ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS .- PoorDefaultStorage ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS .- PoorStorage ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS .- TabPartCountMismatch ... 1102000400 <= *All Rel* 12/07 11:48:43 PASS .- OrphanedTabComPart ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- MissingSum$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- MissingDir$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- DuplicateDataobj ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- ObjSynMissing ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- ObjSeqMissing ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedUndo ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedIndex ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedIndexPartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedTable ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedTablePartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- MissingPartCol ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedSeg$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- DuplicateBlockUse ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- FetUet ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- Uet0Check ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- SeglessUET ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- BadInd$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- BadTab$ ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- BadIcolDepCnt ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- ObjIndDobj ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- TrgAfterUpgrade ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- ObjType0 ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- BadOwner ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- StmtAuditOnCommit ... 1102000400 <= *All Rel* 12/07 11:48:44 PASS .- BadPublicObjects ... 1102000400 <= *All Rel* 12/07 11:48:45 PASS .- BadSegFreelist ... 1102000400 <= *All Rel* 12/07 11:48:45 PASS .- BadDepends ... 1102000400 <= *All Rel* 12/07 11:48:45 PASS .- CheckDual ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- ObjectNames ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- BadCboHiLo ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- ChkIotTs ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- NoSegmentIndex ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- BadNextObject ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- DroppedROTS ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- FilBlkZero ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- DbmsSchemaCopy ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- OrphanedObjError ... 1102000400 > 1102000000 12/07 11:48:47 PASS .- ObjNotLob ... 1102000400 <= *All Rel* 12/07 11:48:47 PASS .- MaxControlfSeq ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS .- SegNotInDeferredStg ... 1102000400 > 1102000000 12/07 11:48:48 PASS .- SystemNotRfile1 ... 1102000400 > 902000000 12/07 11:48:48 PASS .- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS .- OrphanTrigger ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS .- ObjNotTrigger ... 1102000400 <= *All Rel* 12/07 11:48:48 PASS --------------------------------------- 07-DEC-2020 11:48:48 Elapsed: 8 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) PL/SQL procedure successfully completed. Statement processed. Complete output is in trace file: /u01/app/oracle/diag/rdbms/killdb/killdb/trace/killdb_ora_32724_HCHECK.trc |
操作有风险！上述操作均为个人测试，生产环境建议慎重。如果可能，还是重建库比较好。
Leave a Reply
You must be logged in to post a comment.