Converting Physical Standby Database into Snapshot Standby Database

There are three
restrictions where we cannot convert to a snapshot.

a) If it is the
only standby of a Maximum Protection configuration.
b) If it is the target of
a current switchover operation.
c) If it is the designated failover database
for Fast Start Failover (FSFO).

If this is your only standby database,
if you have to failover to it, the failover is going to take longer – because
the standby has to be flashed back, and caught up, before the transition can
complete, be aware that worst case scenario. It’s always recommended considering a Data Guard
configuration that consists of two physical standby databases (at least), one is
always kept physical standby database, and the other can be used/tested as a
snapshot standby.

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

Step 1: Check if Flashback is enabled or not.
This conversion is
achieved using flashback database, but the physical standby database does not
need to have flashback database explicitly enabled. Even if the flashback is not
enabled explicitly the conversion will work.

i) SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from
v$database;
NAME OPEN_MODE
DATABASE_ROLE
—————————————- ———-
—————-
PHYSTD2 MOUNTED PHYSICAL STANDBY

ii) SQL> Show parameter
db_recovery_file_dest
NAME TYPE
VALUE
—————————————————————
———– ——————————
db_recovery_file_dest string
/u01/oradata/phystd2/FRA/
db_recovery_file_dest_size big integer
5G


iii) SQL> select flashback_on from
v$database;
FLASHBACK
———
YES

Step 2: Stop
Redo Apply, if it is active.
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 3: 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 4: Ensure that the database is mounted.

Step 5: Perform the conversion from physical standby to snapshot
standby database:
The conversion from
physical standby to snapshot standby database and vice versa can be done in
three ways.

I). Manual method. This can be done by
issuing the below SQL command in the physical standby database. Once it is converted the database can
be opened in read/write mode.
SQL> ALTER
DATABASE CONVERT TO SNAPSHOT STANDBY;


II). Data Guard Broker
A physical standby
database that is managed by the Data Guard broker can be converted into a
snapshot standby database using either DGMGRL or Oracle Enterprise Manager.

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

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

DGMGRL> CONVERT
DATABASE phystd2 TO SNAPSHOT STANDBY
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

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

Step 6: The
database is dismounted during this conversion and we have to restart the
database, which will be the snapshot standby database.
SQL>
startup

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

Meanwhile it will
be receiving the redo from the
primary, which will be automatically applied when
snapshot standby database is converted back to physical standby
database.

When we convert a
physical standby database to snapshot standby database, then 
  • Redo gap
    detection and resolution will work as before.
  • A snapshot standby
    database cannot be the target of a switchover or failover. A snapshot standby
    database must first be converted back into a physical standby database before
    performing a role transition to it.
  • In case of role
    transition of one of the standby database (like failover or switchover), the
    snapshot standby database continues to receive the
    redo from the new
    primary.
  • Even if the
    primary database has a new incarnation (because of
    Flashback database or Open
    resetlogs), the snapshot database continues to receive the
    redo from the
    primary.
Recent Posts