View Management of Oracle DG logic Standby

1. DBA_LOGSTDBY_EVENTS
This view can be viewed as a logical Standby operation log. If an error occurs, you can view the recent logical Standby operations in this view. By default, this view retains only the records of the last 100 events (you can modify the number of records saved through the relevant process ).
For example:
SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
Order by EVENT_TIMESTAMP;
EVENT_TIM STATUS EVENT
------------------------------------------------------------------------------
05-MAY-10 ORA-16111: log mining and apply setting up
05-MAY-10 ORA-16257: Switchover initiated stop apply success

2. DBA_LOGSTDBY_LOG
This view is used to record the application status of the current redo log. The function is similar to V $ ARCHIVED_LOG in physical Standby.
In most cases, you only need to pay attention to the SEQUENCE #, APPLIED, and other limited columns, that is, to check the log serial number and whether the log is APPLIED. Of course, this view also provides more information, such as the application SCN and application time, for example:
SQL> SELECT SEQUENCE #, FIRST_CHANGE #, NEXT_CHANGE #,
TIMESTAMP, applied from DBA_LOGSTDBY_LOG;
SEQUENCE # FIRST_CHANGE # NEXT_CHANGE # TIMESTAMP APPLIED
----------------------------------------------------
110 572160 572171 05-MAY-10 CURRENT
111 572171 572175 05-MAY-10 NO
Generally, this query returns only a few records. If your database operates very frequently, the number of records may be slightly larger, but if the number of records is very large, so you need to pay attention to the problem. Isn't the SQL application not started?

3. V $ LOGSTDBY_STATS
This view is used to display the status of LogMiner and other related information, such:
SQL> SELECT * FROM V $ LOGSTDBY_STATS;
NAME VALUE
-------------------------------------------------------------------------------
Number of preparers 1
Number of appliers 5
Maximum SGA for LCR cache 30
Parallel servers in use 9
Maximum events recorded 100
Preserve commit order TRUE
Transaction consistency FULL
Record skip errors Y
Record skip DDL Y
Record applied DDL N
Record unsupported operations N

4. V $ LOGSTDBY_PROCESS
This view displays information about the current log application service. It is often used to diagnose the performance of the archive log logic application (which will be involved in the optimization section later) and contains a wide range of information, including:
ID: SID, SERIAL #, and SPID.
SQL application process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER.
Current STATUS of the process: see the STATUS_CODE or STATUS column.
The maximum SCN: HIGH_SCN column of the REDO Record of the current operation of this process.
For example:
SQL> SELECT SID, SERIAL #, SPID, TYPE, STATUS, HIGH_SCN FROM V $ LOGSTDBY_PROCESS;
Sid serial # SPID TYPE STATUS
----------------------------------------------------------------------------
139 303 6831 COORDINATOR ORA-16116: no work available
153 292 6833 READER ORA-16240: Waiting for logfil
136 5 6835 BUILDER ORA-16116: no work available
137 5 6837 PREPARER ORA-16116: no work available
128 1 6841 ANALYZER ORA-16116: no work available
132 1 6843 APPLIER ORA-16116: no work available
133 2 6845 APPLIER ORA-16116: no work available
130 1 6847 APPLIER ORA-16116: no work available
129 1 6849 APPLIER ORA-16116: no work available
131 1 6851 APPLIER ORA-16116: no work available

5. V $ LOGSTDBY_PROGRESS
This view displays the current progress of LOG application services, such as the SCN and time from the current application to the logical Standby, the SCN and time from the SQL application to the application, and the received and applied SCN and time.
For example, to view the SCN information of the current application:
SQL> SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V $ LOGSTDBY_PROGRESS;
APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
------------------------------------------
572164 572232 572166

Recommended reading:

When querying the Oracle v $ view in the Sh script, you must add the Escape Character "\" before "$".

Manage frequently used data dictionaries and dynamic performance views in Oracle

Analysis of insufficient permissions for creating view in Oracle

Oracle materialized view creation reports ORA-00942 error resolution

1 2 Next Page