DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, June 27, 2016

Converting Snapshot Standby Database into Physical Standby Database


Oracle uses restore point and Flashback Database feature to accomplish this conversion of snapshot standby database to physical standby database. Restore point and Flashback Database concepts were first introduced in Oracle 10g. When we convert the physical standby database to snapshot standby database a guaranteed restore point is created. This guaranteed restore point is used to flashback a snapshot standby database to its original state. The name of this guaranteed restore points begins with ‘SNAPSHOT_STANDBY_REQUIRED_’.

When the database is converted back, it is flashed back to the guaranteed restore point, put back into the full Data Guard configuration, and the redo is applied eventually catching the standby database back up to the primary. Once this is completed, the guaranteed restore point is removed automatically. Using the guaranteed restore point the snapshot standby database will be flashed back to its original state and subsequently the restore point will be dropped. After the conversion the redo will be applied which were received by the snapshot standby database.

Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.

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

Perform the following steps to convert a snapshot standby database into a physical standby database:

Step 1: Check for current database role
SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME      OPEN_MODE DATABASE_ROLE
----------------------------------------- ---------- ----------------
PHYSTD2 READ WRITE SNAPSHOT STANDBY

SQL> select name, guarantee_flashback_database from v$restore_point;
NAME                                                                       GUA
----------------------------------------------------------------- -------
SNAPSHOT_STANDBY_REQUIRED_08/18/2012 17:13:31   YES

Step 2: If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversion commands.

Step 3: Ensure that the database is mounted.

Step 4: Do the conversion of snapshot standby database to physical standby database.

I
). Manual method.
Issue the following SQL statement to convert the snapshot standby back to the physical standby:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

II). Data Guard Broker
DGMGRL> show configuration;

Configuration - fsfo_config

Protection Mode:     MaxAvailability

Databases:
 prima - Primary database
 phystd1 - Physical standby database
 phystd2 - Snapshot standby database

Fast-Start Failover: DISABLED

Current status:
SUCCESS

The DGMGRL command that converts the database is:
DGMGRL> CONVERT DATABASE standby_unique_name TO PHYSICAL STANDBY

DGMGRL> CONVERT DATABASE phystd2 TO PHYSICAL STANDBY
DGMGRL> show configuration;

Configuration - fsfo_config

Protection Mode:     MaxAvailability

Databases:
 prima - Primary database
 phystd1 - Physical standby database
 phystd2 - Physical standby database

Fast-Start Failover: DISABLED

Current status:
SUCCESS

III). Enterprise Manager (If the physical standby is managed by DGMGRL).

Step 5: Start database in mount, if not already mounted.
SQL> alter database mount standby database;

Step 6: Recover Physical Standby database
SQL> recover managed standby database disconnect from session;

Step 7: Check for database role
SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------------------------------- ---------- ----------------
PHYSTD2 MOUNTED PHYSICAL STANDBY

No comments: