Solved

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

Posted on 2003-10-21
6,633 Views
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
0
Question by:ChrisSchreiber
[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
• 6
• 5
• 2
• +4

Author Comment

ID: 9595706
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 ID: 9595740 Can you start the instance? How about just destroying and recreating the RBS tablespace? It's only rollback, not data. 0 Author Comment ID: 9595805 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 ID: 9595853 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 ID: 9595887 0 LVL 8 Expert Comment ID: 9595891 0 Author Comment ID: 9595937 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 ID: 9595955 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

ID: 9595978
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
0

LVL 8

Expert Comment

ID: 9596056
0

LVL 8

Expert Comment

ID: 9598494
Hi  ChrisSchreiber,

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

LVL 23

Expert Comment

ID: 9598743
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

SDutta earned 500 total points
ID: 9599169
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.
0

LVL 8

Expert Comment

ID: 9599361
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
0

Author Comment

ID: 9599782
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).

0

LVL 10

Expert Comment

ID: 9599947
Thanks,
It is really satisfying to see a successful recovery !
0

Author Comment

ID: 9600259
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

ID: 10642130
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

Question has a verified solution.

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