0

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

Improve this question
2
  • 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 ?
    – user953
    Aug 5, 2013 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, 2013 at 20:29

3 Answers 3

Reset to default
1

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.

Improve this answer
14
  • 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, 2013 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';
    – user953
    Aug 5, 2013 at 21:14
  • Nope. Just the bkp files. This is pretty much all we have: pastebin.com/dhc804Gy
    – Alex
    Aug 5, 2013 at 22:50
  • Try restoring controlfile from the smallest file. It may be autobackup of controlfile.
    – Mindaugas Riauba
    Aug 6, 2013 at 5:50
  • Well tried that. I ended up with an error message : backup is set to archivelog cannot be processed.
    – Alex
    Aug 6, 2013 at 8:05
0

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.

Improve this answer
1
  • 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, 2013 at 8:56
0

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
Improve this answer
4
  • 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, 2013 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, 2013 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, 2013 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, 2013 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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