Managing a Logical Standby Database
9.1 Overview of the SQL Apply Architecture
Managing a Logical Standby Database
This chapter contains the following topics:
■ Overview of the SQL Apply Architecture
■ Views Related to Managing and Monitoring a Logical Standby Database
■ Monitoring a Logical Standby Database
■ Customizing a Logical Standby Database
■ Managing Specific Workloads In the Context of a Logical Standby Database
■ Tuning a Logical Standby Database
9.1 Overview of the SQL Apply Architecture
SQL Apply uses a collection of parallel execution servers and background processes to apply changes from the primary database to the logical standby database.
Figure 9–1 shows the flow of information and the role that each process performs.
Figure 9–1 SQL Apply Processing
Redo Data from Primary Database
Reader
Redo Records
Logical Change Reords Not Grouped
Into Transactions
Log Mining Apply Processing
Preparer
LCR LCR LCR
. . . Shared Pool
Builder
Transaction Groups
Analyzer
Applier Coordinator
Transactions to be Applied
Transactions Sorted in Dependency Order LCR
Overview of the SQL Apply Architecture
The different processes involved and their functions during log mining and apply processing are as follows:
During log mining:
■ The READER process reads redo records from the archived redo log files or standby redo log files.
■ The PREPARER process converts block changes contained in redo records into logical change records (LCRs). Multiple PREPARER processes can be active for a given archived redo log file or standby redo log file. The LCRs are staged in the shared pool of the system global area (SGA), known as the LCR cache.
■ The BUILDER process groups LCRs into transactions, and performs other tasks, such as memory management in the LCR cache, checkpointing related to SQL Apply restart and filtering out of uninteresting changes.
During apply processing:
■ The ANALYZER process examines the transaction chunks containing a group of LCRs, possibly filtering out uninteresting transactions, and identifying
dependencies between different transactions.
■ The COORDINATOR process (LSP):
– Assigns transactions
– Monitors dependencies between transactions and coordinates scheduling – Authorizes the commitment of changes to the logical standby database
■ The APPLIER processes:
– Applies the LCRs to the database
– Asks the COORDINATOR process to approve transactions with unresolved dependencies
– Commits the transactions
You can query the V$LOGSTDBY_PROCESS view to examine the activity of the SQL Apply processes. Another view that provides information about current activity is the V$LOGSTDBY_STATS view that displays statistics, current state, and status
information for the logical standby database during SQL Apply activities. These and other relevant views are discussed in more detail in Section 9.2, "Views Related to Managing and Monitoring a Logical Standby Database".
9.1.1 Various Considerations for SQL Apply
This section contains the following topics:
■ Transaction Size Considerations
■ Pageout Considerations
■ Restart Considerations
■ DML Apply Considerations
■ DDL Apply Considerations
9.1.1.1 Transaction Size Considerations
SQL Apply categorizes transactions into two classes: small and large:
Overview of the SQL Apply Architecture
■ Small transactions—SQL Apply starts applying LCRs belonging to a small transaction once it has encountered the commit record for the transaction in the redo log files.
■ Large transactions—SQL Apply breaks large transactions into smaller pieces called transaction chunks, and starts applying the chunks before the commit record for the large transaction is seen in the redo log files. This is done to reduce
memory pressure on the LCR cache and to reduce the overall failover time.
For example, without breaking into smaller pieces, a SQL*Loader load of ten million rows, each 100 bytes in size, would use more than 1 GB of memory in the LCR cache. If the memory allocated to the LCR cache was less than 1 GB, it would result in pageouts from the LCR cache.
Apart from the memory considerations, if SQL Apply did not start applying the changes related to the ten million row SQL*Loader load until it encountered the COMMIT record for the transaction, it could stall a role transition. A switchover or a failover that is initiated after the transaction commit cannot finish until SQL Apply has applied the transaction on the logical standby database.
All transactions start out categorized as small transactions. Depending on the amount of memory available for the LCR cache and the amount of memory consumed by LCRs belonging to a transaction, SQL Apply determines when to recategorize a transaction as a large transaction.
9.1.1.2 Pageout Considerations
Pageouts occur in the context of SQL Apply when memory in the LCR cache is exhausted and space needs to be released for SQL Apply to make progress.
For example, assume the memory allocated to the LCR cache is 100 MB and SQL Apply encounters an INSERT transaction to a table with a LONG column of size 300 MB. In this case, the log-mining component will page out the first part of the LONG data to read the later part of the column modification. In a well-tuned logical standby database, pageout activities will occur occasionally and should not effect the overall throughput of the system.
9.1.1.3 Restart Considerations
Modifications made to the logical standby database do not become persistent until the commit record of the transaction is mined from the redo log files and applied to the logical standby database. Thus, every time SQL Apply is stopped, whether as a result of a user directive or because of a system failure, SQL Apply must go back and mine the earliest uncommitted transaction again.
In cases where a transaction does little work but remains open for a long period of time, restarting SQL Apply is prohibitively costly. This is because SQL Apply may have to mine a large number of archived redo log files again, just to read the redo data for a few uncommitted transactions. To mitigate this, SQL Apply periodically
checkpoints old uncommitted data. The SCN at which the checkpoint is taken is reflected in the RESTART_SCN column of V$LOGSTDBY_PROGRESS view.
Upon restarting, SQL Apply starts mining redo records that are generated at an SCN See Also: See Section 9.4, "Customizing a Logical Standby Database"
for more information about how to identify problematic pageouts and perform corrective actions
Views Related to Managing and Monitoring a Logical Standby Database
Certain workloads, such as large DDL transactions, parallel DML statements (PDML), and direct-path loads, will prevent the RESTART_SCN from advancing for the duration of the workload.
9.1.1.4 DML Apply Considerations
SQL Apply has the following characteristics when applying DML transactions that affect the throughput and latency on the logical standby database:
■ Batch updates or deletes done on the primary database, where a single statement results in multiple rows being modified, are applied as individual row
modifications on the logical standby database. Thus, it is imperative for each maintained table to have a unique or a primary key. See Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified" for more information.
■ Direct path inserts performed on the primary database are applied using a conventional INSERT statement on the logical standby database.
■ Parallel DML (PDML) transactions are not executed in parallel on the logical standby database.
9.1.1.5 DDL Apply Considerations
SQL Apply has the following characteristics when applying DDL transactions that affect the throughput and latency on the logical standby database:
■ Parallel DDL (PDDL) transactions are not performed in parallel on a logical standby database.
■ DDL transactions are applied serially on the logical standby database. Thus, DDL transactions applied concurrently on the primary database are applied one at a time on the logical standby database.
■ CREATE TABLE AS SELECT (CTAS) statements are executed such that the DML activities (that are part of the CTAS statement) are suppressed on the logical standby database. The rows inserted in the newly created table as part of the CTAS statement are mined from the redo log files and applied to the logical standby