Solved

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

Posted on 2002-05-28
9
563 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 200 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
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.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

776 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