Monitoring Log Apply Services For Logical Standby Databases

Monitoring Log Apply Services for Logical Standby Databases To monitor the status of archived redo logs and obtain information on log apply services on a logical standby database, query the fixed views described in this section. You can also monitor the standby database using Data Guard Manager. See Also: Appendix A, "Troubleshooting the Standby Database" This section contains the following topics: • • • • •

Accessing the DBA_LOGSTDBY_EVENTS View Accessing the DBA_LOGSTDBY_LOG View Accessing the DBA_LOGSTDBY_PROGRESS View Accessing the V$LOGSTDBY Fixed View Accessing the V$LOGSTDBY_STATS Fixed View

6.6.1 Accessing the DBA_LOGSTDBY_EVENTS View If log apply services should stop unexpectedly, the reason for the problem is shown in this view. Note: Errors that cause SQL apply operations to stop are always recorded in the events table (unless there is insufficient space in the system tablespace). These events are always put into the ALERT.LOG file as well, with the phrase 'LOGSTDBY event' included in the text. When querying the view, select the columns in order by EVENT_TIME, COMMIT_SCN, and CURRENT_SCN. This ordering ensures that a shutdown failure appears last in the 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 Session altered.

= 'DD-MON-YY HH24:MI:SS';

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 log apply services were started and stopped a few times. It also shows what DDL was applied and skipped. If log apply services had stopped, the last record in the query would have shown the cause of the problem.

6.6.2 Accessing the DBA_LOGSTDBY_LOG View The DBA_LOGSTDBY_LOG view provides dynamic information about what is happening to log apply services. This view is helpful when you are diagnosing performance problems with log apply services applying archived redo logs to the logical standby database, and it can be helpful for other problems. For example: SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, 2> TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG 3> ORDER BY SEQUENCE#; FILE_NAME END THR# --------------------------- ---/oracle/dbs/hq_nyc_2.log NO 1 /oracle/dbs/hq_nyc_3.log NO 1 /oracle/dbs/hq_nyc_4.log NO 1 /oracle/dbs/hq_nyc_5.log YES 1

SEQ# FIRST_CHANGE# NEXT_CHANGE# TIMESTAM BEG ---- ------------- ------------ -------- --2

101579

101588 11:02:58 NO

3

101588

142065 11:02:02 NO

4

142065

142307 11:02:10 NO

5

142307

142739 11:02:48 YES

/oracle/dbs/hq_nyc_6.log NO 1 /oracle/dbs/hq_nyc_7.log NO 1 /oracle/dbs/hq_nyc_8.log NO 1 /oracle/dbs/hq_nyc_9.log NO 1 /oracle/dbs/hq_nyc_10.log NO 1 /oracle/dbs/hq_nyc_11.log NO 1 /oracle/dbs/hq_nyc_12.log NO 1 /oracle/dbs/hq_nyc_13.log NO 1

6

142739

143973 12:02:10 NO

7

143973

144042 01:02:11 NO

8

144042

144051 01:02:01 NO

9

144051

144054 01:02:16 NO

10

144054

144057 01:02:21 NO

11

144057

144060 01:02:26 NO

12

144060

144089 01:02:30 NO

13

144089

144147 01:02:41 NO

The output from this query shows that a LogMiner dictionary build starts at log file sequence number 5. The most recent archive log file is sequence number 13 and it was received at the logical standby database at 01:02:41.

6.6.3 Accessing the DBA_LOGSTDBY_PROGRESS View To quickly determine if all log file information 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 logs, join the DBA_LOGSTDBY_PROGRESS view with the DBA_LOGSTDBY_LOG view, as shown in the following example: SQL> ALTER SESSION SET NLS_DATE_FORMAT Session altered.

= 'DD-MON-YY HH24:MI:SS';

SQL> SELECT L.SEQUENCE#, L.FIRST_TIME, 2 (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES' 3 WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' 4 ELSE 'NO' END) APPLIED 5 FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P 6 ORDER BY SEQUENCE#; SEQUENCE# ---------24 25

FIRST_TIME -----------------23-JUL-02 18:19:05 23-JUL-02 18:19:48

APPLIED ------YES YES

26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02 23-JUL-02

18:19:51 18:19:54 18:19:59 18:20:03 18:20:13 18:20:18 18:20:21 18:32:11 18:32:19 19:13:20 19:13:43 19:13:46 19:13:50 19:13:54 19:14:01 19:15:11 19:15:54

YES YES YES YES YES YES YES YES CURRENT CURRENT CURRENT CURRENT CURRENT CURRENT CURRENT NO NO

19 rows selected.

In the previous query, the computed APPLIED column displays YES, CURRENT, NO. The logs with YES were completely applied and those files are no longer needed by the logical standby database. The logs with CURRENT contain information that is currently being worked on. Because logical standby applies transactions, and because transactions span logs, it is common for log apply services to be applying changes from multiple logs. 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.

6.6.4 Accessing the V$LOGSTDBY Fixed View To inspect the process activity for SQL apply operations, query the V$LOGSTDBY fixed view on the logical standby database. 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 PREPARER ANALYZER transac

191896 ORA-16116: no work available 191902 ORA-16117: processing 191820 ORA-16120: dependencies being computed for tion at SCN 0x0000.0002ed4e

APPLIER ano

191209 ORA-16124: transaction 1 16 1598 is waiting on ther transaction

APPLIER APPLIER anot

191205 ORA-16116: no work available 191206 ORA-16124: transaction 1 5 1603 is waiting on

APPLIER APPLIER ano

191213 ORA-16117: processing 191212 ORA-16124: transaction 1 20 1601 is waiting on

her transaction

ther transaction APPLIER anot

191216 ORA-16124: transaction 1 4 1602 is waiting on her transaction

11 rows selected.

The previous query displays one row for each process involved in reading and applying redo logs. 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.

6.6.5 Accessing the V$LOGSTDBY_STATS Fixed View The V$LOGSTDBY_STATS fixed view provides a collection of state and statistical information for log apply services. 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> SQL> SQL> 2>

COLUMN NAME FORMAT A35 COLUMN VALUE FORMAT A35 SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';

NAME ----------------------------------coordinator state transactions ready transactions applied coordinator uptime

VALUE ----------------------------------APPLYING 7821 7802 73

This query shows how long SQL apply operations have been running and how many transactions have been applied in that time. It also shows how many transactions are available to be applied, indicating that more work is necessary.