30 Identifying Problems in an Oracle Streams Environment

Viewing Oracle Streams Alerts

An alert is a warning about a potential problem or an indication that a critical threshold has been crossed. There are two types of alerts:

• Stateless: Alerts that indicate single events that are not necessarily tied to the system state. For example, an alert that indicates that a capture aborted with a specific error is a stateless alert.

• Stateful: Alerts that are associated with a specific system state. Stateful alerts are usually based on a numeric value, with thresholds defined at warning and critical levels. For example, an alert on the current Oracle Streams pool memory usage percentage, with the warning level at 85% and the critical level at 95%, is a stateful alert.

An Oracle Database 11g Release 1 or later database generates a stateless Oracle Streams alert under the following conditions:

• A capture process aborts.

• A propagation aborts after 16 consecutive errors.

• An apply process aborts.

• An apply process with an empty error queue encounters an apply error.

An Oracle Database 11g Release 1 or later database generates a stateful Oracle Streams alert under the following condition:

• Oracle Streams pool memory usage exceeds the percentage specified by the STREAMS_POOL_USED_PCT metric. You can manage this metric with the SET_THRESHOLD procedure in the DBMS_SERVER_ALERT package.

You can view alerts in Oracle Enterprise Manager Cloud Control, or you can query the following data dictionary views:

• The DBA_OUTSTANDING_ALERTS view records current stateful alerts. The DBA_ALERT_HISTORY view records stateless alerts and stateful alerts that have been cleared. For example, if the memory usage in the Oracle Streams pool exceeds the specified threshold, then a stateful alert is recorded in the DBA_OUTSTANDING_ALERTS view.

• The DBA_ALERT_HISTORY data dictionary view shows alerts that have been cleared from the DBA_OUTSTANDING_ALERTS view. For example, if the memory usage in the Oracle Streams pool falls below the specified threshold, then the alert recorded in the DBA_OUTSTANDING_ALERTS view is cleared and moved to the DBA_ALERT_HISTORY view.

For example, to list the current stateful Oracle Streams alerts, run the following query on the DBA_OUTSTANDING_ALERTS view:

COLUMN REASON HEADING 'Reason for Alert' FORMAT A35
COLUMN SUGGESTED_ACTION HEADING 'Suggested Response' FORMAT A35
 
SELECT REASON, SUGGESTED_ACTION 
   FROM DBA_OUTSTANDING_ALERTS
   WHERE MODULE_ID LIKE '%STREAMS%';

To list the Oracle Streams stateless alerts and cleared Oracle Streams stateful alerts, run the following query on the DBA_ALERT_HISTORY view:

COLUMN REASON HEADING 'Reason for Alert' FORMAT A35
COLUMN SUGGESTED_ACTION HEADING 'Suggested Response' FORMAT A35
 
SELECT REASON, SUGGESTED_ACTION 
   FROM DBA_ALERT_HISTORY 
   WHERE MODULE_ID LIKE '%STREAMS%';

The following is example output from a query on the DBA_ALERT_HISTORY view:

Reason for Alert                    Suggested Response
----------------------------------- -----------------------------------
STREAMS apply process "APPLY_EMP_DE Obtain the exact error message in d
P" aborted with ORA-26714           ba_apply, take the appropriate acti
                                    on for this error, and restart the
                                    apply process using dbms_apply_adm.
                                    start_apply.  If the error is an OR
                                    A-26714, consider setting the 'DISA
                                    BLE_ON_ERROR' apply parameter to 'N
                                    ' to avoid aborting on future user
                                    errors.
 
STREAMS error queue for apply proce Look at the contents of the error q
ss "APPLY_EMP_DEP" contains new tra ueue as well as dba_apply_error to
nsaction with ORA-26786             determine the cause of the error.
                                    Once the errors are resolved, reexe
                                    cute them using dbms_apply_adm.exec
                                    ute_error or dbms_apply_adm.execute
                                    _all_errors.

Using the Streams Configuration Report and Health Check Script

The Streams Configuration Report and Health Check Script provides important information about the Oracle Streams components in an individual Oracle database. The report is useful to confirm that the prerequisites for Oracle Streams are met and to identify the database objects of interest for Oracle Streams. The report also analyzes the rules in the database to identify common problems with Oracle Streams rules.

The Streams Configuration Report and Health Check Script is available on the My Oracle Support (formerly OracleMetaLink) Web site. To run the script, complete the following steps:

1. Using a Web browser, go to the My Oracle Support Web site:

   http://support.oracle.com/
   

2. Log in to My Oracle Support.

   Note:

   If you are not a My Oracle Support registered user, then click Register Here and register.

3. Find the database bulletin with the following title:

   Streams Configuration Report and Health Check Script
   

   The doc ID for this bulletin is 273674.1.

4. Follow the instructions to download the script for your release, run the script, and analyze the results.

Handling Performance Problems Because of an Unavailable Destination

When a database in Oracle Streams replication environment has one capture process that captures changes for multiple destination databases, performance problems can result when one of the destination databases becomes unavailable. If this happens, and the changes for the unavailable destination cannot be propagated, then these changes can build up the capture process's queue and eventually spill to hard disk. Spilling messages to hard disk at the capture database can degrade the performance of the Oracle Streams replication environment. You can query the V$BUFFERED_QUEUES view to check the number of messages in a queue and how many have spilled to hard disk. Also, you can query the DBA_PROPAGATION and V$PROPAGATION_SENDER views to show the propagations in a database and the status of each propagation.

If you encounter this situation, then you can use the SPLIT_STREAMS and MERGE_STREAMS_JOB procedures in the DBMS_STREAMS_ADM package to address the problem. The SPLIT_STREAMS procedure splits the problem stream off from the other streams flowing from the capture process. By splitting the stream off, you can avoid performance problems while the destination is unavailable. After the problem at the destination is resolved, the MERGE_STREAMS_JOB procedure merges the stream back with the other streams flowing from the capture process.

Checking the Trace Files and Alert Log for Problems

Messages about each capture process, propagation, and apply process are recorded in trace files for the database in which the process or propagation job is running. A local capture process runs on a source database, a downstream capture process runs on a downstream 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 an Oracle Streams environment.

All trace files for background processes are written to the Automatic Diagnostic Repository. 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_xxxx_iiiii.trc, where:

• sid is the system identifier for the database

• xxxx 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 a capture process and an 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.

You can control the information in the trace files by setting the trace_level capture process or apply process parameter using the SET_PARAMETER procedure in the DBMS_CAPTURE_ADM and DBMS_APPLY_ADM packages.

Use the following checklist to check the trace files related to Oracle Streams:

• Does a Capture Process Trace File Contain Messages About Capture Problems?

• Do the Trace Files Related to Propagation Jobs Contain Messages About Problems?

• Does an Apply Process Trace File Contain Messages About Apply Problems?