Tuesday, December 25, 2018

Convert physical standby to snapshot standby database

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 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
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 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 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 11.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