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: DCPROC
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: DRPROC
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 DRPROC 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> 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
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 DRPROC 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> 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.
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 DRPROC 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
No comments:
Post a Comment