# Db recovery results in ORA-01113 - down production system

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.
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-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
SVRMGR> recover database using backup controlfile;
ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
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! Commented: Can you start the instance? How about just destroying and recreating the RBS tablespace? It's only rollback, not data. Author Commented: 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. Commented: 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. Commented: Commented: Author Commented: Daniel, We tried the alter database datafile ... end backup; and got the ORA-00600 and ORA-01110 errors I posted Thanks Commented: 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.

Author Commented:
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-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
SVRMGR> recover database using backup controlfile;
ORA-00279: change 1717652848 generated at 10/18/2003 01:34:57 needed for thread
1
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
Commented:
Commented:
Hi  ChrisSchreiber,

how are you doing there? what's the status now?
Commented:
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
Commented:
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

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.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
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.

Regards
/Hasse
Author Commented:
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).