Considering RMAN image copy backup for your production databases in your backup strategy is a very helpful approach to easily recover from disk failure scenarios.
Why 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 eliminating the need for 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.
This script can help you out in creating RMAN image copy backup:
https://www.dropbox.com/s/umempc88srgr8k8/schedule_rman_image_copy_bkp.sh?dl=0
You have to change the variables inside the script as instructed by the comments to match your environment.
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.
4-You want to move your database datafiles to a different location on the same server with a minimal downtime.
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 an 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 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 until the time of failure, this means that the archives from the time of last RMAN copy backup till the time of the failure must 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;
The 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 been never considered during the backup time, so you just need to re-add them back.
Switch back the database to its original files: [In case you managed to restore back the original datafiles]
Datafiles should exist in 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:
RMAN> recover database;
Open the database:
RMAN> alter database open;
Now the datafiles are pointing to its 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 its 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 archive logs from the time of image copy backup creation until the time of failure should exist.
Why 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 eliminating the need for 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.
This script can help you out in creating RMAN image copy backup:
https://www.dropbox.com/s/umempc88srgr8k8/schedule_rman_image_copy_bkp.sh?dl=0
You have to change the variables inside the script as instructed by the comments to match your environment.
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.
4-You want to move your database datafiles to a different location on the same server with a minimal downtime.
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 an 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 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 until the time of failure, this means that the archives from the time of last RMAN copy backup till the time of the failure must 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;
The 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;
}
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 been never considered during the backup time, so you just need to re-add them back.
Switch back the database to its original files: [In case you managed to restore back the original datafiles]
Datafiles should exist in 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:
RMAN> recover database;
Open the database:
RMAN> alter database open;
Now the datafiles are pointing to its 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 its 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 archive logs from the time of image copy backup creation until the time of failure should exist.
