2 Configuring for High-Availability

2.2.1.1 Enable ARCHIVELOG Mode

ARCHIVELOG mode enables online database backup and is necessary to recover the database to a point in time later than what has already been restored. Architectures such as Oracle Data Guard and Flashback Database require that the production database run in ARCHIVELOG mode.

2.2.1.2 Enable Block Checksums

By default, Oracle always validates the data blocks that it reads from disk. Enabling data and log block checksums by setting DB_BLOCK_CHECKSUM to TYPICAL enables Oracle to detect other types of corruption caused by underlying disks, storage systems, or I/O systems. Before a data block is written to disk, a checksum is computed and stored in the block. When the block is subsequently read from disk, the checksum is computed again and compared with the stored checksum. Any difference is treated as a media error, and an ORA-1578 error is signaled. Block checksums are always maintained for the SYSTEM tablespace. If DB_BLOCK_CHECKSUM is set to FULL, then in-memory corruption is also detected before being written to disk.

In addition to enabling data block checksums, Oracle also calculates a checksum for every redo log block before writing it to the current log. Redo record corruption is found as soon as the log is archived. Without this option, corruption in a redo log can go unnoticed until the log is applied to a standby database, or until a backup is restored and rolled forward through the log containing the corrupt log block.

RMAN also calculates checksums when taking backups to ensure that all blocks being backed up are validated.

Ordinarily the overhead for TYPICAL is one to two percent and for FULL is four to five percent. The default setting, TYPICAL, provides critical detection of corruption at very low cost and remains a requirement for high availability. Measure the performance impact with your workload on a test system and ensure that the performance impact is acceptable before moving from TYPICAL to FULL on an active database.

2.2.2.1 Configure the Size of Redo Log Files and Groups Appropriately

Use Oracle log multiplexing to create multiple redo log members in each redo group, one in the data area and one in the flash recovery area. This protects against a failure involving the redo log, such as a disk or I/O failure for one of the members, or a user error that accidentally removes a member through an operating system command. If at least one redo log member is available, then the instance can continue to function.

All online redo log files should be the same size and configured to switch approximately once an hour during normal activity. They should not switch more frequently than every 20 minutes during peak activity.

There should be a minimum of four online log groups to prevent LGWR from waiting for a group to be available following a log switch. A group might be unavailable because a checkpoint has not yet completed or because the group has not yet been archived.

2.2.2.2 Use a Flash Recovery Area

The flash recovery area is Oracle managed disk space that provides a centralized disk location for backup and recovery files. The flash recovery area is defined by setting the following database initialization parameters:

• DB_RECOVERY_FILE_DEST

  This parameter specifies the default location for the flash recovery area.

• DB_RECOVERY_FILE_DEST_SIZE

  This parameter specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the recovery area location.

The flash recovery area should be the primary location for recovery. When the flash recovery area is properly sized, files needed for repair will be readily available. The minimum recommended disk limit is the combined size of the database, incremental backups, all archived redo logs that have not been copied to tape, and flashback logs.

2.2.2.3 Enable Flashback Database

Flashback Database enables you to rewind the database to a previous point in time without restoring backup copies of the datafiles. Flashback Database is a revolutionary recovery feature that operates on only the changed data. Flashback Database makes the time to correct an error proportional to the time to cause and detect the error, without recovery time being a function of the database size. You can flash back a database from both RMAN and SQL*Plus with a single command instead of using a complex procedure.

During normal runtime, Flashback Database buffers and writes before images of data blocks into the flashback logs, which reside in the flash recovery area. Ensure there is sufficient I/O bandwidth available to the flash recovery area to maintain flashback write throughput. If flashback writes are slow, as evidenced by the flashback free buffer waits wait event, then database throughput is affected. The amount of disk writes caused by Flashback Database differs depending on the workload and application profile. For a typical OLTP workload that is using a flash recovery area with sufficient disk spindles and I/O throughput, the overhead incurred by Flashback Database is less than two percent.

Flashback Database can flash back a primary or standby database to a point in time prior to a role transition. In addition, a Flashback Database can be performed to a point in time prior to a resetlogs operation, which allows administrators more flexibility to detect and correct human errors. Flashback Database is required when using fast-start failover so that Data Guard Broker can automatically reinstate the primary database following an automatic failover.

If you have a standby database, then set DB_FLASHBACK_RETENTION_TARGET to the same value for both primary and standby databases.

2.2.2.4 Use Fast-Start Fault Recovery to Control Instance Recovery Time

The fast-start fault recovery feature reduces the time required to recover from a crash. It also makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint. With this feature, the FAST_START_MTTR_TARGET initialization parameter simplifies the configuration of recovery time from instance or system failure. This parameter specifies a target for the expected recover time objective (RTO), which is the time (in seconds) that it should take to start up the instance and perform cache recovery. Once this parameter is set, the database manages incremental checkpoint writes in an attempt to meet that target. If you have chosen a practical value for this parameter, then you can expect your database to recover, on average, in approximately the number of seconds you have chosen.

2.2.2.5 Enable Database Block Checking

Enable database block checking by setting DB_BLOCK_CHECKING to LOW, MEDIUM, or FULL. The block checking performed for each value is as follows:

• LOW

  Block checking is performed after any in-memory block change.

• MEDIUM

  All in-memory block change checking is performed as well as semantic block checking for all non index organized-table blocks.

• FULL

  Block checking is performed for all in-memory block changes as well as semantic block checking for all non index organized-table blocks and index blocks.

When one of these three forms of block checking is enabled, Oracle Database verifies that the block is self-consistent whenever the corresponding types of block change occur. If the block is inconsistent, then it is marked corrupt, an ORA-1578 error is returned, and a trace file is created containing details of the problem. Without block checking enabled, corruption can go undetected until the block is accessed again. Block checking for the SYSTEM tablespace is always enabled, no matter what setting is chosen for DB_BLOCK_CHECKING.

Block checking can often prevent memory and data corruption. Turning on this feature typically causes an additional one percent to ten percent overhead, depending on the setting and the workload. Measure the performance impact on a test system using your workload and ensure that it is acceptable before introducing this feature on an active database.

To check for block corruption on a disk that was not preventable by utilizing DB_BLOCK_CHECKING use one of the following:

• RMAN BACKUP command with the VALIDATE option

• DBVERIFY utility

• ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement

2.2.2.6 Set DISK_ASYNCH_IO

Set DISK_ASYNCH_IO=TRUE to enable asynchronous disk I/O for optimal I/O performance.

2.2.2.7 Set LOG_BUFFER to At Least 8 MB

For large production databases, set the LOG_BUFFER initialization parameter to a minimum of 8 MB. This setting ensures the database allocates maximum memory (typically 16 MB) for writing Flashback Database logs.

2.2.2.8 Use Automatic Shared Memory Management

Memory management has improved significantly with the advent of Automatic Shared Memory Management (ASM). By setting the SGA_TARGET parameter to a nonzero value, the shared pool, large pool, Java pool, Streams pool, and buffer cache can automatically and dynamically resize, as needed. See the Oracle Database Administrator's Guide for more information.

2.2.2.9 Increase PARALLEL_EXECUTION_MESSAGE_SIZE

Increase initialization parameter PARALLEL_EXECUTION_MESSAGE_SIZE from default value of 2048 to 4096. This configuration step accelerates parallel executions, including instance recovery.

2.2.2.10 Tune PARALLEL_MIN_SERVERS

Set PARALLEL_MIN_SERVERS so that the required number of parallel recovery processes are pre-spawned for fast recovery from an instance or node crash. This works with FAST_START_MTTR_TARGET to bound recovery time.

PARALLEL_MIN_SERVERS = CPU_COUNT + average number of parallel query processes in use for GV$ queries and parallel execution

2.2.2.11 Disable Parallel Recovery

When the value of RECOVERY_ESTIMATED_IOS in the V$INSTANCE_RECOVERY view is small (for example, < 5000), then the overhead of parallel recovery may outweigh any benefit. This will typically occur with a very aggressive setting of FAST_START_MTTR_TARGET. In this case, set RECOVERY_PARALLELISM to 1 to disable parallel recovery.

2.2.3.1 Use Automatic Performance Tuning Features

Effective data collection and analysis is essential for identifying and correcting performance problems. Oracle provides a number of tools that allow a performance engineer to gather information regarding database performance.

The Oracle Database automatic performance tuning features include:

• Automatic Workload Repository (AWR)

• Automatic Database Diagnostic Monitor (ADDM)

• SQL Tuning Advisor

• SQL Access Advisor

When using AWR, consider the following best practices:

• Set the AWR automatic snapshot interval to 10-20 minutes to capture performance peaks during stress testing or to diagnose performance issues.

• Under usual workloads a 60-minute interval is sufficient

2.2.3.2 Use a Server Parameter File

The server parameter file (SPFILE) enables a single, central parameter file to hold all database initialization parameters associated with all instances of a database. This provides a simple, persistent, and robust environment for managing database parameters. An SPFILE is required when using Data Guard Broker.

2.2.3.3 Use Automatic Undo Management

With automatic undo management, the Oracle Database server effectively and efficiently manages undo space, leading to lower administrative complexity and cost. When Oracle Database internally manages undo segments, undo block and consistent read contention are eliminated because the size and number of undo segments are automatically adjusted to meet the current workload requirement.

To use automatic undo management, set the following initialization parameters:

• UNDO_MANAGEMENT

  This parameter should be set to AUTO.

• UNDO_RETENTION

  This parameter specifies the desired time in seconds to retain undo data. It must be the same on all instances.

• UNDO_TABLESPACE

  This parameter should specify a unique undo tablespace for each instance.

Advanced object recovery features, such as Flashback Query, Flashback Version Query, Flashback Transaction Query, and Flashback Table, require automatic undo management. By default, Oracle Database automatically tunes undo retention by collecting database use statistics and estimating undo capacity needs. You can affect this automatic tuning by setting the UNDO_RETENTION initialization parameter. It is only necessary to set this initialization parameter in the following cases:

• The undo tablespace has the AUTOEXTEND option enabled.

• You want to have undo retention for LOBs.

• You want a retention guarantee.

With the retention guarantee option, the undo guarantee is preserved even if there is need for DML activity (DDL statements are still allowed). If the tablespace is configured with less space than the transaction throughput requires, then the following four things will occur in this sequence:

1. If you have an autoextensible file, then it will automatically grow to accommodate the retained undo data.

2. A warning alert is issued at 85 percent full.

3. A critical alert is issued at 97 percent full.

4. Transactions receive an out-of-space error.

2.2.3.4 Use Locally Managed Tablespaces

Locally managed tablespaces perform better than dictionary-managed tablespaces, are easier to manage, and eliminate space fragmentation concerns. Locally managed tablespaces use bitmaps stored in the datafile headers and, unlike dictionary managed tablespaces, do not contend for centrally managed resources for space allocations and de-allocations.

2.2.3.5 Use Automatic Segment Space Management

Automatic segment space management simplifies space administration tasks, thus reducing the chance of human error. An added benefit is the elimination of performance tuning related to space management. It facilitates management of free space within objects such as tables or indexes, improves space utilization, and provides significantly better performance and scalability with simplified administration. The automatic segment space management feature is enabled by default for all new tablespaces created using default attributes.

2.2.3.6 Use Temporary Tablespaces and Specify a Default Temporary Tablespace

Temporary tablespaces improve the concurrency of multiple sort operations, reduce sort operation overhead, and avoid data dictionary space management operations altogether. This is a more efficient way of handling temporary segments, from the perspective of both system resource usage and database performance.

A default temporary tablespace should be specified for the entire database to prevent accidental exclusion of the temporary tablespace clause. This can be done at database creation time by using the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement, or after database creation by the ALTER DATABASE statement. Using the default temporary tablespace ensures that all disk sorting occurs in a temporary tablespace and that other tablespaces are not mistakenly used for sorting.

2.2.3.7 Use Resumable Space Allocation

Resumable space allocation provides a way to suspend and later resume database operations if there are space allocation failures. The affected operation is suspended instead of the database returning an error. No processes need to be restarted. When the space problem is resolved, the suspended operation is automatically resumed.

To use resumable space allocation, set the RESUMABLE_TIMEOUT initialization parameter to the number of seconds of the retry time. You must also at the session level issue the ALTER SESSION ENABLE RESUMABLE statement.

2.2.3.8 Use Database Resource Manager

The Database Resource Manager gives database administrators more control over resource management decisions, so that resource allocation can be aligned with the business objectives of an enterprise. The Database Resource Manager provides the ability to prioritize work within the Oracle Database server. Availability of the database encompasses both its functionality and performance. If the database is available but users are not getting the level of performance they need, then availability and service level objectives are not being met. Application performance, to a large extent, is affected by how resources are distributed among the applications that access the database. The main goal of the Database Resource Manager is to give the Oracle Database server more control over resource management decisions, thus circumventing problems resulting from inefficient operating system management and operating system resource managers. The Database Resource Manager is enabled by default.

2.4.1.1 Benefits of a Physical Standby Database

A physical standby database provides the following benefits:

• Disaster recovery and high availability

  A physical standby database enables a robust and efficient disaster recovery and high-availability solution. Easy-to-manage switchover and failover capabilities allow easy role reversals between primary and physical standby databases, minimizing the downtime of the primary database for planned and unplanned outages.

• Data protection

  Using a physical standby database, Data Guard can ensure no data loss with certain configurations, even in the face of unforeseen disasters. A physical standby database supports all datatypes, and all DDL and DML operations that the primary database can support. It also provides a safeguard against data corruption and user errors. Storage level physical corruption on the primary database do not propagate to the standby database. Similarly, logical corruption or user errors that cause the primary database to be permanently damaged can be resolved. Finally, the redo data is validated when it is applied to the standby database.

• Reduction in primary database workload

  Oracle Recovery Manager (RMAN) can use physical standby databases to off-load backups from the primary database saving valuable CPU and I/O cycles. The physical standby database can also be opened in read-only mode for reporting and queries.

• Performance

  The Redo Apply technology used by the physical standby database applies changes using low-level recovery mechanisms, which bypass all SQL level code layers; therefore, it is the most efficient mechanism for applying high volumes of redo data.

• Read-write testing and reporting database

  Using Flashback Database and a physical standby database, you can configure a temporary clone database for testing and reporting. The temporary clone can later be resynched with the primary database.

2.4.1.2 Benefits of a Logical Standby Database

A logical standby database provides similar disaster recovery, high availability, and data protection benefits as a physical standby database. It also provides the following specialized benefits:

• Efficient use of standby hardware resources

  A logical standby database can be used for other business purposes in addition to disaster recovery requirements. It can host additional database schemas beyond the ones that are protected in a Data Guard configuration, and users can perform normal DDL or DML operations on those schemas any time. Because the logical standby tables that are protected by Data Guard can be stored in a different physical layout than on the primary database, additional indexes and materialized views can be created to improve query performance and suit specific business requirements.

• Reduction in primary database workload

  A logical standby database can remain open at the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. This makes a logical standby database an excellent choice to do queries, summations, and reporting activities, thereby off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.

• Database rolling upgrade

  A logical standby database can be upgraded to the next version and subsequently become the new primary database after a Data Guard switchover. This rolling upgrade procedure can dramatically reduce the planned downtime of a database upgrade.

2.4.1.3 Determining Which Standby Type Is Best for Your Application

Determining which standby type to implement can be accomplished by examining several key areas. Because logical standby does not support all datatypes, you must first determine if your application uses any unsupported datatypes. To determine if your application uses unsupported datatypes, run the following queries on the primary database:

• To list unsupported tables, issue the following query:

  SET PAGES 200 LINES 132 
  COL OWNER FORMAT A8 
  COL DATA_TYPE FORMAT A15
  COL TABLE_NAME FORMAT A32
  COL COLUMN_NAME FORMAT A25
  COL ATTRIBUTES FORMAT A15
  SELECT OWNER, TABLE_NAME, REASON
  FROM DBA_STREAMS_UNSUPPORTED
  WHERE OWNER NOT IN (SELECT OWNER FROM DBA_LOGSTDBY_SKIP 
      WHERE STATEMENT_OPT='INTERNAL SCHEMA')
  ORDER BY OWNER
  
  

• To list unsupported tables with column and data type information, issue the following query:

  COL OWNER FORMAT A9
  COL DATA_TYPE FORMAT A35
  COL TABLE_NAME FORMAT A35
  SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
  FROM DBA_TAB_COLS
  WHERE OWNER NOT IN (SELECT OWNER FROM DBA_LOGSTDBY_SKIP 
      WHERE STATEMENT_OPT='INTERNAL SCHEMA')    AND DATA_TYPE NOT IN ('BINARY_DOUBLE', 'BINARY_FLOAT', 'INTERVAL YEAR TO
      MONTH', 'INTERVAL DAY TO SECOND', 'BLOB', 'CLOB','CHAR', 'DATE','LONG',
      'LONG RAW', 'NCHAR', 'NCLOB','NUMBER', 'NVARCHAR2','RAW','TIMESTAMP',
      'TIMESTAMP(6)','TIMESTAMP(6) WITH TIME ZONE','TIMESTAMP(9)', 'TIMESTAMP
      WITH LOCAL TIMEZONE', 'TIMESTAMP WITH TIMEZONE', 'VARCHAR','VARCHAR2')
  ORDER BY 1,2
  
  

If either query returns rows with essential application tables, then use a physical standby database or investigate changing the primary database to use only supported datatypes. If the query does not return any rows with essential application tables, then you can use either a physical or a logical standby database.

Next, consider the need for the standby database to be accessible while changes are being applied. If you require that the standby is opened read/write with read-only access to the data being maintained and your application does not make use of unsupported datatypes, then logical standby is your best choice. If access to the standby while changes are being applied is not required or you have datatypes that are not supported by logical standby, then you should implement a physical standby.

If a logical standby database is still a viable choice, then you need to evaluate if it can handle your peak workloads. Because a logical standby database applies changes with SQL instead of a low level recovery mechanism, you need to assess database performance carefully. See "Oracle Database 10g Release 2 Best Practices: Data Guard SQL Apply" at http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm

2.4.4.1 Enable Flashback Database for Easy Reinstantiation After Failover

Enable Flashback Database on both the primary and standby database so that the old primary database can be easily reinstated as a new standby database following a failover. If there is a failure during the switchover process, then it can easily be reversed when Flashback Database is enabled.

2.4.4.2 Use FORCE LOGGING Mode

When the production database is in FORCE LOGGING mode, all database data changes are logged. FORCE LOGGING mode ensures that the standby database remains consistent with the production database. If this is not possible because you require the load performance with NOLOGGING operations, then you must ensure that the corresponding physical standby datafiles are subsequently synchronized. The physical standby datafiles can be synchronized by either applying an incremental backup created from the primary database or by replacing the affected standby datafiles with a backup of the primary datafiles taken after the nologging operations Before the file transfer, the physical standby database must stop recovery.

For logical standby databases, when SQL Apply encounters a redo record for an operation performed with the NOLOGGING clause, it skips over the record and continues applying changes from later records. Later, if an attempt is made to access one of the records that was updated with NOLOGGING in effect, the following error is returned: ORA-01403 no data found. To recover after the NOLOGGING clause is specified for a logical standby database, re-create one or more tables from the primary database, as described in Oracle Data Guard Concepts and Administration in Section 9.4.6 "Adding or Re-Creating Tables On a Logical Standby Database."

You can enable force logging immediately by issuing an ALTER DATABASE FORCE LOGGING statement. If you specify FORCE LOGGING, then Oracle waits for all ongoing unlogged operations to finish.

2.4.4.3 Use Data Guard Broker

Use Data Guard Broker to create, manage, and monitor a Data Guard configuration. The benefits of using Data Guard Broker include:

• Integration with RAC

  Data Guard Broker is integrated with CRS so that database role changes occur smoothly and seamlessly. This is especially apparent in the case of a planned role switchover (for example, when a physical standby database is directed to take over the primary role while the former primary database assumes the role of standby). Data Guard Broker and CRS work together to temporarily suspend service availability on the primary database, accomplish the actual role change for both databases during which CRS works with Data Guard Broker to properly restart the instances as necessary, and then resume service availability on the new primary database. Data Guard Broker manages the underlying Data Guard configuration and its database roles while CRS manages service availability that depends upon those roles. Applications that rely on CRS for managing service availability will see only a temporary suspension of service as the role change occurs in the Data Guard configuration.

• Automated creation of a Data Guard configuration

  Oracle Enterprise Manager provides a wizard that automates the complex tasks involved in creating a Data Guard Broker configuration, including:

  • Adding an existing standby database, or a new standby database created from existing backups taken through Enterprise Manager

  • Configuring the standby control file, server parameter file, and datafiles

  • Initializing communication with the standby databases

  • Creating standby redo log files

  • Enabling Flashback Database if you plan to use fast-start failover

  Although the Data Guard command-line interface (DGMGRL) cannot automatically create a new standby database, you can use DGMGRL commands to configure and monitor an existing standby database, including those created using Enterprise Manager.

• Simplified switchover and failover operations

  Data Guard Broker simplifies switchovers and failovers by allowing you to invoke them using a single key click in Oracle Enterprise Manager or a single command at the DGMGRL command-line interface. (referred to in this documentation as manual failover). For lights-out administration, you can enable fast-start failover to allow Data Guard Broker to determine if a failover is necessary and initiate the failover to a pre-specified target standby database automatically, with no need for DBA intervention and with no loss of data.

  Fast-start failover allows you to increase availability with less need for manual intervention, thereby reducing management costs. Manual failover gives you control over exactly when a failover occurs and to which target standby database. Regardless of the method you choose, Data Guard Broker coordinates the role transition on all databases in the configuration.

• Built-in monitoring and alert and control mechanisms

  Data Guard Broker provides built-in validation that monitors the health of all databases in the configuration. From any system in the configuration connected to any database, you can capture diagnostic information and detect obvious and subtle problems quickly with centralized monitoring, testing, and performance tools. Both Enterprise Manager and DGMGRL retrieve a complete configuration view of the progress of redo transport services on the primary database and the progress of Redo Apply or SQL Apply on the standby database.

  The ability to monitor local and remote databases and respond to events is significantly enhanced by the Data Guard Broker health check mechanism and by its tight integration with the Oracle Enterprise Manager event management system.

2.4.4.4 Use a Simple, Robust Archiving Strategy and Configuration

This archiving strategy is based on the following assumptions:

• Each database uses a flash recovery area.

• The production instances archive remotely to only one apply instance.

Table 2-2 describes the recommendations for a robust archiving strategy when managing a Data Guard configuration through SQL*Plus. All of the following items are handled automatically when the Data Guard Broker is managing a configuration.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

The following example illustrates the recommended initialization parameters for a primary database communicating to a physical standby database. There are two instances, SALES1 and SALES2, running in maximum protection mode.

*.DB_RECOVERY_FILE_DEST=+RECO
*.LOG_ARCHIVE_DEST_1='SERVICE=SALES stby LGWR SYNC AFFIRM NET_TIMEOUT=30
    REOPEN=300 VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES)) DB_UNIQUE_NAME=SALES_stby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

The flash recovery area must be accessible to any node within the cluster and use a shared file system technology such as automatic storage management (ASM), a cluster file system, a global file system, or high-availability network file system (HA NFS). You can also mount the file system manually to any node within the cluster very quickly. This is necessary for recovery because all archived redo log files must be accessible on all nodes.

On the standby database nodes, recovery from a different node is required when the node currently running standby apply fails and cannot be restarted. In that case, any of the existing standby instances residing on a different node can initiate managed recovery. In the worst case, when the standby archived redo log files are inaccessible, the new managed recovery process (MRP) or logical standby process (LSP) on the different node fetches the archived redo log files using the FAL server to retrieve from the production nodes directly.

When configuring hardware vendor shared file system technology, verify the performance and availability implications. Investigate the following issues before adopting this strategy:

• Is the shared file system accessible by any node regardless of the number of node failures?

• What is the performance impact when implementing a shared file system?

• Is there any effect on the interconnect traffic?

2.4.4.5 Use Standby Redo Logs and Configure Size Appropriately

Standby redo logs (SRLs) should be used on both sites for improved availability and performance. Use the following formula to determine the number of SRLs:

# of SRLs = sum of all production online log groups for each thread + number of threads

For example, if a primary database has two instances (threads) and each thread has four online log groups, then there should be ten SRLs. Having one more standby log group for each thread than the number of the online redo log groups for the production database reduces the likelihood that the LGWR for the production instance is blocked because an SRL cannot be allocated on the standby.

The following are additional guidelines for creating SRLs:

• Create the same number of SRLs for both production and standby databases

• All online redo logs and SRLs for both production and standby databases should be the same size

• SRLs should exist on both production and standby databases

• SRLs should be created in the Data Area protected through ASM or external redundancy

• In a RAC environment, SRLs must be on a shared disk

• In a RAC environment, assign the SRL to a thread when the SRL is created

2.4.4.6 Parameter Configuration Example

Appendix A, "Database SPFILE and Oracle Net Configuration File Samples" provides detailed examples of parameter settings, including SPFILE samples and Oracle Net configuration files.

2.4.5.1 Conduct Performance Assessment with Proposed Network Configuration

Oracle recommends that you conduct a performance assessment with your proposed network configuration and current (or anticipated) peak redo rate. The network impact between the production and standby databases, and the impact on the primary database throughput, needs to be understood. Because the network between the production and standby databases is essential for the two databases to remain synchronized, the infrastructure must have the following characteristics:

• Sufficient bandwidth to accommodate the maximum redo generation rate

• Minimal latency to reduce the performance impact on the production database

• Multiple network paths for network redundancy

The required bandwidth of a dedicated network connection is determined by the maximum redo rate of the production database and actual network efficiency. Depending on the data protection mode, there are other recommended practices and performance considerations. Maximum protection mode and maximum availability mode require LGWR SYNC transport. Maximum performance protection mode uses the ASYNC transport option or the archiver (ARCHn).

When you compare maximum protection mode or maximum availability mode (with LGWR SYNC operations) with maximum performance mode (with LGWR ASYNC operations), measure whether performance or throughput will be degraded due to the incurred latency. You should also check whether the new throughput and response time are within your application performance requirements. Distance and network configuration directly influence latency, while high latency might slow down your potential transaction throughput and increase response time. The network configuration, number of repeaters, the overhead of protocol conversions, and the number of routers also affect the overall network latency and transaction response time.

2.4.5.2 Best Practices for Primary Database Throughput

When sending redo to a standby database using the LGWR SYNC attributes, a transaction on the primary database will not return commit complete to the foreground until the redo associated with that transaction has been written both locally and remotely. The commit time when using LGWR SYNC will be directly impacted by the network latency and bandwidth, as well as the I/O capacity on the standby database. The total commit time is comprised of the primary database's local write (log file parallel write) and the following factors that are captured through the LNS wait event on SENDREQ: network time + standby write (RFS write obtained from the V$SYSTEM_EVENT view on the standby database) + network acknowledgement. However, how much the primary database is impacted depends on the application profile. In general, batch updates with infrequent commits and message queuing applications may not observe any noticeable difference.

When sending redo to a standby database using the LGWR ASYNC attributes, the effect on primary database throughput is minimal due to the true asynchronous behavior of ASYNC redo transport. Furthermore, there is little effect on the primary database throughput (redo bytes per second) as network latency increases. With the ASYNC attribute, the log writer process writes to the local online redo log file, while the LGWR network server (LNSn) processes (one for each destination) read redo from the online redo log and asynchronously transmit the redo to remote destinations. The LGWR process continues processing the requests without waiting for the LNS network I/O to complete. If redo transport services transmit redo data to multiple remote destinations, then the LNSn processes initiate the network I/O to all of the destinations in parallel.

Figure 2-3 shows the LNSn process collecting redo data from the online redo log files and transmitting it over Oracle Net to the RFS process on the standby database.

Figure 2-3 LGWR ASYNC Archival with Network Server (LNSn) Processes

Description of "Figure 2-3 LGWR ASYNC Archival with Network Server (LNSn) Processes"

For remote destinations that are serviced by the ARCH process, you can configure the remote transfer to use multiple streams for more efficient use of network resources. Configuration of multiple streams is performed by setting the MAX_CONNECTIONS attribute in the LOG_ARCHIVE_DEST_n initialization parameter to a value greater than 1. The value determines the maximum number of network connections that will be used to perform the remote archival. The maximum value is five streams for each remote destination.

Because the network connections used in the multiple streams are performed by the ARCH process, care must be taken when setting the LOG_ARCHIVE_MAX_PROCESSES initialization parameter. The value of both LOG_ARCHIVE_MAX_PROCESSES and PARALLEL_MAX_SERVERS initialization parameters must be at least one greater than the total number specified for MAX_CONNECTIONS for all remote destinations.

2.4.5.3 Best Practices for Network Configuration and Highest Network Redo Rates

The following sections include best practices for network configuration and highest redo network redo rates:

• Properly Configure TCP Send / Receive Buffer Sizes

• Increase SDU Size

• Ensure TCP.NODELAY is YES

• Increase PARALLEL_MAX_SERVERS

• Account for Additional Disk I/O with LGWR ASYNC

2.4.6.1 Redo Apply Best Practices for Physical Standby Databases

To use Oracle Data Guard Redo Apply with a physical standby database, or to use any media recovery operation effectively, you need to tune your database recovery by following these best practices:

1. Maximize I/O rates on standby redo logs and archived redo logs.

   Measure read I/O rates on the standby redo logs and archived redo log directories. Concurrent writing of shipped redo on a standby database might reduce the redo read rate due to I/O saturation. The overall recovery rate will always be bounded by the rate at which redo can be read; so ensure that the redo read rate surpasses your required recovery rate.

2. Assess recovery rate.

   To obtain the history of recovery rates, use the following query to get a history of recovery progress:

   SELECT * FROM V$RECOVERY_PROGRESS;
   
   

   If your ACTIVE APPLY RATE is greater than the maximum redo generation rate at the primary database or twice the average generation rate at the primary database, then no tuning is required; otherwise follow the tuning tips below. The redo generation rate for the primary database can be monitored from Grid Control or extracted from AWR reports under statistic REDO SIZE. If CHECKPOINT TIME PER LOG is greater than ten seconds, then investigate tuning I/O and checkpoints.

3. Use defaults for DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM

   The default setting for DB_BLOCK_CHECKING is OFF. Setting DB_BLOCK_CHECKING to FULL may reduce the recovery rate. Block checking is always recommended on the primary database and might be enabled on the standby database if the recovery rate meets expectations.

   The default setting for DB_BLOCK_CHECKSUM is TYPICAL. Block checksum should always be enabled for both primary and standby databases. It catches most block corruption while incurring negligible overhead.

4. Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096

   Increasing this parameter to 4096 may improve recovery by as much as twenty percent over the default setting of 2152. The message size parameter is used by parallel query operations, so there must be sufficient shared pool to support this increase.

5. Set DB_CACHE_SIZE to a value greater than that for the primary database. Set DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE to 0.

   Having a large database cache size can improve media recovery performance by reducing the amount of physical data block reads. Because media recovery does not require DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE or require a large SHARED_POOL_SIZE, the memory can be reallocated to the DB_CACHE_SIZE.

   Prior to converting the standby database into a primary database, reset these parameters to the primary database settings.

6. Assess database waits

   You can determine the top system and session wait events by querying the standby database's V$SYSTEM_EVENTS, V$SESSION_WAITS, and V$EVENT_HISTOGRAM and looking for the largest TIME_WAITED value. You may have to capture multiple snapshots of the query results and manually extract the difference to accurately assess a certain time period. Unfortunately, there is no equivalent AWR report for physical standby databases.

   If recovery is applying a lot of redo efficiently, the system will be I/O bound and the I/O wait should be reasonable for your system. The following table shows the top recovery-related waits that you may observe and the tuning tips appropriate for each wait type. Apply the tuning tips only if the recovery events are in the top ten waits.

   Wait Name	Description	Tuning Tips
   log file sequential read	Coordinator (recovery session or MRP process) wait for log file read I/O	Tune log read I/O
   px deq: par recov reply	Coordinator synchronous wait for slave (wait for checkpoints)	Tune datafile write I/O, increase DBWR processes, increase primary/standby redo log size
   px deq credit: send blkd	Coordinator streaming wait for slave (wait for apply)	Increase PARALLEL_EXECUTION_MESSAGE_SIZE to 8192
   free buffer waits	Foreground waiting available free buffer in the buffer cache	Increase DB_CACHE_SIZE and remove any KEEP or RECYCLE POOL settings
   recovery read	Wait for data block read	Tune datafile read I/O
   direct path read	Coordinator wait for file header read at log boundary checkpoint	Tune datafile read I/O
   direct path write	Coordinator wait for file header write at log boundary checkpoint	Tune datafile write I/O
   checkpoint completed (serial recovery only)	Wait for checkpoint completed	Tune datafile write I/O Increase number of DBWR processes
   db file parallel read (serial recovery only)	Wait for data block read	Tune file read I/O

   
   

7. Tune I/O operations.

   DBWR must write out modified blocks from the buffer cache to the data files. Always use native asynchronous I/O by setting DISK_ASYNCH_IO to TRUE (default). In the rare case that asynchronous I/O is not available, use DBWR_IO_SLAVES to improve the effective data block write rate with synchronous I/O.

   Ensure that you have sufficient I/O bandwidth and that I/O response time is reasonable for your system either by doing some base I/O tests, comparing the I/O statistics with those for the primary database, or by looking at some historical I/O metrics. Be aware that I/O response time may vary when many applications share the same storage infrastructure such as with a Storage Area Network (SAN) or Network Attached Storage (NAS).

8. Assess system resources.

   Use system commands such as UNIX sar and vmstat or system monitoring tools to assess system resources. Alternatively, refer to Enterprise Manager, AWR reports or performance views such as V$SYSTEM_EVENT, V$ASM_DISK and V$OSSTAT.

   1. If there are I/O bottlenecks or excessive wait I/O operations, then investigate operational or application changes that increased the I/O volume. If the high waits are due to insufficient I/O bandwidth, then add more disks to the relevant ASM disk group. Verify that this is not a bus or controller bottleneck or any other I/O bottleneck. The read I/O from the standby redo log should be greater than the expected recovery rate.

   2. Check for excessive swapping or memory paging.

   3. Check to ensure the recovery coordinator or MRP is not CPU bound during recovery.

9. Increase log group size for the primary and standby databases.

   Increase the online redo log size for the primary database and the standby redo logs size for the standby database to a minimum of 1 GB. Oracle Database does a full checkpoint and updates all the file headers (in an optimized manner) at each log file boundary during media recovery. To reduce the frequency of a full database checkpoint and updating all the file headers, increase the log group size so that a log switch is occurring at a minimum of 15 minute intervals.

   If real-time apply is being used and redo is being sent synchronously or asynchronously by way of the LGWR process, then there is no additional data loss risk with this change. If archiver is sending the redo or the primary database is converting to ARCH mode due to heavy load, then you must balance faster recovery rates and higher data loss risk.

   To ensure that the crash recovery time for the primary database is minimized even with very large redo group sizes, set FAST_START_MTTR_TARGET to a nonzero value to enable fast-start fault recovery. If it is currently not set, then set it to 3600. This initialization parameter is relevant only for the primary database.

10. Assess different degrees of recovery parallelism

    Parallel recovery is enabled by default for media and crash recovery with the default degree of parallelism set to the number of CPUs available. In most cases this is the optimal setting. However, in some circumstances faster recovery may be obtained by using a degree of parallelism that is different (higher or lower) than the default. To override the default setting, explicitly specify it as follows:

    RECOVER MANAGED STANDBY DATABASE PARALLEL <#>;
    

2.4.6.2 SQL Apply Best Practices for Logical Standby Databases

This section discusses recommendations for Data Guard SQL Apply and logical standby databases.

This section contains these topics:

• Set the MAX_SERVERS Initialization Parameter

• Increase the PARALLEL_MAX_SERVERS Initialization Parameter

• Set the PRESERVE_COMMIT_ORDER Initialization Parameter

• Skip SQL Apply for Unnecessary Objects

• Set the LOG_AUTO_DELETE SQL Apply Parameter

MAX_SERVERS = <current MAX_SERVERS setting> + <PQ Slaves needed for reporting and decision-support operations>

2.4.7.1 Role Transition During Switchover

A database switchover performed by Oracle Data Guard is a planned transition that includes a series of steps to switch roles between a standby database and a production database. Following a successful switchover operation, the standby database assumes the production role and the production database becomes a standby database. In a RAC environment, a switchover requires that only one instance be active for each database, production and standby.

At times the term switchback is also used within the scope of database role management. A switchback operation is a subsequent switchover operation to return the roles to their original state.

Data Guard enables you to change these roles dynamically by:

• Using Oracle Enterprise Manager, as described in Section 4.2.3.2.1, "Using Enterprise Manager to Perform a Data Guard Switchover"

• Using the Oracle Data Guard Broker command-line interface

• Issuing SQL statements, as described in Section 4.2.3.2.2, "Using SQL for Data Guard Switchover to a Physical Standby Database" and Section 4.2.3.2.3, "Using SQL for Data Guard Switchover to a Logical Standby Database" beginning

2.4.7.2 Role Transition During Failover

Failover is the operation of taking the production database offline at one site and bringing one of the standby databases online as the new production database. A failover operation can be invoked when a catastrophic failure occurs on the production database, and there is no possibility of recovering the production database in a timely manner.

With Data Guard the process of failover can be completely automated using fast-start failover, or it can be user driven. Fast-start failover eliminates the uncertainty inherent in a process that requires manual intervention. It automatically executes a zero data loss failover within seconds of an outage being detected.

Oracle recommends that you use fast-start failover. The initial MAA tests running Oracle Database 10g Release 2 (10.2) show that failovers performed using the Data Guard Broker and fast-start failover offer a significant improvement in availability.

Manual failover allows for a failover process where decisions are user driven. Manual failover can be accomplished by:

• Issuing SQL statements, as described in Section 4.2.2.2.2, "Using SQL to Fail Over to a Physical Standby Database"

• Using Oracle Enterprise Manager, as described in Section 4.2.2.2.1, "Using Enterprise Manager to Perform a Data Guard Failover"

• Using the Oracle Data Guard Broker command-line interface (DGMGRL)

This section contains these topics:

• Comparing Fast-Start Failover and Manual Failover

• Fast-Start Failover Best Practices

• Manual Failover Best Practices

2.5.1.1 Use Recovery Manager to Back Up Database Files

Recovery Manager (RMAN) is Oracle's utility to backup and recover the Oracle Database. Because of its tight integration with the database, RMAN determines automatically what files need to be backed up. But more importantly, RMAN knows what files need to be restored for media- recovery operations. RMAN uses server sessions to perform backup and recovery operations and stores metadata about backups in a repository. RMAN offers many advantages over typical user-managed backup methods, including:

• Online database backups without placing tablespaces in backup mode

• Incremental backups

• Data block integrity checks during backup and restore operations

• Test backups and restores without actually performing the operation

RMAN automates backup and recovery. User-managed methods require you to locate backups for each datafile, copy them to the correct place using operating system commands, and choose which logs to apply. RMAN manages these tasks automatically.

There are also capabilities of Oracle backup and recovery that are only available when using RMAN, such as automated tablespace point-in-time recovery and block-media recovery.

2.5.1.2 Use Oracle Secure Backup

Oracle Secure Backup provides data protection for heterogeneous UNIX, Linux, Windows and Network Attached Storage (NAS) environments. Oracle Secure Backup provides tape data protection for the entire Oracle Environment:

• Oracle Database through integration with RMAN

• Seamless support of Oracle Real Application Clusters (RAC)

• File system data protection of distributed servers including:

  • Oracle Application Servers

  • Oracle Collaboration Suites

  • Oracle home and binaries

The combination of RMAN and Oracle Secure Backup provides an end-to-end tape backup solution, eliminating the need for third-party backup software.

2.5.1.3 Use Restore Points

Oracle restore points can be used to protect against logical failures at risky conjunctions during database maintenance. Creating a normal restore point assigns a restore point name to a specific point in time or SCN. The restore point name can be used with Flashback Table and Flashback Database operations. Restore points can be guaranteed to ensure that a Flashback Database operation will succeed in rewinding the database back to the restore point.Guaranteed restore points are recommended for database-wide maintenance such as database or application upgrades, or running batch processes. Guaranteed restore points enable Flashback Database and retain all flashback logs necessary to ensure the database can be flashed back to the restore point. Once maintenance activities complete and results are verified, guaranteed restore points that are no longer needed should be deleted.

2.5.2.1 Understand When to Use Backups

Using backups to resolve an unscheduled outage of a production database may not allow you to meet your Recovery Time Objective (RTO) or SLA. For example, some outages are handled best by using Flashback Database or the standby database. However, some situations require using database backups, including the following:

• Initial Data Guard Environment Setup

• Recovering from Data Failures Using File or Block Media Recovery

• Double Failure Resolution

Initial Data Guard Environment Setup During initial setup of a standby database, a backup of the production database is required at the secondary site to create the initial standby database.

Recovering from Data Failures Using File or Block Media Recovery When a block corruption, media failure, or other data failure occurs in an environment that does not include Data Guard, the only method of recovery is using an existing backup.

Double Failure Resolution A double failure scenario affects the availability of both the production and standby databases. An example of a double failure scenario is a site outage at the secondary site, which eliminates fault tolerance, followed by a media failure on the production database. The only resolution of this situation is to re-create the production database from an available backup and then re-create the standby database.

Some multiple failures, or more appropriately disasters (such as a primary site outage followed by a secondary site outage), might require the use of backups that exist only in an offsite location. Developing and following a process to deliver and maintain backup tapes at an offsite location, therefore, is necessary to restore service in the most dire of circumstances.

2.5.2.2 Determine a Backup Frequency

It is important to determine a backup frequency policy and to perform regular backups. A backup retention policy helps ensure that needed data is not destroyed too soon.

Factors Determining Backup Frequency Frequent backups are essential for any recovery scheme. Base the frequency of backups on your estimated recovery time objective for outages that cannot be resolved by Data Guard or Flashback technology. Repair time will be dictated by restore time plus recovery time. The frequency of the backup and the location of the backup will impact both of these factors. The other factor that influences how frequently a datafile is backed up is the rate or frequency of database changes such as:

• Addition and deletion of tables

• Insertions and deletions of rows in existing tables

• Updates to data in tables

To simplify database backup and recovery, the Oracle suggested backup strategy implements the flash recovery area while using incremental backups and updated incremental backup features. For more information about the Oracle suggested strategy, see the section titled "Using the Oracle-Suggested Backup Strategy" in Oracle Database 2 Day DBA.

Establishing a Backup Retention Policy A backup retention policy is a rule set regarding which backups must be retained (on disk or other backup media) to meet recovery and other requirements. It may be safe to delete a specific backup because it is old enough to be superseded by more recent backups or because it has been stored on tape. You may also have to retain a specific backup on disk for other reasons such as archival requirements. A backup that is no longer needed to satisfy the backup retention policy is said to be obsolete.

Backup retention policy can be based on redundancy or a recovery window. In a redundancy-based retention policy, you specify a number n such that you always keep at least n distinct backups of each file in your database. In a recovery window-based retention policy, you specify a time interval in the past (for example, one week or one month) and keep all backups required to let you perform point-in-time recovery to any point during that window.

Keeping Long-Term Backups Some businesses require the ability to maintain long-term backups that may be needed years into the future. By using RMAN with the KEEP option, it is possible to retain backups that are exempt from the retention policy and never expire, providing the capability to restore and recover the database to any desired point in time. It is important that a recovery catalog be used for the RMAN repository so that backup metadata is not lost due to lack of space, which may occur when using the target database control file for the RMAN repository.

2.5.2.3 Use an RMAN Recovery Catalog

RMAN automatically manages the backup metadata in the control file of the database that is being backed up. To protect and keep backup metadata for long periods of time, the RMAN repository is created in a separate database. This repository is usually referred to as a recovery catalog. The advantages of using a recovery catalog include:

• Stores backup information long-term

• Store metadata for multiple databases

• Restore an available backup onto another system

Another reason to use a recovery catalog is the limited maximum size of the target database control file. If the control file is too small to hold additional backup metadata, then existing backup information is overwritten, making it difficult to restore and recover using those backups.

2.5.2.4 Enable Block Change Tracking for Incremental Backups

Oracle Database 10g includes a change tracking feature for incremental backups, which improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, then RMAN uses the change tracking file to identify changed blocks for incremental backup. This avoids the need to scan every block in the datafile, reducing the number of disk reads during backup.

2.5.2.5 Enable Autobackup for Control File and Server Parameter File

RMAN can be configured to automatically back up the control file and server parameter file (SPFILE) whenever the database structure metadata in the control file changes or when a backup record is added. The autobackup enables RMAN to recover the database even if the current control file, catalog, and SPFILE are lost. The RMAN autobackup feature is enabled with the CONFIGURE CONTROLFILE AUTOBACKUP ON statement.

2.5.3.1 Determine Disk Backup Methods

When selecting a backup mechanism, the following priorities will drive your backup strategy:

• Overall backup time

• Impact on primary

• Space used by backup

• Recovery time

Table 2-4 compares different backup alternatives against the different priorities you might have. The table should guide you to choose the best backup approach for your specific business requirements. You might want to minimize backup space while sacrificing recovery time. Alternatively, a higher priority might be on recovery and backup times while space is not an issue.

Best Practices on Optimizing Recovery Times If restore time is the primary concern, then either a database copy or an incremental backup with roll forward immediately should be performed. These two are the only options that provide an immediately usable backup of the database, which then needs only to be recovered to the time of the failure using archive logs created since the last backup was performed.

Best Practices on Minimizing Space Usage If space usage is the primary concern, then an incremental backup with a deferred roll forward should be performed. If a cumulative level 1 incremental backup is performed, then it stores only those blocks that have been changed since the last level 0 backup. With a cumulative incremental backup, the last level 1 backup need only be applied to the level 0 backup. With a differential incremental backup, all level 1 backups have to be applied to the level 0 backup. A cumulative incremental backup will initially consume more space in the flash recovery area than a differential incremental backup. But over time the cumulative incremental backup will consume less space.

Best Practices on Minimizing System Resource Consumption (I/O and CPU) If system resource consumption is the primary concern, then an incremental backup with a block change-tracking file will consume the least amount of resources on the database. This is true when the amount of data changed for each backup window is below 20% of the total database size. When the amount of data changed for each backup window exceeds 20%, then performing incremental backups will still reduce the amount of disk space required to hold the backup, but may not reduce the backup time.

Example

For many applications, only a small percentage of the entire database is changed each day regardless of whether the transaction rate is very high. Frequently, applications modify a same set of blocks frequently; so, the total dirty block set is small.

For example, a database contains about 600 GB of user data, not including temp files and redo logs. Every 24 hours, approximately 2.5% of the database is changed, which is approximately 15 GB of data. The first level 0 backup takes about 180 minutes and a subsequent level 1 backup takes 20 minutes, while the merge of the backups take 45 minutes. In this example, we observed the following results:

• Level 0 backup takes 180 minutes, including READs from the data area and WRITEs to the flash recovery area

• Level 1 backup takes 20 minutes, including READs from the data area and WRITEs to the flash recovery area

• Rolling forward and merging the backups takes only 45 minutes included READs and WRITEs from the flash recovery area, which offloads possibly contention to the data area if they use separate storage.

• The net savings are:

  • 115 minutes or 64% time savings to create a complete backup

  • Reduced I/O on the database during backups

For bigger databases, we observed even larger gains.

2.5.3.2 Create Backups in NOCATALOG Mode and RESYNC CATALOG Afterwards

When creating backups to disk or tape, use the target database control file as the RMAN repository, so that backup success does not depend on the availability of the database holding the RMAN repository. This is accomplished by running RMAN with the NOCATALOG option. After the backup is complete, the new backup information stored in the target database control file can be resynchronized with the recovery catalog using the RESYNC CATALOG command.

2.5.3.3 Create Database Backups on Disk in the Flash Recovery Area

Using automatic disk-based backup and recovery, you can create a flash recovery area which automates management of backup-related files:

1. Choose a location on disk.

   This location is specified by DB_RECOVERY_FILE_DEST.

2. Choose an upper bound for storage space.

   This upper bound is specified by DB_RECOVERY_FILE_DEST_SIZE.

3. Set a retention policy that governs how long backup files are needed for recovery.

Oracle Database 10g then manages the storage used for backup, archived redo logs, and other recovery-related files for your database within this space. Files no longer needed are eligible for deletion when RMAN must reclaim space for new files.

2.5.3.4 In a Data Guard Environment, Back Up to Flash Recovery Area on All Sites

Take backups at the primary and secondary sites. The advantages of this practice include:

• Significantly reduces RTO in certain double outage scenarios

• Avoids introducing new backup procedures upon a switchover or failover

• RMAN file and block media recovery is a recovery option for data failure outages at both primary and secondary sites

Consider a scenario in which backups are done only at the secondary site. Suppose there is a site outage at the secondary site where the estimated time to recover is three days. The primary site is completely vulnerable to an outage that is typically resolved by a failover, or any outage that could be resolved by having a local backup (such as a data failure outage resolved by block media recovery).

In this scenario, a production database outage can be resolved only by physically shipping the off-site tape backups that were taken at the standby site. If primary site backups were available, then restoring locally would be an available option in place of the failover that cannot be done. Data might be lost, but having primary site backups significantly shortens the RTO.

Primary site disk backups are also necessary to ensure a reasonable RTO when using RMAN file or block media recovery. Without a local on disk backup, a backup taken at the standby site must be restored to the primary site, significantly lengthening the RTO for this type of outage.

2.5.4.1 Create Tape Backups from the Flash Recovery Area

Use RMAN command BACKUP RECOVERY FILE DESTINATION to move disk backups created in the flash recovery area to tape. Using a single command, all files not backed up to tape previously are backed up. This prevents you from backing up files more than once and wasting tape or tracking files not backed up before. Tape backups are used to handle certain outage scenarios and for offsite and long-term storage.

2.5.4.2 Maintain Offsite Backups

Regardless of the architecture deployed, including the existence of a standby database, it is still important to have offsite backups for business requirements, to protect against disasters, and to comply with legal and regulatory requirements such as the Securities and Exchange Commission (SEC) and Health Insurance Portability and Accountability Act (HIPPA).

2.5.5.1 Regularly Check Database Files for Corruption

Use RMAN command BACKUP VALIDATE RMAN to regularly check database files for block corruption that has not yet been reported by a user session or by normal backup operations. RMAN scans the specified files and verifies content-checking for physical and logical errors, but does not actually perform the backup or recovery operation. Oracle records the address of the corrupt block and the type of corruption in the control file. Access these records through the V$DATABASE_BLOCK_CORRUPTION view, which can be used by RMAN block media recovery.

If BLOCK CHANGE TRACKING is enabled, then do not use the INCREMENTAL LEVEL option with BACKUP VALIDATE to ensure that all data blocks are read and verified.

To detect all types of corruption that are possible to detect, specify the CHECK LOGICAL option. Do not specify the MAXCORRUPT OR NOCHECKSUM option of the BACKUP VALIDATE command.

2.5.5.2 Periodically Test Recovery Procedures

Complete, successful, and tested backups are fundamental to the success of any recovery. Create test plans for different outage types. Start with the most common outage types and progress to the least probable.

Monitor the backup procedure for errors, and validate backups by testing your recovery procedures periodically. Also, validate the ability to backup and restore by using the RMAN commands BACKUP VALIDATE and RESTORE...VALIDATE .

2.5.5.3 Regularly Backup the Recovery Catalog Database

Include the recovery catalog database in your backup and recovery strategy. If you do not back up the recovery catalog and a disk failure occurs that destroys the recovery catalog database, then you may lose the metadata in the catalog. Without the recovery catalog contents, recovery of your other databases is likely to be more difficult.