Oracle 19c-Step by Step Configure Snapshot Standby Database

Description:-

  • Oracle Database 11g introduced the Snapshot Standby feature in Data Guard
  • A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
  • A snapshot standby database receives and archives, but does not apply,
    redo data from a primary database.
  • The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Dataguard Configuration:-

Step:1 Check both primary and standby role.

Primary:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME     DATABASE_ROLE     OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN            class             PRIMARY         READ WRITE

Standby:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS         INSTANCE_NAME    DATABASE_ROLE        OPEN_MODE
------------ ---------------- ----------------   --------------------
MOUNTED        class            PHYSICAL STANDBY     MOUNTED

Step 2:- Check the archived sequence in standby,

select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1              37

Step 3:- Check the flashback status and db_recovery_file_set location,

select flashback_on from v$database;

show parameter db_recovery_file_dest

SQL> show parameter db_recovery_file_dest

NAME                          TYPE            VALUE
-------------------------- ----------- ------------------------------
db_recovery_file_dest       string      /u01/app/oracle/recovery_dest/
db_recovery_file_dest_size  big integer            2G


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step:4 In standby side,stop the mrp process.

 alter database recover managed standby database cancel;

SQL> recover managed standby database cancel;
Media recovery complete.

Step:5 Bounce the database and keep in mount stage,

shut immediate

startup mount

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 583008256 bytes
Database Buffers 348127232 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:6 Convert to snapshot standby database

alter database convert to snapshot standby

SQL> alter database convert to snapshot standby;

Database altered.

Step:7 Open the standby database in READ/WRITE mode

alter database open

SQL> alter database open;

Database altered.

Step:8 Check the DATABASE_ROLE and OPEN_MODE,

select status,instance_name,database_role,open_mode from v$database,v$Instance

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME     DATABASE_ROLE       OPEN_MODE
------------ ----------------  ----------------  --------------------
OPEN            class          SNAPSHOT STANDBY    READ WRITE

Step:9 Testing on the snapshot standby database,

create user test identified by test;
grant connect,resource to test;
conn test/test
create table t1(sno number, name varchar2(20));
insert into t1 values (1,’example’);
commit;

SQL> alter user test quota unlimited on USERS;

User altered.

SQL> conn test/test;
Connected.

SQL> create table t1(sno number, name varchar2(20));

Table created.

SQL> insert into t1 values (1,'example');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

SNO           NAME
---------- --------------------
1             example

SQL>

Step:10 Both side verify the archived sequence

Primary side
select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
----------  --------------
1              47

Standby side

select thread#,max(sequence#) from v$archived_log group by thread#;
select process,status,sequence# from v$managed_standby;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1               47

Step 11: In Standby Side,bounce and mount the database

shut immediate
startup mount

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:12 Convert the snapshot standby database to physical standby database,

alter database convert to physical standby;

SQL> alter database convert to physical standby;

Database altered.

Step:13 Bounce the database,

shut immediate
startup

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.

Step:14 Check the standby database_role and mode.

select status,instance_name,database_role,open_mode from v$database,v$Instance;

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME    DATABASE_ROLE      OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN          class           PHYSICAL STANDBY    READ ONLY

Step:15 Enable the MRP process
alter database recover managed standby database disconnect from session;

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step:16 After convert to physical standby database,check the table status

Note:  After convert back to Physical standby database  once again restart the MRP process.

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

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Leave a Reply

Your email address will not be published. Required fields are marked *