6.4 Monitoring Redo Transport Services
This section discusses the following topics:
6.4.1 Monitoring Redo Transport Status
- Step 1 Determine the most recently archived redo log file.
- Perform the following query on the redo source database to determine the most recently archived sequence number for each thread:
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG - > WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) - > GROUP BY THREAD#; - Step 2 Determine the most recently archived redo log file at each redo transport destination.
- Perform the following query on the redo source database to determine the most recently archived redo log file at each redo transport destination:
SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# - > FROM V$ARCHIVE_DEST_STATUS - > WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ# ------------------ ------ ---------------- ------------- /private1/prmy/lad VALID 1 947 standby1 VALID 1 947The most recently archived redo log file should be the same for each destination. If it is not, a status other than VALID may identify an error encountered during the archival operation to that destination. - Step 3 Find out if archived redo log files have been received at a redo transport destination.
- A query can be performed at a redo source database to find out if an archived redo log file has been received at a particular redo transport destination. Each destination has an ID number associated with it. You can query the
DEST_IDcolumn of theV$ARCHIVE_DESTview on a database to identify each destination's ID number.Assume that destination 1 points to the local archived redo log and that destination 2 points to a redo transport destination. Perform the following query at the redo source database to find out if any log files are missing at the redo transport destination:SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM - > (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) - > LOCAL WHERE - > LOCAL.SEQUENCE# NOT IN - > (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND - > THREAD# = LOCAL.THREAD#); THREAD# SEQUENCE# --------- --------- 1 12 1 13 1 14 - Step 4 Trace the progression of redo transmitted to a redo transport destination.
- Set the
LOG_ARCHIVE_TRACEdatabase initialization parameter at a redo source database and at each redo transport destination to trace redo transport progress. SeeAppendix F for complete details and examples.
6.4.2 Monitoring Synchronous Redo Transport Response Time
The
V$REDO_DEST_RESP_HISTOGRAM view contains response time data for each redo transport destination. This response time data is maintained for redo transport messages sent via the synchronous redo transport mode.
The data for each destination consists of a series of rows, with one row for each response time. To simplify record keeping, response times are rounded up to the nearest whole second for response times less than 300 seconds. Response times greater than 300 seconds are round up to 600, 1200, 2400, 4800, or 9600 seconds.
Each row contains four columns:
FREQUENCY, DURATION, DEST_ID, and TIME.
The
FREQUENCY column contains the number of times that a given response time has been observed. The DURATION column corresponds to the response time. TheDEST_ID column identifies the destination. The TIME column contains a timestamp taken when the row was last updated.
The response time data in this view is useful for identifying synchronous redo transport mode performance issues that can affect transaction throughput on a redo source database. It is also useful for tuning the
NET_TIMEOUT attribute.
The next three examples show example queries for destination 2, which corresponds to the
LOG_ARCHIVE_DEST_2 parameter. To display response time data for a different destination, simply change the DEST_ID in the query.
Perform the following query on a redo source database to display the response time histogram for destination 2:
SQL> SELECT FREQUENCY, DURATION FROM -
> V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1;
Perform the following query on a redo source database to display the slowest response time for destination 2:
SQL> SELECT max(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM -
> WHERE DEST_ID=2 AND FREQUENCY>1;
Perform the following query on a redo source database to display the fastest response time for destination 2:
SQL> SELECT min( DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM -
> WHERE DEST_ID=2 AND FREQUENCY>1;
Note:
The highest observed response time for a destination cannot exceed the highest specified NET_TIMEOUT value specified for that destination, because synchronous redo transport mode sessions are terminated if a redo transport destination does not respond to a redo transport message within NET_TIMEOUTseconds.6.4.3 Redo Gap Detection and Resolution
A redo gap occurs whenever redo transmission is interrupted. When redo transmission resumes, redo transport services automatically detects the redo gap and resolves it by sending the missing redo to the destination.
The time needed to resolve a redo gap is directly proportional to the size of the gap and inversely proportional to the effective throughput of the network link between the redo source database and the redo transport destination. Redo transport services has two options that may reduce redo gap resolution time when low performance network links are used:
- Redo Transport CompressionThe
COMPRESSIONattribute of theLOG_ARCHIVE_DEST_nparameter is used to specify that redo data be compressed before transmission to the destination. - Parallel Redo Transport Network SessionsThe
MAX_CONNECTIONSattribute of theLOG_ARCHIVE_DEST_nparameter can be used to specify that more than one network session be used to send the redo needed to resolve a redo gap.
See Chapter 15, "LOG_ARCHIVE_DEST_n Parameter Attributes" for more information about the
COMPRESSION and MAX_CONNECTIONS attributes.6.4.3.1 Manual Gap Resolution
In some situations, gap resolution cannot be performed automatically and it must be performed manually. For example, redo gap resolution must be performed manually on a logical standby database if the primary database is unavailable.
Perform the following query at the physical standby database to determine if there is redo gap on a physical standby database:
SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 7 10
The output from the previous example indicates that the physical standby database is currently missing log files from sequence 7 to sequence 10 for thread 1.
Perform the following query on the primary database to locate the archived redo log files on the primary database (assuming the local archive destination on the primary database is
LOG_ARCHIVE_DEST_1):SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND -
> DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;
NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc
/primary/thread1_dest/arcr_1_8.arc
/primary/thread1_dest/arcr_1_9.arc
Note:
This query may return consecutive sequences for a given thread. In that case, there is no actual gap, but the associated thread was disabled and enabled within the time period of generating these two archived logs. The query also does not identify the gap that may exist at the tail end for a given thread. For instance, if the primary database has generated archived logs up to sequence 100 for thread 1, and the latest archived log that the logical standby database has received for the given thread is the one associated with sequence 77, this query will not return any rows, although we have a gap for the archived logs associated with sequences 78 to 100.
Copy these log files to the physical standby database and register them using the
ALTER DATABASE REGISTER LOGFILE. For example:SQL> ALTER DATABASE REGISTER LOGFILE -
> '/physical_standby1/thread1_dest/arcr_1_7.arc';
SQL> ALTER DATABASE REGISTER LOGFILE -
> '/physical_standby1/thread1_dest/arcr_1_8.arc';
SQL> ALTER DATABASE REGISTER LOGFILE -
> '/physical_standby1/thread1_dest/arcr_1_9.arc';
Note:
The V$ARCHIVE_GAP view on a physical standby database only returns the gap that is currently blocking Redo Apply from continuing. After resolving the gap, query the V$ARCHIVE_GAP view again on the physical standby database to determine if there is another gap sequence. Repeat this process until there are no more gaps.
To determine if there is a redo gap on a logical standby database, query the
DBA_LOGSTDBY_LOG view on the logical standby database. For example, the following query indicates there is a gap in the sequence of archived redo log files because it displays two files for THREAD 1 on the logical standby database. (If there are no gaps, the query will show only one file for each thread.) The output shows that the highest registered file is sequence number 10, but there is a gap at the file shown as sequence number 6:SQL> COLUMN FILE_NAME FORMAT a55
SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L -
> WHERE NEXT_CHANGE# NOT IN -
> (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#) -
> ORDER BY THREAD#, SEQUENCE#;
THREAD# SEQUENCE# FILE_NAME
---------- ---------- -----------------------------------------------
1 6 /disk1/oracle/dbs/log-1292880008_6.arc
1 10 /disk1/oracle/dbs/log-1292880008_10.arc
Copy the missing log files, with sequence numbers 7, 8, and 9, to the logical standby system and register them using the
ALTER DATABASE REGISTER LOGICAL LOGFILE statement. For example:SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE -
> '/disk1/oracle/dbs/log-1292880008_7.arc';
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE -
> '/disk1/oracle/dbs/log-1292880008_8.arc';
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE -
> '/disk1/oracle/dbs/log-1292880008_9.arc';
Note:
A query based on the DBA_LOGSTDBY_LOG view on a logical standby database, as specified above, only returns the gap that is currently blocking SQL Apply from continuing. After resolving the gap, query the DBA_LOGSTDBY_LOG view again on the logical standby database to determine if there is another gap sequence. Repeat this process until there are no more gaps.6.4.4 Redo Transport Services Wait Events
Table 6-2 lists several of the Oracle wait events used to track redo transport wait time on a redo source database. These wait events are found in the
V$SYSTEM_EVENTdynamic performance view.
For a complete list of the Oracle wait events used by redo transport, see the Oracle Data Guard Redo Transport and Network Best Practices white paper on the Oracle Maximum Availability Architecture (MAA) home page at:
| Wait Event | Description |
|---|---|
LNS wait on ATTACH
|
Total time spent waiting for redo transport sessions to be established to all
ASYNC and SYNC redo transport destinations |
LNS wait on SENDREQ
|
Total time spent waiting for redo data to be written to all
ASYNC and SYNC redo transport destinations |
LNS wait on DETACH
|
Total time spent waiting for redo transport connections to be terminated to all
ASYNC and SYNC redo transport destinations |
No comments:
Post a Comment