Oracle Snapshot Standby Database

You can find the below informations about Snapshot Standby Database in this article.

  • What is Oracle Snapshot Standby Database?
  • Convert Physical Standby to Snapshot Standby
  • Convert Physical Standby to Snapshot Standby using Dgmgrl
  • Convert Snapshot Standby Back to Physical Standby

What is Oracle Snapshot Standby Database?

Snapshot Standby Database is an updatable database created from Physical Standby Database.

There is redo flow to Snapshot Standby Database, but there is no redo apply.

As soon as the Physical Standby Database is converted to Snapshot Standby Database, an implicit restore point is created and Flashback logs are activated.

Besides being a nice feature to be updated, Snapshot Standby Database also has dangerous points.

Dangerous Points of Snapshot Database

  • Suppose one of the redo log files is corrupted in Snapshot Standby Database. In this case, we cannot know what the problem is until we convert it to a physical standby database and redo-apply it with the MRP process. If something happens to our Primary database at this moment, we will have a definite data loss because there is no other place to get that log.
  •  If Flashback Logs are corrupted or deleted, we cannot return to Physical Standby Database.
  •  Let’s say something happens to Primary Database while Standby Database is in Snapshot mode. In this case, it will be rewound with Flashback logs first. Since it will run the archives later, it means an interruption for as much as this time.

Physical Standby Database cannot be converted to Snapshot Standby Database in the following cases;

  • If Maximum Protection mode is used and there is only one Standby Database in the Data Guard Environment. The reason is that when there is only one Standby Database, it must be sync with Primary as much as possible so that it can be Primary quickly if a Failover occurs.
  •  If it is the Standby Database where Fast-Start Failover will be made.

Convert Physical Standby to Snapshot Standby

Step1:

Some checks are made on the Primary and Standby sides.

Step2:

Stop the Redo-Apply on the Standby.

Step3:

Convert Physical Standby to Snapshot Standby.

Step4:

Check the databse role and Flashback status.

Step5:

The created restore point’s storage size can be seen.

Step6:

Check databases modes.

Step7:

Check if the flashback logs start to created.

Step8:

Change Standby Database Mode as Open.

Step9:

Check the Log Sequence numbers for both sides.

Step10:

Create a new table.

Step11:

On the primary side, perform log switch operation on each instance and to check whether Redo-Transport is working properly.

Convert Physical Standby to Snapshot Standby using Dgmgrl

Step1:

Check the Configuration.

Step2:

Convert Physical Standby to Snapshot Standby using dgmgrl.

Step3:

Check the configuration again.

Step4:

Check the Flashback status.

Step5:

Check the database role.

Sometimes, when it is checked from the Broker after this convert operation, the following warnings about the configuration can be received. A few minutes after these warnings, errors appear to solved on their own without any intervention from the DBA. The cause and solution of the error is below.

ORA-16778: redo transport error for one or more databases

Step1:

Convert Physical Standby to Snapshot Standby using dgmgrl.

Step2:

Check the configuration.

Step3:

Check the archives’ status in Primary and Snapshot.

Step4:

Check if the logs are transferred to snapshot by performing the Log Switch.

Note: We have seen that the logs are not transferred to the Snapshot database.

Step5:

Check RedoRoutes from Primary Far SYNC.

There is no error related to Redo Transport. When the error is investigated, it is understood that it is caused by the “TransportDisconnectedThreshold” and “ReopenSecs” parameters.

TransportDisconnectedThreshold: Indicates that the last communication with the Primary has exceeded the specified threshold. After this exceed, Primary cannot send redos to this location. Default is 30 seconds.

ReopenSecs: If one of the destinations where the Redos will be sent fails, it determines how many seconds later it will try to send. It is 300 seconds in the default.

MaxFailure: It determine maxfailure count. If you set 3 , it tries 3 times to send redos even if it has failed. Default is 0. So it tries endless times.

Step7:

The ReopenSecs parameter is reduced to 30 seconds. After this parameter is set, sending logs is started.

In addition, the TransportDisconnectedThreshold parameter is set to 300 seconds so that no errors are received.

Step8:

Check the configuration again.

Convert Snapshot Standby Back to Physical Standby

Step1:

Shut down all Snapshot Standby Instances

Step2:

Start only the first Snapshot Standby Instance in mount mode.

Step3:

Convert snapshot standby back to physical standby .

The logs are as follows.

Step4:

Close Standby – 1 again and start in mount mode.

Step5:

Start Standby -2 in mount mode.

Step6:

If you will use Active Data Guard, open the databases.

Step7:

Check the existence of the table created when we convert it to Snapshot Standby.

Step8:

Start recovery.

The logs are as follows.

Step9:

Check for flashback logs and restore points.