- 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
