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

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.
ShaunMarionAsked:
Who is Participating?
 
banickiConnect With a Mentor Commented:
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
 
ShaunMarionAuthor Commented:
...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
 
ShaunMarionAuthor Commented:
> 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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
banickiCommented:
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
 
ShaunMarionAuthor Commented:
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
 
banickiCommented:
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
 
banickiCommented:
How did you resolve this?
0
 
netbankerCommented:
Yes, have you sovled this problem?

eager to know the solution.

best regards,
0
 
ShaunMarionAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.