Advertisements
Skip to content
February 4, 2013 / Shivananda Rao P

Snapshot Standby database and how to convert physical standby database to snapshot standby database (READ WRITE mode)

Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions. Quite often we have the standby database opened in READ Only mode for reporting purposes and optionally have the active dataguard implemented, but a snapshot standby database can be used to perform both READ and WRITE activities. Most importantly, a snapshot standby database keeps receiving the redo data from the primary database but does not apply them. These redo data received from the primary database would be applied only when the snapshot standby database is converted back to the Physical standby mode. There by the snapshot standby database provides data protection on primary database.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is a mirrored copy of the primary database). This allows the users to test the application on a standby database which has the primary data before implementing it in the Real production environment. When a physical standby database is converted to 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 that the transactions that were made when the standby database was opened 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 Database version: 11.2.0.3 Enterprise Edition
Primary database: SRPRIM
Details with respect to the primary database:


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

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

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

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

Standby database Details:
Oracle database version: 11.2.0.3 Enterprise Edition
Standby database name: SRPS
Details with respect to the physical standby database:


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

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- ----------------
OPEN         srps             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            206

SQL>
SQL> select flashback_on from v$database;

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

You can observe that the standby database is in sync with the primary database. Below outcome shows that the Flash Recovery Area is configured on the physical standby database.


SQL> show parameter db_recovery_file_dest

NAME                         TYPE         VALUE
---------------------------  -----------  -------------
db_recovery_file_dest        string       +FRA_NEW
db_recovery_file_dest_size   big integer  4122M

SQL>

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
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             805306608 bytes
Database Buffers          452984832 bytes
Redo Buffers                8847360 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        srps            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@srps
Connected.
SQL>
SQL> create table test(code number, name char(20));
Table created.

SQL> insert into test values (100,'ARUN');
1 row created.

SQL> insert into test values(200,'SHIVU');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;
CODE       NAME
---------- --------------------
100        ARUN
200        SHIVU

SQL> update snaptest.test set code=500 where name='ARUN';
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from snaptest.test;
CODE       NAME
---------- --------------------
500        ARUN
200        SHIVU

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 208 and that on the standby database, the RFS process is idle for sequence 209.

Primary:


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

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

Standby:


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

PROCESS   STATUS        SEQUENCE#
 --------- ------------ ----------
 ARCH      CLOSING               1
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 RFS       IDLE                  0
 RFS       IDLE                209
 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
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             805306608 bytes
Database Buffers          452984832 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL>

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-01507: database not mounted

ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             805306608 bytes
Database Buffers          452984832 bytes
Redo Buffers                8847360 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         srps            PHYSICAL STANDBY  READ ONLY

SQL> alter database recover managed standby database disconnectfrom 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
 RFS       IDLE                  0
 RFS       IDLE                  0
 MRP0      WAIT_FOR_LOG        213

8 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 bythread#;
THREAD#    MAX(SEQUENCE#)
---------- --------------
1          212

On Standby database:


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

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


SQL> select * from snaptest.test;
select * from snaptest.test
*
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

Here you go !!

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

12 Comments

Leave a Comment
  1. Ashish / Feb 5 2013 7:23 am

    Nice Docs…..
    I have a query …I have DG setup which contain 1 prim and 1 Standby which in mount state…So can i change Stdby Db(mount) to Snapshot DB?

    Regards,
    Ashish

  2. Shivananda Rao P / Feb 5 2013 7:40 am

    Hello Ashish,

    Yes, you can have it converted to snapshot and revert it back to physical standby.

    Regards,
    Shivananda

  3. Ashish / Feb 5 2013 11:04 am

    Thanks Shiva….

    Can you provide me docs related sql tuning?

    Regards,
    Ashish

  4. Mahir M. Quluzade / Feb 5 2013 11:29 am

    Hi,

    This is very good feature include Oracle Data Guard 11g.

    I want share with you my video tutorial same your topic : http://www.mahir-quluzade.com/2012/01/oracle-data-guard-11g-overview-snapshot.html

    Thanks
    Mahir M. Quluzade

    p.s. I saw you have deprecated parameter in your parameter files. I think your deprecated parameter is log_archive_start.

  5. Shivananda Rao P / Feb 5 2013 11:35 am

    Thanks Mahir. Yes, there is a deprecated parameter involved in the database but I’ve just ignored it as it was only for testing purpose.

    Regards,
    Shivananda

  6. Raheel / Feb 20 2014 10:44 am

    Dear Shiv Your Post is too good easy and understandable. i can perform failover switchover and snapshot standby with the help of your post and understand very well thanks for share knowledge.

    Regards,
    Syed Raheel ur Rehman

  7. Ashu / Feb 27 2014 4:05 pm

    This is f9.In snapshot standby.Does RFS recieve redo data from redo transport service.Or It recieving from ARC process..suppose (1) is right,then At the same point.If we do some transtion on standby transtion side.RFS righting both content on online redo (coming from production side and second one its own transaction) simultanously..can you please tell me what actually happens internally?

  8. lone / May 13 2014 9:57 am

    FLASHBACK should be ON

  9. Shivananda Rao P / May 18 2014 8:36 pm

    Hello,

    It is not required for you to have the FLASHBACK ON.
    Oracle automatically takes care. The same has been demonstrated in this article.

    Regards,
    Shivananda

  10. Joshua / Sep 22 2015 11:42 am

    Hello Shivananda

    When I run ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; it is taking forever to run. It has failed to run the above script. Could anyone be knowing the possible cause of this issue? Am using Oracle 11gR2 on SLES 11, data guard is setup well and logs are being applied so I needed to to have some tests on the standby database.

    Regards
    Josh

  11. Shivananda Rao P / Sep 22 2015 1:32 pm

    Hi Joshua,

    Just a few questions in order to understand what’s happening.

    1. Do you see any errors being written in the alert log of the standby ?
    2. Is the physical standby in mount and recovery cancelled ? Also, is it receiving the redo from the primary ?
    3. Is FRA configured and enough free space available ?

    Regards,
    Shivananda

  12. Joshua / Sep 22 2015 2:54 pm

    Hello Shivanand,

    1. The alert log file is not registering any errors
    2. The physical standby is in mount mode and I cancelled the apply process first
    3. The FRA is configured well and has a size of 20GB

    However after several attempts, recreated the alert log file restarted the database several times and tried again, it was successful.

    Thanks though.
    Josh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: