A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Recovery using datafile copy – SWITCH DATAFILE TO COPY

5 min read
close up photo of programming of codes

Photo by luis gomes on Pexels.com

By 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