00913 oracle,ORA-00913 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜分...

由于字典被人恶意进行损坏,进行了一系列恢复之后,数据库依旧无法正常启动,而且出现比较诡异的错误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’));

),再重启系统数据库,恢复正常

该库有可能还有很多字典不一致问题,建议尽快逻辑方式重建该库.