Change Protection Mode in Oracle 12C Dataguard

• Kiran Dalvi
• 02 Aug, 2020
• 0 Comments
• 2 Mins Read

Change Protection Mode in Oracle 12C Dataguard

Primary Side


SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   prim          PRIMARY       MAXIMUM PERFORMANCE


Standby Side


SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   stand         STANDBY       MAXIMUM PERFORMANCE



Verify Online logfile on Primary

SQL> select group#,bytes/1024/1024 from v$log;
 
GROUP# BYTES/1024/1024
------ ------------------
1      50
2      50
3      50


Verify Online logfile on Standby

SQL> select group#,bytes/1024/1024 from v$log;
 
GROUP# BYTES/1024/1024
------ ----------------
1      50
3      50
2      50


Verify Standby logfile on Primary and Standby

SQL> select group#,bytes/1024/1024 from v$standby_log;
 
no rows selected

Add Standby logfile on Primary

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.



Cancel the MRP process on Standby.

SQL> alter database recover managed standby database cancel;
 
Database altered.



Add Standby logfile on Standby

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.


Verify standby logfile on primary

SQL> select group#,bytes/1024/1024 from v$standby_log;
 
GROUP# BYTES/1024/1024
------ ---------------
4      50
5      50
6      50
7      50


Verify standby logfile on Standby

SQL> select group#,bytes/1024/1024 from v$standby_log;
 
GROUP# BYTES/1024/1024
------ ---------------
4      50
5      50
6      50
7      50


Check if your primary and standby is in sync.Your databases should be in Sync

Primary Side

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
 
THREAD# MAX(SEQUENCE#)
------- --------------
1       40


Standby Side

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

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



Primary Side


Change Log_arc_dest_2 parameter to use Sync mode.


SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------ ------ -----------------------------------
log_archive_dest_2 string service=stand valid_for=(online_logfiles,primary_role) db_unique_name=stand

SQL> alter system set log_archive_dest_2='service=stand LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=stand';

System altered.



Shutdown Primary database and mount

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

Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 180356360 bytes
Database Buffers 20971520 bytes
Redo Buffers 5214208 bytes
Database mounted.
SQL>


Change protection mode to maximize availability or maximum protection

SQL> alter database set standby database to maximize availability;
 
Database altered.

Open the Primary databases

SQL> alter database open;
 
Database altered.
 
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ----------------------
OPEN   prim        PRIMARY       MAXIMUM AVAILABILITY