Oracle Storage Snapshot Optimization enables you to use third-party storage snapshots to recover a databases to the current time or a specific point-in-time without having the database placed in backup mode.
To use Storage Snapshot Optimization, the snapshot technology must conform to the following requirements.
- The database is crash consistent during the snapshot.
- The snapshot preserves write order for each file.
- The snapshot technology stores the time at which the snapshot is completed.
Storage Snapshot Optimization provides the following benefits.
- Eliminates the complexity and overhead associated with placing the database in backup mode.
- Performs the recovery in a single step by using the RECOVER … SNAPSHOT TIME command.
- You can recover either to the current time or to a point in time after the snapshot was taken.
If your storage vendor can’t guarantee compliance to the above requirements, you will then have to put the datafiles into backup mode by using the ALTER DATABASE or ALTER TABLESPACE statement with the BEGIN BACKUP clause.
Please note the Storage Snapshot Optimization option is only available to Oracle Database Enterprise Edition (EE) and Oracle Database Enterprise Edition on Engineered Systems (EE-ES) as per Oracle 19c Licensing Information.
Using Backup Mode
Place your data files in backup mode just before you take the snapshot.
When a tablespace is in backup mode, the database writes the before image for an entire block to the redo stream before modifying a block. The database also records changes to the block in the online redo log, thus increasing the volume of logging during the time the tablespace is in backup mode.
Backup mode also freezes the data file checkpoint until the file is removed from backup mode. The Oracle Database performs this safeguard because it cannot guarantee that a third-party backup tool copies the file header before copying the data blocks.
Once the snapshot has been created, use the ALTER DATABASE or ALTER TABLESPACE ALTER command with the END BACKUP clause to take the data files out of backup mode.
Historically, with disk based storage platforms there was a performance overhead whilst the database was in backup mode due to the increased logging during the time the database is in backup mode. With modern all-Flash-Storage platforms this is no longer an issue as the database only needs to be in backup mode for a very short period of time.
Using Storage Snapshot Optimization
I have defined a protection group which includes my data, control / redo and fast recovery area volumes on my lab Pure Storage FlashArray.
Let’s start by using the command line interface to take a protection group snapshot, I could also equally use with WebUI, REST API or SDKs to perform the same.
ssh -l ${FA_USER} ${FA_IP} purepgroup snap --suffix $SUFFIX ${PG_NAME}
Name Source Created
z-oracle-DEVL.demo z-oracle-DEVL 2021-07-12 15:36:43 BST
Now we have a snapshot I will create a table with 1 million rows of random data.
SQL> CREATE TABLE test_table TABLESPACE USERS AS
( SELECT LEVEL id,
SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) date_value,
DBMS_RANDOM.string('A', 20) text_value
FROM dual
CONNECT BY LEVEL <= 1000000);
Shutdown Database
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
Unmount the ASM data volume.
SQL> alter diskgroup DATA1 dismount;
Restore ASM data volume from the storage protection group snapshot.
ssh -l ${FA_USER} ${FA_IP} purevol copy ${PG_NAME}.${SUFFIX}.${DATA_VOL} ${DATA_VOL} --overwrite
Name Size Source Created Serial
z-oracle-dg_oradata1 1T z-oracle-dg_oradata1 2021-07-12 15:35:43 BST 50C939582B0F46C0003369D7
Mount the restored ASM data diskgroup volume prior to the creation of the 1 million row test table.
SQL> alter diskgroup DATA1 mount;
Start Oracle Database
$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 12 14:47:24 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 1.6267E+11 bytes Fixed Size 30386024 bytes Variable Size 1.9327E+10 bytes Database Buffers 1.4281E+11 bytes Redo Buffers 506482688 bytes Database mounted. ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '+DATA1/DEVL/system01.dbf'
Oracle informs us that the database needs recovery, our datafiles have been rolled back
Recover Database
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
Session altered.
SQL> RECOVER DATABASE SNAPSHOT TIME '2021/07/12 15:35:43';
Media recovery complete.
SQL> alter database open;
Database altered.
Check Test Table
SQL> select count(*) as "Number of Test Table Rows" from test_table; Number of Test Table Rows ------------------------- 1000000 SQL> select text_value from test_table where rownum <=10; TEXT_VALUE -------------------------------------------------------------------------------- bJzXUvUSOpVvHyFlSnci nlhwwcyNgxLRnTJKaZfK BdvvKlathsQbhjHCAygn fXAbPufodqrKWAZESMMQ lLhOuOMfnqZefGXHxVqw awSilZuqalbiNUiLYlvT PUSJTJOXGIJKqgDMlmjv UrxwgHscZUWCPEcPFjfS XUPQLeBzoNvQsxCzeuFo NoBqFugKVPkqYMpAbyAW
Summary
In this post I have shared how we can use Storage Snapshot Optimization to recover an Oracle database using a storage snapshot.
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]