Recovery using datafile copy – SWITCH DATAFILE TO COPY

by

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.

1
2
3
4
5
6
[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

1
[oracle@localhost orcl]$ rm *.dbf

Stop & Start the Database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[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.

1
2
3
4
5
6
7
[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)
1
2
3
4
5
6
7
8
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
1
2
3
RMAN> alter database open;
 
Statement processed
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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.
1
2
3
4
5
6
7
8
9
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
1
2
3
RMAN> list copy of database;
 
specification does not match any datafile copy in the repository
1
2
3
4
5
6
7
8
9
RMAN> RUN
{
  RECOVER COPY OF DATABASE
    WITH TAG 'orcl_bkp';
  BACKUP
    INCREMENTAL LEVEL 1
    FOR RECOVER OF COPY WITH TAG 'orcl-bkp'
    DATABASE;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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