Oracle database 'switch database to copy' can be used to avoid lengthy restore times and therefore minimize downtime in the event of a database disaster or to move from a slow running storage to a faster storage.
A quite common scenario is that we have 3 disk groups, +DATA, +FRA and +REDO with different performance characteristics, like the following:
+DATA Diskgoup is on fast storage (10k rpm)
+FRA Diskgroup is on slowest storage (7200 rpm)
+REDO Diskgroup is on very fast storage (15k rpm)
example, +PURE_STORAGE_FLASH fastest disk group mounted so we switch the DB to use this diskgroup.
In case +DATA is slow and want to move to a faster storage then use this Oracle feature of switch database to copy with near zero downtime and without any lengthy restore/recover procedure.
Check RMAN backup status
$ rman target=/ catalog rman/pwd_here@mycatdb
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 30 2020 10:27:26 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYTESTDB (DBID=64278080088)
RMAN> list copy of database;
specification does not match any datafile copy in the repository
RMAN> list backup of database;
specification does not match any backup in the repository
Take RMAN backup copy
RMAN> run
{
allocate channel c1 device type disk format '+PURE_STORAGE_FLASH/%U';
recover copy of database with tag 'FLASHBLADE';
backup incremental level 1 for recover of copy with tag 'FLASHBLADE' database;
}
Now Let's have a look at our backup using the RMAN tag 'FLASHBLADE'
RMAN> list copy of database tag 'FLASHBLADE';
Key File S Completion Time Ckp SCN Ckp Time Sparse
967 1 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-SYSTEM_FNO-1_onut4h02
Tag: FLASHBLADE
961 2 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-SOE_FNO-2_ohut4gkn
Tag: FLASHBLADE
968 3 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr
Tag: FLASHBLADE
966 4 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq
Tag: FLASHBLADE
962 5 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-SOE_FNO-5_oiut4gmp
Tag: FLASHBLADE
965 7 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-USERS_FNO-7_oout4h09
Tag: FLASHBLADE
963 8 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-SOE_FNO-8_ojut4gp4
Tag: FLASHBLADE
964 9 A 30-NOV-2020 12760708 29-NOV-2020 NO
Name: +PURE_STORAGE_FLASH/MYTESTDB/data_D-MYTESTDB_I-3539279042_TS-SOE_FNO-9_okut4gr5
Tag: FLASHBLADE
SQL> create table t1 as select * from SCOTT.EMP;
COUNT(*)
22
Hostname : ed-ol-raclin01
Date Created : 22/02/20 11:11:11
Date Started : 29/11/20 11:11:11
DB Status : OPEN
Space Allocated: 144.40 GB
Space Used : 81.15 GB
Simulate Disaster
$ sqlplus / as sysdba
SQL> shutdown abort;
SQL> startup mount;
Total System Global Area 1.6267E+11 bytes
Fixed Size 30140696 bytes
Variable Size 1.9864E+10 bytes
Database Buffers 1.4227E+11 bytes
Redo Buffers 506728448 bytes
Database mounted.
Now let’s switch the database to use our RMAN image copy backup.
RMAN> switch database to copy;
Recover Database
Now recover the database to apply all changes since the last Incremental backup.
media recovery complete, elapsed time: 00:00:05
Finished recover at 30-Nov-2020
RMAN> alter database open;
Statement processed
SQL> select count(*) from t1;
COUNT(*)
22
SELECT * FROM V$DATAFILE;
RMAN> REPORT SCHEMA;
Summary
By using the 'Switch database to copy' you will be able to minimize downtime by avoiding a 'restore database' and also this feature can be used to switch the database to a faster storages like FLASH or SSDs compared to databases running on SAS or SATA disks.
