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