Oracle 11g Snapshot Standby Database (Read- Write mode for standby db)

Oracle 11g Dataguard introduced powerful new features viz. Active Data Guard and Snapshot Standby Database. Snapshot standby database specially is an amazing feature. Where you can convert your standby database into read-write mode while the log shipping is happening. Once you finish your testing (feel free to drop tables, run rogue update statements or anything under the sun) you can tell the database to apply the already received logs.  You never lose a single transaction. Because in the background the archivelogs are being shipped,  just not being applied. I will demonstrate how to convert you physical standby database into snapshot database and refresh it again.

1. Connect to the Standby database

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
—————- ——————–
PHYSICAL STANDBY NOT ALLOWED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> alter system set db_recovery_file_dest=’E:\recovery_dest’ scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=5g;

System altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 5G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest=’location=E:\recovery_dest’ scope=spfile;

System altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      E:\recovery_dest
db_recovery_file_dest_size           big integer 5G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> alter database mount standby database;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
MOUNTED              SNAPSHOT STANDBY

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
READ WRITE           SNAPSHOT STANDBY

After you finish your testing, convert the snapshot database back to physical standby again.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1383768 bytes
Variable Size             234881704 bytes
Database Buffers          536870912 bytes
Redo Buffers                5251072 bytes

SQL> alter database mount;

Database altered.

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  778387456 bytes
Fixed Size                  1383768 bytes
Variable Size             234881704 bytes
Database Buffers          536870912 bytes
Redo Buffers                5251072 bytes

SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
MOUNTED              PHYSICAL STANDBY

 

Helpful Link:

http://repettas.wordpress.com/2008/10/24/rman-11g-snapshot-standby-database/

Advertisements

One thought on “Oracle 11g Snapshot Standby Database (Read- Write mode for standby db)

  1. In snapshot standby database,if I modify some data in a table,but the same table in primary database is not modified.
    Then I convert standby database to a physical standby database,what will be happen about the table in the standby database.

    Like

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