DBA Tips Archive for Oracle

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

Contents

Introduction

Oracle Data Guard (known as Oracle Standby Database prior to Oracle9i), forms an extension to the Oracle RDBMS and provides organizations with high availability, data protection, and disaster recovery for enterprise databases. Oracle Data Guard provides the DBA with services for creating, maintaining, managing, and monitoring one or more standby databases. The functionality included with Oracle Data Guard enables enterprise data systems to survive both data corruption as well as major disasters.

Oracle9i Release 2 was greatly enhanced with new features and functionality to not only enable better flexibility, but to also make it easier for the DBA to build a stable and available infrastructure. One of those new features in Oracle9i Release 2 is the ability for the DBA to place the database into one of the following protection modes:

 

These three new modes replace the guaranteed, instance, rapid, and delayed modes of data protection available in Oracle9i Release 1 (9.0.1).

Data Guard protection modes are simply a set of rules that the primary database must adhere to when running in a Data Guard configuration. A protection mode is only set on the primary database and defines the way Oracle Data Guard will maximize a Data Guard configuration for performance, availability, or protection in order to achieve the maximum amount of allowed data loss that can occur when the primary database or site fails.

A Data Guard configuration will always run in one of the three protection modes listed above. Each of the three modes provide a high degree of data protection; however they differ with regards to data availability and performance of the primary database.

When selecting a protection mode, always consider the one that best meets the needs of your business. Carefully take into account the need to protect the data against any loss vs. availability and performance expectations of the primary database. This article will explain the new protection modes available in Oracle9i Release 2, how Log Transport Services works to support them, and finally the steps necessary to configure the standby database to the desired protection mode.

Log Transport Services

Log Transport Services enables and controls the automated transfer of redo data within a Data Guard configuration from the primary site to each of its standby sites.

Log transport services also controls the level of data protection for your database. The DBA will configure log transport services to balance data protection and availability against database performance. Log transport services will also coordinate with Log Apply Services and Role Transition Services for switchover and failover operations.

Maximum Protection Mode

Maximum Protection mode offers the ultimate in data protection. It guarantees no data loss will occur in the event the primary database fails. In order to provide this level of protection, the redo data needed to recover each transaction must be written to both the local (online) redo log and to a standby redo log on at least one standby database before the transaction can be committed. In order to guarantee no loss of data can occur, the primary database will shut down if a fault prevents it from writing its redo data to at least one remote standby redo log.

In a multiple-instance RAC database environment, Data Guard will shut down the primary database if it is unable to write the redo data to at least one properly configured database instance (see minimum requirements below).

In order to participate in Maximum Protection mode:

The minimum requirements are described in the following table:

Minimum Requirements for Maximum Protection Mode
Redo Archival Process LGWR
Network Transmission Mode SYNC
Disk Write Option AFFIRM
Standby Redo Logs? Yes
Standby Database Type Physical Only

For example:


log_archive_dest_2='service=testdb_standby LGWR SYNC AFFIRM'

The SYNC attribute with the LGWR process specifies that network I/O is to be performed synchronously for the destination. This means that once the I/O (redo data) is initiated, the archiving process waits for the I/O to complete before continuing. When specifying the SYNC attribute, all network I/O operations are performed synchronously, in conjunction with each write operation to the online redo log. The transaction is not committed on the primary database until the redo data necessary to recover that transaction is received by the destination.

 

It is highly recommended that a Data Guard configuration operating in Maximum Protection mode contain at least two physical standby databases that meet the requirements listed in the table above. That way, the primary database can continue processing if one of the physical standby databases cannot receive redo data from the primary database. If only one standby database is configured with the minimum requirements listed above, the primary database will shut down when the physical standby databases cannot receive redo data from the primary database!

Maximum Availability Mode

Maximum Availability mode provides the highest level of data protection that is possible without affecting the availability of the primary database. This protection mode is very similar to Maximum Protection where a transaction will not commit until the redo data needed to recover that transaction is written to both the local (online) redo log and to at least one remote standby redo log. Unlike Maximum Protection mode; however, the primary database will not shut down if a fault prevents it from writing its redo data to a remote standby redo log. Instead, the primary database will operate in Maximum Performance mode until the fault is corrected and all log gaps have been resolved. After all log gaps have been resolved, the primary database automatically resumes operating in Maximum Availability mode.

 

Please note that Maximum Availability mode guarantees that no data will be lost if the primary fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Just like Maximum Protection mode, Maximum Availability requires:

The minimum requirements are described in the following table:

Minimum Requirements for Maximum Availability Mode
Redo Archival Process LGWR
Network Transmission Mode SYNC
Disk Write Option AFFIRM
Standby Redo Logs? Required for physical standby databases only. Standby redo logs are not supported for logical standby databases.
Standby Database Type Physical or Logical

For example:


log_archive_dest_2='service=testdb_standby LGWR SYNC AFFIRM'

Maximum Performance Mode

Maximum Performance mode is the default protection mode and provides the highest level of data protection that is possible without affecting the performance or availability of the primary database. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.

When configuring the standby destination service in the LOG_ARCHIVE_DEST_n initialization parameter on the primary database, log transport services can be set to use either LGWR / ASYNC or ARCH. In order to reduce the amount of data loss on the standby destination if the primary database were to fail, set the LGWR and ASYNC attribute. Using this configuration, the primary database writes its redo stream to the standby redo logs on the standby database asynchronously with respect to the commitment of the transactions that create the redo data. When the nodes are connected with sufficient bandwidth, this mode provides a level of data protection that approaches that of Maximum Availability mode with minimal impact on primary database performance.

Note that the use of standby redo logs while operating in Maximum Performance mode is only necessary when configuring log transport services to use LGWR. When log transport services is configured to use ARCH, standby redo logs are not required.

The following table describes the attributes that should be defined for the LOG_ARCHIVE_DEST_n initialization parameter for the standby database destination to participate in Maximum Performance mode:

Minimum Requirements for Maximum Performance Mode
Redo Archival Process LGWR or ARCH
Network Transmission Mode ASYNC when using LGWR process. Not applicable when using the ARCH process.
Disk Write Option NOAFFIRM
Standby Redo Logs? Required for physical standby databases using the LGWR process.
Standby Database Type Physical or Logical

For example:


log_archive_dest_2='service=testdb_standby ARCH NOAFFIRM' or log_archive_dest_2='service=testdb_standby LGWR ASYNC NOAFFIRM'

LGWR / ASYNC vs. ARCH

When configuring log transport services to use LGWR and remotely archive in ASYNC mode, the LGWR process does not wait for each network I/O to complete before proceeding. This behavior is made possible by the use of an intermediate process, known as a Log Writer Network Server Process (LNS), which performs the actual network I/O and waits for each network I/O to complete. Each LNS has a user configurable buffer that is used to accept outbound redo data from the LGWR process. This is configured by specifying the size (in 512 byte blocks) on the ASYNC attribute in the archivelog parameter for the standby destination service. For example, ASYNC=2048 indicates a 1MB buffer. As long as the LNS process is able to empty this buffer faster than the LGWR can fill it, the LGWR process will never stall. If the LNS cannot keep up, then the buffer will become full and the LGWR process will stall until either sufficient buffer space is freed up by a successful network transmission or a timeout occurs.

When configuring log transport services to remotely archive using the ARCH attribute, redo logs are transmitted to the destination only during an archival operation. This means that the standby database does not receive any redo data until the primary database fills its current online redo and archives it. The data received and applied to the standby database is only as current as the last archived redo log sent from the primary database. The background archiver processes (ARCn) or a foreground archival process operation serves as the redo log transport service. Using ARCH to remotely archive redo data does not impact the primary database throughput as long as enough redo log groups exist so that the most recently used group can be archived before it must be reopened.

Setting the Data Protection Mode of a Data Guard Configuration

In this section, I will provide the steps necessary to change the protection mode of an existing physical Data Guard configuration. The protection mode will be changed from its default of Maximum Performance to Maximum Protection.

First though, let's review the available protection modes discussed in the previous sections. The protection modes run in the order from highest (most data protection) to the lowest (least data protection):

Each of the Data Guard data protection modes require that at least one standby database in the configuration meet the minimum set of requirements listed in the table below.

Minimum Requirements for Data Protection Modes
  Maximum Protection Maximum Availability Maximum Performance
Redo Archival Process LGWR LGWR LGWR or ARCH
Network Transmission Mode SYNC SYNC ASYNC when using LGWR process. Not applicable when using ARCH process.
Disk Write Option AFFIRM AFFIRM NOAFFIRM
Standby Redo Logs Required? Yes Required for physical standby databases only. Standby redo logs are not supported for logical standby databases. Required for physical standby databases using the LGWR process.
Database Type Physical only Physical and Logical Physical and Logical

Take note of the following important points to understand when configuring the standby database:

Upgrading the Protection Mode

Use the following steps to upgrade the protection mode from the default of Maximum Performance.

  1. Identify the current configuration:

    • Primary Database (TNS Service Name):    TESTDB_VMLINUX3.IDEVELOPMENT.INFO
    • Standby Database (TNS Service Name):    TESTDB_VMLINUX4.IDEVELOPMENT.INFO
    • Current Protection Mode:    Maximum Performance mode using ARCH

  2. Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. If standby redo logs do not exist for the standby database, create them now.


    [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba [Verify if using oracle managed files] SQL> select value from v$parameter where name = 'db_create_online_log_dest_1'; VALUE ------------------- /u02/oradata/TESTDB [Do any standby redo logs exist on the standby database?] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE [Need to first cancel Managed Recovery mode if it is running] SQL> alter database recover managed standby database cancel; [Create appropriate number standby redo log files on the standby database] SQL> alter database add standby logfile group 4 size 100m; SQL> alter database add standby logfile group 5 size 100m; SQL> alter database add standby logfile group 6 size 100m; SQL> alter database add standby logfile group 7 size 100m; [Put standby database back into Managed Recovery mode] SQL> alter database recover managed standby database disconnect from session; [Verify new standby redo logs on standby database] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE 4 104857600 STANDBY 5 104857600 STANDBY 6 104857600 STANDBY 7 104857600 STANDBY

  3. Ensure that the attributes for LOG_ARCHIVE_DEST_n are configured on the primary instance to support the desired protection mode. Also note that the destination standby database should be enabled to support the target protection mode as documented in this article.

     

    For the mode of Maximum Protection, the standby database must be up and mounted!

    Set the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to support the required protection mode:


    [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Configure log transport services to support the desired protection mode] SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info LGWR SYNC AFFIRM'; SQL> alter system set log_archive_dest_state_2=enable;

  4. The primary database will need to be closed and then placed in the MOUNT stage.


    SQL> shutdown immediate SQL> startup mount

  5. On the primary database, change the protection mode and open the database:

    For Maximum Protection mode:


    SQL> alter database set standby database to maximize protection; SQL> alter database open;

    For Maximum Availability mode:


    SQL> alter database set standby database to maximize availability; SQL> alter database open;

  6. Query the data dictionary on the primary database to verify the new protection mode:


    SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE; PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE ------------------- ------------------- -------------- MAXIMUM PROTECTION MAXIMUM PROTECTION PRIMARY

  7. (Optional) Although the steps in this section are optional, there are highly recommended so that the primary database can easily and quickly switchover to a standby role without the need for DBA intervention. For example, standby redo logs are only used on the physical standby database; however, creating and having standby redo logs ready to go on the physical primary database makes role transition much easier if the primary would every have to become the standby:


    [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Do any standby redo logs exist on the primary database?] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE [Create appropriate number standby redo log files on the primary database] SQL> alter database add standby logfile group 4 size 100m; SQL> alter database add standby logfile group 5 size 100m; SQL> alter database add standby logfile group 6 size 100m; SQL> alter database add standby logfile group 7 size 100m; [Verify new standby redo logs on primary database] SQL> select group#, bytes, 'online' as type from v$log 2 union 3 select group#, bytes, 'standby' as type from v$standby_log 4 order by 1; GROUP# BYTES TYPE ---------- ---------- ------- 1 104857600 ONLINE 2 104857600 ONLINE 3 104857600 ONLINE 4 104857600 STANDBY 5 104857600 STANDBY 6 104857600 STANDBY 7 104857600 STANDBY

    Also, on the standby database(s), configure the LOG_ARCHIVE_DEST_n parameter attributes so the Data Guard configuration can continue to operate in the new protection mode after a switchover:


    [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba SQL> alter system set log_archive_dest_2='service=testdb_vmlinux3.idevelopment.info LGWR SYNC AFFIRM'; SQL> alter system set log_archive_dest_state_2=defer;

Downgrading the Protection Mode

Use the following steps to downgrade the protection mode from the current higher protection mode.

  1. Identify the current configuration:

    • Primary Database (TNS Service Name):    TESTDB_VMLINUX3.IDEVELOPMENT.INFO
    • Standby Database (TNS Service Name):    TESTDB_VMLINUX4.IDEVELOPMENT.INFO
    • Current Protection Mode:    Maximum protection using LGWR SYNC AFFIRM

  2. Configure the attributes for LOG_ARCHIVE_DEST_n on the primary instance to support the new desired protection mode:


    [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Configure log transport services to support the desired protection mode] SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info ARCH'; SQL> alter system set log_archive_dest_state_2=enable;

  3. Shutdown and mount the primary and standby database:


    [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba SQL> shutdown immediate SQL> startup mount [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba SQL> alter database recover managed standby database cancel; SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database;

  4. Downgrade the primary database to the desired protection mode:


    [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
    [For Maximum Performance mode]
    
    SQL> alter database set standby database to maximize performance;
    [For Maximum Availability mode]
    
    SQL> alter database set standby database to maximize availability;

  5. Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. It you were to downgrade from Maximum Protection mode to Maximum Availability mode, you would need to keep the standby redo logs that exist for the standby database. If downgrading to Maximum Performance mode, you can drop the standby redo logs from the standby unless you are going to be configuring log transport services to use LGWR. For this example, I am going to be downgrading to maximum performance mode and using ARCH for log transport services and can therefore drop any standby redo logs from the standby database (and the primary if they exist):


    [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba [Drop any standby redo logs from the standby database] SQL> alter database drop standby logfile group 4; SQL> alter database drop standby logfile group 5; SQL> alter database drop standby logfile group 6; SQL> alter database drop standby logfile group 7;

    If standby redo logs exist on the primary and you are downgrading to Maximum Performance mode, they can be removed:


    [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba [Drop any standby redo logs from the primary database] SQL> alter database drop standby logfile group 4; SQL> alter database drop standby logfile group 5; SQL> alter database drop standby logfile group 6; SQL> alter database drop standby logfile group 7;

  6. Put the standby database in managed recovery mode and open the primary database with the new protection mode enabled:


    [Connect to the standby database] SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba SQL> alter database recover managed standby database disconnect from session; [Connect to the primary database] SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba SQL> alter database open;

  7. Query the data dictionary on the primary database to verify the new protection mode:


    SQL> select protection_mode, protection_level, database_role from v$database; PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE -------------------- -------------------- -------------- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

Further Reading

Additional information on Oracle Data Guard can be found in the Data Guard Concepts and Administration guide which is available from the Oracle Documentation Library:

  Data Guard Concepts and Administration - (Oracle9i Release 2)
  Data Guard Concepts and Administration - (Oracle Database 10g Release 2)

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 17-Apr-2014 02:44:50 EDT
Page Count: 25409