[Data Guard] Snapshot Standby Database

Since 11g, Oracle supports converting physical standby database to "snapshot" standby database and vice versa, which is quite helpful. As though physical standby database can be opened for access, the databse is just read-only, we cannot make changes to the database. If, for some reason, we find some bugs in the production db env and we want to have some debug. Operating on the prod db directly is not a good idea, exp/imp prod db is too time consuming. Snapshot standby database come into help under such circustance. 

Let's set up a snapshot standby database now. 

First create another physical standby database (let's call it snapshot), refer to this article for how to do this.

After opening the "snapshot" and start the real time redo apply, we need to make the "primary" database aware of a new standby database need to be synchorized. Issue the following statements in the "primary" database to do this...

SQL> show parameter log_archive_con

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(primary, standby)
SQL> alter system set log_archive_config='DG_CONFIG=(primary, standby, snapshot)';

System altered.

SQL> alter system set log_archive_dest_3='service=snapshot LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name=snapshot';

System altered.

SQL> alter system set log_archive_dest_state_3='ENABLE';

System altered.

SQL>

 

 Then let's convert the "snapshot" from physical standby databse to snapshot database. It's quite simple, just issuing the command "alter database convert to snapshot standby". However, please note that after conversion, the databse will be put in "mounted" state. You need to put it into "open" again for access.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
snapshot

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

 

 Now since the databse is "read write", we can make changes to the database. Like...

SQL> show user
USER is "SYS"
SQL> select * from test;

no rows selected

SQL> drop table test purge;

Table dropped.

SQL> create table test2(id int);

Table created.

SQL> insert into test2 values(1);

1 row created.

SQL> commit;

Commit complete.

 

 I dropped the table "test" and created a new table "test2". Since we changed the state of the "standby" database, what if we convert the snapshot standby database to physical standby database? Will the change be lost? Let's try and see.

SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


SQL> alter database close;
alter database close
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter database close;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database convert to physical standby;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>


C:\Documents and Settings\yufa>set oracle_sid=snapshot

C:\Documents and Settings\yufa>sqlplus sys/a as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 9 14:00:07 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  296513536 bytes
Fixed Size                  2254504 bytes
Variable Size             184551768 bytes
Database Buffers          100663296 bytes
Redo Buffers                9043968 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> select * from test;

no rows selected

SQL> select * from test2;
select * from test2
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

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

Database altered.

SQL>

 

 After we convert the snapshot standby database back to physical standby database again, we can see that the changes we made (drop table "test" and create new table "test2") had lost! This is just as what we expected, otherwise the standby database and primary database will be in different state.

Please also note that after coversion(alter database convert to physical standby), the database need to be restarted!