Oracle database recovery using Storage Snapshot Optimization

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’]