Reducing Downtime with Switch Database to Copy

In this Blog I am going to show how the Oracle database switch database to copycan be used to avoid lengthy restore times and therefore minimise downtime in the event of a database disaster.

RMAN backup status

$ rman target=/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 8 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: LABS (DBID=3539279042)

RMAN> list copy of database;

using target database control file instead of recovery catalog
specification does not match any datafile copy in the repository

RMAN> list backup of database;

specification does not match any backup in the repository

From the above we can see that my LABS database has currently no image backups or backupsets.

For this Blog I am going to backup my database to a secondary storage array using Oracle dNFS (Direct Network File System), before we start we need to create an NFS file system mount point and update our oranfstab file ( $ORACLE_HOME/dbs/oranfstab ) e.g.

server: flashblade
local: 192.168.4.10 path: 192.168.4.150
nfs_version: nfsv3
export: /oracle_labs_incr  mount: /mnt/orabkup

I have already created a file system on my Pure Storage FlashBlade and exported it to my database server and mounted it as below.

$ df -h /mnt/orabkup
Filesystem                       Size  Used Avail Use% Mounted on
192.168.4.150:/oracle_labs_incr  1.0T  141M  1.0T   1% /mnt/orabkup

Oracle Incremental Image Merge

In the Oracle RMAN (Recovery Manager) ‘RUN’ block example I have specified an dNFS mount point as my off-primary array NFS storage destination with incremental level 1.

RUN
{
  allocate channel c1 device type disk format '/mnt/orabkup/%U';
  recover copy of database with tag 'FLASHBLADE';
  backup incremental level 1 for recover of copy with tag 'FLASHBLADE' database;
}

From below, we can see the FlashBlade has achieved at Data Reduction of 3.2 to 1, which is similar to the the Data Reduction achieved on my lab FlashArrays.

FlashBlade RMAN backup destination

Let’s have a look at our backup using the RMAN tag ‘FLASHBLADE’

RMAN> list copy of database tag 'FLASHBLADE';

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
967     1    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_onut4h02
        Tag: FLASHBLADE

961     2    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-2_ohut4gkn
        Tag: FLASHBLADE

968     3    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr
        Tag: FLASHBLADE

966     4    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq
        Tag: FLASHBLADE

962     5    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-5_oiut4gmp
        Tag: FLASHBLADE

965     7    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-USERS_FNO-7_oout4h09
        Tag: FLASHBLADE

963     8    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-8_ojut4gp4
        Tag: FLASHBLADE

964     9    A 12-APR-20       12760708   11-APR-20       NO    
        Name: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-9_okut4gr5
        Tag: FLASHBLADE

We can also see from the Operating System our ASM managed datafiles have the expected size.

[oracle@z-oracle orabkup]$ ls -lh /mnt/orabkup/data_D-LABS*
-rw-r----- 1 oracle asmadmin  31G Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-2_ohut4gkn
-rw-r----- 1 oracle asmadmin  31G Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-5_oiut4gmp
-rw-r----- 1 oracle asmadmin  31G Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-8_ojut4gp4
-rw-r----- 1 oracle asmadmin  31G Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-9_okut4gr5
-rw-r----- 1 oracle asmadmin 1.3G Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr
-rw-r----- 1 oracle asmadmin 941M Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_onut4h02
-rw-r----- 1 oracle asmadmin  23G Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq
-rw-r----- 1 oracle asmadmin  54M Apr 12 17:00 /mnt/orabkup/data_D-LABS_I-3539279042_TS-USERS_FNO-7_oout4h09

Now let’s create some data after our last incremental backup.

SQL> create table t1 as select * from WAREHOUSES;
SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

Before we perform our ‘switch database to copy’ let’s check our datafile locations.

Database Details
===============================================
Hostname       : z-oracle.uklab.purestorage.com
Database Name  : labs
Date Created   : 27/11/19 12:50:10
Date Started   : 09/04/20 15:20:05
Resetlogs Date : 27/11/19 12:50:13
DB Status      : OPEN
Space Allocated:    144.40 GB
Space Used     :     81.15 GB

Data File Name                                Tbsp      Data MB
--------------------------------------------- --------- -------
+DATA/LABS/DATAFILE/system.286.1025441287     SYSTEM        940
+DATA/LABS/DATAFILE/sysaux.287.1025441321     SYSAUX      1,310
+DATA/LABS/DATAFILE/soe.297.1025448855        SOE        30,720
+DATA/LABS/DATAFILE/users.289.1025441337      USERS          54
+DATA/LABS/DATAFILE/soe.298.1025449357        SOE        30,720
+DATA/LABS/DATAFILE/undotbs1.288.1025441337   UNDOTBS1   22,685
+DATA/LABS/DATAFILE/soe.299.1025452299        SOE        30,720
+DATA/LABS/DATAFILE/soe.300.1025456873        SOE        30,720

Temp File Name                                Temp MB
--------------------------------------------- ----------
+DATA/LABS/TEMPFILE/temp.294.1025441421       5,825

BCT File Name
------------------------------------------------------------
+DATA/LABS/CHANGETRACKING/ctf.296.1025442733

Control File Name
------------------------------------------------------------
+DATA/LABS/CONTROLFILE/current.290.1025441411
+FRA/LABS/CONTROLFILE/current.266.1025441411

REDO Member
------------------------------------------------------------
+DATA/LABS/ONLINELOG/group_3.293.1025441413
+FRA/LABS/ONLINELOG/group_3.269.1025441415
+DATA/LABS/ONLINELOG/group_2.292.1025441413
+FRA/LABS/ONLINELOG/group_2.268.1025441415
+DATA/LABS/ONLINELOG/group_1.291.1025441413
+FRA/LABS/ONLINELOG/group_1.267.1025441415

Simulate Disaster

$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 13 13:30:11 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

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.

Report Database Schema

$ rman target=/

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 13 13:41:32 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LABS (DBID=3539279042, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name LABS

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- ---------- ------- ------------------------
1    940      SYSTEM     ***     +DATA/LABS/DATAFILE/system.286.1025441287
2    30720    SOE        ***     +DATA/LABS/DATAFILE/soe.298.1025449357
3    1310     SYSAUX     ***     +DATA/LABS/DATAFILE/sysaux.287.1025441321
4    22685    UNDOTBS1   ***     +DATA/LABS/DATAFILE/undotbs1.288.1025441337
5    30720    SOE        ***     +DATA/LABS/DATAFILE/soe.297.1025448855
7    53       USERS      ***     +DATA/LABS/DATAFILE/users.289.1025441337
8    30720    SOE        ***     +DATA/LABS/DATAFILE/soe.299.1025452299
9    30720    SOE        ***     +DATA/LABS/DATAFILE/soe.300.1025456873

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ---------- ----------- --------------------
1    5825     TEMP       32767       +DATA/LABS/TEMPFILE/temp.294.1025441421

Switch Database to Copy

Before you switch to your RMAN copy you should consider what will happen if you have another disaster during the time you are using your RMAN backup ?

Forturnatley I am using a Pure Storage FlashBlade storage array in my lab so I can use my python script to take a read-only storage snapshot of the filesystem to protect my RMAN backup. By doing this after every Incremental backup I also have the option to restore or switch to copy from previous days, weeks or months.

$ python3 pureTakeFBsnapshot.py -s ${FLASHBLADE} -t ${API_TOKEN} -f ${FILESYSTEM} -S ${DAY}

Now let’s switch the database to use our RMAN image copy backup.

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_onut4h02"
datafile 2 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-2_ohut4gkn"
datafile 3 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr"
datafile 4 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq"
datafile 5 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-5_oiut4gmp"
datafile 7 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-USERS_FNO-7_oout4h09"
datafile 8 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-8_ojut4gp4"
datafile 9 switched to datafile copy "/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-9_okut4gr5"

Recover Database

I will now recover the database to apply all changes since my last Incremental backup.

RMAN> recover database;

Starting recover at 13-APR-20
allocated channel: ORA_DISK_1
...
allocated channel: ORA_DISK_16
channel ORA_DISK_16: SID=2664 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: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_onut4h02
destination for restore of datafile 00002: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-2_ohut4gkn
destination for restore of datafile 00003: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr
destination for restore of datafile 00004: /mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq
destination for restore of datafile 00005: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-5_oiut4gmp
destination for restore of datafile 00007: /mnt/orabkup/data_D-LABS_I-3539279042_TS-USERS_FNO-7_oout4h09
destination for restore of datafile 00008: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-8_ojut4gp4
destination for restore of datafile 00009: /mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-9_okut4gr5
channel ORA_DISK_1: reading from backup piece /mnt/orabkup/uautfip5_1_1
channel ORA_DISK_1: piece handle=/mnt/orabkup/uautfip5_1_1 tag=FLASHBLADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 1191 is already on disk as file +FRA/LABS/ARCHIVELOG/2020_04_12/thread_1_seq_1191.1712.1037557325
archived log for thread 1 with sequence 1192 is already on disk as file +FRA/LABS/ARCHIVELOG/2020_04_12/thread_1_seq_1192.1695.1037574149
archived log for thread 1 with sequence 1193 is already on disk as file +FRA/LABS/ARCHIVELOG/2020_04_13/thread_1_seq_1193.1679.1037602843
archived log file name=+FRA/LABS/ARCHIVELOG/2020_04_12/thread_1_seq_1191.1712.1037557325 thread=1 sequence=1191
media recovery complete, elapsed time: 00:00:05
Finished recover at 13-APR-20

Open Database

RMAN> alter database open;

Statement processed

Let’s check the previously created test table ‘t1’.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

Check usage of dNFS

From the below we can see the database is now using the datafiles from my RMAN backup located on my FlashBlade using dNFS.

SQL> select filename, to_char(filesize/1024/1024,'9,999,999') as filesize from v$dnfs_files;

File Name                                                      File Size
-------------------------------------------------------------- ----------
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_onut4h02       940
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-2_ohut4gkn       30,720
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr     1,310
/mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq  22,685
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-5_oiut4gmp       30,720
/mnt/orabkup/data_D-LABS_I-3539279042_TS-USERS_FNO-7_oout4h09         54
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-8_ojut4gp4       30,720
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-9_okut4gr5       30,720

Database Details

Database Details
===============================================
Hostname       : z-oracle.uklab.purestorage.com
Database Name  : labs
Date Created   : 27/11/19 12:50:10
Date Started   : 13/04/20 14:08:09
Resetlogs Date : 27/11/19 12:50:13
DB Status      : OPEN
Space Allocated:    144.40 GB
Space Used     :     81.15 GB

Data File Name                                                Tbsp   Data MB
------------------------------------------------------------- ------ -------
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_onut4h02 SYSTEM    940
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_omut4gvr SYSAUX  1,310
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-5_oiut4gmp    SOE    30,720
/mnt/orabkup/data_D-LABS_I-3539279042_TS-USERS_FNO-7_oout4h09  USERS      54
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-2_ohut4gkn    SOE    30,720
/mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq  UNDOTBS1   22,685
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-8_ojut4gp4    SOE    30,720
/mnt/orabkup/data_D-LABS_I-3539279042_TS-SOE_FNO-9_okut4gr5    SOE    30,720

Temp File Name                               Temp MB
-------------------------------------------- ----------
+DATA/LABS/TEMPFILE/temp.294.1025441421      5,825

BCT File Name
----------------------------------------------------------------------------
+DATA/LABS/CHANGETRACKING/ctf.296.1025442733

Control File Name
------------------------------------------------------------
+DATA/LABS/CONTROLFILE/current.290.1025441411
+FRA/LABS/CONTROLFILE/current.266.1025441411

REDO Member
------------------------------------------------------------
+DATA/LABS/ONLINELOG/group_3.293.1025441413
+FRA/LABS/ONLINELOG/group_3.269.1025441415
+DATA/LABS/ONLINELOG/group_2.292.1025441413
+FRA/LABS/ONLINELOG/group_2.268.1025441415
+DATA/LABS/ONLINELOG/group_1.291.1025441413
+FRA/LABS/ONLINELOG/group_1.267.1025441415

Switch Back

To switch-back we could perform another RMAN backup, but this time to our original’+DATA’ area and then perform another ‘switch database to copy’.

Alternatively, to further reduce downtime why not use the on-line datafile move features introduced at 12.2, you can read more here.

SQL> alter database move datafile '/mnt/orabkup/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_olut4gtq' to '+DATA' KEEP;

After repeating this for every datafile I am back on my starting position with all my data within my +DATA area on my FlashArray.

Data File Name                               Tbsp       Data MB
-------------------------------------------- ---------- -------
+DATA/LABS/DATAFILE/system.302.1037635199     SYSTEM        940
+DATA/LABS/DATAFILE/sysaux.303.1037635223     SYSAUX      1,310
+DATA/LABS/DATAFILE/soe.304.1037635261        SOE        30,720
+DATA/LABS/DATAFILE/users.305.1037635293      USERS          54
+DATA/LABS/DATAFILE/soe.306.1037635327        SOE        30,720
+DATA/LABS/DATAFILE/undotbs1.308.1037635601   UNDOTBS1   22,685
+DATA/LABS/DATAFILE/soe.307.1037635493        SOE        30,720
+DATA/LABS/DATAFILE/soe.301.1037634913        SOE        30,720

Summary

By using the ‘switch database to copy‘ I have been able to minimise downtime by avoiding a ‘restore database’.

Remember when you are running your database out of an area previously identified for backups you will need to redirect your backups to another location to ensure your database is fully protected. Also you may need to consider the impact of using your secondary storage on your database performance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: