Streams Replication| Monitoring Streams Capture Processes
Capture process is an optional Streams client that is an Oracle background process that scans a database redo log to capture DML and DDL changes made to database objects.
1) Displaying the Queue, Rule Sets, and Status of Each Capture Process 2) Displaying Change Capture Information About Each Capture Process 3) Displaying State Change and Message Creation Time for Each Capture Process 4) Displaying Elapsed Time Performing Capture Operations for Each Capture Process 5) Displaying Information About Each Downstream Capture Process 6) Displaying the Registered Redo Log Files for Each Capture Process 7) Displaying the Redo Log Files that Are Required by Each Capture Process 8) Displaying SCN Values for Each Redo Log File Used by Each Capture Process 9) Displaying the Last Archived Redo Entry Available to Each Capture Process 10) Listing the Parameter Settings for Each Capture Process 11) Viewing the Extra Attributes Captured by Each Capture Process 12) Determining the Applied SCN for All Capture Processes in a Database 13) Determining Redo Log Scanning Latency for Each Capture Process 14) Determining Message Enqueuing Latency for Each Capture Process 15) Displaying Information About Rule Evaluations for Each Capture Process |
1) Displaying the Queue, Rule Sets, and Status of Each Capture Process
To display this general information about each capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15 COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15 COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15 COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15 SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS FROM DBA_CAPTURE; Capture Capture Capture Process Process POSITIVE Negative Process Name Queue Rule SET Rule SET Status --------------- --------------- --------------- --------------- --------------- STREAMS_DATA_C STREAMS_DATA_C RULESET$_54 ENABLED _Q |
If the status of a capture process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_CAPTURE data dictionary view to determine the error. 2) Displaying Change Capture Information About Each Capture Process The query in this section displays the following information about each capture process in a database: The current state of the capture process: INITIALIZING,WAITING FOR DICTONARY REDO, DICTIONARY INITIALIZATION, MINING, LOADING, CAPTURING CHANGES, WAITING FOR REDO, EVALUATING RULE, CREATING LCR, ENQUEUING MESSAGE, PAUSED FOR FLOW CONTROL, SHUTTING DOWN The total number of redo entries passed by LogMiner to the capture process for detailed rule evaluation. A capture process converts a redo entry into a message and performs detailed rule evaluation on the message when capture process prefiltering cannot discard the change.
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15 COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7 COLUMN SID HEADING 'Session|ID' FORMAT 9999 COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999 COLUMN STATE HEADING 'State' FORMAT A27 COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Evaluated|In Detail' FORMAT 9999999 COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999 SELECT c.CAPTURE_NAME, SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME, c.SID, c.SERIAL#, c.STATE, c.TOTAL_MESSAGES_CAPTURED, c.TOTAL_MESSAGES_ENQUEUED FROM V$STREAMS_CAPTURE c, V$SESSION s WHERE c.SID = s.SID AND c.SERIAL# = s.SERIAL#; Redo Capture Session Entries Capture Process Session Serial Evaluated Name NUMBER ID NUMBER State IN Detail --------------- ------- ------- ------- --------------------------- --------- Total LCRs Enqueued -------- STREAMS_DATA_C CP01 217 2119 WAITING FOR TRANSACTION ######## ####### |
3) Displaying State Change and Message Creation Time for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15 COLUMN STATE HEADING 'State' FORMAT A27 COLUMN STATE_CHANGED HEADING 'State|Change Time' COLUMN CREATE_MESSAGE HEADING 'Last Message|Create Time' SELECT CAPTURE_NAME, STATE, TO_CHAR(STATE_CHANGED_TIME, 'HH24:MI:SS MM/DD/YY') STATE_CHANGED, TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_MESSAGE FROM V$STREAMS_CAPTURE; Capture State LAST Message Name State Change TIME CREATE TIME --------------- --------------------------- ----------------- ----------------- STREAMS_DATA_C WAITING FOR TRANSACTION 13:03:11 01/26/10 13:03:13 01/26/10 |
4) Displaying Elapsed Time Performing Capture Operations for Each Capture Process
To display this information for each capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15 COLUMN ELAPSED_CAPTURE_TIME HEADING 'Elapsed|Capture|Time' FORMAT 99999999.99 COLUMN ELAPSED_RULE_TIME HEADING 'Elapsed|Rule|Evaluation|Time' FORMAT 99999999.99 COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Elapsed|Enqueue|Time' FORMAT 99999999.99 COLUMN ELAPSED_LCR_TIME HEADING 'Elapsed|LCR|Creation|Time' FORMAT 99999999.99 COLUMN ELAPSED_PAUSE_TIME HEADING 'Elapsed|Pause|Time' FORMAT 99999999.99 SELECT CAPTURE_NAME, (ELAPSED_CAPTURE_TIME/100) ELAPSED_CAPTURE_TIME, (ELAPSED_RULE_TIME/100) ELAPSED_RULE_TIME, (ELAPSED_ENQUEUE_TIME/100) ELAPSED_ENQUEUE_TIME, (ELAPSED_LCR_TIME/100) ELAPSED_LCR_TIME, (ELAPSED_PAUSE_TIME/100) ELAPSED_PAUSE_TIME FROM V$STREAMS_CAPTURE; Elapsed Elapsed Elapsed Rule Elapsed LCR Elapsed Capture Capture Evaluation Enqueue Creation Pause Name TIME TIME TIME TIME TIME --------------- ------------ ------------ ------------ ------------ ------------ STREAMS_DATA_C 279.21 .26 .43 .26 .00 |
5) Displaying Information About Each Downstream Capture Process
A downstream capture is a capture process runs on a database other than the source database.
You can display the following information about each downstream capture process in a database by running the query in this section:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A15 COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15 COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15 COLUMN USE_DATABASE_LINK HEADING 'Uses|Database|Link?' FORMAT A8 SELECT CAPTURE_NAME, SOURCE_DATABASE, QUEUE_NAME, STATUS, USE_DATABASE_LINK FROM DBA_CAPTURE WHERE CAPTURE_TYPE = 'DOWNSTREAM'; |
6) Displaying the Registered Redo Log Files for Each Capture Process
This query displays information about these files for both local capture processes and downstream capture processes.
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10 COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999 COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A20 COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10 COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10 SELECT r.CONSUMER_NAME, r.SOURCE_DATABASE, r.SEQUENCE#, r.NAME, r.DICTIONARY_BEGIN, r.DICTIONARY_END FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME; Capture Dictionary Dictionary Process Source Sequence Archived Redo LOG Build Build Name Database NUMBER File Name BEGIN END --------------- ---------- -------- -------------------- ---------- ---------- STREAMS_DATA_C PROD 7175 /mnt/logs/oradata/ NO NO PROD/arch/1_7175_732278995.arc STREAMS_DATA_C PROD 7176 /mnt/logs/oradata/ NO NO PROD/arch/1_7176_732278995.arc STREAMS_DATA_C PROD 7177 /mnt/logs/oradata/ NO NO PROD/arch/1_7177_732278995.arc |
7) Displaying the Redo Log Files that Are Required by Each Capture Process
A capture process needs the redo log file that includes the required checkpoint SCN, and all subsequent redo log files.
You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process. Redo log files prior to the redo log file that contains the required checkpoint SCN are no longer needed by the capture process. These redo log files can be stored offline if they are no longer needed for any other purpose. If you reset the start SCN for a capture process to a lower value in the future, then these redo log files might be needed.
To display this information about each required archive redo log file in a database, run the following query:
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10 COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999 COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A40 SELECT r.CONSUMER_NAME, r.SOURCE_DATABASE, r.SEQUENCE#, r.NAME FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN; Capture Required Process Source Sequence Archived Redo LOG Name Database NUMBER File Name --------------- ---------- -------- ---------------------------------------- STREAMS_DATA_C PROD 13418 /mnt/logs/oradata/PROD/arch/2_13418_732278995.arc STREAMS_DATA_C PROD 10762 /mnt/logs/oradata/PROD/arch/1_10762_732278995.arc |
8) Displaying SCN Values for Each Redo Log File Used by Each Capture Process:
You can display information about the SCN values for archived redo log files that are registered for each capture process in a database by running the query in this section.
This query displays information the SCN values for these files for both local capture processes and downstream capture processes. This query also identifies redo log files that are no longer needed by any capture process at the local database.
To display this information about each registered archive redo log file in a database, run the following query:
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A25 COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999999 COLUMN NEXT_SCN HEADING 'Next SCN' FORMAT 99999999999 COLUMN PURGEABLE HEADING 'Purgeable?' FORMAT A10 SELECT r.CONSUMER_NAME, r.NAME, r.FIRST_SCN, r.NEXT_SCN, r.PURGEABLE FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME; Capture Process Archived Redo LOG Name File Name FIRST SCN Next SCN Purgeable? --------------- ------------------------- ------------ ------------ ---------- STREAMS_DATA_C /mnt/logs/oradata/PROD/ 7499664298 7499908128 NO arch/1_7175_732278995.arc STREAMS_DATA_C /mnt/logs/oradata/PROD/ 7499908128 7500009109 NO arch/1_7176_732278995.arc |
9) Displaying the Last Archived Redo Entry Available to Each Capture Process
For a local capture process, the last archived redo entry available is the last entry from the online redo log flushed to an archived log file.
For a downstream capture process, the last archived redo entry available is the redo entry with the most recent SCN in the last archived log file added to the LogMiner session used by the capture process.
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A20 COLUMN LOGMINER_ID HEADING 'LogMiner ID' FORMAT 9999 COLUMN AVAILABLE_MESSAGE_NUMBER HEADING 'Last Redo SCN' FORMAT 9999999999 COLUMN AVAILABLE_MESSAGE_CREATE_TIME HEADING 'Time of|Last Redo SCN' SELECT CAPTURE_NAME, LOGMINER_ID, AVAILABLE_MESSAGE_NUMBER, TO_CHAR(AVAILABLE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') AVAILABLE_MESSAGE_CREATE_TIME FROM V$STREAMS_CAPTURE; Capture TIME OF Name LogMiner ID LAST Redo SCN LAST Redo SCN -------------------- ----------- ------------- ----------------- STREAMS_DATA_C 9 8087814747 23:13:18 10/26/10 |
10) Listing the Parameter Settings for Each Capture Process
Following query displays the current setting for each capture process parameter for each capture process in a database:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A25 COLUMN PARAMETER HEADING 'Parameter' FORMAT A25 COLUMN VALUE HEADING 'Value' FORMAT A10 COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A15 SELECT CAPTURE_NAME, PARAMETER, VALUE, SET_BY_USER FROM DBA_CAPTURE_PARAMETERS; Capture Process Name Parameter VALUE SET BY USER? ------------------------- ------------------------- ---------- --------------- STREAMS_DATA_C PARALLELISM 1 NO STREAMS_DATA_C STARTUP_SECONDS 0 NO STREAMS_DATA_C TRACE_LEVEL 0 NO STREAMS_DATA_C TIME_LIMIT INFINITE NO STREAMS_DATA_C MESSAGE_LIMIT INFINITE NO STREAMS_DATA_C MAXIMUM_SCN INFINITE NO STREAMS_DATA_C WRITE_ALERT_LOG Y NO STREAMS_DATA_C DISABLE_ON_LIMIT N NO STREAMS_DATA_C DOWNSTREAM_REAL_TIME_MINE Y NO STREAMS_DATA_C IGNORE_TRANSACTION NO STREAMS_DATA_C MESSAGE_TRACKING_FREQUENC 2000000 NO Y STREAMS_DATA_C IGNORE_UNSUPPORTED_TABLE * NO STREAMS_DATA_C SKIP_AUTOFILTERED_TABLE_D Y NO DL STREAMS_DATA_C SPLIT_THRESHOLD 1800 NO STREAMS_DATA_C MERGE_THRESHOLD 60 NO STREAMS_DATA_C CAPTURE_SEQUENCE_NEXTVAL N NO STREAMS_DATA_C XOUT_CLIENT_EXISTS N NO STREAMS_DATA_C EXCLUDEUSER NO STREAMS_DATA_C EXCLUDEUSERID NO STREAMS_DATA_C EXCLUDETRANS NO STREAMS_DATA_C GETREPLICATES N NO STREAMS_DATA_C GETAPPLOPS Y NO STREAMS_DATA_C CAPTURE_IDKEY_OBJECTS N NO STREAMS_DATA_C MAX_SGA_SIZE INFINITE NO |
11) Viewing the Extra Attributes Captured by Each Capture Process
You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process to capture one or more extra attributes from the redo log.
The following query displays the extra attributes included in the LCRs captured by each capture process in the local database:
COLUMN CAPTURE_NAME HEADING 'Capture Process' FORMAT A20 COLUMN ATTRIBUTE_NAME HEADING 'Attribute Name' FORMAT A15 COLUMN INCLUDE HEADING 'Include Attribute in LCRs?' FORMAT A30 SELECT CAPTURE_NAME, ATTRIBUTE_NAME, INCLUDE FROM DBA_CAPTURE_EXTRA_ATTRIBUTES ORDER BY CAPTURE_NAME; Capture Process Attribute Name Include Attribute IN LCRs? -------------------- --------------- ------------------------------ STREAMS_DATA_C THREAD# NO STREAMS_DATA_C ROW_ID NO STREAMS_DATA_C TX_NAME NO STREAMS_DATA_C SERIAL# NO STREAMS_DATA_C USERNAME NO STREAMS_DATA_C SESSION# NO |
12) Determining the Applied SCN for All Capture Processes in a Database
The applied system change number (SCN) for a capture process is the SCN of the most recent message dequeued by the relevant apply processes. All changes below this applied SCN have been dequeued by all apply processes that apply changes captured by the capture process.
COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30 COLUMN APPLIED_SCN HEADING 'Applied SCN' FORMAT 99999999999 SELECT CAPTURE_NAME, APPLIED_SCN FROM DBA_CAPTURE; Capture Process APPLIED_SCN -------------------- ----------- STREAMS_DATA_C 8087783293 |
13) Determining Redo Log Scanning Latency for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10 COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999 COLUMN LAST_STATUS HEADING 'Seconds Since|Last Status' FORMAT 999999 COLUMN CAPTURE_TIME HEADING 'Current|Process|Time' COLUMN CREATE_TIME HEADING 'Message|Creation Time' FORMAT 999999 SELECT CAPTURE_NAME, ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS, ((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS, TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME, TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME FROM V$STREAMS_CAPTURE; Capture Latency CURRENT Process IN Seconds Since Process Message Name Seconds LAST Status TIME Creation TIME ---------- ------- ------------- ----------------- ----------------- INFO_CONTR 2 1 23:21:36 10/26/10 23:21:35 10/26/10 ACT_C |
The “Latency in Seconds” returned by this query is the difference between the current time (SYSDATE) and the “Message Creation Time.” The “Seconds Since Last Status” returned by this query is the difference between the current time (SYSDATE) and the “Current Process Time.”
14) Determining Message Enqueuing Latency for Each Capture Process
Run the following query to determine the message capturing latency for each capture process:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10 COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999 COLUMN CREATE_TIME HEADING 'Message Creation|Time' FORMAT A20 COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20 COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999 SELECT CAPTURE_NAME, (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS, TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME, TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME, ENQUEUE_MESSAGE_NUMBER FROM V$STREAMS_CAPTURE; Capture Latency Process IN Message Creation Message Name Seconds TIME Enqueue TIME NUMBER ---------- ------- -------------------- -------------------- ------- STREAMS_DATA -3 23:22:25 10/26/10 23:22:22 10/26/10 ####### |
15) Displaying Information About Rule Evaluations for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15 COLUMN TOTAL_PREFILTER_DISCARDED HEADING 'Prefilter|Changes|Discarded' FORMAT 9999999999 COLUMN TOTAL_PREFILTER_KEPT HEADING 'Prefilter|Changes|Kept' FORMAT 9999999999 COLUMN TOTAL_PREFILTER_EVALUATIONS HEADING 'Prefilter|Evaluations' FORMAT 9999999999 COLUMN UNDECIDED HEADING 'Undecided|After|Prefilter' FORMAT 9999999999 COLUMN TOTAL_FULL_EVALUATIONS HEADING 'Full|Evaluations' FORMAT 9999999999 SELECT CAPTURE_NAME, TOTAL_PREFILTER_DISCARDED, TOTAL_PREFILTER_KEPT, TOTAL_PREFILTER_EVALUATIONS, (TOTAL_PREFILTER_EVALUATIONS - (TOTAL_PREFILTER_KEPT + TOTAL_PREFILTER_DISCARDED)) UNDECIDED, TOTAL_FULL_EVALUATIONS FROM V$STREAMS_CAPTURE; Prefilter Prefilter Undecided Capture Changes Changes Prefilter After Full Name Discarded Kept Evaluations Prefilter Evaluations --------------- ---------- ----------- ----------- ----------- ----------- STREAMS_DATA_C 53128265 1662026 54790291 0 801 |
The total number of prefilter evaluations equals the sum of the prefilter changes discarded, the prefilter changes kept, and the undecided changes.
Leave a Reply
You must be logged in to post a comment.