Oracle Snapshot Standby

What is Oracle Snapshot Standby?

Oracle Data Guard Snapshot Standby allows you to open an Oracle physical standby database for read/write access. Whilst you can replicate this functionality from Oracle 10g by using a combination of flashback database and manually running the required commands, Oracle Snapshot Standby offers a far more simple approach.

Oracle Snapshot Standby simplifies the whole process of opening a standby database for read/write access by reducing the number of commands required to open, and converting it back to being a standby. The conversion from standby to “primary” is performed in addition to the pre-requisite tasks of enabling flashback database and creating a restore point.

Oracle Snapshot Standby allows for Log Transport to continue sending redo data from the primary whilst the snapshot standby database remains open. This is a major advantage over using manual flashback as you avoid impacting your RPO during DR testing.

Use Cases for Oracle Snapshot Standby

There are some interesting use cases and possibilities from using snapshot standby in your Oracle environment. Here are a couple of use cases where we think this is particularly helpful:

• Application change testing and validation on an exactly copy of the production database.
• Database statistics collection and validation. Database stats can then be exported and imported into the primary database after collection on the snapshot standby. This can be particularly useful if you need to collect stats at a point in time.
• Test environment provisioning using storage snapshots of the snapshot standby database.
• Performance diagnosis and fix testing.

These are just a few examples where we have found this capability to add value.

Using Oracle Snapshot Standby

1. Cancel media recovery on the standby:

alter database recover managed standby cancel;

2. Convert and Open

alter database recover managed standby database cancel;
alter database convert to snapshot standby;
shutdown immediate;
startup;

No you can use the snapshot database as an exact copy of production. When finished you need to convert the snapshot standby back to a physical standby.

3.Convert back to physical standby

startup mount force;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database disconnect from session;
exit 

Note: Check the applicable syntax for your version. Oracle 19c USING CURRENT LOGFILE has been deprecated. You may need to add “USING CURRENT LOGFILE” to your syntax depending on your Oracle version and RPO requirements.

This is a far more streamlined approach compared to the manual steps required in Oracle 10g. The standby database can be quickly and easily opened to test changes and DR processes whilst maintaining your sites RPO requirements.

Book a session today to see how we can help you optimise your Oracle platform.