Solved

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

Posted on 2002-05-28
9
553 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
Comment Utility
...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 200 total points
Comment Utility
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
Comment Utility
> 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
 
LVL 2

Expert Comment

by:banicki
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ShaunMarion
Comment Utility
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
Comment Utility
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
Comment Utility
How did you resolve this?
0
 

Expert Comment

by:netbanker
Comment Utility
Yes, have you sovled this problem?

eager to know the solution.

best regards,
0
 

Author Comment

by:ShaunMarion
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 39
Oracle TEXT search question 9 26
Oracle regular expression 6 29
query returning everything 11 64
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now