Snapshot Standby is actually quite an old feature of Oracle Data Guard. According to note Doc ID 443720.1 it has been available since 11.2.

Snapshot Standby is a feature that allows temporarily to open existing physical standby database in READ WRITE mode – for example running some tests – and then convert it back to physical standby, discarding all the changes that were made during the testing. Another key feature is that the standby also retains some of the standby functions while it is open in READ WRITE mode, the snapshot standby keeps receiving redo from the primary database – just receiving, not applying. So even when you perform some testing on the standby, your data is still being protected by Data Guard. Although applying the shipped changes to the standby could take extended amount of time later.

Snapshot Standby relies on flashback database feature, but the process of converting back and forth is handled automatically by Data Guard Broker. But since it is creating a guaranteed restore point, then explicitly enabling flashback database is not required – although internally it is still flashback database so its restrictions apply.

My setup

DGMGRL> show configuration;

Configuration - devdbs02

  Protection Mode: MaxPerformance
  Members:
  devdbs02i1 - Primary database
    devdbs02i2 - Physical standby database 

Fast-Start Failover:  Disabled

Two databases in the configuration – devdbs02i1 is currently primary and devdbs02i2 is physical standby that I want to use temporarily for some testing purposes.

Converting standby to snapshot standby

Here I convert devdbs02i2 to a snapshot standby, which will open it temporarily in READ WRITE mode (independently from the primary database). All the changes made in devdbs02i2 will be flashed back (lost) when the database is converted back to physical standby later.

First make sure, that you have enough disk space for recovery area available. While the database is in snapshot standby mode, the recovery area will have to store flashback logs (for the entire duration while the standby is in snapshot standby mode!), archive logs from primary database and also archive logs from your testing activities. So quite a lot of extra pressure on the recovery area. Make it large.

$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 27 13:18:43 2024
Version 19.21.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "devdbs02i2"
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - devdbs02

  Protection Mode: MaxPerformance
  Members:
  devdbs02i1 - Primary database
    devdbs02i2 - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 38 seconds ago)

And do the conversion.

DGMGRL> convert database devdbs02i2 to snapshot standby;
Converting database "devdbs02i2" to a Snapshot Standby database, please wait...
Database "devdbs02i2" converted successfully

DGMGRL> show configuration verbose;

Configuration - devdbs02

  Protection Mode: MaxPerformance
  Members:
  devdbs02i1 - Primary database
    devdbs02i2 - Snapshot standby database

So, now devdbs02i2 is in snapshot standby mode, meaning it is open READ WRITE for temporary testing purposes.

SYS @ devdbs02i2:>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           SNAPSHOT STANDBY

Data Guard Broker has automatically created a guaranteed restore point for us. And looks like the developer who wrote this piece of code was american and used their non-standard date format 🙁 Kudos for using standard time format tho 🙂

SYS @ devdbs02i2:>select con_id, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

    CON_ID NAME                                           GUA
---------- ---------------------------------------------- ---
         0 SNAPSHOT_STANDBY_REQUIRED_02/27/2024 13:20:13  YES

And devdbs02i2 still keeps receiving redo from the primary database. This is one key benefit of using a snapshot standby.

SYS @ devdbs02i2:>select process,status,thread#,sequence#,block# from v$managed_standby where process='RFS';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
RFS       IDLE                  1          0          0
RFS       IDLE                  1          7       4029
RFS       IDLE                  0          0          0

And primary database still keeps sending the redo

SYS @ devdbs02i1:>select process,status,thread#,sequence#,block# from v$managed_standby where process='LGWR';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
LGWR      WRITING               1          7       4078

What I also notice is that the snapshot standby incarnation has changed (as expected). The snapshot standby is on incarnation 31, while the primary database is still on 30.

SYS @ devdbs02i2:>select LAST_OPEN_INCARNATION# from v$database;

LAST_OPEN_INCARNATION#
----------------------
                    31

SYS @ devdbs02i1:>select LAST_OPEN_INCARNATION# from v$database;

LAST_OPEN_INCARNATION#
----------------------
                    30

Using the stapshot standby in read write mode

Nothing special about it, just connect and run your statements.

SYS @ devdbs02i2:>alter session set container=lbtest1;

Session altered.

SYS @ devdbs02i2:>create table ilmker.just_some_test_data as select * from all_objects union all select * from all_objects;

Table created.

Restoring the database to physical standby

After your testing is done you should return the database back to being physical standby. Before the recovery area diskspace runs out.

First mount the snapshot standby instance.

SYS @ devdbs02i2:>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS @ devdbs02i2:>startup mount

Then connect to Broker using SYSDG privileges. With password! Broker needs to connect to the primary database, so it needs the password this time.

$ dgmgrl c##dataguard@devdbs02-2.db.example.net/devdbs02i2.dev.mt1
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Feb 28 16:22:02 2024
Version 19.21.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "devdbs02i2"
Connected as SYSDG.

DGMGRL> show configuration;

Configuration - devdbs02

  Protection Mode: MaxPerformance
  Members:
  devdbs02i1 - Primary database
    devdbs02i2 - Snapshot standby database 
      Warning: ORA-16782: instance not open for read and write access

DGMGRL> convert database devdbs02i2 to physical standby;
Converting database "devdbs02i2" to a Physical Standby database, please wait...
Operation requires a connection to database "devdbs02i1"
Connecting ...
Connected to "DEVDBS02I1"
Connected as SYSDG.
Database "devdbs02i2" converted successfully

DGMGRL> show configuration;

Configuration - devdbs02

  Protection Mode: MaxPerformance
  Members:
  devdbs02i1 - Primary database
    devdbs02i2 - Physical standby database

And devdbs02i2 is back as serving as physical standby database. Really convenient feature.

Why was I writing about this quite old feature? I was exploring it for the purpose of using it to refresh our performance test databases, because they are very large, they require cleaning of PII data and we need to complete the switch to a new copy in a very short time frame. But that story is for another post.

Oracle released a new SQL Developer edition, this time as extension for VS Code.

Setting up a new connection is pretty straight forward, you have all the usual connection fields available, but my first worry was that how to create a database connection if you require more advanced features turned on, for example TLS or Radius authentication.

SQL Developer extension for VS Code seems to use 21c JDBC database driver and the extra parameters can be supplied, per connection, under the advanced tab. Here is an example how to supply custom trust store for TLS encrypted connection and enabling RADIUS authentication.