由于字典被人恶意进行损坏,进行了一系列恢复之后,数据库依旧无法正常启动,而且出现比较诡异的错误ORA-00913: too many values
数据库启动报ORA-00913错
Starting background process MMNL Mon Jul 16 11:55:30 2018 MMNL started with pid=30, OS id=37580 ALTER SYSTEM enable restricted session; ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; Autotune of undo retention is turned off. ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY; ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY; ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; Resource Manager disabled during database migration: plan '' not set ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY; Resource Manager disabled during database migration Errors in file /s01/orabase/diag/rdbms/xff/xff1/trace/xff1_ora_37231.trc: ORA-00913: too many values Errors in file /s01/orabase/diag/rdbms/xff/xff1/trace/xff1_ora_37231.trc: ORA-00913: too many values Error 913 happened during db open, shutting down database USER (ospid: 37231): terminating the instance due to error 913 Instance terminated by USER, pid = 37231 ORA-1092 signalled during: alter database open upgrade... opiodr aborting process unknown ospid (37231) as a result of ORA-1092 Mon Jul 16 11:55:32 2018 ORA-1092 : opitsk aborting process
通过跟踪启动过程发现对SYS_FBA_TRACKEDTABLES表的插入报错
PARSE ERROR #140275116997968:len=70 dep=1 uid=0 oct=2 lid=0 tim=1531713543033057 err=913 insert into SYS_FBA_TRACKEDTABLES values (-1, -1, 0, '', '', 1, NULL) Flashback Archive: Error ORA-913 in SQL insert into SYS_FBA_TRACKEDTABLES values (-1, -1, 0, '', '', 1, NULL)
通过工具分析问题
DUL> desc sys.SYS_FBA_TRACKEDTABLES object_id: 1304, dataobj#: 1304, cluster tab#: 0 segment header: (ts#: 0, rfile#: 0, block#: 0)) Name Null? Type -------------------- --------------- -------------- OBJ# NOT NULL NUMBER FA# NOT NULL NUMBER DROPSCN NUMBER OBJNAME VARCHAR2(30) OWNERNAME VARCHAR2(30) FLAGS NUMBER SPARE NUMBER
发现奇怪segment header记录为空,根据经验很可能tab$中该记录丢失,通过分析验证,果然是tab$中记录丢失
C:\Users\Xifenfei>cat D:\RECOVER\dul\obj.dat |grep 1304 1304|1304|0|SYS_FBA_TRACKEDTABLES|1||2|| 11304||1|USER_SQLTUNE_RATIONALE_PLAN|1||5|| 13040||0|ORA$AUTOTASK_CLEAN|1||66|| 13041||0|AUTO_TASK_CONSUMER_GROUP|24||48|| 13042||0|WEEKNIGHT_WINDOW|1||69|| 13043||0|WEEKEND_WINDOW|1||69|| 13044||0|HM_CREATE_OFFLINE_DICTIONARY|1||66|| 13045||0|DRA_REEVALUATE_OPEN_FAILURES|1||66|| 13046|13046|0|ALERT_QT|1||2|| 13049|13049|0|SYS_C003549|4||1|| 13048|13048|0|SYS_IL0000013046C00070$$|4||1|| 13047|13047|0|SYS_LOB0000013046C00070$$|8||21|| 13065||0|QT13046_BUFFER|1||4|| 16281||0|/e5913043_DualReaderBuilderSub|1||29|| 16282||1|/e5913043_DualReaderBuilderSub|1||5|| 21304||0|/997def1d_MetalIconFactoryInte|1||29|| 31304||0|/859af54a_AppOutputStream|1||29|| 41304||0|/38d9d6d4_JAXBContextImpl7|1||29|| 51304||0|sun/awt/FontConfiguration$2|1||29|| 61304||0|/8cc2fced_CacheCustomizerError|1||29|| 71304||51|/ec0dfc12_AnnotationHandlerExt|1||29|| 81304|81304|63|WWV_FLOW_PAGE_PLUG_IDX2|4||1|| C:\Users\Xifenfei>cat D:\RECOVER\dul\tab.dat |grep 1304 5323|5323|0|1|11304|||7|2|1|536870912| 13046|13046|1|2|2258|||29|||539101186|
解决方法
通过一些内部方法,挂起数据库(不让他在open的过程报错),通过其他正常库获得该条tab$记录,然后插入数据库中(insert into sys.tab$ (OBJ#, DATAOBJ#, TS#, FILE#, BLOCK#, BOBJ#, TAB#, COLS, CLUCOLS, PCTFREE$, PCTUSED$, INITRANS, MAXTRANS, FLAGS, AUDIT$, ROWCNT, BLKCNT, EMPCNT, AVGSPC, CHNCNT, AVGRLN, AVGSPC_FLB, FLBCNT, ANALYZETIME, SAMPLESIZE, DEGREE, INSTANCES, INTCOLS, KERNELCOLS, PROPERTY, TRIGFLAG, SPARE1, SPARE2, SPARE3, SPARE4, SPARE5, SPARE6) values (1304, 1304, 0, 1, 8120, null, null, 7, null, 10, 40, 1, 255, 529, ‘————————————–‘, 1, 1, 0, 0, 0, 13, 0, 0, to_date(’24-08-2013 11:43:19’, ‘dd-mm-yyyy hh24:mi:ss’), 1, null, null, 7, 7, 9126805504, 0, 736, null, null, null, null, to_date(’24-08-2013 18:37:48′, ‘dd-mm-yyyy hh24:mi:ss’));
),再重启系统数据库,恢复正常
该库有可能还有很多字典不一致问题,建议尽快逻辑方式重建该库.
