Snapshot standby:
Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database. i. e we can convert the physical standby database to snapshot standby. On that, we can do all types of testing or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.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.
DEMONSTRATION:
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 12c version
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
mydb1 MOUNTED
mydb2 MOUNTED
CHECKING RECOVERY AREA AND ALLOCATE SIZE
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
db_recovery_file_dest string +EXA_RECO
db_recovery_file_dest_size big integer 400G
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
CHECKING THE STATUS OF THE DATABASE
NOW WE ARE STOPPING & CONVERTING SNAPSHOT DATABASE IN TO PHYSICAL 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 mydb
$ srvctl start database -d mydb -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_12/25/2018 10:30:00 20539509
25-DEC-18 10.30.00.000000000 AM
Now CREATE 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 test;
COUNT(1)
100786
SQL> delete test where owner='SAMI MALIK';
100786 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from TEST;
COUNT(1)
100111
NOW WE ARE STOPPING & CONVERTING SNAPSHOT DATABASE IN TO PHYSICAL STANDBY DATABASE
$ srvctl stop database -d MYDB
# 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 12.2.0.4.0 Production on Mon Dec 25 10:40:11 2018
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 mydb
$ srvctl start database -d mydb
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
