30.1 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_PCTmetric. You can manage this metric with the
SET_THRESHOLDprocedure in the
You can view alerts in Oracle Enterprise Manager Cloud Control, or you can query the following data dictionary views:
DBA_OUTSTANDING_ALERTSview records current stateful alerts. The
DBA_ALERT_HISTORYview 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_ALERT_HISTORYdata dictionary view shows alerts that have been cleared from the
DBA_OUTSTANDING_ALERTSview. For example, if the memory usage in the Oracle Streams pool falls below the specified threshold, then the alert recorded in the
DBA_OUTSTANDING_ALERTSview is cleared and moved to the
For example, to list the current stateful Oracle Streams alerts, run the following query on the
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
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
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.
Oracle Streams alerts are informational only. They do not need to be managed. If you monitor your Oracle Streams environment regularly and address problems as they arise, then you might not need to monitor Oracle Streams alerts.
Oracle Enterprise Manager Cloud Control online help for more information about Oracle Streams alerts
Oracle Database 2 Day + Performance Tuning Guide for information about managing alerts and metric thresholds
Oracle Database Administrator's Guide for information about alerts and for information about subscribing to the
ALERT_QUEqueue to receive notifications when new alerts are generated
Oracle Database PL/SQL Packages and Types Reference for more information about the
Oracle Streams Replication Administrator's Guide for information about setting initialization parameters that are relevant to Oracle Streams
Oracle Streams Replication Administrator's Guide for information about configuring the Oracle Streams pool
30.2 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:
- Using a Web browser, go to the My Oracle Support Web site:
- Log in to My Oracle Support.
If you are not a My Oracle Support registered user, then click Register Here and register.
- Find the database bulletin with the following title:
Streams Configuration Report and Health Check Script
The doc ID for this bulletin is 273674.1.
- Follow the instructions to download the script for your release, run the script, and analyze the results.
30.3 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
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
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.
Oracle Streams Replication Administrator's Guide for more information about splitting and merging a destination
30.4 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
sidis the system identifier for the database
xxxxis the name of the process
iiiiiis 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
Use the following checklist to check the trace files related to Oracle Streams:
Oracle Database Administrator's Guide for more information about trace files and the alert log, and for more information about their names and locations
Oracle Database PL/SQL Packages and Types Reference for more information about setting the
trace_levelcapture process parameter and the
trace_levelapply process parameter
Your operating system specific Oracle documentation for more information about the names and locations of trace files
30.4.1 Does a Capture Process Trace File Contain Messages About Capture Problems?
A capture process is an Oracle background process named
nn can include letters and numbers. For example, on some operating systems, if the system identifier for a database running a capture process is
hqdb and the capture process number is
01, then the trace file for the capture process starts with
"Displaying Change Capture Information About Each Capture Process" for a query that displays the capture process number of a capture process
30.4.2 Do the Trace Files Related to Propagation Jobs Contain Messages About Problems?
Each propagation uses a propagation job that depends on one or more slave processes named
nnn is the slave process number. For example, on some operating systems, if a slave process is
001, then the trace file for the slave process includes
j001 in its name. You can check the process name by querying the
PROCESS_NAME column in the
DBA_QUEUE_SCHEDULES data dictionary view.
"Is the Propagation Enabled?" for a query that displays the job slave used by a propagation job
30.4.3 Does an Apply Process Trace File Contain Messages About Apply Problems?
An apply process is an Oracle background process named
nn can include letters and numbers. For example, on some operating systems, if the system identifier for a database running an apply process is
hqdb and the apply process number is
01, then the trace file for the apply process starts with
An apply process also uses other processes. Information about an apply process might be recorded in the trace file for one or more of these processes. The process name of the reader server and apply servers is
nn can include letters and numbers. So, on some operating systems, if the system identifier for a database running an apply process is
hqdb and the process number is
01, then the trace file that contains information about a process used by an apply process starts with
"Displaying General Information About Each Coordinator Process" for a query that displays the apply process number of an apply process
"Displaying Information About the Reader Server for Each Apply Process" for a query that displays the process used by the reader server of an apply process
"Displaying Information About the Apply Servers for Each Apply Process" for a query that displays the processes used by the apply servers of an apply process