Advertisements
Skip to content
June 2, 2013 / Shivananda Rao P

Changing Protection Mode in a Dataguard Environment

Primary Database :  SRPRIM
Standby Database :  SRPSTB
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   srprim        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 srpstb        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 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=srpstb valid_for=(online_logf                          iles,primary_role) db_unique_name=srp                          stb

SQL> alter system set log_archive_dest_2='service=srpstb LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=srpstb';

System altered.

SQL> show parameter dest_2

NAME                        TYPE   VALUE
--------------------------- ------ ----------------------------
db_create_online_log_dest_2 string
log_archive_dest_2          string service=srpstb LGWR AFFIRM                              SYNC valid_for=(online_logfiles,p
                            rimary_role) db_unique_name=srpstb

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   srprim        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 srpstb        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 !!

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

5 Comments

Leave a Comment
  1. varun / Jun 27 2013 10:09 am

    Hi Shiva,
    The above topic is very useful in changing the Database protection mode.
    But can you please explain detail why do we need to create standby redo logs and log_archive_dest_2 , and how they are useful in data synchronization.

    I doubt that these things are already set in primary database during standby setup.
    Please explain the below step in detail please.

    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 shipment parameter (log_archive_dest_2) on the primary database should be configured to use SYNCHRONOUS
    (“SYNC”) mode.

  2. varun / Jul 3 2013 9:32 am

    Please write a topic on Performance Tuning which will help us.
    1) What are the things we have to look into when a user reports there is a performance issue ( in general)
    2) What are the roles played by AWR, ADDM, EXPLAIN PLAN reports in performance issues.
    3) Something about wait events,Statistics,Index Rebuilds,Bind variables,Parameters and Cursors.
    4) How SQL hints and SQL Profiles will help us.

    Thanks in Advance.

  3. homepage / Jan 30 2014 2:16 pm

    I’m extremely impressed together with your writing abilities and also with the layout on your weblog.

    Is this a paid theme or did you customize it your self?
    Anyway stay up the excellent quality writing, it is uncommon to peer a nice blog like this one today..

  4. Shivananda Rao P / Jan 30 2014 3:13 pm

    Thanks Michal. Will try to put in more interesting articles. The theme is not a paid one but the domain is a paid one.

    Regards,
    Shivananda

  5. badoo download / Aug 26 2014 12:36 pm

    I like the valuavle info you provide on your articles.
    I’ll bookmark your blog aand take a look at once more right here frequently.
    I am rather sure I’ll learn many new stuff right here!
    Best of luck for the following!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: