Data Guard SQL Apply Mode Tips Oracle Tips by Burleson

Data Guard Apply Modes

The log apply service in a Data Guard environment keeps the Oracle instance up to date with the primary database. The log apply mechanism differs based on the type of standby database. A physical Oracle instance is an exact replica of the primary database; therefore, the log apply service does a block-per-block media recovery using archived redo logs of the primary database.

The log apply service on a logical standby database uses the SQL apply mode to recover the standby database. In the following two sections details on the apply mechanism will be presented.

Redo Log Apply Mode

The principle of the Redo Apply mode in a Data Guard environment is the same as the media recovery of any Oracle database. In the redo apply mode, the archived redo log files on the standby site are read and applied to the standby database. The redo records generated at the primary database and recorded in the archived redo log files are used to roll forward the standby database, just as it works in media recovery.

During this phase, the undo records, which are registered in the undo segment of the standby database, are also generated. These undo records are then used to roll back the standby database in order to discard any uncommitted transactions that may have been applied during the roll forward phase of recovery. The redo apply mode is used by the log apply service on physical standby databases.

SQL Apply Mode

The log apply service uses the SQL apply mode to update logical standby databases. In the SQL apply mode, the log apply service starts a LSP on the standby site, which reads redo records from archived redo log files and then converts the redo records into SQL statements.

Depending on the subset of objects to be maintained in the logical standby database, the log apply engine filters the SQL statements and then selectively applies those to the standby database. A SQL apply mode uses log miner technology to generate the SQL statements from the redo records of the archived redo log file. A brief overview of Log Miner technology is provided in the next section.

The following background processes and parallel execution servers are used in the SQL apply mode:

• COORDINATOR – This is the main LSP process that starts other background processes and coordinates the tasks performed by the other background processes involved in recovery.
   

• READER – Reads the redo entries from the archived redo log file.
   

• PREPARER – Redo entries in the redo log files are stored in the form of changes in Oracle blocks. This process converts the changes in blocks to changes in Oracle segments.
   

• BUILDER – Builds the completed transactions to be applied on the standby database.