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.
Hi,
ReplyDeleteThank you for a good explanation of this method.
In scenario #1:
Shouldn't
Recover the database:
SQL> recover database;
be
Recover the database:
RMAN> recover database;
In my understanding I need to be connected to RMAN to recover, instead of recover the database from sqlprompt.
In my case I would be connected to a RMAN catalog.
Hi Laurens,
ReplyDeleteThanks for your valuable comment, of course when it comes to database recovery RMAN have advantage over sqlplus. When RMAN recover the database it considers incremental backups and backed up archives inside backupsets, whereas sqlplus doesn't consider backupsets in the recovery process.
Hi, thank you for your reply !
ReplyDeleteI have another question:
A few days back we had to perform the SWITCH DATABASE TO COPY and our database is now running with the copy. We needed to change the DB_RECOVERY_FILE_DEST after that to keep on making RMAN backups.
Now we are about to switch back the copy (and your procedure is helping bigtime !), so after the "recover database" and "open database" we want to bring down the database again to change the DB_RECOVERY_FILE_DEST back to it's original path in the pfile.
I assume that is the right way to do it...
Thank you in advance.
Hi Laurens,
ReplyDeleteI didn't think I got your question, but I guess you want to catalog the original database files to RMAN to easily switch back your datafiles from the image copy to the original datafiles, here what you can do:
Let's suppose you already switched your database to the image backup (which located under DB_RECOVERY_FILE_DEST:
SQL> select name from v$datafile;
NAME
----------------------------------------
/backup/flashback/SALES/datafile/o1_mf_system_bfsrlpy2_.dbf
/backup/flashback/SALES/datafile/o1_mf_sysaux_bfsro27v_.dbf
/backup/flashback/SALES/datafile/o1_mf_undotbs1_bfsrsbs1_.dbf
/backup/flashback/SALES/datafile/o1_mf_users_bfsrr6yh_.dbf
/backup/flashback/SALES/datafile/o1_mf_example_bfsrq3tn_.dbf
Now you want to switch back to the original datafiles that are located under /database/datafiles/sales
RMAN gives you the option to catalog those files as a copy backup, so with one switch command your database will switch to them without the need to copy them under DB_RECOVERY_FILE_DEST nor using rename file command:
RMAN> catalog start with '/database/datafiles/sales/' noprompt;
you can use "list copy" command to verify cataloged datafiles.
Now let's switch the database datafiles:
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
SQL> select name from v$datafile;
NAME
---------------------------------------
/database/datafiles/sales/system01.dbf
/database/datafiles/sales/sysaux01.dbf
/database/datafiles/sales/undotbs01.dbf
/database/datafiles/sales/users01.dbf
/database/datafiles/sales/example01.dbf
Now datafiles are back.
Please note that all transactions ran on the database between those switch operations will be recovered from archive logs when you issued "recover database" command, in other word no transactions will be lost in that operation as long as you have all archive logs there.
I hope that was clear for you.
Good one Mahmoud.
ReplyDelete