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;



3 comments:

  1. Its not possible to catalog a backup at nomount stage

    ReplyDelete
    Replies
    1. Thanks Anu ! Step 4 & 5 have been shuffled , mount mode will allow to catalog backup sets

      Delete
  2. Offsite backups are extremely important as it provides redundancy of your data in case of a natural disaster. If you are a business, you must consider the risk to your business if you were to lose all your data.

    ReplyDelete

Popular Posts