Oracle Streams STRMMON Monitoring Utility [ID 290605.1]

rongshiyuan發表於2012-11-13

In this Document
Purpose
Scope and Application
Oracle Streams STRMMON Monitoring Utility
Installation
Usage
Output
Known problems
References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4 - Release: 9.2 to 10.2
Information in this document applies to any platform.
On release 11G and onwards STRMMON functionality has been replaced by the Streams Performance Advisor distributed within the Streams product and implemented through DBMS_STREAMS_ADVISOR_ADM PL/SQL package. Please refer to chapter "Monitoring the Oracle Streams Topology and Performance" on "Oracle Streams Concepts and Administration 11g Release 1" guide for further information on the Streams Performace Advisor

Purpose

***Checked for relevance on 10-Jan-2011***

The purpose of the note is to explain how to install, use and interpret the output of STRMMON utility.

Scope and Application

STRMMON is a monitoring tool focused on Oracle Streams. Using this tool,Database administrators get a quick overview of the Streams activity occurring within a database.

STRMMON can also be used to report Streams activity on two databases at a time within the same strmmon session.

In releases 9i and 10G Release 1, STRMMON is not a supported tool, as of Oracle 10g Release 2, STRMMON is distributed as in the demo directory of the database distribution code.

Also note that alternatively and in newer releases of RDA you can get using similar information that STRMMON retrieve by using Streams Monitoring Information module (STM). So you can get this information by running

./rda.sh -vCRP STM or
./rda.sh -vCRP OS DB DBA NET D2PC STC STM

Oracle Streams STRMMON Monitoring Utility

Installation

STRMMON is delivered as a zip file that includes an OCI program and make file for use with Oracle releases prior to 10g Release 2.

Click here to download the STRMMON zip file. Change directory to a new directory. If necessary transfer the tar file in BINARY mode. Extract the files from the zip file.

For releases prior to Oracle Database 10g Release 2

Create a directory for the STRMMON files. Extracted the files from the downloaded STRMMON.zip file. Before compiling and linking the strmmon program, make sure that the ORACLE_HOME and LD_LIBRARY_PATH environmental variables are set up appropriately. To compile and link, use the command:

make –f strmmon.mk strmmon

After the program has been compiled and linked, strmmon can be used for monitoring a Streams environment.

NOTE: STRMMON is an OCI program. If you have difficulty using the supplied make file, follow the recommended procedure for compiling and linking OCI programs for your platform. (demo_rdbms.mk in the $ORACLE_HOME/rdbms/demo directory).

The strmmon.mk file identified above is a copy of the demo_rdbms.mk file from the Oracle 9i Database Release 2 software for the Solaris 32-bit platform.

For Oracle Database 10g Release 2

STRMMON is distributed in the rdbms/demo directory on Release 10G release 2.

It is installed when installing "Oracle Database 10G Products" in the Companion CD.

To use the latest version of strmmon in Oracle Database 10g Release 2, archive the existing strmmon.c, strmmon.o, and strmmon files in the $ORACLE_HOME/rdbms/demo directory into a backup location. Then, extract the strmmon.c and the strmmon.html files from the STRMMON zip file into the rdbms/demo directory. Use the existing make file (demo_rdbms.mk) in the demo directory to generate the strmmon executable.

Usage

There are 7 command line input parameters for STRMMON: interval, count, user, passwd, dbname, sysdba and long.

The first 2 parameters (interval and count) control the sampling rate and the amount of output. The next 4 parameters specify the connect information to the particular Streams database.

Use multiple occurrences of these 4 parameters to monitor multiple databases within the same strmmon command.

Specifying the last parameter (long) displays more detailed information about each process.

When the command strmmon is issued without any parameters, a usage message is displayed:

% strmmon

Usage: strmmon -interval -count [-user ]
[-passwd ] [-dbname ] [-sysdba]

Parameters

Parameter Name	Value Units	Description
-interval	Seconds	The interval at which STRMMON will monitor the database. To specify that the sampling rate to be every 3 seconds: -interval 3 This is a required parameter for strmmon.
-count	Number	The number of iterations to monitor the Streams environment. To specify 5 iterations, use the following: -count 5 This is a required parameter for strmmon.
-user	Username	The schema name for logging into the database. Any schema name can be specified. If the SYS schema is specified, additional information is displayed. To specify the SYSTEM schema, use -user SYSTEM This parameter should not be specified if logging in as / as sysdba is desired. -user is an optional parameter for strmmon.
-passwd	password	The login password for the schema identified with the -user clause. To specify the password for the SYSTEM schema, use -passwd oracle This parameter should not be specified if logging in as / as sysdba is desired -passwd is an optional parameter for strmmon.
-dbname	Service name	The connection information or service name from tnsnames.ora for the specific database to be monitored. To specify the connect information for the monitored database, use -dbname ORCL.WORLD This is an optional parameter for strmmon.
-sysdba		This flag indicates that the login role is SYSDBA. This optional parameter is typically used with the SYS schema. When logging in as / as sysdba, the -user and -passwd parameters are not required
-long		This flag indicates that the more detailed report is desired. This is an optional parameter for STRMMON. By default, only the capture, apply and propagation rates are displayed.

STRMMON have to be used with the Oracle release used for compilation or linking although it can connect to a different release database by using -dbname parameter.

Output

The STRMMON output format comes in two formats: default and long.

The default format reports the rate of activity occuring for Streams processes.

The long format provides the detailed information that was available in previous releases of STRMMON. The reporting interval and number of iterations to display are configurable.

The strmmon output begins with a banner line identifying the program parameters and database. This information is followed with a brief description of the major components of the output display.

After this initial information about the program, Strmmon produces a single line of output representing the current status of Oracle Streams after the requested interval for each iteration. For example, if strmmon is invoked with '-interval 3 -count 5’, a line of output will be displayed every 3 seconds. After 5 lines have been displayed (15 seconds), the monitoring will end. .

Each line is composed of multiple blocks of information dependent on the streams processes configured within the database. These blocks are displayed by a keyword to identify the component followed by the statistics for that particular component. The separator between the components is the '|' symbol.

There are 7 components for Streams: LOG, NET, Cxxx, Qx, PRxx, PSxx and Axxx.

Except for the LOG and NET components, multiple occurrences of each component are possible dependent on the streams processes configured at database.

ARxx (Apply Reader) and ASxx (Apply Server) are subcomponents associated with each Axxx component.

An example of this identifying output is shown below:

% strmmon -interval 3 -count 5 -sysdba

STREAMS Monitor, v 2.5 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5
Logon= @ ORACLE 10.2.0.2.0
Streams Pool Size = 152M

LOG :
NET:
Cxxx:
MEM : %
PRxx:
Qx :
PSxx:
Axxx:
: flow control in effect
: potential bottleneck
AR: apply reader
AS(n): n number of apply server
:
xx->: database instance name

To connect to multiple databases, the following command can be issued. inst1 and inst2 are the network service names (tnsnames.ora) for each database.

An example of the identifying output for the long report is shown below

% strmmon -interval 1 -count 1 -user sys -passw change_on_install \
-dbname inst1 -user sys -passw change_on_install -dbname inst2 \
-sysdba -long

STREAMS Monitor, v 2.5 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5
Logon=sys@inst1 ORACLE 10.2.0.2.0
Streams Pool Size = 152M

LOG :
NET:
Cxxx:
MEM : %
PRxx: /
Qx : /
PSxx: /
Axxx:
: flow control in effect
: potential bottleneck
AR: apply reader
AS(n): n number of apply serve
:
xx->: database instance name

STREAMS Monitor, v 2.5 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5
Logon=sys@inst2 ORACLE 10.2.0.2.0
Streams Pool Size = 152M

LOG :
NET:
Cxxx:
MEM : %
PRxx: /
Qx : /
PSxx: /
Axxx:
: flow control in effect
: potential bottleneck
AR: apply reader
AS(n): n number of apply serve
:
xx->: database instance name

NOTE: The information about the Streams Pool Size is displayed only for database versions greater than or equal to 10gR1.

LOG

Information about the redo log activity is written in this block.

The first statistic following the LOG: keyword is the current SCN that has been written to the redo log. This number represents the current activity within the database. If this number does not increase, no activity is occurring on the database.

The second statistic is the last block number written in the redo log. Redo blocks are always 512 bytes, so this statistic can be used to calculate the amount of redo generated between intervals.

The output for the LOG component under the -long option is always the first entry after the timestamp and the database name on the display and appears as follows:

2005-08-13 18:05:23 || lnxmain-> | LOG 2952151 812574 |
2005-08-13 18:05:23 || lnxmain-> | LOG 2952151 812574 |
2005-08-13 18:05:27 || lnxmain-> | LOG 2952155 812576 |
2005-08-13 18:05:30 || lnxmain-> | LOG 2952156 812577 |
2005-08-13 18:05:33 || lnxmain-> | LOG 2952156 812577 |

In the above example, the current scn that written to the redo log is 2952151 and the last block number is 812574. Since the strmmon command was issued with –interval 3 –count 5, 5 lines of output are displayed with a 3 second interval between them. The activity on this database is very low, as seen by the fact that the current scn increases infrequently (note change between 18:05:23 and 18:05:27 and 18:05:30).

For the default output, the LOG component entry shows the rate at which redo information is generated:

2005-08-13 18:08:51 || lnxmain-> | LOG 1K |
2005-08-13 18:08:54 || lnxmain-> | LOG 341 |
2005-08-13 18:08:57 || lnxmain-> | LOG 170 |
2005-08-13 18:09:00 || lnxmain-> | LOG 0 |

The printed value is the average number of bytes of redo generated per

NET

For the default output, the average number of bytes per second generated over the network by clients and dblinks is reported. For the -long output, the current values for the statistics of client and dblink network activity are reported.

Cxxx

For each capture process configured in the database, a separate block will be displayed. Each block displays the number of lcrs captured per sec, number of lcrs enqueued per sec and the capture latency. If "-long" option is specified, then in each block, the Logminer read scn, the total number of messages captured from the redo log and the most recent scn captured from the redo log are shown. In addition, the number of messages that match the rules specified for the capture process including the most recent message scn enqueued are shown along with the capture latency.

One can also use the difference between successive capture "messages captured" statistics to determine the rate at which capture is mining the redo log. The enqueue scn of capture is an indicator of where the capture process will restart, if capture is stopped and restarted while the database is running. This statistic can also be used for comparison with the appropriate database Apply process high-water mark scn. If these statistics match, the capture and apply are caught up and the data is synchronized.

MEM%

If strmmon is run from the SYS schema connected as SYSDBA, and the database version is 10gR1 or higher, then this displays the percentage of Streams Pool memory currently in use and the size of the Streams Pool. These statistics are reported in both the default and –long output formats.

Qx

For each streams queue in the database, a separate block displays the queue identifier, the cumulative message rate and the spill rate. If "-long" option is specified, then for each streams queue in the database, a separate block will display the queue identifier as well as the number of outstanding messages in the buffered queue, the cumulative number of messages that have been in the queue and the number of messages spilled from memory to disk. In version 9iR2, the number of messages currently spilled is displayed. In 10gR1, the cumulative number of messages spilled is displayed. For 9iR2, the default and the "-long" option display the same data.

The queue identifier (QID) can be used to identify the name of the queue. Use the QID in queries against the DBA_QUEUES view to identify the particular queue in the database. In Oracle Database 9i Release 2, the number of outstanding messages in the buffered queue is only displayed if strmmon is run from the SYS schema as SYSDBA. In 9iR2, if the number of spilled messages becomes non-zero, consider stopping capture temporarily to slow down the flow of data. See Metalink Note 259609.1 for an example flow control script.

PSxx

For each propagation sender, the number of lcrs propagated per sec is displayed. If "-long" option is specified, the for each propagation, the total number of messages and the total number of bytes propagated to the destination site and the total time needed to propagate those messages is displayed.

PRxx

For each propagation receiver, the number of lcrs received per sec is displayed. If "-long" option is specified, the for each propagation, the total number of messages received and the total time to receive those messages is displayed

Axxx

For each apply process, the number of lcrs applied per sec, the number of transactions applied per second, and the apply latency are displayed. If the "-long" option is specified, then for each apply process in the database, the total messages dequeued and the most recent scn dequeued by the apply reader, the dequeue latency, the total number of transactions received, assigned and applied by the coordinator are displayed. The apply high water mark scn along with the apply high water mark latency is also shown. This statistic records the most recent scn from the source site that has been applied at the destination site.

AR

This subcomponent lists the percentage of idle events, flow control events, and the single most significant wait event for the apply reader process if the database version is 10gR1 or higher.
AS(n)

This subcomponent lists the percentage of idle events, flow control events, and the single most significant wait event for the apply server process(es) if the database version is 10gR1 or higher. When apply parallelism is enabled, the values reflect the sum of all associated apply servers in each category: idle, flow control, and wait event.

Flags

- Flow Control

This indicates that the capture process is blocked due to flow control.

- Potential Bottlenecks
This indicates that the capture or apply process is currently a bottleneck.
-
When displayed for a component, x%I indicates the percentage(x%) of idle(I) time for the component. x%F indicates the percentage(x%) of time waiting on flow control events(F). The event with the highest percent (x%) of time waited is indicated(xx)the percentage of time(x%) spent waiting on
- Instance
This indicates the name of the database instance for which the data follows.

Known problems
Compilation/Linking errors while running make command

Error: sh: cc: not found
Cause : C compiler can not be found in the path.
Solution: Include the path to the C compiler in the PATH environmental variable.

Error:
strmmon.c:1114: parse error before "ub8"
strmmon.c:1114: warning: no semicolon at end of struct or union
strmmon.c:1115: warning: data definition has no type or storage class
strmmon.c: In function `print_prop_stats':
Cause : strmmon make use of ub8 definition, sometimes the expansion for this definition has failed.
Solution: Include the definition of ub8 in the strmmon file, just before the definition of prop struct.
Definition of ub8 is as follows:
#define ub8 unsigned long long

Execution errors while running strmmon utility

Error: Strmmon dump a core or raise a Memory fault
Cause : Compilation parameters are not the best for this platform
Solution : Use -user and -passwd parameters with user SYSTEM

Error: Error while loading shared libraries
Cause: LD_LIBRARY_PATH not defined
Solution : Define LD_LIBRARY_PATH

Error: OCIEnvCreate() failed
Cause : strmmon has been compiled/linked with a release different to the one that is being used now.
Solution : Use the right ORACLE_HOME and use -dbname parameter to access a different database release

Still have questions ?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support Streams and Distributed Database Community

References
NOTE:259609.1 - Script. to Prevent Excessive Spill of Message From the Streams Buffer Queue To Disk
NOTE:273674.1 - Streams Configuration Report and Health Check Script