Re: Database startup showing ORA-00942: table or view does not exist in trace
Date: Thu, 17 Feb 2011 11:59:02 -0800 (PST)
Message-ID: <526329.75725.qm_at_web83603.mail.sp1.yahoo.com>
Hi Sreejith,
Interesting! Looks like the data dictionary table that stores "sequences" is missing. The SQL in question is a recursive SQL that is I think run when a sequence is referenced (with NEXTVAL) that possibly required the CACHE to be re-filled. Something that happens on a busy system when the CACHE value is not modified from its default value of 20. The weird part is why a core data dictionary table is missing!
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
Founder/Principal, DBPerfMan LLC
http://www.dbperfman.com
Phone - 001-(650)-743-6060
Co-author:Oracle Insights:Tales of the Oak Table -
http://www.apress.com/book/bookDisplay.html?bID=314
Co-author:Oracle Performance Tuning 101 -
http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com> To: tanel_at_poderc.com
Cc: landstander668_at_gmail.com; niall.litchfield_at_gmail.com; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org Sent: Wed, February 16, 2011 9:29:46 PM
Subject: Re: Database startup showing ORA-00942: table or view does not exist in trace
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 notexist 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 bytesDatabase 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.
--
"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-lReceived on Thu Feb 17 2011 - 13:59:02 CST