Managing a Logical Standby Database
9.2 Views Related to Managing and Monitoring a Logical Standby Database
9.2 Views Related to Managing and Monitoring a Logical Standby Database
The following performance views monitor the behavior of SQL Apply maintaining a logical standby database. The following sections describe the key views that can be used to monitor a logical standby database:
■ DBA_LOGSTDBY_EVENTS View
■ DBA_LOGSTDBY_LOG View
■ V$LOGSTDBY_STATS View
■ V$LOGSTDBY_PROCESS View
■ V$LOGSTDBY_PROGRESS View
■ V$LOGSTDBY_STATE View
■ V$LOGSTDBY_STATS View
Views Related to Managing and Monitoring a Logical Standby Database
9.2.1 DBA_LOGSTDBY_EVENTS View
The DBA_LOGSTDBY_EVENTS view record interesting events that occurred during the operation of SQL Apply. By default, the view records the most recent 100 events.
However, you can change the number of recorded events by calling DBMS_
LOGSTDBY.APPLY_SET() PL/SQL procedure. If SQL Apply should stop unexpectedly, the reason for the problem is also recorded in this view.
The view also contains other information, such as which DDL transactions 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_TIMESTAMP, COMMIT_SCN;
EVENT_TIME STATUS
---EVENT
---23-JUL-02 18:20:12 ORA-16111: log mining and apply setting up
23-JUL-02 18:25:12 ORA-16128: User initiated shut down successfully completed 23-JUL-02 18:27:12 ORA-16112: log mining and apply stopping
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 hr.test_emp (empno number, ename varchar2(64)) 23-JUL-02 20:22:55 ORA-16205: DDL skipped due to skip setting
create database link link_to_boston connect to system identified by change_on_inst 7 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.
9.2.2 DBA_LOGSTDBY_LOG View
The DBA_LOGSTDBY_LOG view provides dynamic information about archived logs being processed by SQL Apply.
For example:
SQL> COLUMN DICT_BEGIN FORMAT A10;
SQL> SET NUMF 9999999
SQL> SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#, -Note: Errors that cause SQL Apply to stop are recorded in the events table These events are put into the ALERT.LOG file as well, with the LOGSTDBY keyword included in the text. When querying the view, select the columns in order by EVENT_TIME_STAMP, COMMIT_SCN, and CURRENT_SCN. This ordering ensures a shutdown failure appears last in the view.
See Also: DBA_LOGSTDBY_EVENTS view in Oracle Database Reference
Views Related to Managing and Monitoring a Logical Standby Database
FILE_NAME SEQ# F_SCN N_SCN TIMESTAM BEG END THR# APPLIED --- ---- --- --- --- --- --- --- ---/oracle/dbs/hq_nyc_2.log 2 101579 101588 11:02:58 NO NO 1 YES /oracle/dbs/hq_nyc_3.log 3 101588 142065 11:02:02 NO NO 1 YES /oracle/dbs/hq_nyc_4.log 4 142065 142307 11:02:10 NO NO 1 YES /oracle/dbs/hq_nyc_5.log 5 142307 142739 11:02:48 YES YES 1 YES /oracle/dbs/hq_nyc_6.log 6 142739 143973 12:02:10 NO NO 1 YES /oracle/dbs/hq_nyc_7.log 7 143973 144042 01:02:11 NO NO 1 YES /oracle/dbs/hq_nyc_8.log 8 144042 144051 01:02:01 NO NO 1 YES /oracle/dbs/hq_nyc_9.log 9 144051 144054 01:02:16 NO NO 1 YES /oracle/dbs/hq_nyc_10.log 10 144054 144057 01:02:21 NO NO 1 YES /oracle/dbs/hq_nyc_11.log 11 144057 144060 01:02:26 NO NO 1 CURRENT /oracle/dbs/hq_nyc_12.log 12 144060 144089 01:02:30 NO NO 1 CURRENT /oracle/dbs/hq_nyc_13.log 13 144089 144147 01:02:41 NO NO 1 NO
The YES entries in the BEG and END columns indicate 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.
The APPLIED column indicates that SQL Apply has applied all redo before SCN 144057. Since transactions can span multiple archived log files, multiple archived log files may show the value CURRENT in the APPLIED column.
9.2.3 V$LOGSTDBY_STATS View
This view provides information related to the failover characteristics of the logical standby database, including:
■ The time to failover (apply finish time)
■ How current is the committed data in the logical standby database (lag time)
■ What the potential data loss will be in the event of a disaster (potential data loss).
For example:
SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$LOGSTDBY_STATS;
NAME VALUE TIME_COMPUTED
--- --- ---apply finish time +00 00:00:00.1 07-APR-2005 08:29:23 lag time +00 00:00:00.1 07-APR-2005 08:29:23 potential data loss +00 00:00:00 07-APR-2005 08:29:23
The unit (metric) of each of the columns displayed is in day (2) to second (1) interval.
The output identifies a logical standby database that is caught up within 0.1 second of the primary database, and no data loss will occur in the event of a primary failure.
9.2.4 V$LOGSTDBY_PROCESS View
This view provides information about the current state of the various processes involved with SQL Apply, including;
■ Identifying information (sid | serial# | spid)
■ SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)
See Also: DBA_LOGSTDBY_LOG view in Oracle Database Reference
See Also: V$LOGSTDBY_STATS view in Oracle Database Reference
Views Related to Managing and Monitoring a Logical Standby Database
■ Status of the process’s current activity (status_code | status)
■ Highest redo record processed by this process (high_scn) For example:
SQL> COLUMN LID FORMAT 9999 SQL> COLUMN SERIAL# FORMAT 9999 SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
The HIGH_SCN column shows that the reader process is ahead of all other processes, and the PREPARER and BUILDER process ahead of the rest.
SQL> COLUMN STATUS FORMAT A40
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
TYPE STATUS_CODE STATUS
The output shows a snapshot of SQL Apply running. On the mining side, the READER process is waiting for additional memory to become available before it can read more, the PREPARER process is processing redo records, and the BUILDER process has no work available. On the apply side, the COORDINATOR is assigning more transactions to APPLIER processes, the ANALYZER is computing dependencies at SCN 7178241034, one APPLIER has no work available, while two have outstanding dependencies that are not yet satisfied.
See Also: V$LOGSTDBY_PROCESS view in Oracle Database Reference
Views Related to Managing and Monitoring a Logical Standby Database
9.2.5 V$LOGSTDBY_PROGRESS View
This view provides detailed information regarding progress made by SQL Apply, including:
■ SCN or time at which all transactions that have been committed on the primary database have been applied to the logical standby database (applied_scn | applied_time)
■ SCN or time at which SQL Apply would begin reading redo records (restart_
scn | restart_time) on restart
■ SCN or time of the latest redo record received on the logical standby database (latest_scn | latest_time)
■ SCN or time of the latest record processed by the BUILDER process (mining_scn
| mining_time) For example:
SQL> SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_
PROGRESS;
APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN --- 7178240496 7178240507 7178240507 7178219805 According to the output:
■ SQL Apply has applied all transactions committed on or before SCN of 7178240496
■ The latest redo record received at the logical standby database was generated at SCN 7178240507
■ The mining component has processed all redo records generate on or before SCN 7178240507
■ If SQL Apply stops and restarts for any reason, it will start mining redo records generated on or after SCN 7178219805
SQL> ALTER SESSION SET NLS_DATE_FORMAT='yy-mm-dd hh24:mi:ss';
Session altered
SQL> SELECT APPLIED_TIME, LATEST_TIME, MINING_TIME, RESTART_TIME FROM V$LOGSTDBY_
PROGRESS;
APPLIED_TIME LATEST_TIME MINING_TIME RESTART_TIME ---05-05-12 10:38:21 ---05-05-12 10:41:21 ---05-05-12 10:41:53 ---05-05-12 10:09:30 According to the output:
■ SQL Apply has applied all transactions committed on or before the time 05-05-12 10:38:21 (APPLIED_TIME)
■ The last redo was generated at time 05-05-12 10:41:53 at the primary database (LATEST_TIME)
■ The mining engine has processed all redo records generated on or before 05-05-12 10:41:21 (MINING_TIME)
■ In the event of a restart, SQL Apply will start mining redo records generated after the time 05-05-12 10:09:30
Views Related to Managing and Monitoring a Logical Standby Database
9.2.6 V$LOGSTDBY_STATE View
This view provides a synopsis of the current state of SQL Apply, including:
■ The DBID of the primary database (primary_dbid).
■ The LogMiner session ID allocated to SQL Apply (session_id).
■ Whether or not SQL Apply is applying in real time (realtime_apply).
■ Where SQL Apply is currently with regard to loading the LogMiner
Multiversioned Data Dictionary (described in Section 4.2.3.2, "Build a Dictionary in the Redo Data"), receiving redo from the primary database, and applying redo data (STATE)
For example:
SQL> COLUMN REALTIME_APPLY FORMAT a15 SQL> COLUMN STATE FORMAT a16
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
--- --- --- 1562626987 1 Y APPLYING
The output shows that SQL Apply is running in the real-time apply mode and is currently applying redo data received from the primary database, the primary
database's DBID is 1562626987 and the LogMiner session identifier associated the SQL Apply session is 1.
9.2.7 V$LOGSTDBY_STATS View
This view provides SQL Apply statistics.
For example:
SQL> COLUMN NAME FORMAT a32 SQL> COLUMN VALUE FORMAT a32
SQL> SELECT * FROM V$LOGSTDBY_STATS;
NAME VALUE
---number of preparers 1
number of appliers 4 maximum SGA for LCR cache 30 parallel servers in use 8 maximum events recorded 1000 preserve commit order TRUE record skip errors Y record skip DDL Y record applied DDL N
See Also: V$DATAGUARD_PROGRESS view in Oracle Database Reference for reference information and Section 9.3.1, "Monitoring SQL Apply Progress" for example output
See Also: V$LOGSTDBY_STATE view in Oracle Database Reference for reference information and Section 9.3.1, "Monitoring SQL Apply Progress" for example output
Monitoring a Logical Standby Database
transactions applied 132118 coordinator uptime 132102 realtime logmining Y apply delay 0 Log Miner session ID 1
bytes of redo processed 130142100140 txns delivered to client 131515 DML txns delivered 128 DDL txns delivered 23 CTAS txns delivered 0 Recursive txns delivered 874 Rolled back txns seen 40 LCRs delivered to client 2246414 bytes paged out 0 secs spent in pageout 0 bytes checkpointed 0 secs spent in checkpoint 0 bytes rolled back 0 secs spent in rollback 0 secs system is idle 2119 32 rows selected.