| Oracle® Data Guard Concepts and Administration 10g Release 1 (10.1) Part Number B10823-01 |
|
|
View PDF |
| Oracle® Data Guard Concepts and Administration 10g Release 1 (10.1) Part Number B10823-01 |
|
|
View PDF |
This chapter describes how redo data is applied to a standby database. It includes the following topics:
Log apply services automatically apply redo to standby databases to maintain synchronization with the primary database and allow transactionally consistent access to the data.
By default, log apply services wait for the full archived redo log file to arrive on the standby database before recovering it to the standby database. Section 5.3.1 and Section 5.3.2 describe how redo data transmitted from the primary database is received by the remote file server process (RFS) on the standby system where the RFS process writes the redo data to either archived redo log files or optionally to standby redo log files. However, if you use standby redo log files, you can optionally enable real-time apply, which allows Data Guard to recover redo data from the current standby redo log file as it is being filled up by the RFS process. Real-time apply is described in more detail in Section 6.2.1.
Log apply services use the following methods to maintain physical and logical standby databases:
Uses media recovery to keep the primary and physical standby databases synchronized.
|
Caution: You can also open a physical standby database in read-only mode to allow users to query the standby database for reporting purposes. While open, redo data is still received; however, Redo Apply stops and the physical standby database is not kept transactionally current with the primary database. If a failure occurs during this time, it can prolong the time it takes for a failover operation to complete. See Section 8.2, "Using a Standby Database That Is Open for Read-Only Access" for more information. |
Reconstitutes SQL statements from the redo received from the primary database and executes the SQL statements against the logical standby database.
Logical standby databases can be opened in read/write mode, but the target tables being maintained by the logical standby database are opened in read-only mode for reporting purposes (providing the database guard was set appropriately, as described in Section 9.1.2). SQL Apply enables you to use the logical standby database for reporting activities, even while SQL statements are being applied.
The sections in this chapter describe Redo Apply, SQL Apply, real-time apply, and delayed apply in more detail.
This section contains the following topics:
If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins. (Standby redo log files are required to use real-time apply.)
Figure 6-1 shows a Data Guard configuration with a local destination and a standby destination. As the remote file server (RFS) process writes the redo data to standby redo log files on the standby database, log apply services can recover redo from standby redo log files as they are being filled.
Text description of the illustration lgwrarch.gif
Use the ALTER DATABASE statement to enable the real-time apply feature, as follows:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.To determine if real-time apply is enabled, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view. It will display MANAGED REAL-TIME APPLY when real-time apply is enabled.
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. |
You can set a time delay on primary and standby databases, as follows:
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.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.
You can cancel a specified delay interval as follows:
NODELAY keyword of the RECOVER MANAGED STANDBY DATABASE clause:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;
These commands result in log apply services immediately beginning to apply archived redo log files to the standby database, before the time interval expires. Also, see:
DELAY attribute of the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statementDBMS_LOGSTDBY.APPLY_SET procedureAs an alternative to the apply delay configuration option, you can use Flashback Database to protect against the application of corrupted or erroneous data to the standby database. Flashback Database can quickly and easily flash back a standby database to an arbitrary point in time. See Oracle Database Backup and Recovery Advanced User's Guide for more information about enabling and using Flashback Database.
See Chapter 10 for scenarios showing how to use Data Guard with Flashback Database, and Oracle Database Backup and Recovery Advanced User's Guide for more information about enabling and using Flashback Database.
By default, the redo data is applied from archived redo log files. When performing Redo Apply, a physical standby database can use the real-time apply feature to apply redo directly from the standby redo log files as they are being written by the RFS process. Also, log apply services cannot apply redo data to a physical standby database when it is opened in read-only mode.
This section contains the following topics:
To start log apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply using the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement.
You can specify that Redo Apply runs as a foreground session or as a background process.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
If you started a foreground session, by default, control is not returned to the command prompt until recovery is canceled by another session.
DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This does not disconnect the current SQL session.
To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
To stop Redo Apply or real-time apply, issue the following SQL statement in another window:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
To monitor the status of the archived redo log and obtain information about log apply services on a physical standby database, query the fixed views described in this section. You can also monitor the standby database using the Oracle Enterprise Manager GUI.
This section contains the following topics:
See Oracle Database Reference for complete reference information about views.
Query the physical standby database to monitor log apply and log transport services activity at the standby site.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 2> FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ------- ------------ ---------- ---------- ---------- ---------- RFS ATTACHED 1 947 72 72 MRP0 APPLYING_LOG 1 946 10 72
The previous query output shows that an RFS process completed archiving the redo log file with sequence number 947. The output also shows Redo Apply when it is actively applying an archived redo log file with the sequence number 946. The recovery operation is currently recovering block number 10 of the 72-block archived redo log file.
To quickly determine the level of synchronization for the standby database, issue the following query on the physical standby database:
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# 2> FROM V$ARCHIVE_DEST_STATUS; ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ---------------- ------------- --------------- ------------ 1 947 1 945
The previous query output shows that the standby database is two archived redo log files behind the primary database. This might indicate a single recovery process is unable to keep up with the volume of the archived redo log files being received. Using the PARALLEL option might be a solution.
To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view. It will contain the value MANAGED REAL TIME when real-time apply is enabled, as shown in the following example:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ; RECOVERY_MODE ----------------------- MANAGED REAL-TIME APPLY
The V$ARCHIVED_LOG fixed view on the physical standby database shows all the archived redo log files received from the primary database. This view is only useful after the standby site starts receiving redo data, because before that time the view is populated by old archived redo log records generated from the primary control file.
For example, you can execute the following SQL*Plus statement:
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, 2> NEXT_CHANGE# FROM V$ARCHIVED_LOG; REGISTRAR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# --------- ------- ---------- ---------- ------------- ------------ RFS ARCH 1 945 74651 74739 RFS ARCH 1 946 74739 74772 RFS ARCH 1 947 74772 74774
The previous query output shows three archived redo log files received from the primary database.
Query the V$LOG_HISTORY fixed view on the physical standby database to show all the archived redo log files that were applied:
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# 2> FROM V$LOG_HISTORY; THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ------------- ------------ 1 945 74651 74739
The previous query output shows that the most recently applied archived redo log file was sequence number 945.
The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.
The following example shows output from the V$DATAGUARD_STATUS view on a primary database:
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS; MESSAGE -------------------------------------------------------------------------------- ARC0: Archival started ARC1: Archival started Archivelog destination LOG_ARCHIVE_DEST_2 validated for no-data-loss recovery Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2' ARCH: Transmitting activation ID 0 LGWR: Completed archiving log 3 thread 1 sequence 11 Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2' LGWR: Transmitting activation ID 6877c1fe LGWR: Beginning to archive log 4 thread 1 sequence 12 ARC0: Evaluating archive log 3 thread 1 sequence 11 ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed ARC0: Beginning to archive log 3 thread 1 sequence 11 Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/arch/arch_1_11.arc' ARC0: Completed archiving log 3 thread 1 sequence 11 ARC1: Transmitting activation ID 6877c1fe 15 rows selected.
The following example shows the contents of the V$DATAGUARD_STATUS view on a physical standby database:
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS; MESSAGE -------------------------------------------------------------------------------- ARC0: Archival started ARC1: Archival started RFS: Successfully opened standby logfile 6: '/oracle/dbs/sorl2.log' ARC1: Evaluating archive log 6 thread 1 sequence 11 ARC1: Beginning to archive log 6 thread 1 sequence 11 Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/arch/arch_1_11.arc' ARC1: Completed archiving log 6 thread 1 sequence 11 RFS: Successfully opened standby logfile 5: '/oracle/dbs/sorl1.log' Attempt to start background Managed Standby Recovery process Media Recovery Log /oracle/arch/arch_1_9.arc 10 rows selected.
Log apply services convert the data from the archived redo log or standby redo log 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.
This section contains the following topics:
To start SQL Apply, start the logical standby database and issue the following statement to recover redo data from archived redo log files on the logical standby database:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
To start real-time apply on the logical standby database to immediately recover redo data from the standby redo log files on the logical standby database, include the IMMEDIATE keyword as shown in the following statement:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To stop SQL Apply, issue the following statement on the logical standby database:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
To monitor the status of archived redo log files and obtain information about SQL Apply, query the fixed views described in this section. You can also monitor the standby database using the Oracle Enterprise Manager GUI. See Appendix A, "Troubleshooting Data Guard" and Oracle Data Guard Broker.
This section contains the following topics:
Also, see the discussion of the V$ARCHIVE_DEST_STATUS fixed view in Section 6.3.4.2 and Oracle Database Reference for complete reference information about views.
If SQL Apply should stop unexpectedly, the reason for the problem is shown in this view.
The view also contains other information, such as which DDL statements were applied and which were skipped. For example:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS'; Session altered. SQL> COLUMN STATUS FORMAT A60 SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS 2 ORDER BY EVENT_TIME, COMMIT_SCN; EVENT_TIME STATUS ------------------------------------------------------------------------------ EVENT ------------------------------------------------------------------------------- 23-JUL-02 18:20:12 ORA-16111: log mining and apply setting up 23-JUL-02 18:20:12 ORA-16128: User initiated shut down successfully completed 23-JUL-02 18:20:12 ORA-16112: log mining and apply stopping 23-JUL-02 18:20:23 ORA-16111: log mining and apply setting up 23-JUL-02 18:55:12 ORA-16128: User initiated shut down successfully completed 23-JUL-02 18:57:09 ORA-16111: log mining and apply setting up 23-JUL-02 20:21:47 ORA-16204: DDL successfully applied create table mytable (one number, two varchar(30)) 23-JUL-02 20:22:55 ORA-16205: DDL skipped due to skip setting create database link mydblink 8 rows selected.
This query shows that SQL Apply was started and stopped a few times. It also shows what DDL was applied and skipped. If SQL Apply had stopped, the last record in the query would have shown the cause of the problem.
The DBA_LOGSTDBY_LOG view provides dynamic information about what is happening to SQL Apply. This view is helpful when you are diagnosing performance problems when SQL Apply is applying archived redo log files to the logical standby database, and it can be helpful for other problems.
For example:
SQL> COLUMN DICT_BEGIN FORMAT A10; SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, 2> TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# AS THR# FROM DBA_LOGSTDBY_LOG 3> ORDER BY SEQUENCE#; FILE_NAME SEQ# FIRST_CHANGE# NEXT_CHANGE# TIMESTAM BEG END THR# ------------------------- ---- ------------- ------------ -------- --- --- ---- /oracle/dbs/hq_nyc_2.log 2 101579 101588 11:02:58 NO NO 1 /oracle/dbs/hq_nyc_3.log 3 101588 142065 11:02:02 NO NO 1 /oracle/dbs/hq_nyc_4.log 4 142065 142307 11:02:10 NO NO 1 /oracle/dbs/hq_nyc_5.log 5 142307 142739 11:02:48 YES YES 1 /oracle/dbs/hq_nyc_6.log 6 142739 143973 12:02:10 NO NO 1 /oracle/dbs/hq_nyc_7.log 7 143973 144042 01:02:11 NO NO 1 /oracle/dbs/hq_nyc_8.log 8 144042 144051 01:02:01 NO NO 1 /oracle/dbs/hq_nyc_9.log 9 144051 144054 01:02:16 NO NO 1 /oracle/dbs/hq_nyc_10.log 10 144054 144057 01:02:21 NO NO 1 /oracle/dbs/hq_nyc_11.log 11 144057 144060 01:02:26 NO NO 1 /oracle/dbs/hq_nyc_12.log 12 144060 144089 01:02:30 NO NO 1 /oracle/dbs/hq_nyc_13.log 13 144089 144147 01:02:41 NO NO 1
The output from this query shows that a LogMiner dictionary build starts at log file sequence number 5. The most recent archived redo log file is sequence number 13, and it was received at the logical standby database at 01:02:41.
This view shows the state of the LSP process and information about the SQL transactions that were executed on the logical standby database. To quickly determine if all redo from the log file was applied, issue the following query on the logical standby database:
SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM D BA_LOGSTDBY_PROGRESS; APPLIED_SCN NEWEST_SCN ----------- ---------- 211301 211357
If the APPLIED_SCN matches the NEWEST_SCN, then all available log information was applied. To determine how much progress was made through the available log files, query the DBA_LOGSTDBY_LOG view, as shown in the following example:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS'; Session altered. SQL> SELECT SEQUENCE#, FIRST_TIME, APPLIED 2 FROM DBA_LOGSTDBY_LOG 3 ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME APPLIED ---------- ------------------ ------- 24 23-JUL-02 18:19:05 YES 25 23-JUL-02 18:19:48 YES 26 23-JUL-02 18:19:51 YES 27 23-JUL-02 18:19:54 YES 28 23-JUL-02 18:19:59 YES 29 23-JUL-02 18:20:03 YES 30 23-JUL-02 18:20:13 YES 31 23-JUL-02 18:20:18 YES 32 23-JUL-02 18:20:21 YES 33 23-JUL-02 18:32:11 YES 34 23-JUL-02 18:32:19 CURRENT 35 23-JUL-02 19:13:20 CURRENT 36 23-JUL-02 19:13:43 CURRENT 37 23-JUL-02 19:13:46 CURRENT 38 23-JUL-02 19:13:50 CURRENT 39 23-JUL-02 19:13:54 CURRENT 40 23-JUL-02 19:14:01 CURRENT 41 23-JUL-02 19:15:11 NO 42 23-JUL-02 19:15:54 NO 19 rows selected.
In the previous query, the computed APPLIED column displays YES, CURRENT, NO. The log files with YES were completely applied and those files are no longer needed by the logical standby database. The log files with CURRENT contain information that is currently being worked on. Because logical standby applies transactions, and because transactions span log files, it is common for SQL Apply to be applying changes from multiple log files. For logs with NO, information from those files is not being applied. Although it is possible that the files might have been open and read.
To inspect the process activity for SQL Apply, query the V$LOGSTDBY fixed view on the logical standby database. This view provides information about the processes that are reading redo data and applying it to logical standby databases. For example:
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-16117: processing READER ORA-16127: stalled waiting for additional transact ions to be applied BUILDER 191896 ORA-16116: no work available PREPARER 191902 ORA-16117: processing ANALYZER 191820 ORA-16120: dependencies being computed for transac tion at SCN 0x0000.0002ed4e APPLIER 191209 ORA-16124: transaction 1 16 1598 is waiting on ano ther transaction APPLIER 191205 ORA-16116: no work available APPLIER 191206 ORA-16124: transaction 1 5 1603 is waiting on anot her transaction APPLIER 191213 ORA-16117: processing APPLIER 191212 ORA-16124: transaction 1 20 1601 is waiting on ano ther transaction APPLIER 191216 ORA-16124: transaction 1 4 1602 is waiting on anot her transaction 11 rows selected
The previous query displays one row for each process involved in reading and applying archived redo log files. The different processes perform different functions as described by the TYPE column. The HIGH_SCN column is a progress indicator. As long as it keeps changing, from query to query, you know progress is being made. The STATUS column gives a text description of activity.
The V$LOGSTDBY_STATS fixed view provides a collection of state and statistical information for SQL Apply. Most options have default values, and this view displays what values are currently in use. It also provides statistical information that helps indicate progress. Issue the following query to view database state information:
SQL> COLUMN NAME FORMAT A35 SQL> COLUMN VALUE FORMAT A35 SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS 2> WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%'; NAME VALUE ----------------------------------- ----------------------------------- coordinator state APPLYING transactions ready 7821 transactions applied 7802 coordinator uptime 73
This query shows how long SQL Apply was running and how many transactions were applied in that time. It also shows how many transactions are available to be applied, indicating that more work is necessary.
Consider using the following methods to optimize the time it takes to apply redo to physical standby databases. Also, see the Oracle Media Recovery Best Practices white paper for more information: http://otn.oracle.com/deploy/availability/htdocs/maa.htm.
During media recovery or Redo Apply, the redo log file is read, and data blocks that require redo application are parsed out. With parallel media recovery, these data blocks are subsequently distributed evenly to all recovery processes to be read into the buffer cache. The default is serial recovery or zero parallelism, which implies that the same recovery process reads the redo, reads the data blocks from disk, and applies the redo changes.
To implement parallel media recovery or Redo Apply, add the optional PARALLEL clause to the recovery command. Furthermore, set the database parameter PARALLEL_MAX_SERVERS to at least the degree of parallelism. The following examples show how to set recovery parallelism:
RECOVER STANDBY DATABASE PARALLEL #CPUs * 2;
You should compare several serial and parallel recovery runs to determine optimal recovery performance.
Setting the DB_BLOCK_CHECKING=FALSE parameter during standby or media recovery can provide as much as a twofold increase in the apply rate. The lack of block checking during recovery must be an accepted risk. Block checking should be enabled on the primary database. The DB_BLOCK_CHECKSUM=TRUE (the default) should be enabled for both production and standby databases. Because the DB_BLOCK_CHECKING parameter is dynamic, it can be toggled without shutting down the standby database.
When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.
The PARALLEL_EXECUTION_MESSAGE_SIZE parameter is also used by parallel query operations and should be tested with any parallel query operations to ensure there is sufficient memory on the system. A large number of parallel query slaves on a 32-bit installation may reach memory limits and prohibit increasing the PARALLEL_EXECUTION_MESSAGE_SIZE from the default 2K (2048) to 4K.
The biggest bottlenecks encountered during recovery are read and write I/O. To relieve the bottleneck, use native asynchronous I/O and set the database parameter DISK_ASYNCH_IO to TRUE (the default). The DISK_ASYNCH_IO parameter controls whether or not network I/O to datafiles is asynchronous. Asynchronous I/O should significantly reduce database file parallel reads and should improve overall recovery time.
|
 Copyright © 1999, 2003 Oracle Corporation All Rights Reserved. |
|