Solved

Db recovery results in ORA-01113 - down production system

Posted on 2003-10-21
18
6,609 Views
Last Modified: 2011-09-20
Database crashed after auto extend of RBS tablespace.  Unable to recover the tablespace.  Recovered all db files from hot backup taken before auto extend.  Attempt to recover database results in errors:

SVRMGR> connect internal
Connected.
SVRMGR> startup mount pfile='d:\oracle\admin\pacs\pfile\initpacs.ora';
ORACLE instance started.
Total System Global Area                       2750289948 bytes
Fixed Size                                          75804 bytes
Variable Size                                   602198016 bytes
Database Buffers                               2147483648 bytes
Redo Buffers                                       532480 bytes
Database mounted.
SVRMGR> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
ORA-00289: suggestion : K:\ORACLE\ORADATA\PACS\ARCHIVE\PACST001S150470.ARC
ORA-00280: change 1717652848 for thread 1 is in sequence #150470
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\PACS\SYSTEM01.DBF'
SVRMGR> recover database using backup controlfile;
ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
ORA-00289: suggestion : K:\ORACLE\ORADATA\PACS\ARCHIVE\PACST001S150470.ARC
ORA-00280: change 1717652848 for thread 1 is in sequence #150470
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\PACS\SYSTEM01.DBF'
0
Comment
Question by:ChrisSchreiber
  • 6
  • 5
  • 2
  • +4
18 Comments
 

Author Comment

by:ChrisSchreiber
Comment Utility
Well we're trying to make some progress at recovery, and found the tablespaces in hot backup mode:

SVRMGR> select file#,status from v$backup;
FILE#      STATUS
---------- ------------------
         1 ACTIVE
         3 ACTIVE
         4 ACTIVE
         5 ACTIVE
         6 ACTIVE
         7 ACTIVE
         8 ACTIVE
         9 ACTIVE
        10 ACTIVE
        11 ACTIVE
        12 ACTIVE
        13 ACTIVE
        14 ACTIVE
        15 ACTIVE
        16 ACTIVE
        17 ACTIVE
        18 ACTIVE
        19 ACTIVE
        20 ACTIVE
        21 ACTIVE
        22 ACTIVE
        23 ACTIVE
        24 ACTIVE
        25 ACTIVE
        26 ACTIVE
        27 ACTIVE
        28 ACTIVE
        29 ACTIVE
        30 ACTIVE
        31 ACTIVE
        32 ACTIVE
        33 ACTIVE
        34 ACTIVE
        35 ACTIVE
34 rows selected.
SVRMGR>

But are getting another error trying to use alter database end backup:


SVRMGR> alter database datafile 'H:\ORACLE\ORADATA\PACS\DATA04.DBF' end backup;
alter database datafile 'H:\ORACLE\ORADATA\PACS\DATA04.DBF' end backup
*
ORA-00600: internal error code, arguments: [kfhpfh_03-1210], [fno =], [35], [fhc
pc =], [722], [fhccc =], [1360], []
ORA-01110: data file 35: 'H:\ORACLE\ORADATA\PACS\DATA04.DBF'


Any help would be appreciated!

0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
Can you start the instance? How about just destroying and recreating the RBS tablespace? It's only rollback, not data.
0
 

Author Comment

by:ChrisSchreiber
Comment Utility
We were able to mount the instance, but we can't open it until recovery is complete.  We also aren't even getting to the point where it's trying to go through (and apply) any rollback.
0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
Looks like maybe you didn't get a proper backup. Can you try using the latest (unrecovered, post-crash) db files but with RBS offline?

Sorry, gotta go. Will check in later.
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
0
 

Author Comment

by:ChrisSchreiber
Comment Utility
Daniel,

We tried the alter database datafile ... end backup;
and got the ORA-00600 and ORA-01110 errors I posted

Thanks
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
it's hard to say where you are now.
Can you do this? shut down your instance, try the following 3 steps, see what's happening.

To recover the database without using the END BACKUP statement:

1  Mount the database:
STARTUP MOUNT;
   
         
2  Recover the database:
RECOVER DATABASE;
   
         
3 Use the V$BACKUP view to confirm that there are no active datafiles:
SQL>  SELECT * FROM v$backup WHERE status = 'ACTIVE';
FILE#      STATUS             CHANGE#    TIME    
---------- ------------------ ---------- ---------
0 rows selected.
   
0
 

Author Comment

by:ChrisSchreiber
Comment Utility
Daniel,

That's the first thing we had tried and got this:

SVRMGR> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
ORA-00289: suggestion : K:\ORACLE\ORADATA\PACS\ARCHIVE\PACST001S150470.ARC
ORA-00280: change 1717652848 for thread 1 is in sequence #150470
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\PACS\SYSTEM01.DBF'
SVRMGR> recover database using backup controlfile;
ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
ORA-00289: suggestion : K:\ORACLE\ORADATA\PACS\ARCHIVE\PACST001S150470.ARC
ORA-00280: change 1717652848 for thread 1 is in sequence #150470
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\PACS\SYSTEM01.DBF'
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 8

Expert Comment

by:Danielzt
Comment Utility
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
Hi  ChrisSchreiber,

how are you doing there? what's the status now?
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
ChrisSchreiber:

do you have a cold backup of the database?
do you have all the archivelog files since the cold backup of the database?
if you have both:

then do a incomplete recovery :

1. put back all the datafiles where they should be from the most recent cold backup.
2. in the SQLPLUS window, do "recover database until cancel"


then try to open the database
0
 
LVL 10

Accepted Solution

by:
SDutta earned 500 total points
Comment Utility
If you used a hot backup, your database needs to apply the archivelogs to recover the datafiles. When you get the following message, provide the file PACST001S150470.ARC in the required location and press RETURN.

ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
ORA-00289: suggestion : K:\ORACLE\ORADATA\PACS\ARCHIVE\PACST001S150470.ARC

Once at least one archivelog has been applied, you may want to CANCEL recovery at that point or go further to the last available archivelog. Only after this should you try opening the database with RESETLOGS.
0
 
LVL 8

Expert Comment

by:heskyttberg
Comment Utility
Hi!

I had this problem on a Oracle 8.0 this is what I did.

1. Restore all datafile AGAIN, you tried to roll them forward so they are screwed.
2. Make sure you do not restore archive logs, If you have done this skip point 5 and goto point 6..
3. Restore archive logs that goes with the backup, you need the time when backup ended.
4. startup database, mount it.
5. in svrmgr: recover database until time '2003-10-21:14:40:00' using backup control file;
Where the time should be 2-3 minutes before you altered RBS.
6. if you only have archive logs from last hot backup in svrmgr: recover database until time '2003-10-21:14:40:00' using backup control file;
Where time should be 2-3 minutes before backup finished.

If this dosen't work, you need to use the backup from the day before, use archive logs both from that backup and last backup and do a recover untile time where time should be a few minutes before second backup ended.

Hope any of this will help you resolve the problem.

Regards
/Hasse
0
 

Author Comment

by:ChrisSchreiber
Comment Utility
Thanks for the last responses, we are now back up and running.  We were able to apply all of the archive logs from the time of the backup until the time the backup completed and were able to do a succesful recovery at this point (we had a very large number of archive logs after this we didn't want to apply because of the time it would have taken, but these logs were from a batch update that ran over the weekend that we can re-run at some point later).

Thanks all for your help!
0
 
LVL 10

Expert Comment

by:SDutta
Comment Utility
Thanks,
It is really satisfying to see a successful recovery !
0
 

Author Comment

by:ChrisSchreiber
Comment Utility
Yes it sure is... our alternate plan was an export that was a few days old so we would have lost transactions that had been entered.  I know our users are much happier today, they've been very active!  Thanks again :)
0
 

Expert Comment

by:ClaytonMitchell
Comment Utility
I'm having the same problem except I don't care if I recover from the point of my last backup (3/14/2004).  I've actually  restored all the files from that backup but the database won't open and the recover still is trying to recover to the point of the crash.  This is my first major failure so I'm grasping a bit and I'd really be happy with the database restored to 3/13 or 3/14.  Any help?
Thanks,
Clay
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

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

10 Experts available now in Live!

Get 1:1 Help Now