Change Protection Mode for Active Dataguard (11GR2)
Change
Protection Mode for Active Dataguard (11GR2)
Primary Database : TESTDB
Standby Database : TESTDBDR
Database version : 11gR2
Standby Database : TESTDBDR
Database version : 11gR2
The database is running under Maximum
Performance mode.
|
SQL> select
status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME
DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN TESTDB
PRIMARY MAXIMUM PERFORMANCE
|
In order to change the protection mode to either MAXIMUM
AVAILABILITY/MAXIMUM PROTECTION, we need to have the standby redo logs
configured on
the standby database. Also, the redo shippment parameter (log_archive_dest_2) on the primary database should be configured to use SYNCHRONOUS
(“SYNC”) mode.
the standby database. Also, the redo shippment parameter (log_archive_dest_2) on the primary database should be configured to use SYNCHRONOUS
(“SYNC”) mode.
Let’s check the number of online redo logs and it’s size on primary
database
|
SQL> select
group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ------------------
1 512
2 512
|
It can be noticed from below that there are no standby redo log groups
configured on the primary database.
|
SQL> select group#,bytes/1024/1024
from v$standby_log;
no rows selected
|
Add standby redo log groups on the primary database with the same size
as that of the online redo log groups.
|
SQL> alter database add
standby logfile group 4 size 512M;
Database altered.
SQL> alter database add
standby logfile group 5 size 512M;
Database altered.
SQL> alter database add
standby logfile group 6 size 512M;
Database altered.
SQL> alter database add
standby logfile group 7 size 512M;
Database altered.
|
We can now notice that 4 standby redo log groups have been added with
the same size as that of the online redo logs.
These standby redo logs will not be used on the primary database and will be used only when a switchover takes place.
These standby redo logs will not be used on the primary database and will be used only when a switchover takes place.
|
SQL> select
group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4 512
5 512
6 512
7 512
SQL> select
thread#,max(sequence#) from v$archived_log group bythread#;
THREAD# MAX(SEQUENCE#)
------- --------------
1 311
|
Standby:
|
SQL> select thread#,max(sequence#)
from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
------- --------------
1 311
|
Standby database is in sync with the primary database.
Standby database details:
|
SQL> select
status,instance_name,database_role from v$database,v$Instance;
STATUS INSTANCE_NAME
DATABASE_ROLE
------- ------------- ------------------
MOUNTED TESTDBDR
PHYSICAL STANDBY
|
On the standby database, there are 3 online redo log groups with the
size 512M and there are no standby redo log groups.
|
SQL> select
group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ----------------
1 512
3 512
2 512
SQL> select group#,bytes/1024/1024
from v$standby_log;
no rows selected
|
Let’s add standby redo log groups on the standby database but before
that, we need to check if MRP (Managed Recovery Process) is running on
the standby database and if running, then it needs to be cancelled.
the standby database and if running, then it needs to be cancelled.
|
SQL> select
process,status,sequence# from v$managed_standby;
PROCESS
STATUS SEQUENCE#
------- ------------ ----------
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
RFS
IDLE 0
RFS
IDLE 0
RFS
IDLE 0
MRP0
WAIT_FOR_LOG 312
8 rows selected.
|
Cancel the MRP process on the standby database:
|
SQL> alter database recover
managed standby database cancel;
Database altered.
|
Add 4 Standby Redo Log (SRL) groups of size same as online redo log
groups (512M) on the standby database:
|
SQL> alter database add
standby logfile group 4 size 512M;
Database altered.
SQL> alter database add
standby logfile group 5 size 512M;
Database altered.
SQL> alter database add
standby logfile group 6 size 512M;
Database altered.
SQL> alter database add
standby logfile group 7 size 512M;
Database altered.
SQL> select
group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4 512
5 512
6 512
7 512
|
once the SRLs are added, start the MRP on the standby database
|
SQL> alter database recover
managed standby database disconnectfrom session;
Database altered.
SQL> select
process,status,sequence# from v$managed_standby;
PROCESS
STATUS SEQUENCE#
------- ----------- ----------
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
RFS
IDLE 0
RFS
IDLE 0
RFS
IDLE 0
MRP0
WAIT_FOR_LOG 312
8 rows selected.
|
As said earlier, configure the redo shippment parameter
(log_archive_dest_2) on the primary database to use SYNCHRONOUS mode.
Primary database:
|
SQL> show parameter
log_archive_dest_2
NAME
TYPE
VALUE
------------------ ------ -----------------------------------
log_archive_dest_2 string
service=TESTDBDR
valid_for=(online_logf
iles,primary_role)
db_unique_name=srp
stb
SQL> alter system set log_archive_dest_2='service=TESTDBDR LGWR AFFIRM
SYNC valid_for=(online_logfiles,primary_role) db_unique_name=TESTDBDR';
System altered.
SQL> show parameter dest_2
NAME
TYPE
VALUE
--------------------------- ------ ----------------------------
db_create_online_log_dest_2
string
log_archive_dest_2
string service=TESTDBDR LGWR
AFFIRM
SYNC valid_for=(online_logfiles,p
rimary_role) db_unique_name=TESTDBDR
|
shutdown the primary database and mount it.
|
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>
|
Now change the protection mode on the primary database according to the
requirement using the below command
“alter database set standby database to maximize {AVAILABILITY |
PROTECTION| PERFORMANCE}”
Here, I am changing the protection mode from MAXIMUM PERFORMANCE to
MAXIMUM AVAILABILITY
|
SQL> alter database set standby database to maximize availability;
Database altered.
|
Once the mode is changed, open the primary database and verify the same.
|
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 TESTDB
PRIMARY MAXIMUM AVAILABILITY
|
check if the standby database is in sync with the primary database
On primary:
|
SQL> select
thread#,max(sequence#) from v$archived_log group bythread#;
THREAD# MAX(SEQUENCE#)
------- --------------
1 316
|
Standby:
|
SQL> select
status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME
DATABASE_ROLE PROTECTION_MODE
------- ------------- ---------------- --------------------
MOUNTED TESTDBDR
PHYSICAL STANDBY MAXIMUM AVAILABILITY
SQL> select
thread#,max(sequence#) from v$archived_log where applied='YES' group by
thread#;
THREAD# MAX(SEQUENCE#)
------- --------------
1 316
|
standby database is in sync with the primary and also the PROTECTION
mode has been changed to MAXIMUM AVAILABILITY.
Here we go !!
Comments
Post a Comment