Recovery using datafile copy – SWITCH DATAFILE TO COPY
5 min readBy using Optimized Incremental Backup to disk backup strategy, an up to date copy of the database is always available on disk. In the event of a failure, the SWITCH command will point the controlfile to the backup of the datafiles that are present on disk.
In this way, we can significantly reduce the downtime related to restoring large datafiles from tape in the event of a media failure.
The downside of this is that we need to consider the disk space (and cost) of additional disks which will be required to hold the recovered incremental copies of backups on disk. Also as we will see, the SWITCH DATABASE TO COPY command will alter the names of all the datafiles and there is manual work involved in renaming them back to their original datafile names.
[oracle@localhost]$ cd /u01/app/oracle/oradata/orcl
[oracle@host01 orcl]$ ls
app_data01.dbf pdbseed redo03.log temp01.dbf
blk_chng_trk01.dbf redo01.log sysaux01.dbf undotbs01.dbf
control01.ctl redo02.log system01.dbf users01.dbf
Delete the files from OS level
[oracle@localhost orcl]$ rm *.dbf
Stop & Start the Database.
[oracle@localhost]$ sqlplus sys as sysdba
SQL*Plus: Release 12.2.0.1.0 Production
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1124073472 bytes
Fixed Size 8619840 bytes
Variable Size 805308608 bytes
Database Buffers 301989888 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL>
Login to RMAN Target.
[oracle@localhost]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1490662194, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_system_f3dc8ow5_.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_sysaux_f3dc956t_.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_undotbs1_f3dc9dfb_.dbf"
datafile 5 switched to datafile copy "/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_app_data_f3dc9hkd_.dbf"
datafile 7 switched to datafile copy "/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_users_f3dc9jqo_.dbf"
Start DB Recovery
RMAN> recover database;
Starting recover at 10-DEC-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_system_f3dc8ow5_.dbf
destination for restore of datafile 00003: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_sysaux_f3dc956t_.dbf
destination for restore of datafile 00004: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_undotbs1_f3dc9dfb_.dbf
destination for restore of datafile 00005: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_app_data_f3dc9hkd_.dbf
destination for restore of datafile 00007: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_users_f3dc9jqo_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/10-DEC-21/o1_mf_nnnd1_OSS_BKP_f3dcbtlm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/10-DEC-21/o1_mf_nnnd1_OSS_BKP_f3dcbtlm_.bkp tag=OSS_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 10-DEC-21
RMAN> alter database open;
Statement processed
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_system_f3dc8ow5_.dbf
/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_sysaux_f3dc956t_.dbf
/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_undotbs1_f3dc9dfb_.dbf
/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_app_data_f3dc9hkd_.dbf
/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_users_f3dc9jqo_.dbf
SQL> alter database move datafile '/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_system_f3dc8ow5_.dbf' to '/u01/app/oracle/oradata/orcl/system01.dbf';
Database altered.
SQL> alter database move datafile '/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_sysaux_f3dc956t_.dbf' to '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
Database altered.
SQL> alter database move datafile '/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_undotbs1_f3dc9dfb_.dbf' to '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
Database altered.
SQL> alter database move datafile '/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_app_data_f3dc9hkd_.dbf' to '/u01/app/oracle/oradata/orcl/app_data01.dbf';
Database altered.
SQL> alter database move datafile '/u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_users_f3dc9jqo_.dbf' to '/u01/app/oracle/oradata/orcl/users01.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/app_data01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
RMAN> list copy of database;
specification does not match any datafile copy in the repository
RMAN> RUN
{
RECOVER COPY OF DATABASE
WITH TAG 'orcl_bkp';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'orcl-bkp'
DATABASE;
}
RMAN> list copy of database;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
16 1 A 10-DEC-21 2158124 10-DEC-21 NO
Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_system_f3dfq757_.dbf
Tag: orcl_bkp
17 3 A 10-DEC-21 2158186 10-DEC-21 NO
Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_sysaux_f3dfqpgm_.dbf
Tag: orcl_bkp
18 4 A 10-DEC-21 2158191 10-DEC-21 NO
Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_undotbs1_f3dfqxnl_.dbf
Tag: orcl_bkp
19 5 A 10-DEC-21 2158194 10-DEC-21 NO
Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_app_data_f3dfqyrb_.dbf
Tag: orcl_bkp
20 7 A 10-DEC-21 2158198 10-DEC-21 NO
Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_users_f3dfqzz2_.dbf
Tag: orcl_bkp
