Applying Redo Data To Logical Standby Databases

Applying Redo Data to Logical Standby Databases Log apply services convert the data from the redo logs into SQL statements and then executes these SQL statements on the logical standby database. Because the logical standby database remains open, tables that are maintained can be used simultaneously for other tasks such as reporting, summations, and queries. Figure 6-2 shows log apply services applying redo data to a logical standby database. Figure 6-2 Automatic Updating of a Logical Standby Database

Text description of the illustration sqlapply.gif The logical standby database uses the following processes: •

Remote file server (RFS) The remote file server process receives redo data from the primary database. The RFS process communicates with the logical standby process (LSP) to coordinate and record which files arrived.

•

Logical standby process (LSP) The logical standby process is the coordinator process for a set of processes that concurrently read, prepare, build, analyze, and apply completed SQL transactions from the archived redo logs. The LSP also maintains metadata in the database.

Table 6-2 summarizes the basic tasks for configuring log apply services.

Table 6-2 Task List: Configuring Log Apply Services for Logical Standby Databases

Step

Task

See ...

1

Start log apply services.

Section 6.3.1

2

Ensure that redo logs are being applied.

Section 6.3.2

3

Manage SQL apply operations.

Section 9.1

In addition to providing detailed information about the tasks presented in Table 6-2, the following sections also describe how to delay the application of archived redo logs.

6.3.1 Starting and Stopping Log Apply Services To start log apply services, start the logical standby database, and then use the following statement. (Starting a logical standby database is done in the same manner as starting a primary database.) SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

To stop log apply services, use the following statement: SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

6.3.2 Ensuring That Redo Logs Are Being Applied Redo logs are read and applied to a logical standby database when a log switch occurs, not as they arrive on the standby site. You can verify the status of archived redo log apply operations by querying the following views: •

V$LOGSTDBY

Use this view to verify that the archived redo logs are being applied to the standby database. This view provides information about the processes that are reading redo data and applying archived redo logs to logical standby databases. For example, the following query shows typical output during the initialization phase: SQL> COLUMN STATUS FORMAT A50 SQL> COLUMN TYPE FORMAT A12 SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY; TYPE HIGH_SCN STATUS ------------ ----------------------------------------------------------COORDINATOR ORA-16115: loading Log Miner dictionary data READER ORA-16127: stalled waiting for additional transact

BUILDER PREPARER

ions to be applied ORA-16117: processing ORA-16116: no work available

SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY; TYPE HIGH_SCN STATUS ------------ ----------------------------------------------------------COORDINATOR ORA-16126: loading table or sequence object number READER ORA-16116: no work available BUILDER ORA-16116: no work available PREPARER ORA-16116: no work available

•

DBA_LOGSTDBY_PROGRESS

Use this view for information about the progress of the log apply services. This view shows the state of the LSP and information about the SQL transactions that were executed on the logical standby database. For example: SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS; APPLIED_SCN NEWEST_SCN ----------- ---------180702 180702

When the numbers in the APPLIED_SCN and NEWEST_SCN columns are equal (as shown in the query example), it means that all of the available data in the redo log was applied. These values can be compared to the values in the FIRST_CHANGE# column in the DBA_LOGSTDBY_LOG view to see how much log information has to be applied and how much remains. See Also: Chapter 9 for information about managing a logical standby database and Chapter 14 for more information about views that are used in a Data Guard environment