Primary Database : DCPROC
Standby Database : PRPROC
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 DCPROC 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.
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 100
2 100
3 100
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 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
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.
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4 100
5 100
6 100
7 100
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 DRPROC PHYSICAL STANDBY
On the standby database, there are 3 online redo log groups with the size 100M and there are no standby redo log groups.
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ----------------
1 100
3 100
2 100
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.
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 (100M) on the standby database:
SQL> alter database add standby logfile group 4 size 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
Database altered.
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4 100
5 100
6 100
7 100
Once the SRLs are added, start the MRP on the standby database
SQL> alter database recover managed standby database disconnect from 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=DRPROC valid_for=(online_logfiles,primary_role)
db_unique_name=DRPROC
SQL> alter system set log_archive_dest_2='service=DRPROC LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=DRPROC';
System altered.
SQL> show parameter dest_2
NAME TYPE VALUE
--------------------------- ------ --------------------------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string service=DRPROC LGWR AFFIRM SYNC valid_for= (online_logfiles,primary_role) db_unique_name=DRPROC
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 DCPROC 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 DRPROC 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.
Standby Database : PRPROC
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 DCPROC 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.
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 100
2 100
3 100
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 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
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.
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4 100
5 100
6 100
7 100
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 DRPROC PHYSICAL STANDBY
On the standby database, there are 3 online redo log groups with the size 100M and there are no standby redo log groups.
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ----------------
1 100
3 100
2 100
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.
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 (100M) on the standby database:
SQL> alter database add standby logfile group 4 size 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
Database altered.
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4 100
5 100
6 100
7 100
Once the SRLs are added, start the MRP on the standby database
SQL> alter database recover managed standby database disconnect from 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=DRPROC valid_for=(online_logfiles,primary_role)
db_unique_name=DRPROC
SQL> alter system set log_archive_dest_2='service=DRPROC LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=DRPROC';
System altered.
SQL> show parameter dest_2
NAME TYPE VALUE
--------------------------- ------ --------------------------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string service=DRPROC LGWR AFFIRM SYNC valid_for= (online_logfiles,primary_role) db_unique_name=DRPROC
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 DCPROC 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 DRPROC 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.
No comments:
Post a Comment