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

Share and Enjoy !

0Shares
0 0 0

Recommended For You

ankushthavali

About the Author: ankushthavali

Leave a Reply

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