| Oracle® Streams Concepts and Administration 11g Release 1 (11.1) Part Number B28321-01 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 11g Release 1 (11.1) Part Number B28321-01 |
|
|
View PDF |
Both capture processes and synchronous captures perform implicit capture.
The following topics describe monitoring Oracle Streams implicit capture:
Note:
The Oracle Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor an Oracle Streams environment. See Oracle Database 2 Day + Data Replication and Integration Guide and the online Help for the Oracle Streams tool for more information.See Also:
Oracle Database Reference for information about the data dictionary views described in this chapter
Oracle Streams Replication Administrator's Guide for information about monitoring an Oracle Streams replication environment
This section provides sample queries that you can use to monitor Oracle Streams capture processes.
This section contains these topics:
Displaying the Queue, Rule Sets, and Status of Each Capture Process
Displaying Change Capture Information About Each Capture Process
Displaying State Change and Message Creation Time for Each Capture Process
Displaying Elapsed Time Performing Capture Operations for Each Capture Process
Displaying Information About Each Downstream Capture Process
Displaying the Registered Redo Log Files for Each Capture Process
Displaying the Redo Log Files that Are Required by Each Capture Process
Displaying SCN Values for Each Redo Log File Used by Each Capture Process
Displaying the Last Archived Redo Entry Available to Each Capture Process
Determining the Applied SCN for All Capture Processes in a Database
Determining Redo Log Scanning Latency for Each Capture Process
Determining Message Enqueuing Latency for Each Capture Process
Displaying Information About Rule Evaluations for Each Capture Process
Determining Which Capture Processes Use Combined Capture and Apply
You can display the following information about each capture process in a database by running the query in this section:
The capture process name
The name of the queue used by the capture process
The name of the positive rule set used by the capture process
The name of the negative rule set used by the capture process
The status of the capture process, which can be ENABLED, DISABLED, or ABORTED
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;
Your output looks similar to the following:
Capture Capture Capture Process Process Positive Negative Process Name Queue Rule Set Rule Set Status --------------- --------------- --------------- --------------- --------------- STRM01_CAPTURE STREAMS_QUEUE RULESET$_25 RULESET$_36 ENABLED
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.
See Also:
"Is the Capture Process Enabled?" for an example query that shows the error number and error message if a capture process is abortedThe query in this section displays the following information about each capture process in a database:
The name of the capture process.
The process number CPnn, where nn can include letters and numbers
The session identifier.
The serial number of the session.
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.
The total number LCRs enqueued since the capture process was last started.
To display this information for each capture process in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A7
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#;
Your output looks similar to the following:
Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
------- ------- ------- ------- --------------------------- --------- --------
CAPTURE CP00 1000 17 CAPTURING CHANGES 21008 322
The number of redo entries scanned can be higher than the number of DML and DDL redo entries captured by a capture process. Only DML and DDL redo entries that satisfy the rule sets of a capture process are captured and enqueued into the capture process queue. Also, the total LCRs enqueued includes LCRs that contain transaction control statements. These row LCRs contain directives such as COMMIT and ROLLBACK. Therefore, the total LCRs enqueued is a number higher than the number of row changes and DDL changes enqueued by a capture process.
See Also:
"Row LCRs" for more information about transaction control statements
The query in this section displays the following information for each capture process in a database:
The name of the capture process
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 date and time when the capture process state last changed
The date and time when the capture process last created an LCR
To display this information for each capture process in a database, run the following query:
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;
Your output looks similar to the following:
Capture State Last Message Name State Change Time Create Time --------------- --------------------------- ----------------- ----------------- CAPTURE_SIMP CAPTURING CHANGES 13:24:42 11/08/04 13:24:41 11/08/04
See Also:
"Capture Process States"The query in this section displays the following information for each capture process in a database:
The name of the capture process
The elapsed capture time, which is the amount of time (in seconds) spent scanning for changes in the redo log since the capture process was last started
The elapsed rule evaluation time, which is the amount of time (in seconds) spent evaluating rules since the capture process was last started
The elapsed enqueue time, which is the amount of time (in seconds) spent enqueuing messages since the capture process was last started
The elapsed LCR creation time, which is the amount of time (in seconds) spent creating logical change records (LCRs) since the capture process was last started
The elapsed pause time, which is the amount of time (in seconds) spent paused for flow control since the capture process was last started
Note:
All times for this query are displayed in seconds. TheV$STREAMS_CAPTURE view displays elapsed time in centiseconds by default. A centisecond is one-hundredth of a second. The query in this section divides each elapsed time by one hundred to display the elapsed time in seconds.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;
Your output looks similar to the following:
Elapsed Elapsed
Elapsed Rule Elapsed LCR Elapsed
Capture Capture Evaluation Enqueue Creation Pause
Name Time Time Time Time Time
--------------- ------------ ------------ ------------ ------------ ------------
STM1$CAP 1213.92 .04 33.84 185.25 600.60
A downstream capture is a capture process that 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:
The capture process name
The source database of the changes captured by the capture process
The name of the queue used by the capture process
The status of the capture process, which can be ENABLED, DISABLED, or ABORTED
Whether the downstream capture process uses a database link to the source database for administrative actions
To display this information about each downstream capture process in a database, run the following query:
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';
Your output looks similar to the following:
Capture Capture Capture Uses Process Source Process Process Database Name Database Queue Status Link? --------------- --------------- --------------- --------------- -------- STRM03_CAPTURE DBS1.NET STRM03_QUEUE ENABLED YES
In this case, the source database for the capture process is dbs1.net, but the local database running the capture process is not dbs1.net. Also, the capture process returned by this query uses a database link to the source database to perform administrative actions. The database link name is the same as the global name of the source database, which is dbs1.net in this case.
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.
Note:
At the source database for an Oracle Streams downstream capture process, you can query theV$ARCHIVE_DEST_STATUS view to display information about the downstream database. The following columns in the view relate to the downstream database:
The TYPE column shows DOWNSTREAM if redo log information is being shipped to a downstream capture database.
The DESTINATION column shows the name of the downstream capture database.
See Also:
"Configuring an Archived-Log Downstream Capture Process that Assigns Logs Implicitly"
"Is the Capture Process Enabled?" for an example query that shows the error number and error message if a capture process is aborted
You can display information about the 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 about these files for both local capture processes and downstream capture processes.
The query displays the following information for each registered archived redo log file:
The name of a capture process that uses the file
The source database of the file
The sequence number of the file
The name and location of the file at the local site
Whether the file contains the beginning of a data dictionary build
Whether the file contains the end of a data dictionary build
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 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;
Your output looks similar to the following:
Capture Dictionary Dictionary Process Source Sequence Archived Redo Log Build Build Name Database Number File Name Begin End --------------- ---------- -------- -------------------- ---------- ---------- STRM02_CAPTURE DBS2.NET 15 /orc/dbs/log/arch2_1 NO NO _15_478347508.arc STRM02_CAPTURE DBS2.NET 16 /orc/dbs/log/arch2_1 NO NO _16_478347508.arc STRM03_CAPTURE DBS1.NET 45 /remote_logs/arch1_1 YES YES _45_478347335.arc STRM03_CAPTURE DBS1.NET 46 /remote_logs/arch1_1 NO NO _46_478347335.arc STRM03_CAPTURE DBS1.NET 47 /remote_logs/arch1_1 NO NO _47_478347335.arc
Assume that this query was run at the dbs2.net database, and that strm02_capture is a local capture process, and strm03_capture is a downstream capture process. The source database for the strm03_capture downstream capture process is dbs1.net. This query shows that there are two registered archived redo log files for strm02_capture and three registered archived redo log files for strm02_capture. This query shows the name and location of each of these files in the local file system.
See Also:
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.
The query displays the following information for each required archived redo log file:
The name of a capture process that uses the file
The source database of the file
The sequence number of the file
The name and location of the required redo log file at the local site
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;
Your output looks similar to the following:
Capture Required Process Source Sequence Archived Redo Log Name Database Number File Name --------------- ---------- -------- ---------------------------------------- STRM02_CAPTURE DBS2.NET 16 /orc/dbs/log/arch2_1_16_478347508.arc STRM03_CAPTURE DBS1.NET 47 /remote_logs/arch1_1_47_478347335.arc
See Also:
"Required Checkpoint SCN"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.
The query displays the following information for each registered archived redo log file:
The capture process name of a capture process that uses the file
The name and location of the file at the local site
The lowest SCN value for the information contained in the redo log file
The lowest SCN value for the next redo log file in the sequence
Whether the redo log file is purgeable
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;
Your output looks similar to the following:
Capture
Process Archived Redo Log
Name File Name First SCN Next SCN Purgeable?
--------------- ------------------------- ------------ ------------ ----------
CAPTURE_SIMP /private1/ARCHIVE_LOGS/1_ 509686 549100 YES
3_502628294.dbf
CAPTURE_SIMP /private1/ARCHIVE_LOGS/1_ 549100 587296 YES
4_502628294.dbf
CAPTURE_SIMP /private1/ARCHIVE_LOGS/1_ 587296 623107 NO
5_502628294.dbf
The redo log files with YES for Purgeable? for all capture processes will never be needed by any capture process at the local database. These redo log files can be removed without affecting any existing capture process at the local database. The redo log files with NO for Purgeable? for one or more capture processes must be retained.
See Also:
"ARCHIVELOG Mode and a 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.
You can display the following information about the last redo entry that was made available to each capture process by running the query in this section:
The name of the capture process
The identification number of the LogMiner session used by the capture process
The SCN of the last redo entry available for the capture process
The time when the last redo entry became available for the capture process
The information displayed by this query is valid only for an enabled capture process.
Run the following query to display this information for each 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;
Your output looks similar to the following:
Capture Time of Name LogMiner ID Last Redo SCN Last Redo SCN -------------------- ----------- ------------- ----------------- STREAMS_CAPTURE 1 322953 11:33:20 10/16/03
The 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 A26
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;
Your output looks similar to the following:
Capture Process Name Parameter Value Set by User? ------------------------- -------------------------- ---------- --------------- CAPTURE_HNS DISABLE_ON_LIMIT N NO CAPTURE_HNS DOWNSTREAM_REAL_TIME_MINE Y YES CAPTURE_HNS MAXIMUM_SCN INFINITE NO CAPTURE_HNS MESSAGE_LIMIT INFINITE NO CAPTURE_HNS MESSAGE_TRACKING_FREQUENCY 2000000 NO CAPTURE_HNS PARALLELISM 1 NO CAPTURE_HNS STARTUP_SECONDS 0 NO CAPTURE_HNS TIME_LIMIT INFINITE NO CAPTURE_HNS TRACE_LEVEL 0 NO CAPTURE_HNS WRITE_ALERT_LOG Y NO
Note:
If theSet by User? column is NO for a parameter, then the parameter is set to its default value. If the Set by User? column is YES for a parameter, then the parameter might or might not be set to its default value.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.
To display the applied SCN for all of the capture processes in a database, run the following query:
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;
Your output looks similar to the following:
Capture Process Name Applied SCN ------------------------------ ----------- CAPTURE_EMP 177154
You can find the following information about each capture process by running the query in this section:
The redo log scanning latency, which specifies the number of seconds between the creation time of the most recent redo log entry scanned by a capture process and the current time. This number might be relatively large immediately after you start a capture process.
The seconds since last recorded status, which is the number of seconds since a capture process last recorded its status.
The current capture process time, which is the latest time when the capture process recorded its status.
The message creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo data for the most recently captured LCR.
The information displayed by this query is valid only for an enabled capture process.
Run the following query to determine the redo 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;
Your output looks similar to the following:
Capture Latency Current Process in Seconds Since Process Message Name Seconds Last Status Time Creation Time ---------- ------- ------------- ----------------- ----------------- CAPTURE 4 4 12:04:13 03/01/02 12:04:13 03/01/02
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."
You can find the following information about each capture process by running the query in this section:
The message enqueuing latency, which specifies the number of seconds between when an entry was recorded in the redo log and when the message was enqueued by the capture process
The message creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo data for the most recently enqueued message
The enqueue time, which is when the capture process enqueued the message into its queue
The message number of the enqueued message
The information displayed by this query is valid only for an enabled 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;
Your output looks similar to the following:
Capture Latency Process in Message Creation Message Name Seconds Time Enqueue Time Number ---------- ------- -------------------- -------------------- ------- CAPTURE 0 10:56:51 03/01/02 10:56:51 03/01/02 253962
The "Latency in Seconds" returned by this query is the difference between the "Enqueue Time" and the "Message Creation Time."
You can display the following information about rule evaluation for each capture process by running the query in this section:
The name of the capture process.
The number of changes discarded during prefiltering since the capture process was last started. The capture process determined that these changes definitely did not satisfy the capture process rule sets during prefiltering.
The number of changes kept during prefiltering since the capture process was last started. The capture process determined that these changes definitely satisfied the capture process rule sets during prefiltering. Such changes are converted into LCRs and enqueued into the capture process queue.
The total number of prefilter evaluations since the capture process was last started.
The number of undecided changes after prefiltering since the capture process was last started. These changes might or might not satisfy the capture process rule sets. Some of these changes might be filtered out after prefiltering without requiring full evaluation. Other changes require full evaluation to determine whether they satisfy the capture process rule sets.
The number of full evaluations since the capture process was last started. Full evaluations can be expensive. Therefore, capture process performance is best when this number is relatively low.
The information displayed by this query is valid only for an enabled capture process.
Run the following query to display this information 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;
Your output looks similar to the following:
Capture Prefilter Prefilter Undecided Process Changes Changes Prefilter After Full Name Discarded Kept Evaluations Prefilter Evaluations ---------- ---------- ----------- ----------- ----------- ----------- STM1$CAP 219493 2 219961 466 0
The total number of prefilter evaluations equals the sum of the prefilter changes discarded, the prefilter changes kept, and the undecided changes.
See Also:
"Capture Process Rule Evaluation"When a capture process uses combined capture and apply, it sends captured LCRs directly to a single apply process, which applies the changes. Combined capture and apply is typically more efficient than using queues and propagations to send captured LCRs from a capture process to an apply process.
When a capture process uses combined capture and apply, the following columns in the V$STREAMS_CAPTURE data dictionary view are populated:
APPLY_NAME shows the apply process to which the capture process sends captured LCRs
APPLY_DBLINK shows the database link to the remote database if the apply process is at a remote database
APPLY_MESSAGES_SENT shows the number of messages sent by the capture process to the apply process since the capture process last started
APPLY_BYTES_SENT shows the number of bytes sent by the capture process to the apply process since the capture process last started
If a capture process does not use combined capture and apply, then the APPLY_NAME and APPLY_DBLINK columns are not populated, and the APPLY_MESSAGES_SENT and APPLY_BYTES_SENT columns are 0 (zero).
To determine whether a capture process uses combined capture and apply, run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN APPLY_NAME HEADING 'Apply|Name' FORMAT A15
COLUMN APPLY_DBLINK HEADING 'Database|Link' FORMAT A15
COLUMN APPLY_MESSAGES_SENT HEADING 'Number of|Messages Sent' FORMAT 99999999999
COLUMN APPLY_BYTES_SENT HEADING 'Number of|Bytes Sent' FORMAT 99999999999999999
SELECT CAPTURE_NAME,
APPLY_NAME,
APPLY_DBLINK,
APPLY_MESSAGES_SENT,
APPLY_BYTES_SENT
FROM V$STREAMS_CAPTURE;
Your output looks similar to the following:
Capture Apply Database Number of Number of Name Name Link Messages Sent Bytes Sent --------------- --------------- --------------- ------------- ------------------ CAPTURE_HNS APPLY_SPOKE1 SPOKE.NET 260369 49945389
This output indicates that the capture_hns capture process uses combined capture and apply. This capture process sends captured LCRs directly to the apply_spoke1 apply process using the spoke.net database link. Since it last started, the capture process sent 260369 messages and 49945389 bytes to the apply process.
This section provides sample queries that you can use to monitor Oracle Streams synchronous captures.
This section contains these topics:
Displaying the Queue and Rule Set of Each Synchronous Capture
Displaying the Tables For Which Synchronous Capture Captures Changes
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide for an example that configures a replication environment that uses synchronous capture
You can display the following information about each synchronous capture in a database by running the query in this section:
The synchronous capture name
The name of the queue used by the synchronous capture
The name of the positive rule set used by the synchronous capture
The capture user for the synchronous capture
To display this general information about each synchronous capture in a database, run the following query:
COLUMN CAPTURE_NAME HEADING 'Synchronous|Capture Name' FORMAT A20 COLUMN QUEUE_NAME HEADING 'Synchronous|Capture Queue' FORMAT A20 COLUMN RULE_SET_NAME HEADING 'Positive Rule Set' FORMAT A20 COLUMN CAPTURE_USER HEADING 'Capture User' FORMAT A15 SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, CAPTURE_USER FROM DBA_SYNC_CAPTURE;
Your output looks similar to the following:
Synchronous Synchronous Capture Name Capture Queue Positive Rule Set Capture User -------------------- -------------------- -------------------- --------------- SYNC01_CAPTURE STRM01_QUEUE RULESET$_21 STRMADMIN SYNC02_CAPTURE STRM02_QUEUE SYNC02_RULE_SET HR
The DBA_SYNC_CAPTURE_TABLES view displays the tables whose DML changes are captured by any synchronous capture in the local database. The DBA_STREAMS_TABLE_RULES view has information about each synchronous capture name and the rules used by each synchronous capture. You can display the following information by running the query in this section:
The name of each synchronous capture
The name of each rule used by the synchronous capture
If the rule is a subset rule, then the type of subsetting operation covered by the rule
The owner of each table specified in each rule
The name of each table specified in each rule
Whether synchronous capture is enabled or disabled for the table. If the synchronous capture is enabled for a table, then it captures DML changes made to the table. If synchronous capture is not enabled for a table, then it does not capture DML changes made to the table.
To display this information, run the following query:
COLUMN STREAMS_NAME HEADING 'Synchronous|Capture Name' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN SUBSETTING_OPERATION HEADING 'Subsetting|Operation' FORMAT A10
COLUMN TABLE_OWNER HEADING 'Table|Owner' FORMAT A10
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN ENABLED HEADING 'Enabled?' FORMAT A8
SELECT r.STREAMS_NAME,
r.RULE_NAME,
r.SUBSETTING_OPERATION,
t.TABLE_OWNER,
t.TABLE_NAME,
t.ENABLED
FROM DBA_STREAMS_TABLE_RULES r,
DBA_SYNC_CAPTURE_TABLES t
WHERE r.STREAMS_TYPE = 'SYNC_CAPTURE' AND
r.TABLE_OWNER = t.TABLE_OWNER AND
r.TABLE_NAME = t.TABLE_NAME;
Your output looks similar to the following:
Synchronous Subsetting Table Capture Name Rule Name Operation Owner Table Name Enabled? --------------- --------------- ---------- ---------- --------------- -------- SYNC01_CAPTURE EMPLOYEES20 HR EMPLOYEES YES SYNC02_CAPTURE DEPARTMENTS24 DELETE HR DEPARTMENTS YES SYNC02_CAPTURE DEPARTMENTS23 UPDATE HR DEPARTMENTS YES SYNC02_CAPTURE DEPARTMENTS22 INSERT HR DEPARTMENTS YES
This output indicates that synchronous capture sync01_capture captures DML changes made to the hr.employees table. This output also indicates that synchronous capture sync02_capture captures a subset of the changes to the hr.departments table.
If the ENABLED column shows NO for a table, then synchronous capture does not capture changes to the table. The ENABLED column shows NO when a table rule is added to a synchronous capture rule set by a procedure other than ADD_TABLE_RULES or ADD_SUBSET_RULES in the DBMS_STREAMS_ADM package. For example, if the ADD_RULE procedure in the DBMS_RULE_ADM package adds a table rule to a synchronous capture rule set, then the table appears when you query the DBA_SYNC_CAPTURE_TABLES view, but synchronous capture does not capture DML changes to the table. No results appear in the DBA_SYNC_CAPTURE_TABLES view for schema and global rules.
You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process or synchronous capture to capture one or more extra attributes and include the extra attributes in LCRs. The following query displays the extra attributes included in the LCRs captured by each capture process and synchronous capture in the local database:
COLUMN CAPTURE_NAME HEADING 'Capture Process or|Synchronous Capture' 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;
Your output looks similar to the following:
Capture Process or Attribute Name Include Attribute in LCRs? Synchronous Capture -------------------- --------------- ------------------------------ SYNC_CAPTURE ROW_ID NO SYNC_CAPTURE SERIAL# NO SYNC_CAPTURE SESSION# NO SYNC_CAPTURE THREAD# NO SYNC_CAPTURE TX_NAME YES SYNC_CAPTURE USERNAME NO
Based on this output, the capture process or synchronous capture named sync_capture includes the transaction name (tx_name) in the LCRs that it captures, but this capture process or synchronous capture does not include any other extra attributes in the LCRs that it captures. To determine whether name returned by the CAPTURE_NAME column is a capture process or a synchronous capture, query the DBA_CAPTURE and DBA_SYNC_CAPTURE views.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the INCLUDE_EXTRA_ATTRIBUTE procedure
