Step by step guide to convert physical standby database into snapshot standby database (READ WRITE mode)

Step by step guide to convert physical standby database into snapshot standby database (READ WRITE mode)

What is Standby snapshot Database?
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database
receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back
into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the
snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a
physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users
to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted
into a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby
database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means the transactions
which were made in standby database while it was open in READ WRITE mode will be flushed out.

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled.
Below are the steps on how to convert a physical standby database to a snapshot standby database and viceversa.

Oracle version :-  11.2.0.1.0
Primary Database : prim
Standby Database : stand

Lets find out few datails from both database.
On primary database:-

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

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         prim             PRIMARY          READ WRITE

Find out current Log sequence  number.
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
  THREAD# MAX(SEQUENCE#)
---------- --------------
         1             23


Now at Standby database :-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS       INSTANCE_NAME     DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         stand            PHYSICAL   STANDBY READ ONLY WITH APPLY

Current Log sequence number at standby :-

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             23

Also lets find out if Flashback is enabled or not.
SQL>  select flashback_on from v$database;

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


So we can see that the standby database in sync with the primary database.
Also the output of below command shows that the Flash Recovery Area is configured on standby database.

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


Now Cancel the MRP Process in Standby database and restart it into mount stage.
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  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.



Now Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

SQL> alter database convert to snapshot standby;

Database altered.

Now open the database and check its open 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         stand            SNAPSHOT STANDBY READ WRITE

So we can see the database has been opened into read write mode.
Now lets test the scenario
Here on standby database we will do the following things.
1. Create a user called "snapshotstd"
2. Create a table called "test" whose owner is "snapshotstd" and insert some records in it.

SQL> create user snapshotstd identified by snapshotstd;

User created.

SQL> grant connect , resource to snapshotstd;                                                                

Grant succeeded.

SQL> conn snapshotstd/snapshotstd
Connected.
SQL> create table employees (name char(20) , Age number );

Table created.

SQL> insert into employees values ('SOUMYA' , '30' );

1 row created.
SQL> insert into employees values ('UDIT' , '24');

1 row created.

SQL> insert into employees values ('ABHISHEK' ,'25');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from employees;

NAME         AGE
------------ ----------
SOUMYA       30
UDIT         24
ABHISHEK     25

SQL> update employees set age=25 where name ='UDIT';

1 row updated.

SQL> Commit;

Commit complete.

SQL> select * from employees;

NAME         AGE
------------ ----------
SOUMYA       30
UDIT         25
ABHISHEK     25                                                    

Now at primary database:-
Lets update a table on primary database and see if its going to be replicated on physical standby when the database is converted.

SQL> conn  soumya/soumya
connected

SQL> create table xyz (id number);

Table created.

SQL> insert into xyz values (1);

1 row created.

SQL> /

1 row created.

SQL> commit;

On primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26


On standby database:-

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

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              25
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING              26
RFS       IDLE                  0
RFS       IDLE                 27

So we can see that the redo data from the primary database is received by the snapshot standby database but it was has been applied.


At standby database shut down the dbase and start it in mount stage:-
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

Now Lets convert back the snapshot standby database into physical standby database.
SQL> alter database convert to physical standby;

Database altered.

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

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 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         stand            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      CLOSING              28
RFS       IDLE                  0
RFS       IDLE                 29
MRP0      WAIT_FOR_LOG         29

7 rows selected.

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             28


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

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             28

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

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             28

Now we can see

SQL> select * from snapshotstd.employees;
select * from snapshotstd.employees
                          *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Select username,account_status from dba_users where username='SNAPSHOTSTD';

no rows selected


We can see above that the transactions that were carried out when the standby database was opened in READ WRITE mode are flushed out after it was converted back to physical standby
database mode.

Also,
SQL> select * from soumya.xyz;

        ID
----------
         1
         1

So also we see when the standby database was converted into snapshot database, the datas updated in primary database has also replicated into physical standby database now.

Done..




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

1 comment:

  1. If you need to connect to database, you can use different tools such as bigcommerce connector, oracle connector. It depends on your database.

    ReplyDelete