In this Blog I am going to show how the Oracle database ‘switch database to copy‘ can 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.
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.
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]