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
Recent Posts