Re: Database startup showing ORA-00942: table or view does not exist in trace

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
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 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.

--

"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
Received on Thu Feb 17 2011 - 13:59:02 CST

Original text of this message