Snapshot Standby Database help physical standby to open in READ-WRITE mode easily
A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database.A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.
Using a single command change made while the database is in read-write mode can throw away the changes made to the standby database only and re-synchronize the standby database with the production database.
1. Snapshot standby database receives and archives, but does not apply the redo data.
2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.
3. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
Steps to convert Physical Standby Database to the Snapshot Standby Database
I have 2 node primary cluster database and 2 node standby cluster database with the ASM. It is running on 11gR2
Note: In the snapshot standby database, there is no step to be performed in the primary database. all the steps are performed only in the physical standby database
STATUS OF THE STANDBY DATABASE
SQL> select name,open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
BHU MOUNTED
BHU MOUNTED
CHECKING RECOVERY AREA AND ALLOCATE SIZE
SQL> show parameter db_recov
NAME TYPE VALUE
------------------------------------ ----------- -------------db_recovery_file_dest string +BHU_RECO
db_recovery_file_dest_size big integer 4G
CHECKING WHETHER FLASHBACK IS ENABLED OR NOT. TO OPEN A STANDBY DATABASE IN THE READ/WRITE MODE, WE NEED TO HAVE THE FLASHBACK WITH ENOUGH SIZE.
SQL> select flashback_on from gV$database;
FLASHBACK_ON
------------------
YES
YES
CHECKING THE STATUS OF RECOVERY PROCESS, IF IT IS ENABLED THEN WE HAVE TO STOP THE RECOVERY.
SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';
PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK#
--------- -------- ---------- ---------- ----------
RFS LGWR 2 74 6080
MRP0 N/A 2 74 6078
RFS LGWR 1 78 7145
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
AFTER CANCELING THE RECOVERY PROCESS, CHECK THE STATUS
SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';
PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK#
--------- -------- ---------- ---------- ----------
RFS LGWR 2 74 6150
RFS LGWR 1 78 7216
CONVERTING THE STANDBY DATABASE AS A SNAPSHOT STANDBY DATABASE
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
CHECKING THE STATUS OF THE DATABASE
SQL> select open_mode,database_role from gv$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED SNAPSHOT STANDBY
MOUNTED SNAPSHOT STANDBY
$ srvctl stop database -d BHU_b
$ srvctl start database -d BHU_b -o open
CHECKING THE STATUS OF THE DATABASE
SQL> select open_mode,database_role from gv$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE SNAPSHOT STANDBY
READ WRITE SNAPSHOT STANDBY
CHECKING THE SYSTEM CREATED THE RESTORE POINT
SQL> select NAME,SCN,TIME from v$restore_point;
NAME SCN
---------------------------------------------------------------------
TIME
---------------------------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_11/21/2011 10:48:00 20539509
21-NOV-11 10.48.00.000000000 AM
I AM CREATING SOME TABLES & PERFORMING SOME DML OPERATIONS IN THE SNAPSHOT DATABASE
SQL> create table bhuvan as select * from dba_objects;
Table created.
SQL> select count(1) from bhuvan;
COUNT(1)
----------
17065
SQL> delete bhuvan where owner='BHUVAN';
9699 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from bhuvan;
COUNT(1)
----------
7366
NOW WE ARE STOPPING & CONVERTING SNAPSHOT DATABASE IN TO PHYSICAL STANDBY DATABASE
$ srvctl stop database -d BHU_b
# I am using single instance to perform the conversion from the snapshot standby database to the physical standby database
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 21 10:53:16 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.4431E+10 bytes
Fixed Size 2240272 bytes
Variable Size 3892314352 bytes
Database Buffers 1.0503E+10 bytes
Redo Buffers 34148352 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
Once we convert from the snapshot standby database to the physical standby database, database will go to the no mount stage.
SQL> select open_mode,database_role from v$database;
select open_mode,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
NOW WE ARE STOPPING & STARTING THE DATABASE TO MOUNT STAGE AND CHECKING THE RECOVERY PROCESS
$ srvctl stop database -d BHU_b
$ srvctl start database -d BHU_b
CHECKING THE RESTORE POINT, SYSTEM WILL REMOVE IT AUTOMATICALLY ONCE WE ARE CONVERTED TO PHYSICAL STANDBY DATABASE
SQL> select NAME,SCN,TIME from v$restore_point;
no rows selected
CHECKING THE RECOVEY PROCESS
SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';
PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK#
--------- -------- ---------- ---------- ----------
RFS LGWR 2 83 3495
RFS LGWR 1 87 4407
MRP0 N/A 2 83 3495
No comments:
Post a Comment