Troubleshooting a Streams Environment

After configuring streams once it goes to production it is very much necessary to have a document which explains you basic methods and scripts to trouble shoot the streams environment. I have done a small document for our esteemed client.

Once you receive call from client site regarding some problems in streams environment check for

Is the Capture Process Enabled?

SELECT STATUS FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';

Does the Propagation Use the Correct Source and Destination Queue?

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

Is the Propagation Job Used by a Propagation Enabled?

COLUMN DESTINATION_DBLINK HEADING 'Destination|DB Link' FORMAT A15

COLUMN SCHEDULE_DISABLED HEADING 'Schedule' FORMAT A8

COLUMN PROCESS_NAME HEADING 'Process' FORMAT A7

COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 9999

COLUMN LAST_ERROR_TIME HEADING 'Last Error Time' FORMAT A15

COLUMN LAST_ERROR_MSG HEADING 'Last Error Message' FORMAT A18

SELECT p.DESTINATION_DBLINK,

DECODE(s.SCHEDULE_DISABLED,

'Y', 'Disabled',

'N', 'Enabled') SCHEDULE_DISABLED,

s.PROCESS_NAME,

s.FAILURES,

s.LAST_ERROR_TIME,

s.LAST_ERROR_MSG

FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p

WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2'

AND p.DESTINATION_DBLINK = s.DESTINATION

AND s.SCHEMA = p.SOURCE_QUEUE_OWNER

AND s.QNAME = p.SOURCE_QUEUE_NAME;

Troubleshooting Apply Problems

Is the Apply Process Enabled?

SELECT STATUS FROM DBA_APPLY WHERE APPLY_NAME = 'APPLY';

Checking the Trace Files and Alert Log for Problems

Messages about each capture process, propagation job, and apply process are recorded in trace files for the database in which the process or propagation job is running. A capture process runs on a source database, a propagation job runs on the database containing the source queue in the propagation, and an apply process runs on a destination database. These trace file messages can help you to identify and resolve problems in a Streams environment.

All trace files for background processes are written to the destination directory specified by the initialization parameter BACKGROUND_DUMP_DEST. The names of trace files are operating system specific, but each file usually includes the name of the process writing the file.

For example, on some operating systems, the trace file name for a process is sid_xxxxx_iiiii.trc, where:

sid is the system identifier for the database

xxxxx is the name of the process

iiiii is the operating system process number

Also, you can set the write_alert_log parameter to y for both the capture process and apply process. When this parameter is set to y, which is the default setting, the alert log for the database contains messages about why the capture process or apply process stopped.