Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Before I start, I just want to mention that I am not a DBA, I am a programmer/developer who also happens to look after the database. So if I say something wrong, my apologies in advance.

We recently experienced a massive city wide power surge which killed our database server. We have multiple databases running on the server (RHEL6 - Oracle 11gr2). I back up all the data using rman and the command "backup database plus archivelog delete input" and then take an impdp dump of one particular schema so that we can import it into our test environments over night. After our server was destroyed, the only thing we could recover from the hard drives was the backupset folder which contains a lot of bkp files. And that's about it. I don't have a control file or the DBID of the database. I do however have a data dump that's 3 days old

Is there any way we can recover the database on to a new host?

Regards

Alex

share|improve this question
    
Do you also have files in the servers $ORACLE_HOME/dbs/ directory named snapcf_${ORACLE_SID}.f and spfile${ORACLE_SID}.ora or init${ORACLE_SID}.ora ? –  ik_zelf Aug 5 '13 at 20:26
    
There are lot of people who become de facto DBAs because there is no one else to do the job. Some of them are in this area. –  Walter Mitty Aug 5 '13 at 20:29
add comment

3 Answers 3

up vote 1 down vote accepted

Yes, there is. transfer the files to a new server, if you have one, using the same file structures. next create a init.ora file with the dbname and control_file parameters. Start the instance using that init.ora in nomount.

If you happen to have snapshot controlfiles in $ORACLE_HOME/dbs/ of you database, start rman:

rman target=/
restore controlfile from '/where/is/it/o1_mf_ncsnf_TAG20130803T022352_8zrqqfc4_.bkp';
alter database mount;
restore database;
recover database;
alter database open;

Without controlfile it's a little trickier but still possible. A nice explanation is here How to restore an rman backup without any existing control files

Tip: always end your backup by creating a copy of your current controlfile:

BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/controlfile_%Y%M%D.ctl';

Preferably on a smarter location than /tmp. Not only does it contain your database layout, also the administration of your most recent and most valuable backup.

share|improve this answer
    
WE pretty much lost everything on that server. The only reason we have the backupset is because I copied them from the server 5 hours before the power surge hit us. –  Alex Aug 5 '13 at 21:04
    
do you have the logs of the last backup? If so, check where the controlfile did go and issue restore controlfile from '/where/it/went/bkp_XXXX'; –  ik_zelf Aug 5 '13 at 21:14
    
Nope. Just the bkp files. This is pretty much all we have: pastebin.com/dhc804Gy –  Alex Aug 5 '13 at 22:50
    
Try restoring controlfile from the smallest file. It may be autobackup of controlfile. –  Mindaugas Riauba Aug 6 '13 at 5:50
    
Well tried that. I ended up with an error message : backup is set to archivelog cannot be processed. –  Alex Aug 6 '13 at 8:05
show 9 more comments

I think o1_mf_ncsnf_TAG20130803T022352_8zrqqfc4_.bkp contains your control file. Format Of Backup Pieces Managed In FRA (Doc ID 566680.1), says that "c" in third section indicates a control file.

share|improve this answer
    
I tried recovering the controlfile from that file but it didn't quite work. I ended up with an error message : backup is set to archivelog cannot be processed. ik_zelf originally suggested the idea. –  Alex Aug 6 '13 at 8:56
add comment

After restoring the controlfile from o1_mf_ncsnf_TAG20130803T022352_8zrqqfc4_.bkp (in your comments on ik_zelf's post you said that you'd managed to restore it) you've tried to perform a restore which imply a crosscheck and, since the backup files were in a different location, RMAN marked them as EXPIRED. You have 2 choices.

  • A) Check the paths with RMAN> list backup , recreate the paths, move the backup files to their old places and then in RMAN crosscheck backup all (lot of work here if there are several different folders)
  • B) Run RMAN> catalog start with '/new/backup/path' and just restore / recover
share|improve this answer
    
Cheers mate. I did manage to restore the database files but I am stuck as recovering the database itself. I end up with a ORA-19804. I have modified the db_recovery_file_dest_size to 200G and I still get the same error. Thank you for your advice though mate. I appreciate all the help. –  Alex Aug 7 '13 at 13:57
    
Check if path in select name, value from db_recovery_file_dest exist and if there is enough space. PS* Maybe is a good situation to point out that its a good idea for your company to hire a DBA –  vegatripy Aug 7 '13 at 14:48
    
I've typed it wrong. It's: select name, value from v$parameter where name='db_recovery_file_dest' –  vegatripy Aug 7 '13 at 14:56
    
The idea is I eventually attend training and get certified. But in the short term I have to sort things out. I have checked the parameter already and it's correct. I got past that error. The issue was because of a shared drive where the recovery data was being held. –  Alex Aug 7 '13 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.