When you use Oracle Streams, replication of a DML or DDL change typically includes three
steps.
1. A Capture process collects the DML and/or DDL changes and places them into a ueue.
!. A "ropagation process #hich distri$utes change to other ueues in the configuration
%. An Apply process, #hich consumes the change.
&herefore, it is good practice to monitor these three components'
1. Monitoring the Capture "rocess using ()S&*+AMS,CA"&-*+.
An e.ample S/L Statement to monitor the streams capture process is found in the Oracle
Streams Concept and Administration 11g *elease ! 011.!1, Chapter !2, Monitoring Oracle
Streams 3mplicit Capture, in the section titled 4Displaying State Change and Message
Creation &ime for +ach Capture "rocess5.
a. &he S&A&+ of capturing. A full list of Capture "rocess States are located at the lin6
a$o7e. Alerts are usually raised for the S&A&+s of 383&3AL3938:, S-S"+8D+D,
A;O*&38:, and S<-&38: DOW8.
$. C*+A&+,M+SSA:+. C*+A&+ M+SSA:+ indicates the last time a message #as
created. 3f the system e.pects messages to $e created at regular inter7als, a high
C*+A&+,M+SSA:+ time could indicate a capture pro$lem. =or e.ample, if #e
e.pect the system to process transactions e7ery minute, there could $e a pro$lem if no
transactions #ere Captured for > minutes.
c. S&A&+,C<A8:+D is also useful to monitor S&A&+,C<A8:+D for high 7alues.
=or e.ample #e could say that any S&A&+,C<A8:+D time e.cept for 4WA3&38:
=O* &*A8SA&3O85 #hich e.ceeds > minutes could indicate a pro$lem.
!. Monitoring the "ropagation process using D;A,/-+-+,SC<+D-L+S and
D;A,"*O"A:A&3O8.
An e.ample S/L statement to monitor the streams "ropagation process is found in the
Oracle Streams Concept and Administration 11g *elease ! 011.!1, Chapter !>,
Monitoring Oracle Streams /ueues and "ropagations in the section titled 4Displaying
3nformation A$out the Schedules for "ropagation ?o$s5. *e7ie#ing that uery #e can
see that'
a. Monitoring SC<+D-L+,D3SA;L+D #ill pro7ide notification that the
"ropagation is #or6ing properly.
$. Monitoring LA&+8C@ #ill pro7ide notification that system messages are $eing
processed effecti7ely
c. Monitoring =A3L-*+S #ill pro7ide notification that the system is #or6ing
properly.
%. Monitoring the Apply process using D;A,A""L@,+**O*.
D;A,A""L@,+**O* displays all of the Apply errors. 3n a #ell $eha7ed systems #e
#ould e.pect no errors D;A,A""L@,+**O*. A""L@ +**O*S should $e monitored
using the uery $elo# alerting appropriate personnel if a nonABero 7alue is encountered
S+L+C& CO-8&0C1 =*OM D;A,A""L@,+**O*
2. 3t is possi$le that an Apply process can spill messages from memory to hard dis6 #hen
the num$er of messages in a transaction e.ceeds a specified num$er set $y the parameter
txn_lcr_spill_threshold. Spilling transactions can represent a misconfigured
system, a performance pro$lem, or something out of the ordinary is occurring. &herefore
#e recommend this $e monitored and personnel $e alerted.
Customers should monitor the Apply Spill process and alert the appropriate personnel.
&he uery to monitor the Apply Spill process can $e found in the Oracle Streams
Concept and Administration 11g *elease ! 011.!1, Chapter !D, Monitoring Oracle
Streams Apply "rocesses in the section titled 4Monitoring &ransactions and Messages
Spilled $y +ach Apply "rocess5.
>. 3t is important to monitor the amount of time $et#een #hen a message #as created at the
source data$ase and #hen the message #as applied $y an apply process. &his is 6no#n
as Latency. Monitoring Latency allo#s us to set and meet Ser7ice Le7el Agreements asA
#ellAas determine if a system is functioning as e.pected.
&he uery to monitor the Latency $et#een #hen a message #as created and #hen the
message #as applied can $e found in the Oracle Streams Concept and Administration
11g *elease ! 011.!1, Chapter !D, Monitoring Oracle Streams Apply "rocesses in the
section titled 4Determining the Capture to Apply Latency for a Message for +ach Apply
"rocess5. &he 4Latency in Seconds5 column can $e used to monitor the system.
D. An Oracle Data$ase 11g *elease 1 or later generates Streams alerts under the follo#ing
conditions'
a. A capture process a$orts.
$. A propagation a$orts after 1D consecuti7e errors.
c. An apply process a$orts.
d. An apply process #ith an empty error ueue encounters an apply error.
&hese conditions, should $e monitored using D;A,O-&S&A8D38:,AL+*&S and
D;A,AL+*& <3S&O*@ using the S/L statements found in the Oracle Streams Concept
and Administration 11g *elease ! 011.!1, Chapter !E, &rou$leshooting an Oracle Streams
+n7ironment under the section 4(ie#ing Oracle Streams Alerts5. 3f a S+L+C&
CO-8&0C1 7ersion of this uery returns a nonABero 7alue, the appropriate personnel
should $e alerted.
