How to change Physical standby to Snapshot standby database in Oracle Data Guard

Snapshot standby is a feature of Oracle Data Guard. It introduced in Oracle 11g and allows the standby database to be opened in Read - Write mode.

After changing a physical standby database to snapshot standby it can be used as a normal read write database.

When this snapshot standby switched back to standby mode , all changes which you have done in read write mode will be lost. This is achieved by Flashback database, but its not necessary that flashback database has to be enabled to achieve this.

Prerequisites of Snapshot standby :-

1. Managed recovery has to be disabled.
2. Standby database has to be in MOUNT mode.

Steps to create a snapshot standby from Physical standby database :-

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

Now we can use this database as a normal database.

SQL> create table amit
  2  (
  3  id number(12),
  4  name varchar(23));

Table created.

SQL> insert into amit values('&id','&name');
Enter value for id: 1
Enter value for name: f
old   1: insert into amit values('&id','&name')
new   1: insert into amit values('1','f')

1 row created.

SQL> /
Enter value for id: 2
Enter value for name: gf
old   1: insert into amit values('&id','&name')
new   1: insert into amit values('2','gf')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from amit;

        ID NAME
---------- -----------------------
         1 f
         2 gf

We will get below mentioned output in alert log of standby database after snapshot standby process completed :-

Thu Jul 25 15:17:38 2013

RFS[145]: Assigned to RFS process 7930216

RFS[145]: Identified database type as 'snapshot standby': Client is ARCH pid 12648536

Thu Jul 25 15:17:39 2013

To convert it back to physical standby, we will lose all changes which we have made in sanpshot standby mode.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             817891832 bytes

Database Buffers          213909504 bytes

Redo Buffers                9871360 bytes

Database mounted.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2213384 bytes

Variable Size             817891832 bytes

Database Buffers          213909504 bytes

Redo Buffers                9871360 bytes

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON

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

NO

Snapshot standby database switched to Physical standby database. Test redo transport with Primary database :-

SQl> alter system switch logfile;

System altered.

PFB output in alert log of Physical standby dataabase :-

RFS[10]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 60030994

Primary database is in MAXIMUM PERFORMANCE mode

RFS[10]: Selected log 4 for thread 1 sequence 31 dbid 2119844559 branch 821698130

Thu Jul 25 15:30:12 2013

Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_25/o1_mf_1_30_8z1xs2tg_.arc

Media Recovery Waiting for thread 1 sequence 31 (in transit)

Standby Database has been switched back successfully to Physical standby from Snapshot standby.

I hope this article helped you

Regards,

Amit Rath