Creating a Logical Standby Database
Step 5 Confirm the configuration is operating in the new protection mode
5.7 Managing Log Files
---MAXIMUM AVAILABILITY ---MAXIMUM AVAILABILITY
See Chapter 15 and Oracle Database SQL Reference for information about SQL statements.
5.7 Managing Log Files
This section contains the following topics:
■ Specifying Alternate Directory Locations for Archived Redo Log Files
■ Reusing Online Redo Log Files
■ Managing Standby Redo Log Files
■ Planning for Growth and Reuse of the Control Files
■ Sharing a Log File Destination Among Multiple Standby Databases
5.7.1 Specifying Alternate Directory Locations for Archived Redo Log Files
Typically, when redo data is received from the primary database, the redo data is written to archived redo log files that are stored in the directory you specify with the LOCATION attribute of the LOG_ARCHIVE_DEST_n parameter. Alternatively, you can specify the STANDBY_ARCHIVE_DEST initialization parameter on the standby database to indicate an alternate directory where the archived redo log files are to be stored when received from the primary database.
If both parameters are specified, the STANDBY_ARCHIVE_DEST initialization
parameter overrides the directory location specified with the LOG_ARCHIVE_DEST_n parameter.
The location where archived redo log files are stored on the standby database is determined according to the following list of rules. When the database instance is started, the archived redo log files are evaluated in the list order:
1. If the STANDBY_ARCHIVE_DEST initialization parameter is specified on the standby database, that location is used.
2. If the LOG_ARCHIVE_DEST_n parameter contains the VALID_FOR=(STANDBY_
LOGFILE,*) attribute, then the location specified for this destination is used.
3. If the COMPATIBLE parameter is set to 10.0 or greater and none of the LOG_
ARCHIVE_DEST_n parameters contain the VALID_FOR=(STANDBY_
LOGFILE,*)attribute, then an arbitrary LOG_ARCHIVE_DEST_n parameter that is valid for the destination is used.
4. If none of the initialization parameters have been specified, then archived redo log files are stored in the default location for the STANDBY_ARCHIVE_DEST
initialization parameter.
Managing Log Files
SQL> SELECT DEST_NAME, DESTINATION FROM V$ARCHIVE_DEST 2> WHERE DEST_NAME='STANDBY_ARCHIVE_DEST';
DEST_NAME
---
---DESTINATION
---
---STANDBY_ARCHIVE_DEST /oracle/dbs/arch
Redo transport services use the value specified with the STANDBY_ARCHIVE_DEST initialization parameter in conjunction with the LOG_ARCHIVE_FORMAT parameter to generate the filenames for the archived redo log files on the standby site. For example:
STANDBY_ARCHIVE_DEST='/arc_dest/arls' LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
In the example, %s corresponds to the sequence number, and %r corresponds to the resetlogs ID. Together, these ensure unique names are constructed for the archived redo log files across multiple incarnations of the database. The %t, which is required for Real Application Clusters configurations, corresponds to the thread number.
For a physical standby database, redo transport services store the fully qualified filenames in the standby database control file, and Redo Apply uses this information to perform recovery on the standby database.
To display the list of archived redo log files that are on the standby system, query the V$ARCHIVED_LOG view on the standby database:
SQL> SELECT NAME FROM V$ARCHIVED_LOG;
NAME ---/arc_dest/log_1_771.arc
/arc_dest/log_1_772.arc /arc_dest/log_1_773.arc /arc_dest/log_1_774.arc /arc_dest/log_1_775.arc
5.7.2 Reusing Online Redo Log Files
You can specify a policy for reusing the online redo log file by setting the OPTIONAL or MANDATORY attribute of the LOG_ARCHIVE_DEST_n parameter. By default, remote destinations are set to OPTIONAL. The archival operation of an optional destination can fail, and the online redo log file can be reused even though transmitting the redo data and writing the log contents was not successful. If the archival operation of a mandatory destination fails, online redo log files cannot be overwritten until the failed archive is completed to the mandatory destination.
By default, one local destination is mandatory even if you designate all destinations to be optional.
Note: If you have specified the TEMPLATE attribute of the LOG_
ARCHIVE_DEST_n parameter, it will override the filename
generated with the STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_
FORMAT parameter. See Chapter 14 for information about the TEMPLATE attributes.
Managing Log Files
Example 5–8 shows how to set a mandatory local archiving destination and enable that destination. When specifying the MANDATORY attribute, also consider specifying the REOPEN and MAX_FAILURE attributes as described in Section 5.5 to handle failure conditions.
Example 5–8 Setting a Mandatory Archiving Destination LOG_ARCHIVE_DEST_3 = 'LOCATION=/arc_dest MANDATORY'
5.7.3 Managing Standby Redo Log Files
This section contains the following topics:
■ Determining If a Standby Redo Log File Group Configuration Is Adequate
■ Adding Standby Redo Log Members to an Existing Group
5.7.3.1 Determining If a Standby Redo Log File Group Configuration Is Adequate
The easiest way to verify the standby redo log has an appropriate number of log file groups is to examine the RFS process trace file and database alert log. If either log contains messages that indicate the RFS process frequently has to wait for a group because archiving did not complete, then add more log file groups to the standby redo log. The additional standby redo log file groups give the archival operation time to complete before the standby redo log file is reused by the RFS process.
5.7.3.2 Adding Standby Redo Log Members to an Existing Group
In some cases, it might not be necessary to create a complete group of standby redo log files. A group could already exist, but may not be complete because one or more members were dropped (for example, because of disk failure). In this case, you can add new members to an existing group.
To add new members to a standby redo log file group, use the ALTER DATABASE statement with the ADD STANDBY LOGFILE MEMBER clause. The following statement adds a new member to the standby redo log file group number 2:
SQL> ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/disk1/oracle/dbs/log2b.rdo' 2> TO GROUP 2;
Use fully qualified filenames of new members to indicate where the file should be created. Otherwise, files will be created in either the default or current directory of the database, depending on your operating system.
5.7.4 Planning for Growth and Reuse of the Control Files
This section describes:
Caution: Whenever you add an online redo log file group to the primary database, you must add a corresponding standby redo log file group to the standby database. If the number of standby redo log file groups is inadequate, the primary database will shut down if it is operating in maximum protection mode or switch to
maximum performance mode if it is operating in maximum availability mode.
Managing Log Files
5.7.4.1 Sizing the Disk Volumes that Contain the Control Files
As archived redo log files are generated and RMAN backups are made, Oracle adds new records to the reusable section of the control file. If no records are available for reuse (because all records are still within the number of days specified by CONTROL_
FILE_RECORD_KEEP_TIME), then the control file is expanded and new records are added to the control file.
The maximum control file size is 20000 database blocks. If DB_BLOCK_SIZE equals 8192, then the maximum control file size is 156 MB. If the control files are stored in pre-created volumes, then the volumes that contain the primary and standby control files should be sized to accommodate a control file of maximum size. If the control file volume is too small and cannot be extended, then existing records in the control file will be overwritten before their intended reuse. This behavior is indicated by the following message in the alert log:
krcpwnc: following controlfile record written over:
5.7.4.2 Specifying the Reuse of Records in the Control File
The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter specifies the minimum number of days that must pass before a reusable record in the control file can be reused. Setting this parameter appropriately prevents redo transport services from overwriting a reusable record in the control file and ensures redo information remains available on the standby database:
■ Set CONTROL_FILE_RECORD_KEEP_TIME to a value that allows all on disk backup information to be retained in the control file. CONTROL_FILE_RECORD_
KEEP_TIME specifies the number of days that records are kept within the control file before becoming a candidate for reuse.
■ Set CONTROL_FILE_RECORD_KEEP_TIME to a value slightly longer than the oldest backup file that you intend to keep on disk, as determined by the size of the backup area.
For example, if the backup area is sized to maintain two full backups that are taken every 7 days, as well as daily incremental backups and archived redo log files, then set CONTROL_FILE_RECORD_KEEP_TIME to a value of 21 or 30.
Records older than this will be reused. However, the backup metadata will still be available in the RMAN recovery catalog.
Make sure you specify a large enough value if an apply delay is also set for the standby database (described in Section 6.2.2). The range of values for this parameter is 0 to 365 days. The default value is 7 days.
See Oracle Database Reference for more details about the CONTROL_FILE_RECORD_
KEEP_TIME initialization parameter and Oracle Database Backup and Recovery Advanced User's Guide.
5.7.5 Sharing a Log File Destination Among Multiple Standby Databases
Use the DEPENDENCY attribute of the LOG_ARCHIVE_DEST_n initialization parameter to define one archival destination to receive redo data on behalf of several
destinations, rather than transmitting redo data to each individual destination.
Figure 5–7 shows a Data Guard configuration in which the primary database
transports redo data to one archiving destination that shares its archived redo log files with both a logical standby database and a physical standby database. These
destinations are dependent on the successful completion of archival operations to the parent destination.
Managing Archive Gaps
Figure 5–7 Data Guard Configuration with Dependent Destinations
Specifying a destination dependency can be useful in the following situations:
■ When you configure a physical standby database and a logical standby database on the same system.
■ When you configure the standby database and the primary database on the same system. Therefore, the archived redo log files are implicitly accessible to the standby database.
■ When clustered file systems are used to provide remote standby databases with access to the primary database archived redo log files.
■ When operating system-specific network file systems are used, providing remote standby databases with access to the primary database archived redo log files.
For example, assume there are two standby databases stdby1 and stdby2 that reside on the same piece of hardware. There is no need to use network bandwidth and disk space to send the same redo data to both destinations. The databases can share the same archived redo log files if you use the DEPENDENCY attribute to designate one of the destinations as being a dependent destination. That is, the primary database sends redo to be archived on the destination that is not defined as the dependent destination.
If the redo data successfully arrives at that destination, the primary database considers it archived to both destinations. For example:
LOG_ARCHIVE_DEST_1='LOCATION=DISK1 MANDATORY' LOG_ARCHIVE_DEST_2='SERVICE=stdby1 OPTIONAL'
LOG_ARCHIVE_DEST_3='SERVICE=stdby2 OPTIONAL DEPENDENCY=LOG_ARCHIVE_DEST_2'
With these parameter definitions, the primary database transmits redo data to stdby1 but not to stdby2. The stdby2 database instead recovers redo from the archived redo log files that are shipped to stdby1.