Data Guard Maximum Performance mode Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard - MAXIMUM PERFORMANCE mode

This is the lowest data protection mode. In this mode, service on the primary database is independent of the redo log transfer to standby databases. The performance of the primary database is not affected because the primary database does not wait for the log transport service to complete. MAXIMUM PERFORMANCE mode is the default mode. The DBA can choose between LGWR and ARCH for the transfers of redo logs.

The network I/O attribute should be ASYNC when LGWR is writing redo logs on the standby database. Also, the disk I/O attribute should be NOAFFIRM to minimize any performance impact on the primary site. The following examples show the MAXIMUM PERFORMANCE mode settings:

LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1 LGWR ASYNC NOAFFIRM'

Or:

LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1 ARCH'

The following statement will put the primary database in MAXIMUM PERFORMANCE mode:

ALTER DATABASE SET Oracle instance TO MAXIMIZE PERFORMANCE;

The script, db_protection.sql, from the code depot can be used to find the current protection mode of the database. This should be executed on the primary database. The protection_level column in the output shows the aggregated protection level for all the standby databases in the configuration:

     
Select
   NAME,
   PROTECTION_MODE,
   PROTECTION_LEVEL
   See code depot for full scripts
From
   V$DATABASE;

A sample output from the script shows the current protection level:

NAME      PROTECTION_MODE      PROTECTION_LEVEL
--------- -------------------- --------------------
APPSDB    MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE