Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Terrilbe Oracle database recovery scenario --

SOLVED
Go to solution
MAD_2
Super Advisor

Terrilbe Oracle database recovery scenario --

Hardware: RP-5470 on HP-UX 11.0 with - Production Oracle8i Enterprise Edition Release 8.1.7.3.0 - Oracle
Oracle Server Manager Release 3.1.7.0.0

This is the scenario. We recently had three failed disks on our RAID5 array. I have a system backup from Sunday 9/21/03 and after replacing all of the hardware I used that tape to recover our system to a decent state.

We also run a nightly coldbckup that stores all of the database dbf and redo log files. However, because of a small failure on the script the control files were not being copied. The database backup is from the morning of 9/25/03, the morning our system failed (all files except the control files). The system restore provides us with a control file but from 9/21/03.

This is what was tried already:
========================================
1st Attempt:
SVRMGR> connect internal
Connected.
SVRMGR> startup pfile='/oracle/app/oracle/admin/INSTANCE/pfile/initINSTANCE.ora' mount

ORACLE instance started.
Total System Global Area 160549352 bytes
Fixed Size 104936 bytes
Variable Size 92672000 bytes
Database Buffers 67108864 bytes
Redo Buffers 663552 bytes
Database mounted.
SVRMGR> recover
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradata/RCCCDB2/system01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile


2nd Attempt:

SVRMGR> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> connect internal
Connected.
SVRMGR> startup pfile='/oracle/app/oracle/admin/RCCCDB2/pfile/initRCCCDB2.ora' nomount
ORACLE instance started.
Total System Global Area 160549352 bytes
Fixed Size 104936 bytes
Variable Size 92672000 bytes
Database Buffers 67108864 bytes
Redo Buffers 663552 bytes
SVRMGR> recover
ORA-01507: database not mounted
SVRMGR> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SVRMGR>

======================================

What can we do??? This is very HOT!!! We have been down since Thursday and thought that were going to be up today, but this one portion has us begging for answers.
Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with
7 REPLIES
Rita C Workman
Honored Contributor

Re: Terrilbe Oracle database recovery scenario --

Well...first I'm no DBA.

There is a way to recover the database and back up (alter database recover cancel)....but based on what you said above, I don't think you'll be able to recover back to the 9/25 date.... And that is because you have to have that control file or your database is out of sync and will not come up.
From what you say you have control files, etc. all from 9/21/03 system restore .....and that is where I think you stand the best chance to get back to. I'd suggest getting up there first, albeit from the earlier date. If you accomplish that, then see what you can than apply to bring the data forward.
After, I'd be looking for a solid tape backup and fresh exports !

Just a thought,
Rita

...like I said...I'm no DBA, so hopefully others can chime in and maybe offer more suggestions..
Steven E. Protter
Exalted Contributor

Re: Terrilbe Oracle database recovery scenario --

As you know, your situation is pretty rough.

You apparently have a cold backup with no control files.

I believe there is little possibility of rolling the database forward to last transaction.

You've laid down the cold backup. The next step is to regenerate the control files accodring to normal Oracle procedures. I believe there are better procedures on metalink.com but here is what I can find from itrc.

This thread has good advice and solution level points assigned.
http://forums1.itrc.hp.com/service/forums/parseCurl.do?CURL=%2Fcm%2FQuestionAnswer%2F1%2C%2C0x374835a43b46d71190080090279cd0f9%2C00.html&admit=716493758+1064801340838+28353475

I've done this once and did it under the tutelage of Oracle support(yes, they are good for something).

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Hari Kumar
Trusted Contributor
Solution

Re: Terrilbe Oracle database recovery scenario --

Here a scenerio which i feel can work out
#Mount your database;
#Backup control file to trace;
#shutdown immediate;
Find the trace file usually the latest file with *.trc extension, in which u will have the Create control file template and follow the complete steps in it.
Here the example :::::::
Mount
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Shutdown immediate
###here u will be finding your trace file and getting the control file creation sequence,it contains the following######

startup nomount
CREATE CONTROLFILE REUSE DATABASE "dbname" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'path & name of the on-line redo logs' ) SIZE 25M,
GROUP 2 (
'path & name of the on-line redo logs' ) SIZE 25M,
DATAFILE
'path and name of the data file???,
'path and name of the data file???;

RECOVER DATABASE
ALTER DATABASE OPEN;

Hope this will work out.

Thanks,
Information is Wealth ; Knowledge is Power
T G Manikandan
Honored Contributor

Re: Terrilbe Oracle database recovery scenario --

If you have all the database files backedup,

Restore all the files from the old backup version i.e backedup files with the controlfile version and apply all the archived logs to bring it to the present state.

If you do not have archived logs then
Restore the proper version of controlfile from backup.

If you had missed the backup of controlfile then you will have to re-create a new control file.

You can avoid using RESETLOGS option if you have a standby database.

Thanks
MAD_2
Super Advisor

Re: Terrilbe Oracle database recovery scenario --

We got in touch with the Oracle Folks at Metalink and were able to resolve the problem. The one person who got the closest to the solution was Mohan, so I will give him the 8 points.

Attached you all will find a text file which contains the steps provided by the Metalink folks at Oracle and special thanks to Loveen in their team who helped us get out of this nightmare, the guy is very sharp.
Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with
Hari Kumar
Trusted Contributor

Re: Terrilbe Oracle database recovery scenario --

Hi dude,great !Its very nice from you -->giving the reply of your problem and its solution which helps all the others too.
I am filing it in my Recovery scenario's file.

Thanks,
Information is Wealth ; Knowledge is Power
MAD_2
Super Advisor

Re: Terrilbe Oracle database recovery scenario --

Of course, I believe it's not fair to ask for help and then once found not to say how the problem was resolved.

By the way, I made a small mistake in the attachment (you all must forgive me, by that time I had been up for over 24+ hours and was ready to hit the sack; but I am very sure everyone here caught it!)

The portion within the text that reads like this under step #1...

Commands issued:
SVRMGRL>connect internal;
SVRMGRL>startup mount pfile = 'path to the one good control file';

Should actually read:
Commands issued:
SVRMGRL>connect internal;
SVRMGRL>startup mount pfile = 'path to the init.ora at this point only reflecting the one good control file';
Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with