Physical Standby Database To Snapshot standby Database

Convert Physical Standby Database To Snapshot standby Database

The difference between a read-only standby and a snapshot standby is that the snapshot standby is fully up dateable. It was possible in Oracle 10g to open a standby database as read-write but as from version 11g you now have the snapshot feature. This new feature makes it simpler to make the standby read-write and to revert to back again with the use of the Broker, also it is advised to use the flashback database feature as it makes life a whole lot simpler.

This snapshot standby database is fully updatable database and it is opened for read write operation. When the snapshot database is created from physical standby database, redo logs are still transferred to standby site but are not applied.

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

SBYPROD                        MOUNTED              PHYSICAL STANDBY

SQL> alter system set db_flashback_retention_target=1440;

System altered.

SQL>  Show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/flash_recovery

                                                 _area

db_recovery_file_dest_size           big integer 3882M

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

Make DATABASE FLASHBACK ON

SQL> alter system set db_flashback_retention_target=1440;

System altered.

SQL> alter system set db_recovery_file_dest_size=5g;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>  select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database flashback on;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL>  select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

SBYPROD                        MOUNTED              SNAPSHOT STANDBY

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

Database opened.

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

SBYPROD                        READ WRITE           SNAPSHOT STANDBY

****************************************************

Revert back to Physical Standby

****************************************************

First shutdown database

open in mount mode

convert in physical standby mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

AGAIN SHUTDOWN DATABASE AND OPEN IN MOUNT MODE

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  651378688 bytes

Fixed Size                  2216184 bytes

Variable Size             390074120 bytes

Database Buffers          255852544 bytes

Redo Buffers                3235840 bytes

Database mounted.

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

SBYPROD                        MOUNTED              PHYSICAL STANDBY

Recover Physical Standby database

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

ORA-01153: an incompatible media recovery is active

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

SQL> recover managed standby database disconnect from session;

Media recovery complete.

Check for database role

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

SBYPROD                        MOUNTED              PHYSICAL STANDBY

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area/sbyprod/

Oldest online log sequence     0

Next log sequence to archive   0

Current log sequence           42

CHECK MRP PROCESS

SQL> select PROCESS,STATUS from v$managed_standby;

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

MRP0      WAIT_FOR_LOG

33 rows selected.

Pankaj Yadav