Database Recovery #5 - RMAN Recover everything

--Restoring a database from scratch.

--These steps remove the entire instance, and the restore and recover from backup pieces outside the FRA. Please be careful
with the remove script.


--Prep database cleanout
sqlplus /nolog
conn / as sysdba
!rm /tmp/drop_orcl.sql
spool /tmp/drop_orcl.sql
select '!rm '||value from v$parameter where name = 'spfile';
select '!rm '||replace(value,'spfile','init') from v$parameter where name = 'spfile';
select '!rm '||name from v$controlfile;
select '!rm '||file_name from dba_data_files;
select '!rm '||member from v$logfile;
select '!rm -fr '||value||'/ORCL' from v$parameter where name = 'db_recovery_file_dest';
spool off


exit


--Take a backup and note the DBID of the database you are going to use to restore the instance.
[oracle@vmorcl backup]$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 20:29:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1296288227)
--Set controlfile autobackup ON
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>


--Clear out backups and backup the database, make sure SPFILE is backed up
delete noprompt backup;
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;


--List and copy the backup pieces from the recovery area to another location
list backup;


host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6ygq1p_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_nnndf_MASTER_DB_7h6ygrg9_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6yh7t6_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_22/o1_mf_s_770589705_7h6yh9gp_.bkp /u01/backup/';


--Check the backups were moved
host 'ls -rtl /u01/backup';


exit


--Once files are in the location, clear out the instance using the spool commands from the prep, and kill the instance.
--MAKE SURE YOU HAVE THE DBID BEFORE FIRING THIS OFF!
sqlplus / as sysdba @/tmp/drop_orcl.sql


--This should throw an error if the instance has be removed.create table sys.test as select * from v$instance;


--shutdown
shutdown abort;
exit;


--Right, the database is GONE. Time to restore and recover.--set DBID
rman target=/
set DBID=1296288227
--You have no spfile or initfile, the only way to startup is using the default init.ora file supplied by oracle
startup nomount force;


--Restore the spfile from the autobackup piece.run {
restore spfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
shutdown abort;


--Startup using the restored spfile 
startup nomount;


--Restore the control file from the same autobackup piece
run {
restore controlfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}


--Mount the database with the new found control files.
alter database mount;


--Add the rest of the backup pieces to the catalog
catalog start with '/u01/backup/';


--Restore the database to its former glory.
--Get the last sequence number.


list backup of archivelog all;


--Restore the database. Set the UNTIL SEQUENCE clause to the highest sequence + 1


run {
    set until sequence 3;
    restore database;
    recover database;
    }


--Be a hero - open the database with reset logs since we used a backup control file.


alter database open resetlogs;


--Once the database is  open, it is important to take a NEW backup!


backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;


--Done. To handle the password file, either create a new one, or copy the one from the original hosts app tree.