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

  • From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
  • To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 17 Feb 2011 11:59:02 -0800 (PST)

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@xxxxxxxxxx>
To: tanel@xxxxxxxxxx
Cc: landstander668@xxxxxxxxx; niall.litchfield@xxxxxxxxx; 
oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxxx> 
To:        niall.litchfield@xxxxxxxxx 
Cc:        landstander668@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx 
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@xxxxxxxxxxxxx 

________________________________
 


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@xxxxxxxxx> 
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@xxxxxxxxx> wrote: 
On Wed, Feb 16, 2011 at 7:26 AM, Sreejith S Nair 
<Sreejith.Sreekantan@xxxxxxxxxx> 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."

Other related posts: