?
Solved

Urgent (aren't they all) - Datafiles not recognized

Posted on 2002-05-28
9
Medium Priority
?
597 Views
Last Modified: 2012-08-13
Over the weekend, we had some issues with one of our boxes which caused Oracle to die.  After restarting the instance, I logged in and did:

SQL> select count(rowid) from reporter_status;
Error at line 1:
ORA-08103: object no longer exists
SQL> desc reporter_status;
Name           Null?         Type
----------------------------------
IDENTIFIER                   VARCHAR2(255)

so it sees the table when I desc it but it says it no longer exists.  After a little more looking, did:

SQL> Select file_name from dba_data_files;

It returned all the system, temp, etc., however my reporter tablespace (where the bulk of my data is), it only returned 6 datafiles, where there are 12.  I looked at the OS, there were there.  I dumped the control files to trace and indeed, the control files had no ideas that my data files existed.

I made the changes and rebuilt my control files.  If I issue:

SVRMGRL> recover datafile '/data/ora3/blah...blah/reporte06.dbf';
statement processed
SVRMGRL> alter datafile '/data/ora3/blah...blah/reporter06.dbf' online;
statement processed

so then I select the file_name's out of dba_data_files, and still no go.  It's like Oracle knows about my datafiles now, but has no clue that they belong to my reporter tablespace.  Has anybody every seen this?

I get about 150,000 to 200,000 new rows a day.  I run Oracle 8.1.7 on Solaris 2.6.
0
Comment
Question by:ShaunMarion
  • 4
  • 4
9 Comments
 

Author Comment

by:ShaunMarion
ID: 7039524
...In addition, when I said:
"I looked at the OS, there were there", I meant I looked at the OS level to verify that my datafiles actually existed and they were there.

I recovered all datafiles and altered them online, and still a no-go.
0
 
LVL 2

Accepted Solution

by:
banicki earned 600 total points
ID: 7039619
disclaimer:  you should take a backup before starting any recovery procedures.
Here's some quick input...
Is anything in hot backup status?
select * from v$backup;
Is anything in recovery pending?
select * from v$recover_file;
Are the tablespaces listed in dba_tablespace?
Are the datafiles listed in V$datafile
Review the Alert.log file...

Banic

0
 

Author Comment

by:ShaunMarion
ID: 7039644
> Is anything in hot backup status?
    no

> Is anything in recovering pending?
    no

> Are the tablespace listed in dba_tablespace?
    yes

> Are the datafiles listed in V$datafile?
    no

There should be 12 datafiles for the reporter tablespace, however it is only listing 1-5.
   
   
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Expert Comment

by:banicki
ID: 7039645
disclaimer:  you should take a backup before starting any recovery procedures.
Here's some quick input...
Is anything in hot backup status?
select * from v$backup;
Is anything in recovery pending?
select * from v$recover_file;
Are the tablespaces listed in dba_tablespace?
Are the datafiles listed in V$datafile
Review the Alert.log file...

Banic

0
 

Author Comment

by:ShaunMarion
ID: 7039646
I have another tablespace used for a different tool, which luckily we no longer user, however it is listed in dba_tablespaces and it's files are also not listed in v$datafile.

However, when I try to descr one of the tables, I get this error:

SQL> desc reporter_status_ten_days;
ERROR:
ORA-00600: internal error code, arguments: [25012], [5], [19], [], [], [], [],
[]

0
 
LVL 2

Expert Comment

by:banicki
ID: 7039709
The problem is with tablespace #5 and relative file number 19...
based on the documentation below...  If you have Oracle Support, this is the time to call them.
Make it a sev 1, down production database.  Call it in, Skip the iTAR.
Looks like you have  possibility of physical corruption.
Review the Alert.log and the trace file, should be a trace file for the ORA-600


ERROR:              
    ORA-600 [25012][a][b][c][d][e]
   
  VERSIONS:          
    versions 8.0.X to 9.0.X
   
  DESCRIPTION:        

    We are trying to generate the absolute file number given a tablespace
    number and relative file number and cannot find a matching file number
    or the file number is zero.
   
  ARGUMENTS:          
    Arg [a] Tablespace Number
    Arg [b] Relative file number

0
 
LVL 2

Expert Comment

by:banicki
ID: 7054206
How did you resolve this?
0
 

Expert Comment

by:netbanker
ID: 7083043
Yes, have you sovled this problem?

eager to know the solution.

best regards,
0
 

Author Comment

by:ShaunMarion
ID: 7083893
Well, I never actually got the problems solved, I'll give you the points for all your had work, thanks.

I was able to remedy the situation, but that was only by recovering from a full backup (OS Backup, not Oracle...as my Oracle backup was corrupt...figures)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question