Sunday, November 13, 2011

Switch Database to RMAN Image Copy Backup and Switch It Back

Considering RMAN image copy backup for your production databases in your backup strategy is a very helpful approach during hardware failure scenarios.

Why to consider RMAN image backups in your backup strategy:
It helps you fix disastrous situations due to physical damages, such as losing one or more datafiles in a minimal downtime, by eliminate the need of restoring the datafile from scratch.
Note: You should keep a recent RMAN image copy on the disk all the time to help minimize the downtime during physical damage situations.

When to switch to the image backup:
1-You lost a disk contains one datafile or more.
2-A datafile has been deleted or mistakenly overwritten on the OS level.
3-A datafile has a wide physical corruption.

In this demonstration I'll go through two real world scenarios:
Scenario #1 is related to switching the whole database to the RMAN image backup.
Scenario #2 is for switching one datafile only to the RMAN image backup.

Scenario #1 Switching the whole database to "RMAN copy backup": [on Windows Platform]

> For this scenario, let's take first a RMAN backup as copy for the whole database:
RMAN> backup as copy database;

For more information about Why to consider RMAN image copy, How to use it and How to recover an RMAN image copy from incremental backup, visit this link:
http://dba-thoughts.blogspot.ae/2014/08/recover-rman-image-copy-backup.html

Now let's suppose you lost your datafiles and and want to switch to the RMAN image copy:
>Re-bounce the database to the mount mode:
RMAN> shutdown immediate;
RMAN> startup mount;

Switch the database to the image copy backup:
Connect to the RMAN:
# rman target /
RMAN> switch database to copy;
datafile 1 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_7BZ8WZ7S_.DBF"
datafile 2 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSAUX_7BZ8ZZD8_.DBF"
datafile 3 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7BZ93XM6_.DBF"
datafile 4 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_USERS_7BZ963HC_.DBF"
datafile 5 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_EXAMPLE_7BZ95N9Z_.DBF"

RMAN> recover database;

This will recover the database till the time of failure, this means that the archives from the time of last RMAN copy backup till the time of the failure must be exist.
e.g. If the RMAN copy backup has been taken at 1:00am and the failure happen at 9:00am the archives between 1:00 to 9:00am should be available plus the current database redologs.

RMAN> alter database open;

Case of Incomplete Recovery:
In case you lost one or more archive without having a backup for them, you will be forced to recover to the last available archive using:
SQL> RECOVER DATABASE UNTIL CANCEL;
AUTO
Or from RMAN:
RMAN> run{
                    set until sequence xxx;   # Where xxx is the last higher available archivelog sequence.
                     recover database;
                     alter database open resetlogs;
                    }

>Datafiles are now pointing to the RMAN copy backup:
SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------------------------------------------------
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_USERS_7BZK07B2_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7BZJYX5S_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSAUX_7BZJWLDN_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_7BZJT6RF_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_EXAMPLE_7BZK000C_.DBF

Note: Temporary tempfiles will not be switched to the image backup as they never been considered during the backup time, so you just need to re-add them back.

Switch back the database to it's original files: [In case you managed to restore back the original datafiles]

Datafiles should be exist on their original places, whatever was the way you used to copy them back.
You have to use "rename file" command for each datafile to point them to the original location:

>First you have to restart the database to the mount mode:
SQL> shu immediate;
SQL> startup mount;

>Second run the rename command to point to datafile in the original location:
SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_USERS_7BZK07B2_.DBF' to 'D:\ora11g\oradata\orcl\USERS01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7BZJYX5S_.DBF' to 
'D:\ora11g\oradata\orcl\UNDOTBS01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSAUX_7BZJWLDN_.DBF' to 
'D:\ora11g\oradata\orcl\SYSAUX01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_7BZJT6RF_.DBF' to 
'D:\ora11g\oradata\orcl\SYSTEM01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_EXAMPLE_7BZK000C_.DBF' to 
'D:\ora11g\oradata\orcl\EXAMPLE01.dbf';

Note: You can re-create the controlfile pointing the datafiles to their old location instead of renaming the datafiles one by one.

Recover the database:
SQL> recover database;

Open the database:
SQL> alter database open;

Now the datafiles are pointing to it's original location:
SQL> select file_name from dba_data_files;
FILE_NAME
-------------------------------------------
D:\ORA11G\ORADATA\ORCL\USERS01.DBF
D:\ORA11G\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORA11G\ORADATA\ORCL\SYSAUX01.DBF
D:\ORA11G\ORADATA\ORCL\SYSTEM01.DBF
D:\ORA11G\ORADATA\ORCL\EXAMPLE01.DBF


Scenario #2 Switch one datafile to the RMAN copy backup: [on Linux Platform]

The same practice you can do if you lost one or more datafiles:

Let's suppose we lost datafile# 4 which is part of tablespace users and you're getting this error in the alertlog:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/ora_dev1/LEGACY/datafiles/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

If you have a recent RMAN "backup as copy" for the database or for the lost datafile, you can switch that datafile to it's copy piece in the image backup, by doing the following:

>Take the lost datafile offline:
# rman target /
RMAN> sql 'alter database datafile 4 offline';

>Switch the datafile to it's Copy backup piece:
RMAN> switch datafile 4 to copy;
Datafile 4 switched to datafile copy "/ora_dev1/LEGACY/copy/LEGACY_766159463_55"

>Recover the datafile:
RMAN> recover datafile 4;

>Bring back the datafile online:
RMAN> sql 'alter database datafile 4 online';

Now the datafile location will point to the rman "copy backup" piece:

SQL> select tablespace_name,file_name,status from dba_data_files where file_id=4;
TABLESPACE_NAM FILE_NAME STATUS
-------------- -------------------------------------------- ---------
USERS /ora_dev1/LEGACY/copy/LEGACY_766159463_55 AVAILABLE

DONE.

Note: For the above scenario to succeed, all archivelogs from the time of image copy backup creation till the time of failure should be exist.