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 R2 is the ability for the DBA to place the database into one of the following protection modes:

• Maximum Protection
• Maximum Availability
• Maximum Performance

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

A Data Guard configuration will always run in one of the three data 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 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 management services for switchover and failover operations.

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:

• At least one standby instance has to be configured with a standby redo log.
• When configuring the standby instance (destination) in the LOG_ARCHIVE_DEST_n instance parameter on the primary database, you must use the LGWR, SYNC, and AFFIRM attributes.

Maximum Protection Mode Minimum Requirements
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 that is run in maximum protection mode contain at least two physical standby databases that meets 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!

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:

• At least one standby instance has to be configured with a standby redo log.
• When configuring the standby instance (destination) in the LOG_ARCHIVE_DEST_n instance parameter on the primary database, you must use the LGWR, SYNC, and AFFIRM attributes.

Maximum Availability Mode Minimum Requirements
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'

When configuring the destination standby instance in the LOG_ARCHIVE_DEST_n, 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 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 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 needed.

The following table describes the attributes that should be defined for the LOG_ARCHIVE_DEST_n instance parameter for the standby database destination to participate in maximum performance mode:

Maximum Performance Mode Minimum Requirements
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''

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 destination parameter. 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. 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.

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:

• The standby database that is used to satisfy the minimum requirements for a given protection mode must be enabled and ready to receive redo data from the primary database before you can switch to that mode!

• When archiving to a physical standby destination using the LGWR process, note that the changes (transactions) being made and committed on the primary database are not instantly written to the actual database files on the standby database. In Oracle9i Release 2, when log transport services is configured for maximum availability mode or maximum protection mode, the LGWR process on a physical primary database will send redo data to the standby redo logs (located on the standby database) at the same time it is writing redo data to the local (online) redo logs. Keep in mind that the LGWR process is actually communicating with a Remote File Server (RFS) process on the standby database server. This RFS process on the standby database is responsible for capturing and writing the redo data it obtains from the primary database to the standby redo logs.

  The Remote File Server process runs on the standby database and can receive redo data over the network from both LGWR and ARCn. The RFS process will write the redo data it receives to either a standby redo log or to a standby archived redo log.

  When a log switch occurs on the primary database, a log switch is also triggered on the standby database where the ARCH process then archives the standby redo logs to the archive destination specified on the standby database. After the archival process has completed on the standby database, the Managed Recovery Process (MRP) then writes the changes to the actual database files from the archived redo log files. Why is this important to point out? It illustrates the fact that the actual changes (transactions) being made and committed on the primary database do indeed make it over to the standby database, but get applied to the standby redo logs. These changes are only made to the actual database files on the standby database when a log switch occurs on the primary. Note that in Oracle10g, it is possible to configure changes made on the primary database to immediately write the changes to the database files on the standby database.

• When using standby redo logs, you must enable the archiver process (ARCn) on the standby database. Also, always set the initialization parameter LOG_ARCHIVE_START to TRUE on 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 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.

   SQL> -- [Connect to the standby database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux4.idevelopment.info AS SYSDBA
   
   
   SQL> -- [Verify if using Oracle Managed Files]
   SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_create_online_log_dest_1';
   
   VALUE
   -------------------
   /u02/oradata/TESTDB
   
   SQL> -- [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
   
   SQL> -- [Need to first cancel managed recovery mode if it is running]
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
   
   SQL> -- [Create standby redo log files]
   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;
   
   SQL> -- [Put standby database into managed recovery mode]
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   
   SQL> -- [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 instance parameter on the primary database to support the required protection mode:

   SQL> -- [Connect to the primary database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux3.idevelopment.info AS SYSDBA
   
   SQL> -- [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 switch over 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:

   SQL> -- [Connect to the primary database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux3.idevelopment.info AS SYSDBA
   
   SQL> -- [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
   
   SQL> -- [Create standby redo log files]
   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;
   
   SQL> -- [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:

   SQL> -- [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:

   SQL> -- [Connect to the primary database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux3.idevelopment.info AS SYSDBA
   
   SQL> -- [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:

   SQL> -- [Connect to the primary database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux3.idevelopment.info AS SYSDBA
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP MOUNT
   
   SQL> -- [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:

   SQL> -- [Connect to the primary database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux3.idevelopment.info AS SYSDBA

   SQL> -- [For MAXIMUM PERFORMANCE mode]
   SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

   SQL> -- [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):

   SQL> -- [Connect to the standby database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux4.idevelopment.info AS SYSDBA
   
   SQL> -- [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:

   SQL> -- [Connect to the primary database]
   SQL> CONNECT sys/change_on_install@testdb_vmlinux3.idevelopment.info AS SYSDBA
   
   SQL> -- [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:

   SQL> -- [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;
   
   SQL> -- [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