Solved

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

Posted on 2002-05-28
9
580 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

719 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