Delaying the Application of Redo

Specifying a Time Delay for the Application of Archived Redo Log Files

In some cases, you may want to create a time lag between the time when redo data is received from the primary site and when it is applied to the standby database. You can specify a time interval (in minutes) to protect against the application of corrupted or erroneous data to the standby database. When you set a DELAY interval, it does not delay the transport of the redo data to the standby database. Instead, the time lag you specify begins when the redo data is completely archived at the standby destination.

Note: If you define a delay for a destination that has real-time apply enabled, the delay is ignored.

Specifying a Time Delay

You can set a time delay on primary and standby databases, as follows:

• On the primary database and physical standby databases, use the DELAY=minutes attribute of the LOG_ARCHIVE_DEST_n initialization parameter to delay applying archived redo log files to the standby database. The default setting for this attribute is NODELAY. If you specify the DELAY attribute without specifying a value, then the default delay interval is 30 minutes.
• On logical standby databases, use the DBMS_LOGSTDBY.APPLY_SET procedure.

Setting up a time delay on a standby database supersedes any time delay specified on the primary database. For example:

SQL> RECOVER MANAGED STANDBY DATABASE DELAY <minutes>

In a configuration with multiple standby databases, setting a time lag on more than one standby database can be very useful. For example, you can set up a configuration where each standby database is maintained in varying degrees of synchronization with the primary database.

Canceling a Time Delay

You can cancel a specified delay interval as follows:

• On the primary database and physical standby databases, use the NODELAY keyword of the RECOVER MANAGED STANDBY DATABASE clause:

·         SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

·          

• On logical standby databases, specify the following PL/SQL command:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

Setting Up a Data Protection Mode

Setting Up a Data Protection Mode

Data Guard provides three modes of data protection: maximum protection, maximum availability, and maximum performance. The level of data protection you choose controls what happens if the primary database loses its connection to the standby database. 

To determine the appropriate data protection mode to use, review the following descriptions of the data protection modes to help assess your business requirements for data availability against user demands for response time and performance. 

Maximum Protection Mode

This protection mode guarantees that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to at least one remote standby redo log. For multiple-instance RAC databases, Data Guard shuts down the primary database if it is unable to write the redo records to at least one properly configured database instance. The maximum protection mode requires that you:

• Configure standby redo log files on at least one standby database.
• Set the SYNC, LGWR, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least 1 standby database destination.

Maximum Availability Mode

This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to at least one remote standby redo log. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode.

This mode guarantees that no data loss will occur if the primary database 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.

Like maximum protection mode, the maximum availability mode requires that you:

• Configure standby redo log files on at least one standby database.
• Set the SYNC, LGWR, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least 1 standby database.

Maximum Performance Mode

This protection mode (the default) provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.

When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.

The maximum performance mode enables you to either set the LGWR and ASYNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, you can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.

Nondefault ARCn Archival Processing

To transmit redo data to the standby destination at the same time the online redo log file is being archived to the local online redo log files, set the LOG_ARCHIVE_LOCAL_FIRST=FALSE initialization parameter.

LOG_ARCHIVE_LOCAL_FIRST=FALSE
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/
LOG_ARCHIVE_DEST_2='SERVICE=boston ARCH
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

Specifying a Remote Archiving Destination
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2='SERVICE=boston'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
Initialization Parameters for LGWR Synchronous Archival
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/
LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR SYNC NET_TIMEOUT=30'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
Initialization Parameters for LGWR Asynchronous Archiving
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/
LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC=61440'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE


Figure 5-5 shows a Data Guard configuration that uses the LGWR process to synchronously transmit redo data to the standby system at the same time it is writing redo data to the online redo log file on the primary database. On the standby system, the remote file server (RFS) receives redo data over the network from the LGWR process and writes the redo data to the standby redo log files.

A log switch on the primary database triggers a log switch on the standby database, causing ARCn processes on the standby database to archive the standby redo log files to archived redo log files on the standby database. Then, log apply services use Redo Apply (MRP process) or SQL Apply (LSP process) to apply the redo data to the standby database.

If real-time apply is enabled, Data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process.

Figure 5-5 LGWR SYNC Archival to a Remote Destination with Standby Redo Log Files

Creating a Logical Standby Database

Prerequisite Conditions for Creating a Logical Standby Database

Before you create a logical standby database, you must first ensure the primary database is properly configured.

Checklist of the tasks that you perform on the primary database to prepare for logical standby database creation.

Determine Support for Data Types and Storage Attributes for Tables

Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database.

Ensure Table Rows in the Primary Database Can Be Uniquely Identified

The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database.

Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. When database-wide primary-key and unique-constraint/index supplemental logging is enabled, each UPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.

• If a table has a primary key defined, then the primary key is logged along with the modified columns as part of the UPDATE statement to identify the modified row.
• In the absence of a primary key, the shortest nonnull unique-constraint/index is logged along with the modified columns as part of the UPDATE statement to identify the modified row.
• In the absence of both a primary key and a nonnull unique constraint/index, all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. In other words, all columns except those with the following types are logged: LONG, LOB, LONG RAW, object type, and collections.

Oracle recommends that you add a primary key or a nonnull unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.

Perform the following steps to ensure SQL Apply can uniquely identify rows of each table being replicated in the logical standby database.

Step 1   Find tables without unique logical identifier in the primary database.

Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL Apply may not be able to uniquely identify. For example:

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

  2> WHERE (OWNER, TABLE_NAME) NOT IN

 FROM DBA_LOGSTDBY_UNSUPPORTED)

  4> AND BAD_COLLUMN = 'Y'

  3> (SELECT DISTINCT OWNER, TABLE_NAM

E

Step 2   Add a disabled primary-key RELY constraint.

If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database.

To create a disabled RELY constraint on a primary database table, use the ALTER TABLE statement with a RELY DISABLE clause. The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

When you specify the RELY constraint, the system will assume that rows are unique. Because you are telling the system to rely on the information, but are not validating it on every modification done to the table, you must be careful to select columns for the disabled RELY constraint that will uniquely identify each row in the table. If such uniqueness is not present, then SQL Apply will not correctly maintain the table.

To improve the performance of SQL Apply, add a unique-constraint/index to the columns to identify the row on the logical standby database. Failure to do so results in full table scans during UPDATE or DELETE statements carried out on the table by SQL Apply.

Step-by-Step Instructions for Creating a Logical Standby Database

Create a Physical Standby Database

You create a logical standby database by first creating a physical standby database and then transitioning it to a logical standby database.

Stop Redo Apply on the Physical Standby Database

You can run Redo Apply on the new physical standby database for any length of time before converting it to a logical standby database. However, before converting to a logical standby database, stop Redo Apply on the physical standby database. Stopping Redo Apply is necessary to avoid applying changes past the redo that contains the LogMiner dictionary

To stop Redo Apply, issue the following statement on the physical standby database. If the database is a RAC database comprised of multiple instances, then you must first stop all RAC instances except one before issuing this statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Prepare the Primary Database to Support a Logical Standby Database

 Prepare the Primary Database for Role Transitions

you set up several standby role initialization parameters to take effect when the primary database is transitioned to the physical  standby role. If you plan to transition the primary database to the logical standby role, then you must also include a LOG_ARCHIVE_DEST_3 destination on the primary database so that no parameters need to change after a role transition. This parameter only takes effect when the primary database is transitioned to the standby role.

LOG_ARCHIVE_DEST_3=

 'LOCATION=/arch2/chicago/

  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

  DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_STATE_3=ENABLE

To dynamically set the LOG_ARCHIVE_DEST_3 parameter, use the SQL ALTER SYSTEM SET statement and include the SCOPE=BOTH clause so that the change takes effect immediately and persists after the database is shut down and started up again.

Build a Dictionary in the Redo Data

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. As part of building LogMiner Multiversioned Data Dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to obtain a consistent snapshot of the data dictionary that is then logged in the redo stream. Oracle recommends setting the UNDO_RETENTION initialization parameter to 3600 on both the primary and logical standby databases.

Transition to a Logical Standby Database

Convert to a Logical Standby Database

The redo logs contain the information necessary to convert your physical standby database to a logical standby database. To continue applying redo data to the physical standby database until it is ready to convert to a logical standby database, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;

For db_name, specify a database name to identify the new logical standby database. If you are using a server parameter file (spfile) at the time you issue this statement, then the database will update the file with appropriate information about the new logical standby database. If you are not using a spfile, then the database issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.

The statement waits, applying redo data until the LogMiner dictionary is found in the log files. This may take several minutes; depending on how long it takes redo generated to be transmitted to the standby database, and how much redo data need to be applied. If a dictionary build is not successfully performed on the primary database, this command will never complete. You can cancel the SQL statement by issuing the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL statement from another SQL session.

Create a New Password File

Because the conversion process changes the database name (that was originally set with the DB_NAME initialization parameter) for the logical standby database, you must re-create the password file.

Adjust Initialization Parameters for the Logical Standby Database

On the logical standby database, shutdown the instance and issue the STARTUP MOUNT statement to start and mount the database. Do not open the database; it should remain closed to user access until later in the creation process. For example:

SQL> SHUTDOWN;

SQL> STARTUP MOUNT;

You need to modify the LOG_ARCHIVE_DEST_n parameters because, unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files (online redo log files, archived redo log files, and standby redo log files). It is good practice to specify separate local destinations for:

• Archived redo log files that store redo data generated by the logical standby database. In Example 4-2, this is configured as the LOG_ARCHIVE_DEST_1=LOCATION=/arch1/boston destination.
• Archived redo log files that store redo data received from the primary database. In Example 4-2, this is configured as the LOG_ARCHIVE_DEST_3=LOCATION=/arch2/boston destination.

Example 4-2 shows the initialization parameter changes that were modified for the logical standby database. The parameters shown are valid for the Boston logical standby database when it is running in either the primary or standby database role.

Example 4-2 Modifying Initialization Parameters for a Logical Standby Database

LOG_ARCHIVE_DEST_1=

  'LOCATION=/arch1/boston/

   VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)

   DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_2=

  'SERVICE=chicago LGWR ASYNC

   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

   DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_3=

  'LOCATION=/arch2/boston/

   VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

   DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

The following table describes the archival processing defined by the initialization parameters shown in Example 4-2.

	When the Boston Database Is Running in the Primary Role	When the Boston Database Is Running in the Logical Standby Role
LOG_ARCHIVE_DEST_1	Directs archival of redo data generated by the primary database from the local online redo log files to the local archived redo log files in /arch1/boston/.	Directs archival of redo data generated by the logical standby database from the local online redo log files to the local archived redo log files in /arch1/boston/.
LOG_ARCHIVE_DEST_2	Directs transmission of redo data to the remote logical standby database chicago.	Is ignored; LOG_ARCHIVE_DEST_2 is valid only when boston is running in the primary role.
LOG_ARCHIVE_DEST_3	Is ignored; LOG_ARCHIVE_DEST_3 is valid only when boston is running in the standby role.	Directs archival of redo data received from the primary database to the local archived redo log files in /arch2/boston/.

Open the Logical Standby Database

The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.

To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:

SQL> ALTER DATABASE OPEN RESETLOGS;

Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.

Issue the following statement to begin applying redo data to the logical standby database. For example:

SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;