RMAN Backup Restoration example

This blog will guide you through the basic steps of Backup , Restoration & Recovery Using RMAN

Step1 : Online RMAN datafile , control-file & archive-log Backup at Source(MW4-S)
Step2 : Configuring Destination (IEWE-S) database server for restoration & recovery
Step3 : Starting up the instance at destination in nomount
Step4 : restoring control-file
Step5 : Cataloging Backup-sets
Step6 : restoring datafiles
Step7 : Recovery & Open resetlogs


MW4-S is the source database server & IEWE-S is destination where database will be cloned
Environment used is Oracle11g & OS platform Solaris 10 

Let's begin then ..

Step1 : Online RMAN datafile , control-file & archive-log Backup at Source(MW4-S)
Database should be in mount or open mode as a prerequisite of RMAN backup because in the absence of recovery catalog control file is the source of metadata to identify file locations & SCN (system change number , for consistency check)




RMAN>backup as compressed backupset database format ‘<dir_location>/dbp_%U’;

RMAN> backup current controlfile format ‘<dir_location>/ctl_%U’;

RMAN> backup archivelog sequence from sequence <no.> until sequence <no.>;

Backup set file generated in all above steps need to copied in IEWE-S staging location

 
Step2 : Configuring Destination (IEWE-S) database server for restoration & recovery

Copy init<DBNAME>.ora from MW4-S to IEWE-S

Modify initialization parameter in IEWE-S for below parameters

& create spfile from pfile

·        diagnostic_dest

·        control_files

·        log_archive_dest_1

·        audit_file_dest

Step3 : Starting up the instance at destination in nomount

SQL>startup nomount

Step4 : restoring control-file

RMAN> restore controlfile  to ‘file_location’ from ‘ctl_backuppiece_location’;
  

Shut down instance and multiplex control-file to other two locations as a best practice

Step5 : Cataloging Backup-sets
To make instance aware of backup set id/location , cataloging is done.
catalog option is available post oracle 10g only

RMAN>catalog start with '<backup-set file location>';

Step6 : restoring datafiles

run {

set until sequence 5592;

set newname for datafile 1 to '/IEWE/oradata/MW4/system01.dbf';

set newname for datafile 2 to '/IEWE/oradata/MW4/undotbs01.dbf';

set newname for datafile 3 to '/IEWE/oradata/MW4/indx01.dbf';

set newname for datafile 4 to '/IEWE/oradata/MW4/tools01.dbf';

set newname for datafile 5 to '/IEWE/oradata/MW4/users01.dbf';

set newname for datafile 6 to '/IEWE/oradata/IEWE/IEWE_DATA_01.dbf';

set newname for datafile 7 to '/IEWE/oradata/MW4/sysaux01.dbf';

restore database;

switch datafile all;

recover database;

}

Step7 : Recovery & Open restlogs

Verify the file status of the datafile

select status, checkpoint_change#, fuzzy,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, checkpoint_change#, fuzzy, checkpoint_time
order by status, checkpoint_change#, fuzzy, checkpoint_time;

select hxfil file_id, fhscn scn, fhthr thread, fhrba_seq sequence, fhsta status from x$kcvfh;

select * from v$recover_file;

SQL> alter database open resetlogs;

Create temporary table-space by changing default temporary table-space

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPORARY;

create temporary tablespace TEMP01  TEMPFILE '/IEWE/oradata/IEWE/temp01_01.dbf' SIZE 1024M REUSE AUTOEXTEND OFF;

SQL>create temporary tablespace TEMPORARY TEMPFILE '/IEWE/oradata/MW4/temporary_01.dbf'  SIZE 2500M REUSE AUTOEXTEND OFF;