Re: Database startup showing ORA-00942: table or view does not exist in trace
Date: Thu, 17 Feb 2011 10:59:46 +0530
Message-ID: <OF262B9209.6DBE2041-ON6525783A.0017ECB1-6525783A.001E2E33_at_ibsplc.com>
Thanks to all for the help.
I have generated the trace events and the problematic SQL as in trace file using the trace suggested by Tanel is as shown below
Avg compares per lookup = 200/200 = 1.0
- 2011-02-17 11:32:47.675 ksedmp: internal or fatal error ORA-00942: table or view does not exist Current SQL statement for this session: select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1
A trace for *.event="10046 trace name context forever,level 12"
PARSING IN CURSOR #2 len=198 dep=2 uid=0 oct=3 lid=0 tim=65328934266 hv=4125641360 ad='95ac5318'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null END OF STMT
EXEC #2:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=65328934265 WAIT #2: nam='db file sequential read' ela= 13711 file#=1 block#=38516 blocks=1 obj#=-1 tim=65328948014
FETCH #2:c=0,e=13776,p=1,cr=3,cu=0,mis=0,r=0,dep=2,og=4,tim=65328948066
PARSE ERROR #5:len=103 dep=1 uid=0 oct=3 lid=0 tim=65328948157 err=942 select
increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
ORA-00942: table or view does not exist
EXEC
#1:c=0,e=7238307,p=1608,cr=13269,cu=149,mis=0,r=0,dep=0,og=1,tim=65330088085 ERROR #1:err=1092 tim=6689801
It finally looks like Dictionary objects somehow got dropped. This is a development database and I don't have a backup.
Looks like the only option is to recreate the database. But I really want to know how this happened ? Any idea ?
Tried using DBMS_LOGMNR , but didn't get a OPERATION=DDL in any of the redo logs. The DB is in NO ARCHIVE LOG mode.
Thank You,
Kind Regards,
Sreejith Nair
From: Tanel Poder <tanel_at_poderc.com>
To: niall.litchfield_at_gmail.com Cc: landstander668_at_gmail.com, oracle-l_at_freelists.org Date: 02/16/2011 08:43 PM Subject: Re: Database startup showing ORA-00942: table or view does not exist in trace Sent by: oracle-l-bounce_at_freelists.org
Or if you want to know which statement fails (trying to access which object), then either enable SQL trace systemwide (in spfile) and startup or do this:
STARTUP NOMOUNT;
ALTER SESSION SET EVENTS '942 trace name errorstack level 3';
ALTER SYSTEM SET EVENTS '942 trace name errorstack level 3';
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
This should dump the errorstack with current SQL statement (and PL/SQL
line info if in plsql call) whenever an ORA-942 happens...
Once you've figured this out, I'd bounce the instance to get rid of this event. There's a tricky thing with alter system set events - it applies to all new sessions logging on and these sessions will keep this event enabled, even if you later issue an alter system to disable events... In fact, "alter system set events" just modifies the instance event array (template for new sessions logging on), not any existing sessions...
-- Tanel Poder http://tech.e2sn.com http://blog.tanelpoder.com On Wed, Feb 16, 2011 at 4:44 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote: hi or startup nomount alter system set "_system_trig_enabled=false"; alter database open. I agree with the diagnosis BTW. On Wed, Feb 16, 2011 at 2:01 PM, Adric Norris <landstander668_at_gmail.com> wrote: On Wed, Feb 16, 2011 at 7:26 AM, Sreejith S Nair < Sreejith.Sreekantan_at_ibsplc.com> wrote: SQL startup; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 2122544 bytes Variable Size 410819792 bytes Database Buffers 2801795072 bytes Redo Buffers 6488064 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced Any chance there's a buggy "on startup" trigger generating this error? If memory serves, you should able to do a "startup upgrade" (which suppresses such triggers) in this case. --Received on Wed Feb 16 2011 - 23:29:46 CST
"I'm too sexy for my code." -Awk Sed Fred
-- Niall Litchfield Oracle DBA http://www.orawin.info DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- http://www.freelists.org/webpage/oracle-l