24 Monitoring Oracle Streams Implicit Capture

Both capture processes and synchronous captures perform implicit capture.

The following topics describe monitoring Oracle Streams implicit capture:

Note:

The Oracle Streams tool in Oracle Enterprise Manager 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.

Monitoring a Capture Process

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

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 aborted

Displaying Session Information About Each Capture Process

The query in this section displays the following session information about each session associated with a capture process in a database:

  • The capture process component

  • The session identifier

  • The serial number

  • The operating system process identification number

  • The process name of the capture process in the form CPnn, where nn can include letters and numbers

To display this information for each capture process in a database, run the following query:

COLUMN ACTION HEADING 'Capture Process Component' FORMAT A25
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A20
COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7
 
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
       SID,
       SERIAL#,
       PROCESS,
       SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
  FROM V$SESSION
  WHERE MODULE ='Streams' AND
        ACTION LIKE '%Capture%';

Your output looks similar to the following:

                                       Session
                                        Serial Operating System     Process
Capture Process Component Session ID    Number Process Number       Name
------------------------- ---------- --------- -------------------- -------
EMDBA$CAP - Capture               74         9 10019                CP01

See Also:

"Capture Process Subcomponents" for information about capture process parallelism

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 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

    See "Capture Process States".

  • 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 A20
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Evaluated|In Detail' FORMAT 9999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 9999999999

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 CP01        954       3 CAPTURING CHANGES      3719085     3389713
_HNS

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

Displaying State Change and Message Creation Time for Each Capture Process

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

    See "Capture Process States".

  • 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

Displaying Elapsed Time Performing Capture Operations for Each Capture Process

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. The V$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

Displaying Information About Each Downstream Capture Process

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.EXAMPLE.COM     STRM03_QUEUE    ENABLED         YES

In this case, the source database for the capture process is dbs1.example.com, but the local database running the capture process is not dbs1.example.com. 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.example.com 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 the V$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:

Displaying the Registered Redo Log Files for Each Capture Process

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.EXAMP       15 /orc/dbs/log/arch2_1 NO         NO
                LE.COM              _15_478347508.arc
STRM02_CAPTURE  DBS2.EXAMP       16 /orc/dbs/log/arch2_1 NO         NO
                LE.COM              _16_478347508.arc 
STRM03_CAPTURE  DBS1.EXAMP       45 /remote_logs/arch1_1 YES        YES
                LE.COM              _45_478347335.arc
STRM03_CAPTURE  DBS1.EXAMP       46 /remote_logs/arch1_1 NO         NO
                LE.COM              _46_478347335.arc
STRM03_CAPTURE  DBS1.EXAMP       47 /remote_logs/arch1_1 NO         NO
                LE.COM              _47_478347335.arc

Assume that this query was run at the dbs2.example.com 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.example.com. This query shows that there are two registered archived redo log files for strm02_capture and three registered archived redo log files for strm03_capture. This query shows the name and location of each of these files in the local file system.

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 before 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.EXAMP       16 /orc/dbs/log/arch2_1_16_478347508.arc
                LE.COM
STRM03_CAPTURE  DBS1.EXAMP       47 /remote_logs/arch1_1_47_478347335.arc
                LE.COM

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.

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.

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 system change number (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 highest SCN available for the capture process

    For local capture, this SCN is the last redo SCN flushed to the log files. For downstream capture, this SCN is the last SCN added to LogMiner through the archive logs.

  • The timestamp of the highest SCN available for the capture process

    For local capture, this timestamp is the time the SCN was written to the log file. For downstream capture, this timestamp is the time of the most recent archive log (containing the most recent SCN) available to LogMiner.

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 'Highest|Available SCN' FORMAT 9999999999
COLUMN AVAILABLE_MESSAGE_CREATE_TIME HEADING 'Time of|Highest|Available 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:

                                               Time of
Capture                                Highest Highest
Name                 LogMiner ID Available SCN Available SCN
-------------------- ----------- ------------- -----------------
DB1$CAP                        1       1506751 09:46:11 06/29/09

Listing the Parameter Settings for Each Capture Process

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 A30
COLUMN VALUE HEADING 'Value' FORMAT A10
COLUMN SET_BY_USER HEADING 'Set by|User?' FORMAT A10

SELECT CAPTURE_NAME,
       PARAMETER, 
       VALUE,
       SET_BY_USER  
  FROM DBA_CAPTURE_PARAMETERS;

Your output looks similar to the following:

Capture
Process                                                             Set by
Name                      Parameter                      Value      User?
------------------------- ------------------------------ ---------- ----------
DA$CAP                    CAPTURE_IDKEY_OBJECTS          N          NO
DA$CAP                    CAPTURE_SEQUENCE_NEXTVAL       N          NO
DA$CAP                    DISABLE_ON_LIMIT               N          NO
DA$CAP                    DOWNSTREAM_REAL_TIME_MINE      Y          NO
DA$CAP                    EXCLUDETRANS                              NO
DA$CAP                    EXCLUDEUSER                               NO
DA$CAP                    EXCLUDEUSERID                             NO
DA$CAP                    GETAPPLOPS                     Y          NO
DA$CAP                    GETREPLICATES                  N          NO
DA$CAP                    IGNORE_TRANSACTION                        NO
DA$CAP                    IGNORE_UNSUPPORTED_TABLE       *          NO
DA$CAP                    MAXIMUM_SCN                    INFINITE   NO
DA$CAP                    MAX_SGA_SIZE                   INFINITE   NO
DA$CAP                    MERGE_THRESHOLD                60         NO
DA$CAP                    MESSAGE_LIMIT                  INFINITE   NO
DA$CAP                    MESSAGE_TRACKING_FREQUENCY     2000000    NO
DA$CAP                    PARALLELISM                    1          NO
DA$CAP                    SKIP_AUTOFILTERED_TABLE_DDL    Y          NO
DA$CAP                    SPLIT_THRESHOLD                1800       NO
DA$CAP                    STARTUP_SECONDS                0          NO
DA$CAP                    TIME_LIMIT                     INFINITE   NO
DA$CAP                    TRACE_LEVEL                    0          NO
DA$CAP                    WRITE_ALERT_LOG                Y          NO
DA$CAP                    XOUT_CLIENT_EXISTS             N          NO

Note:

If the Set 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 was set by a user and might or might not be set to its default value.

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.

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

Determining Redo Log Scanning Latency for Each Capture Process

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 at the source database 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
---------- ------- ------------- ----------------- -----------------
DA$CAP           1             1 12:33:39 07/14/10 12:33:39 07/14/10

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."

Determining Message Enqueuing Latency for Each Capture Process

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 at the source database 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 at the source database 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 9999999999

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."

Displaying Information About Rule Evaluations for Each Capture Process

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:

                 Prefilter   Prefilter               Undecided
Capture            Changes     Changes   Prefilter       After        Full
Name             Discarded        Kept Evaluations   Prefilter Evaluations
--------------- ---------- ----------- ----------- ----------- -----------
CAPTURE_HNS         927409     3271491     4198900           0           9

The total number of prefilter evaluations equals the sum of the prefilter changes discarded, the prefilter changes kept, and the undecided changes.

Determining Which Capture Processes Use Combined Capture and Apply

A combined capture and apply environment is efficient because the capture process acts as the propagation sender, and the buffered queue is optimized to make replication of changes more efficient.

When a capture process uses combined capture and apply, the OPTIMIZATION column in the V$STREAMS_CAPTURE data dictionary view is greater than zero. When a capture process does not use combined capture and apply, the OPTIMIZATION column is 0 (zero).

To determine whether a capture process uses combined capture and apply, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture Name' FORMAT A30
COLUMN OPTIMIZATION HEADING 'Optimized?' FORMAT A10

SELECT CAPTURE_NAME, 
       DECODE(OPTIMIZATION,
                0, 'No',
                   'Yes') OPTIMIZATION
  FROM V$STREAMS_CAPTURE;

Your output looks similar to the following:

Capture Name                   Optimized?
------------------------------ ----------
CAPTURE_HNS                    Yes

This output indicates that the capture_hns capture process uses combined capture and apply.

Displaying Information About Split and Merge Operations

Splitting and merging an Oracle Streams destination is useful under the following conditions:

  • A single capture process captures changes that are sent to two or more apply processes.

  • An apply process stops accepting changes captured by the capture process. The apply process might stop accepting changes if, for example, the apply process is disabled, the database that contains the apply process goes down, there is a network problem, the computer system running the database that contains the apply process goes down, or for some other reason.

When these conditions are met, it is best to split the problem destination stream off from the other destination streams to avoid degraded performance. When the problem is corrected, the destination stream that was split off can be merged back into the other destination streams for the capture process.

By default, split and merge operations are performed automatically when Oracle Streams detects a problem destination. Two capture process parameters, split_threshold and merge_threshold, control automatic split and merge operations.

The following sections contain queries that you can run to monitor current and past automatic split and merge operations:

Note:

The queries in these sections only show information about automatic split and merge operations. These queries do not show information about operations that split streams manually using the SPLIT_STREAMS procedure in the DBMS_STREAMS_ADM package.

See Also:

Displaying the Names of the Original and Cloned Oracle Streams Components

The query in this section shows the following information about the Oracle Streams components that are involved in a split and merge operation:

  • The name of the original capture process from which a destination stream was split off

  • The name of the cloned capture process that captures changes for the problem destination

  • The name of the original propagation or apply process that was part of the stream that was split off

    In a multiple-database configuration, a propagation sends changes from the capture process's queue to the apply process's queue, and a propagation is shown in this query. In a single-database configuration, an apply process dequeues changes from the queue that is used by the capture process, and an apply process is shown in this query.

  • The name of the cloned propagation or apply process that processes changes for the problem destination

  • The type of the Oracle Streams component that receives changes from the capture process, either PROPAGATION or APPLY

Run the following query to display this information:

COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original|Capture|Process' FORMAT A15
COLUMN CLONED_CAPTURE_NAME HEADING 'Cloned|Capture|Process' FORMAT A15
COLUMN ORIGINAL_STREAMS_NAME HEADING 'Original|Streams|Name' FORMAT A15
COLUMN CLONED_STREAMS_NAME HEADING 'Cloned|Streams|Name' FORMAT A15
COLUMN STREAMS_TYPE HEADING 'Streams|Type' FORMAT A11
 
SELECT ORIGINAL_CAPTURE_NAME,
       CLONED_CAPTURE_NAME,
       ORIGINAL_STREAMS_NAME,
       CLONED_STREAMS_NAME,
       STREAMS_TYPE
 FROM DBA_STREAMS_SPLIT_MERGE;

Your output looks similar to the following:

Original        Cloned          Original        Cloned
Capture         Capture         Streams         Streams         Streams
Process         Process         Name            Name            Type
--------------- --------------- --------------- --------------- -----------
DB$CAP          CLONED$_DB$CAP_ PROPAGATION$_17 CLONED$_PROPAGA PROPAGATION
                1                               TION$_17_2

See Also:

Oracle Streams Replication Administrator's Guide for more information about split and merge operations

Displaying the Actions and Thresholds for Split and Merge Operations

The query in this section shows the following information about the actions performed by the split and merge operation and the thresholds that were set for splitting and merging a problem destination:

  • The name of the original capture process from which a destination stream was split off

  • The script status of the split or merge job, either GENERATING, NOT EXECUTED, EXECUTING, EXECUTED, or ERROR

  • The type of action performed by the job, either SPLIT, MERGE, or MONITOR

    When a SPLIT job determines that a split must be performed, a row with SPLIT action type is inserted into the DBA_STREAMS_SPLIT_MERGE view.

    When the split operation is complete, the SPLIT action type row is copied to the DBA_STREAMS_SPLIT_MERGE_HIST view, and a MERGE job is created. A row with MERGE action type is inserted into the DBA_STREAMS_SPLIT_MERGE view. When merge operation is complete, the MERGE action type row is moved to the DBA_STREAMS_SPLIT_MERGE_HIST view, and the SPLIT action type row is deleted from the DBA_STREAMS_SPLIT_MERGE view. The SPLIT action type row was previously copied to the DBA_STREAMS_SPLIT_MERGE_HIST view.

    Each original capture process has a SPLIT job that monitors all of its destinations. This type of job displays the MONITOR action type in rows in the DBA_STREAMS_SPLIT_MERGE view. MONITOR action type rows are moved to the DBA_STREAMS_SPLIT_MERGE_HIST view only if the SPLIT job is disabled. A SPLIT job can be disabled either by setting the split_threshold capture process parameter to INFINITE or by dropping the capture process.

  • The capture process parameter threshold set for the operation, in seconds

    For SPLIT jobs, the threshold is set by the split_threshold capture process parameter. For MERGE jobs, the threshold is set by the merge_threshold capture process parameter.

  • The status of the action

    For SPLIT actions, the status can be SPLITTING, SPLIT DONE, or ERROR. The SPLITTING status indicates that the split operation is being performed. The SPLIT DONE status indicates that the split operation is complete. The ERROR status indicates that an error was returned during the split operation.

    For MERGE actions, the status can be NOTHING TO MERGE, MERGING, MERGE DONE, or ERROR. The NOTHING TO MERGE status indicates that a split was performed but the split stream is not yet ready to merge. The MERGING status indicates that the merge operation is being performed. The MERGE DONE status indicates that the merge operation is complete. The ERROR status indicates that an error was returned during the merge operation.

    For MONITOR actions, the status can be any of the SPLIT and MERGE status values. In addition, a MONITOR action can show NOTHING TO SPLIT or NONSPLITTABLE for its status. The NOTHING TO SPLIT status indicates that the streams flowing from the capture process are being processed at all destinations, and no stream should be split. The NONSPLITTABLE status indicates that it is not possible to split the stream for the capture process. A NONSPLITTABLE status is possible in the following cases:

    • The capture process is disabled or aborted.

    • The capture process's queue has at least one publisher in addition to the capture process. The additional publisher can be another capture process or a propagation that sends messages to the queue.

    • The capture process has only one destination. Split and merge operations are possible only when there are two or more destinations for the changes captured by the capture process.

  • The date and time when the job status was last updated

Run the following query to display this information:

COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original|Capture|Process' FORMAT A10
COLUMN SCRIPT_STATUS HEADING 'Script|Status' FORMAT A12
COLUMN ACTION_TYPE HEADING 'Action|Type' FORMAT A7
COLUMN ACTION_THRESHOLD HEADING 'Action|Threshold' FORMAT A15
COLUMN STATUS HEADING 'Status' FORMAT A16
COLUMN STATUS_UPDATE_TIME HEADING 'Status|Update|Time' FORMAT A15
 
SELECT ORIGINAL_CAPTURE_NAME,
       SCRIPT_STATUS,
       ACTION_TYPE,
       ACTION_THRESHOLD,
       STATUS,
       STATUS_UPDATE_TIME
 FROM DBA_STREAMS_SPLIT_MERGE
 ORDER BY STATUS_UPDATE_TIME DESC;

Your output looks similar to the following:

Original                                                         Status
Capture    Script       Action  Action                           Update
Process    Status       Type    Threshold       Status           Time
---------- ------------ ------- --------------- ---------------- ---------------
DB$CAP     EXECUTED     SPLIT   1800            SPLIT DONE       31-MAR-09 01.31
                                                                 .37.133788 PM

See Also:

Displaying the Lag Time of the Cloned Capture Process

After a stream is split off from a capture process for a problem destination, you must correct the problem at the destination and ensure that the cloned capture process is enabled. When the cloned capture process is sending changes to the problem destination, and the apply process at the problem destination is applying these changes, an Oracle Scheduler job runs the MERGE_STREAMS_JOB procedure according to its schedule.

The MERGE_STREAMS_JOB procedure queries the CAPTURE_MESSAGE_CREATE_TIME in the GV$STREAMS_CAPTURE view. When the difference between CAPTURE_MESSAGE_CREATE_TIME of the cloned capture process and the original capture process is less than or equal to the value of the merge_threshold capture process parameter, the MERGE_STREAMS_JOB procedure determines that the streams are ready to merge. The MERGE_STREAMS_JOB procedure runs the MERGE_STREAMS procedure automatically to merge the streams.

The LAG column in the DBA_STREAMS_SPLIT_MERGE view tracks the time in seconds that the cloned capture process lags behind the original capture process. The following query displays the lag time:

COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original Capture Process' FORMAT A25
COLUMN CLONED_CAPTURE_NAME HEADING 'Cloned Capture Process' FORMAT A25
COLUMN LAG HEADING 'Lag' FORMAT 999999999999999
 
SELECT ORIGINAL_CAPTURE_NAME,
       CLONED_CAPTURE_NAME,
       LAG
 FROM DBA_STREAMS_SPLIT_MERGE;

Your output looks similar to the following:

Original Capture Process  Cloned Capture Process                 Lag
------------------------- ------------------------- ----------------
DB$CAP                    CLONED$_DB$CAP_1                       526

When the MERGE_STREAMS_JOB runs and the lag time is less than or equal to the value of the merge_threshold capture process parameter, the merge operation begins.

See Also:

Oracle Streams Replication Administrator's Guide for more information about split and merge operations

Displaying Information About the Split and Merge Jobs

The query in this section shows the following information about split and merge jobs:

  • The name of the original capture process from which a destination stream was split off

  • The owner of the job

  • The name of the job

  • The current state of the job, either DISABLED, RETRY SCHEDULED, SCHEDULED, RUNNING, COMPLETED, BROKEN, FAILED, REMOTE, SUCCEEDED, or CHAIN_STALLED

    See Oracle Database Administrator's Guide for information about these job states.

  • The date and time when the job will run next

Run the following query to display this information:

COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original|Capture|Process' FORMAT A10
COLUMN JOB_OWNER HEADING 'Job Owner' FORMAT A10
COLUMN JOB_NAME HEADING 'Job Name' FORMAT A15
COLUMN JOB_STATE HEADING 'Job State' FORMAT A15
COLUMN JOB_NEXT_RUN_DATE HEADING 'Job Next|Run Date' FORMAT A20
 
SELECT ORIGINAL_CAPTURE_NAME,
       JOB_OWNER,
       JOB_NAME,
       JOB_STATE,
       JOB_NEXT_RUN_DATE
 FROM DBA_STREAMS_SPLIT_MERGE;

Your output looks similar to the following:

Original
Capture                                               Job Next
Process    Job Owner  Job Name        Job State       Run Date
---------- ---------- --------------- --------------- --------------------
DB$CAP     SYS        STREAMS_SPLITJO SCHEDULED       01-APR-09 01.14.55.0
                      B$_3                            00000 PM -07:00
DB$CAP     SYS        STREAMS_MERGEJO SCHEDULED       01-APR-09 01.17.08.0
                      B$_6                            00000 PM -07:00

See Also:

Oracle Streams Replication Administrator's Guide for more information about split and merge operations

Displaying Information About Past Split and Merge Operations

The query in this section shows the following historical information about split and merge operations that were performed in the past:

  • The name of the original capture process from which a destination stream was split off

  • The script status of split or merge job

  • The type of action performed by the job, either SPLIT or MERGE

  • The status of the action performed by the job

    See "Displaying the Actions and Thresholds for Split and Merge Operations" for information about the status values.

  • The owner of the job

  • The name of the job

  • The recoverable script ID

Run the following query to display this information:

COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original|Capture|Process' FORMAT A8
COLUMN SCRIPT_STATUS HEADING 'Script|Status' FORMAT A12
COLUMN ACTION_TYPE HEADING 'Action|Type' FORMAT A8
COLUMN STATUS HEADING 'Status' FORMAT A10
COLUMN JOB_OWNER HEADING 'Job Owner' FORMAT A10
COLUMN JOB_NAME HEADING 'Job Name' FORMAT A10
COLUMN RECOVERABLE_SCRIPT_ID HEADING 'Recoverable|Script ID' FORMAT A15
 
SELECT ORIGINAL_CAPTURE_NAME,
       SCRIPT_STATUS,
       ACTION_TYPE,
       STATUS,
       JOB_OWNER,
       JOB_NAME,
       RECOVERABLE_SCRIPT_ID
 FROM DBA_STREAMS_SPLIT_MERGE_HIST;

Your output looks similar to the following:

Original
Capture  Script       Action                                    Recoverable
Process  Status       Type     Status     Job Owner  Job Name   Script ID
-------- ------------ -------- ---------- ---------- ---------- ---------------
DB1$CAP  EXECUTED     SPLIT    SPLIT DONE SYS        STREAMS_SP 6E5C6C49CDB5798
                                                     LITJOB$_9  3E040578C891704
                                                                87
 
DB1$CAP  EXECUTED     MERGE    MERGE DONE SYS        STREAMS_ME 6E5BA57554F1C4C
                                                     RGEJOB$_12 3E040578C89170A
                                                                1F

See Also:

Oracle Streams Replication Administrator's Guide for more information about split and merge operations

Monitoring Supplemental Logging

The following sections contain queries that you can run to monitor supplemental logging at a source database:

The total supplemental logging at a database is determined by the results shown in all three of the queries in these sections combined. For example, supplemental logging can be enabled for columns in a table even if no results for the table are returned by the query in the "Displaying Supplemental Log Groups at a Source Database" section. That is, supplemental logging can be enabled for the table if database supplemental logging is enabled or if the table is in a schema for which supplemental logging was enabled during preparation for instantiation.

Supplemental logging places additional column data into a redo log when an operation is performed. A capture process captures this additional information and places it in LCRs. An apply process that applies these captured LCRs might need this additional information to schedule or apply changes correctly.

Displaying Supplemental Log Groups at a Source Database

To check whether one or more log groups are specified for the table at the source database, run the following query:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20

SELECT 
    LOG_GROUP_NAME, 
    TABLE_NAME, 
    DECODE(ALWAYS,
             'ALWAYS', 'Unconditional',
             'CONDITIONAL', 'Conditional') ALWAYS,
    LOG_GROUP_TYPE
  FROM DBA_LOG_GROUPS;

Your output looks similar to the following:

                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
LOG_GROUP_DEP_PK     DEPARTMENTS     Unconditional  USER LOG GROUP
SYS_C002105          REGIONS         Unconditional  PRIMARY KEY LOGGING
SYS_C002106          REGIONS         Conditional    FOREIGN KEY LOGGING
SYS_C002110          LOCATIONS       Unonditional   ALL COLUMN LOGGING
SYS_C002111          COUNTRIES       Conditional    ALL COLUMN LOGGING
LOG_GROUP_JOBS_CR    JOBS            Conditional    USER LOG GROUP

If the output for the type of log group shows how the log group was created:

  • If the output is USER LOG GROUP, then the log group was created using the ADD SUPPLEMENTAL LOG GROUP clause of the ALTER TABLE statement.

  • Otherwise, the log group was created using the ADD SUPPLEMENTAL LOG DATA clause of the ALTER TABLE statement.

If the type of log group is USER LOG GROUP, then you can list the columns in the log group by querying the DBA_LOG_GROUP_COLUMNS data dictionary view.

Note:

If the type of log group is not USER LOG GROUP, then the DBA_LOG_GROUP_COLUMNS data dictionary view does not contain information about the columns in the log group. Instead, Oracle supplementally logs the correct columns when an operation is performed on the table. For example, if the type of log group is PRIMARY KEY LOGGING, then Oracle logs the current primary key column(s) when a change is performed on the table.

Displaying Database Supplemental Logging Specifications

To display the database supplemental logging specifications, query the V$DATABASE dynamic performance view, as in the following example:

COLUMN log_min HEADING 'Minimum|Supplemental|Logging?' FORMAT A12
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging?' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging?' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging?' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging?' FORMAT A12

SELECT SUPPLEMENTAL_LOG_DATA_MIN log_min, 
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM V$DATABASE;  
  

Your output looks similar to the following:

Minimum      Primary Key  Foreign Key  Unique        All Columns
Supplemental Supplemental Supplemental Supplemental  Supplemental
Logging?     Logging?     Logging?     Logging?      Logging?
------------ ------------ ------------ ------------- ------------
YES          YES          YES          YES           NO

These results show that minimum, primary key, foreign key, and unique key columns are being supplementally logged for all of the tables in the database. Because unique key columns are supplementally logged, bitmap index columns also are supplementally logged. However, all columns are not being supplementally logged.

Displaying Supplemental Logging Specified During Preparation for Instantiation

Supplemental logging can be enabled when database objects are prepared for instantiation using one of the three procedures in the DBMS_CAPTURE_ADM package. A data dictionary view displays the supplemental logging enabled by each of these procedures: PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_GLOBAL_INSTANTIATION.

  • The DBA_CAPTURE_PREPARED_TABLES view displays the supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure.

  • The DBA_CAPTURE_PREPARED_SCHEMAS view displays the supplemental logging enabled by the PREPARE_SCHEMA_INSTANTIATION procedure.

  • The DBA_CAPTURE_PREPARED_DATABASE view displays the supplemental logging enabled by the PREPARE_GLOBAL_INSTANTIATION procedure.

Each of these views has the following columns:

  • SUPPLEMENTAL_LOG_DATA_PK shows whether primary key supplemental logging was enabled by a procedure.

  • SUPPLEMENTAL_LOG_DATA_UI shows whether unique key and bitmap index supplemental logging was enabled by a procedure.

  • SUPPLEMENTAL_LOG_DATA_FK shows whether foreign key supplemental logging was enabled by a procedure.

  • SUPPLEMENTAL_LOG_DATA_ALL shows whether supplemental logging for all columns was enabled by a procedure.

Each of these columns can display one of the following values:

  • IMPLICIT means that the relevant procedure enabled supplemental logging for the columns.

  • EXPLICIT means that supplemental logging was enabled for the columns manually using an ALTER TABLE or ALTER DATABASE statement with an ADD SUPPLEMENTAL LOG DATA clause.

  • NO means that supplemental logging was not enabled for the columns using a prepare procedure or an ALTER TABLE or ALTER DATABASE statement with an ADD SUPPLEMENTAL LOG DATA clause. Supplemental logging might not be enabled for the columns. However, supplemental logging might be enabled for the columns at another level (table, schema, or database), or it might have been enabled using an ALTER TABLE statement with an ADD SUPPLEMENTAL LOG GROUP clause.

The following sections contain queries that display the supplemental logging enabled by these procedures:

Displaying Supplemental Logging Enabled by PREPARE_TABLE_INSTANTIATION

The following query displays the supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure for the tables in the hr schema:

COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging' FORMAT A12

SELECT TABLE_NAME,
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_TABLES
  WHERE TABLE_OWNER = 'HR';  
  

Your output looks similar to the following:

                Primary Key  Foreign Key  Unique         All Columns
                Supplemental Supplemental Supplemental   Supplemental
Table Name      Logging      Logging      Logging        Logging
--------------- ------------ ------------ -------------- ------------
COUNTRIES       NO           NO           NO             NO
REGIONS         IMPLICIT     IMPLICIT     IMPLICIT       NO
DEPARTMENTS     IMPLICIT     IMPLICIT     IMPLICIT       NO
LOCATIONS       EXPLICIT     NO           NO             NO
EMPLOYEES       NO           NO           NO             IMPLICIT
JOB_HISTORY     NO           NO           NO             NO
JOBS            NO           NO           NO             NO

These results show the following:

  • The PREPARE_TABLE_INSTANTIATION procedure enabled supplemental logging for the primary key, unique key, bitmap index, and foreign key columns in the hr.regions and hr.departments tables.

  • The PREPARE_TABLE_INSTANTIATION procedure enabled supplemental logging for all columns in the hr.employees table.

  • An ALTER TABLE statement with an ADD SUPPLEMENTAL LOG DATA clause enabled primary key supplemental logging for the hr.locations table.

Note:

Omit the WHERE clause in the query to list the information for all of the tables in the database.
Displaying Supplemental Logging Enabled by PREPARE_SCHEMA_INSTANTIATION

The following query displays the supplemental logging enabled by the PREPARE_SCHEMA_INSTANTIATION procedure:

COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A20
COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging' FORMAT A12

SELECT SCHEMA_NAME,
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_SCHEMAS;
  

Your output looks similar to the following:

                     Primary Key  Foreign Key  Unique         All Columns
                     Supplemental Supplemental Supplemental   Supplemental
Schema Name          Logging      Logging      Logging        Logging
-------------------- ------------ ------------ -------------- ------------
HR                   NO           NO           NO             IMPLICIT
OE                   IMPLICIT     IMPLICIT     IMPLICIT       NO

These results show the following:

  • The PREPARE_SCHEMA_INSTANTIATION procedure enabled supplemental logging for all columns in tables in the hr schema.

  • The PREPARE_SCHEMA_INSTANTIATION procedure enabled supplemental logging for the primary key, unique key, bitmap index, and foreign key columns in the tables in the oe schema.

Displaying Supplemental Logging Enabled by PREPARE_GLOBAL_INSTANTIATION

The following query displays the supplemental logging enabled by the PREPARE_GLOBAL_INSTANTIATION procedure:

COLUMN log_pk HEADING 'Primary Key|Supplemental|Logging' FORMAT A12
COLUMN log_fk HEADING 'Foreign Key|Supplemental|Logging' FORMAT A12
COLUMN log_ui HEADING 'Unique|Supplemental|Logging' FORMAT A12
COLUMN log_all HEADING 'All Columns|Supplemental|Logging' FORMAT A12

SELECT SUPPLEMENTAL_LOG_DATA_PK log_pk,
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_DATABASE;
  

Your output looks similar to the following:

Primary Key  Foreign Key  Unique         All Columns
Supplemental Supplemental Supplemental   Supplemental
Logging      Logging      Logging        Logging
------------ ------------ -------------- ------------
IMPLICIT     IMPLICIT     IMPLICIT       NO

These results show that the PREPARE_GLOBAL_INSTANTIATION procedure enabled supplemental logging for the primary key, unique key, bitmap index, and foreign key columns in all of the tables in the database.

Monitoring a Synchronous Capture

This section provides sample queries that you can use to monitor Oracle Streams synchronous captures.

This section contains these topics:

See Also:

Displaying the Queue and Rule Set of Each 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

Displaying the Tables For Which Synchronous Capture Captures Changes

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.

Viewing the Extra Attributes Captured by Implicit Capture

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.