How to Convert Physical Standby to Snapshot Standby and Vice Versa ( using DGMGRL )

In DataGuard Broker, this process is very simple, you just type a few commands, re-start your DB and you are ready to test this amazing feature!!!

What Happens in Snapshot Standby Mode ??

The Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.

Note : The Flashback Database feature is required to create a snapshot standby database.

If Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database.

The Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.

So we need to keep in mind that whatever limitations are present in the Flashback database technology, the same will also be inherent in the Snapshot database feature.

The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required.

Step 1- Connect to DG Broker

$ dgmgrl

DGMGRL> connect sys
 

DGMGRL> show configuration

Step 2- Convert the DB.

After the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode

DGMGRL> convert database 'STDBY' to snapshot standby;

Step 3- Verify that the database was successfully converted by issuing the

SHOW CONFIGURATION command:

DGMGRL> show configuration

Configuration - prod_dg
 

  Protection Mode: MaxPerformance
 

Databases:

    PROD  - Primary database

    STDBY – Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status: SUCCESS

Now Let’s see,

How to Convert Snapshot Standby back to Physical Standby with DGMGRL

The Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.

Note:

A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.

Step 1- Connect to DG Broker.

You should be on the primary server to complete this step.

$ dgmgrl

DGMGRL > connect sys

Step 2- Convert the DB back to physical

DGMGRL > convert database 'stdby' to physical standby;

 Step 3- Verify the status, that the database was successfully converted by issuing the SHOW CONFIGURATION command:

DGMGRL> show configuration

Configuration - prod_dg
 

  Protection Mode: MaxPerformance
 

Databases:

    PROD  - Primary database

    STDBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status: SUCCESS

NOTE:

You may get the following message during the conversion process:

“Converting database "stdby" to a Physical Standby database, please wait...

Operation requires shutdown of instance "STDBY" on database "stdby"

Shutting down instance "STDBY"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "STDBY" on database "stdby"

Starting instance "STDBY"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:

        start up and mount instance "STDBY" of database "stdby“

If so, then connect to the STDBY and startup and mount the instance:

SQL> startup mount

Click here to Join Our Blog and Stay Updated

Configure Standby in case you re-create redologs of Primary site

If you resize the primary Redolog files, and you have a Standby configured. Then you need to take care of your Standby database also.

So, the standby logs also need to be resized to match the online redo logs in the Primary database.

Follow the below steps (after you resize your primary RedoLogs)

STEPS TO ACHIEVE THIS

1. Drop and recreate online redologs on the PRIMARY site.

2. On primary site, create the standby controlfile –

SQL> alter database create standby controlfile as ‘/oradata/prim/standby01.ctl’;

3. Copy the standby control file to standby site using the scp command

4. Cancel the recovery(MRP)  &   shutdown immediate the standby database

5. Startup and mount the standby database -using the newly standby controlfile

5. Defer recovery until standby logs are created..… but do not start managed recovery right now.

6. Add the standby logs with same size as Primary online redologs.

7. The online logs on the standby side will be created at switchover time so no need to do anything now.

8. Start recovery on standby site.. (MRP)

Pre-check for DataGuard switchover Process

Please perform these pre-requisite checks before undertaking a switchover to primary.

Ø   ON STANDBY SITE:

The database_role must be “PHYSICAL STANDBY”  . . . check this using below query

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------------

PHYSICAL STANDBY

Note: If you receive a reply like below

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------------

PRIMARY

Then do not proceed if this message is received …in this case you are most probably on the primary site..

Next Check, Again on STANDBY SITE:

While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alertlog file,.. but make sure it is set to ENABLE before starting a switchover.

The below command can be issued to convert it to ENABLE

sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

Next Check :

If any of the datafiles are in RECOVER status, DO NOT PROCEED with the switchover…

SQL> select distinct status from v$datafile;

STATUS

------------

ONLINE

ONLINE

SYSTEM

Next Check:

If the STANDBY database has been opened in READ ONLY mode, then do NOT proceed.

If the database is in mount mode, then only proceed.

(This is to ensure that the logs are being shipped and more importantly the LAST LOG archived has been applied…)

QL> select distinct open_mode from v$database;

OPEN_MODE

-------------------

MOUNTED

Now,.. On PRIMARY site

SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED

——————

25

And  on STANDBY site.

SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED

—————-

25

Both the values are same, so you can Proceed for the Switchover.

Click here to Join Our Blog and Stay Updated

Dataguard Modes

In some situations, a business cannot afford to lose data. In other situations, the availability of the database may be more important than the loss of data. Some applications require maximum database performance and can tolerate some small amount of data loss. The following descriptions summarize the three distinct modes of data protection.

Maximum protection This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.

Maximum availability This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one transactionally consistent standby database. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected, and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode. This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum performance This protection mode (the default) provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.  When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance. The maximum protection and maximum availability modes require that standby redo log files are configured on at least one standby database in the configuration.

 All three protection modes require that specific log transport attributes be specified on the LOG_ARCHIVE_DEST_n initialization parameter to send redo data to at least one standby database