Description:-
- Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode.
- When switched back into standby mode,All changes made whilst in read-write mode are lost. This is achieved using flashback database,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.
Oracle 19c New feature-Oracle 19c Active Data Guard-DML Redirection Click Here
Environment Details:-
Let’s Start the Demo:-
Step1:-Show the Configuration
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 18 seconds ago)
Step2:-Issue the convert Command
SQL> select open_mode,instance_name,database_role from v$database,v$instance;
OPEN_MODE INSTANCE_NAME DATABASE_ROLE
——————– —————- —————-
READ WRITE chennai PRIMARY
SQL> select open_mode,instance_name,database_role from v$database,v$instance;
OPEN_MODE INSTANCE_NAME DATABASE_ROLE
——————– —————- —————-
READ ONLY WITH APPLY delhi PHYSICAL STANDBY
DGMGRL> convert database ‘delhi’ to snapshot standby;
Converting database “delhi” to a Snapshot Standby database, please wait…
Database “delhi” converted successfully
On standby side
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_10/26/2019 14:38:32
Step3:-Show the Configuration
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Snapshot standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 seconds ago)
SQL> select open_mode,instance_name,database_role from v$database,v$instance;
OPEN_MODE INSTANCE_NAME DATABASE_ROLE
——————– —————- —————-
READ WRITE delhi SNAPSHOT STANDBY
Step4:-Testing table on oracle 19c snapshot standby
[oracle@devdr19c DELHI]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Oct 26 14:42:55 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.4.1.0.0
SQL> select open_mode,instance_name,database_role from v$database,v$instance;
OPEN_MODE INSTANCE_NAME DATABASE_ROLE
——————– —————- —————-
READ WRITE delhi SNAPSHOT STANDBY
SQL> create table oracledbwr1 (n number);
Table created.
SQL> insert into oracledbwr1 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from oracledbwr1;
N
———-
1
Step5:-Convert backup to Physical standby database
DGMGRL> CONVERT DATABASE ‘delhi’ to PHYSICAL STANDBY;
Converting database “delhi” to a Physical Standby database, please wait…
Operation requires shut down of instance “delhi” on database “delhi”
Shutting down instance “delhi”…
Connected to “delhi”
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance “delhi” on database “delhi”
Starting instance “delhi”…
Connected to an idle instance.
ORACLE instance started.
Connected to “delhi”
Database mounted.
Connected to “delhi”
Continuing to convert database “delhi” …
Database “delhi” converted successfully
Step6:-Show the Configuration
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 47 seconds ago)
Database level check:-
SQL> select open_mode,instance_name,database_role from v$database,v$instance;
OPEN_MODE INSTANCE_NAME DATABASE_ROLE
——————– —————- —————-
READ ONLY WITH APPLY delhi PHYSICAL STANDBY
Step7:-Check the difference
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 24 24 0
Successfully completed the Converted a Physical Standby database to a oracle 19c Snapshot Standby database using DGMGRL Utlility
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
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
