Troubleshooting a Streams Environment

Troubleshooting Capture Problems
If a capture process is not capturing changes as expected, or if you are having other problems with a capture process, then use the following checklist to identify and resolve capture problems:
Is the Capture Process Enabled?
Is the Capture Process Current?
Are Required Redo Log Files Missing?
Is a Downstream Capture Process Waiting for Redo Data?
Are You Trying to Configure Downstream Capture without DBMS_CAPTURE_ADM?
Are More Actions Required for Downstream Capture without a Database Link?
See Also:
Chapter 2, "Streams Capture Process"
Chapter 11, "Managing a Capture Process"
Chapter 20, "Monitoring Streams Capture Processes"

Is the Capture Process Enabled?
A capture process captures changes only when it is enabled.
You can check whether a capture process is enabled, disabled, or aborted by querying the DBA_CAPTURE data dictionary view. For example, to check whether a capture process named capture is enabled, run the following query:SELECT STATUS FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';
If the capture process is disabled, then your output looks similar to the following:STATUS
--------
DISABLED
If the capture process is disabled, then try restarting it. If the capture process is aborted, then you might need to correct an error before you can restart it successfully.
To determine why the capture process aborted, query the DBA_CAPTURE data dictionary view or check the trace file for the capture process. The following query shows when the capture process aborted and the error that caused it to abort:COLUMN CAPTURE_NAME HEADING 'CaptureProcessName' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40
SELECT CAPTURE_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
FROM DBA_CAPTURE WHERE STATUS='ABORTED';
Are Required Redo Log Files Missing?When a capture process is started or restarted, it might need to scan redo log files that were generated before the log file that contains the start SCN. You can query the DBA_CAPTURE data dictionary view to determine the first SCN and start SCN for a capture process. Removing required redo log files before they are scanned by a capture process causes the capture process to abort and results in the following error in a capture process trace file:ORA-01291: missing logfile
If you see this error, then try restoring any missing redo log file and restarting the capture process. You can check the V$LOGMNR_LOGS dynamic performance view to determine the missing SCN range, and add the relevant redo log files. 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.If you are using the flash recovery area feature of Recovery Manager (RMAN) on a source database in a Streams environment, then RMAN might delete archived redo log files that are required by a capture process. RMAN might delete these files when the disk space used by the recovery-related files is nearing the specified disk quota for the flash recovery area. To prevent this problem in the future, complete one or more of the following actions:Increase the disk quota for the flash recovery area. Increasing the disk quota makes it less likely that RMAN will delete a required archived redo log file, but it will not always prevent the problem.Configure the source database to store archived redo log files in a location other than the flash recovery area. A local capture process will be able to use the log files in the other location if the required log files are missing in the flash recovery area. In this case, a database administrator must manage the log files manually in the other location.See Also:"ARCHIVELOG Mode and a Capture Process""First SCN and Start SCN""Displaying the Registered Redo Log Files for Each Capture Process"Oracle Database Backup and Recovery Basics and Oracle Database Backup and Recovery Advanced User's Guide for more information about the flash recovery area featureIs a Downstream Capture Process Waiting for Redo Data?If a downstream capture process is not capturing changes, then it might be waiting for redo data to scan. Redo log files can be registered implicitly or explicitly for a downstream capture process. Redo log files registered implicitly typically are registered in one of the following ways:For a real-time downstream capture process, redo transport services use the log writer process (LGWR) to transfer the redo data from the source database to the standby redo log at the downstream database. Next, the archiver at the downstream database registers the redo log files with the downstream capture process when it archives them.For an archived-log downstream capture process, redo transport services transfer the archived redo log files from the source database to the downstream database and register the archived redo log files with the downstream capture process.If redo log files are registered explicitly for a downstream capture process, then you must manually transfer the redo log files to the downstream database and register them with the downstream capture process.Regardless of whether the redo log files are registered implicitly or explicitly, the downstream capture process can capture changes made to the source database only if the appropriate redo log files are registered with the downstream capture process. You can query the V$STREAMS_CAPTURE dynamic performance view to determine whether a downstream capture process is waiting for a redo log file. For example, run the following query for a downstream capture process named strm05_capture:SELECT STATE FROM V$STREAMS_CAPTURE WHERE CAPTURE_NAME='STRM05_CAPTURE';
If the capture process state is either WAITING FOR DICTIONARY REDO or WAITING FOR REDO, then verify that the redo log files have been registered with the downstream capture process by querying the DBA_REGISTERED_ARCHIVED_LOG and DBA_CAPTURE data dictionary views. For example, the following query lists the redo log files currently registered with the strm05_capture downstream capture process:COLUMN SOURCE_DATABASE HEADING 'SourceDatabase' FORMAT A15
COLUMN SEQUENCE# HEADING 'SequenceNumber' FORMAT 9999999
COLUMN NAME HEADING 'Archived Redo LogFile Name' FORMAT A30
COLUMN DICTIONARY_BEGIN HEADING 'DictionaryBuildBegin' FORMAT A10
COLUMN DICTIONARY_END HEADING 'DictionaryBuildEnd' FORMAT A10
SELECT r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE c.CAPTURE_NAME = 'STRM05_CAPTURE' AND
r.CONSUMER_NAME = c.CAPTURE_NAME;
If this query does not return any rows, then no redo log files are registered with the capture process currently. If you configured redo transport services to transfer redo data from the source database to the downstream database for this capture process, then make sure the redo transport services are configured correctly. If the redo transport services are configured correctly, then run the ALTER SYSTEM ARCHIVE LOG CURRENT statement at the source database to archive a log file. If you did not configure redo transport services to transfer redo data, then make sure the method you are using for log file transfer and registration is working properly. You can register log files explicitly using an ALTER DATABASE REGISTER LOGICAL LOGFILE statement.If the downstream capture process is waiting for redo, then it also is possible that there is a problem with the network connection between the source database and the downstream database. There also might be a problem with the log file transfer method. Check your network connection and log file transfer method to ensure that they are working properly.If you configured a real-time downstream capture process, and no redo log files are registered with the capture process, then try switching the log file at the source database. You might need to switch the log file more than once if there is little or no activity at the source database.Also, if you plan to use a downstream capture process to capture changes to historical data, then consider the following additional issues:Both the source database that generates the redo log files and the database that runs a downstream capture process must be Oracle Database 10g databases.The start of a data dictionary build must be present in the oldest redo log file added, and the capture process must be configured with a first SCN that matches the start of the data dictionary build.The database objects for which the capture process will capture changes must be prepared for instantiation at the source database, not at the downstream database. In addition, you cannot specify a time in the past when you prepare objects for instantiation. Objects are always prepared for instantiation at the current database SCN, and only changes to a database object that occurred after the object was prepared for instantiation can be captured by a capture process.
Troubleshooting Propagation Problems
If a propagation is not propagating changes as expected, then use the following checklist to identify and resolve propagation problems:
Does the Propagation Use the Correct Source and Destination Queue?
Is the Propagation Enabled?
Are There Enough Job Queue Processes?
Is Security Configured Properly for the ANYDATA Queue?
See Also:
Chapter 3, "Streams Staging and Propagation"
Chapter 12, "Managing Staging and Propagation"
"Monitoring Streams Propagations and Propagation Jobs"

Does the Propagation Use the Correct Source and Destination Queue?
If messages are not appearing in the destination queue for a propagation as expected, then the propagation might not be configured to propagate messages from the correct source queue to the correct destination queue.
For example, to check the source queue and destination queue for a propagation named dbs1_to_dbs2, run the following query:COLUMN SOURCE_QUEUE HEADING 'Source Queue' FORMAT A35
COLUMN DESTINATION_QUEUE HEADING 'Destination Queue' FORMAT A35
SELECT
p.SOURCE_QUEUE_OWNER'.'
p.SOURCE_QUEUE_NAME'@'
g.GLOBAL_NAME SOURCE_QUEUE,
p.DESTINATION_QUEUE_OWNER'.'
p.DESTINATION_QUEUE_NAME'@'
p.DESTINATION_DBLINK DESTINATION_QUEUE
FROM DBA_PROPAGATION p, GLOBAL_NAME g
WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2';
Your output looks similar to the following:Source Queue Destination Queue
----------------------------------- -----------------------------------
STRMADMIN.STREAMS_QUEUE@DBS1.NET STRMADMIN.STREAMS_QUEUE@DBS2.NET
If the propagation is not using the correct queues, then create a new propagation. You might need to remove the existing propagation if it is not appropriate for your environment.
Is the Propagation Enabled?
For a propagation job to propagate messages, the propagation must be enabled. If messages are not being propagated by a propagation as expected, then the propagation might not be enabled.
You can find the following information about a propagation:
The database link used to propagate messages from the source queue to the destination queue
Whether the propagation is ENABLED, DISABLED, or ABORTED
The date of the last error, if there are any propagation errors
If there are any propagation errors, then the error number of the last error
The error message of the last error, if there are any propagation errors
For example, to check whether a propagation named streams_propagation is enabled, run the following query:COLUMN DESTINATION_DBLINK HEADING 'DatabaseLink' FORMAT A10
COLUMN STATUS HEADING 'Status' FORMAT A8
COLUMN ERROR_DATE HEADING 'ErrorDate'
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A50

SELECT DESTINATION_DBLINK,
STATUS,
ERROR_DATE,
ERROR_MESSAGE
FROM DBA_PROPAGATION
WHERE PROPAGATION_NAME = 'STREAMS_PROPAGATION';
If the propagation is disabled currently, then your output looks similar to the following:Database Error
Link Status Date Error Message
---------- -------- --------- --------------------------------------------------
INST2.NET DISABLED 27-APR-05 ORA-25307: Enqueue rate too high, flow control
enabled
Checking for Apply Errors
To check for apply errors, run the following query:COLUMN APPLY_NAME HEADING 'ApplyProcessName' FORMAT A10
COLUMN SOURCE_DATABASE HEADING 'SourceDatabase' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'LocalTransactionID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20
COLUMN MESSAGE_COUNT HEADING 'Messages inErrorTransaction' FORMAT 99999999
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;
If there are any apply errors, then your output looks similar to the following:Apply Local Messages in
Process Source Transaction Error
Name Database ID Error Number Error Message Transaction
---------- ---------- ----------- ------------ -------------------- -----------
APPLY_FROM MULT3.NET 1.62.948 1403 ORA-01403: no data f 1
_MULT3 ound
APPLY_FROM MULT2.NET 1.54.948 1403 ORA-01403: no data f 1
_MULT2 ound