Sunday, August 26, 2012

Backup and restore of Database before upgrade using RMAN


Make sure you always take Database backup before any upgrades and patches.It is always good idea to do this. In the below example I am showing how to take a backup of full database to some location and how to restore it back from backup in-case of any failures.


backup database before upgrade:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL C1 TYPE DISK;
BACKUP DATABASE FORMAT '/u02/backup/backup_%U' TAG "before_upgrade";
BACKUP CURRENT CONTROLFILE FORMAT '/u02/backup/controlfilebkp.ctl';
}



Restoring database from backup in-case of upgrade fail:

RMAN> restore controlfile from '/u02/backup/controlfilebkp.ctl';

Starting restore at 18-JUL-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 18-JUL-12

copy controlfile to location control02, control03 :


[oracle@linux5 test]$ cp control01* control02.ctl
[oracle@linux5 test]$ cp control01* control03.ctl
[oracle@linux5 test]$ pwd
/u01/app/oracle/oradata/test
[oracle@linux5 test]$


RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 18-JUL-12
Starting implicit crosscheck backup at 18-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 18-JUL-12

Starting implicit crosscheck copy at 18-JUL-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JUL-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/backup_06of1qf9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/backup_06of1qf9_1_1 tag=BEFORE_UPGRADE
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-JUL-12


RMAN> recover database;

Starting recover at 18-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc
archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/oradata/test/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc thread=1 sequence=8
archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-JUL-12


Now login as sysdba and reset the logs before you can start using the database:

sql>sqlplus /"As sysdba

SQL> alter database open resetlogs;