Convert Physical Standby to Snapshot Standby And Viceversa

       Convert Physical Standby to Snapshot Standby And Vice versa

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

Primary database Details

Oracle Database version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Primary database name:orcl

Primary database Instance_name:orcl

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         orcl             PRIMARY          READ WRITE

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             18

Standby database Details:

Oracle Database version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Standby database name:orcl

Standby database instance_name:stdby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         stdby            PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             18

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_

                                                 area

db_recovery_file_dest_size           big integer 4560M

Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  629145600 bytes

Fixed Size                  2927528 bytes

Variable Size             494928984 bytes

Database Buffers          125829120 bytes

Redo Buffers                5459968 bytes

Database mounted.

Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

SQL> alter database convert to snapshot standby;

Database altered.

Step 3: You can now open the snapshot standby database and check its mode.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         stdby            SNAPSHOT STANDBY READ WRITE

Small Test on the snapshot standby database.
1. Create a user called “snaptest”
2. Create a table called “TEST” whose owner is “SNAPTEST” and insert some records in it. You can also update some of the records as well.

SQL> create user snaptest identified by oracle;

User created.

SQL> grant connect,resource to snaptest;

Grant succeeded.

SQL> conn snaptest/oracle

Connected.

SQL> create table emp(empno number,name char(20));

Table created.

SQL> insert into emp values(10,'RAMESH');

1 row created

SQL> insert into emp values(11,'REDDY');

1 row created

SQL> commit;

Commit complete.

SQL> select * from emp;

EMPNO       NAME

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

10        RAMESH

11        REDDY

SQL> update snaptest.emp set empno=50 where name='RAMESH';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from snaptest.test;

CODE       NAME

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

50        RAMESH

11        REDDY

In the mean time, you can also see that the redo data from the primary database is received by the snapshot standby database but would not be applied.

On primary database the latest sequence generated is 19 and that on the standby database, the RFS process is idle for sequence 19.

Primary

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             19

Standby

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CLOSING              19

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE                  0

RFS       IDLE                 20

RFS       IDLE                  0

7 rows selected.

Steps on converting back a snapshot standby database to physical standby database.

Step 1: Shut down the snapshot standby database and open it in Mount mode.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  629145600 bytes

Fixed Size                  2927528 bytes

Variable Size             494928984 bytes

Database Buffers          125829120 bytes

Redo Buffers                5459968 bytes

Database mounted.

Step 2: Convert the snapshot standby database to physical standby database.

SQL> alter database convert to physical standby;

Database altered.

Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  629145600 bytes

Fixed Size                  2927528 bytes

Variable Size             494928984 bytes

Database Buffers          125829120 bytes

Redo Buffers                5459968 bytes

Database mounted.

Database opened.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE

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

OPEN         stdby            PHYSICAL STANDBY READ ONLY

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE                  0

MRP0      APPLYING_LOG         20

6 rows selected.

Crosscheck whether the physical standby database is in sync with the primary database.

On Primary database:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             19

On Standby database:

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1             19

You can see below that the transactions that were carried out when the standby database is opened in READ WRITE i.e after creating snapshot standby mode are flushed out after it was converted back to physical standby database mode.

SQL> select * from snaptest.emp;

select * from snaptest.emp

                       *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select username,account_status from dba_users where username='SNAPTEST';

no rows selected