Master Note for Troubleshooting Advanced Queuing and Oracle Streams Propagation Issues (Doc ID 233099.1)
Copyright (c) 2010, Oracle Corporation. All Rights Reserved.
In this Document Purpose Last Review Date Instructions for
the Reader Troubleshooting Details 1. Scope and Application 2. Definitions and Classifications 3. How to Use This Guide 4. Basic AQ Propagation Troubleshooting 5. Additional Troubleshooting Steps for AQ Propagation of User-Enqueued and Dequeued Messages 6. Additional Troubleshooting Steps for Propagation in an Oracle Streams Environment 7. Performance Issues References
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.2 - Release: 8.1.7 to 11.2Information in this document applies to any platform.
Purpose
This document presents a step-by-step methodology for troubleshooting and resolving problems with Advanced Queuing Propagation in both Streams and basic Advanced Queuing environments. It also serves as a master reference for other more specific notes on Oracle Streams Propagation and Advanced Queuing Propagation issues.
Last Review Date
December 20, 2010
Instructions for
the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in
the document to assist in troubleshooting.
Troubleshooting Details
1. Scope and Application
This note is intended for Database Administrators of Oracle databases where issues are being encountered with propagating messages between advanced queues, whether
the queues are used for user-created messaging systems or for Oracle Streams. It contains troubleshooting steps and links to notes for further problem resolution.It can also be used a template to document a problem when it is necessary to engage Oracle Support Services. Knowing what is NOT happening can frequently speed up
the resolution process by focusing solely on
the pertinent problem area. This guide is divided into five parts:
Section 2: Definitions and Classifications (discusses
the different types and features of propagations possible - helpful for understanding
the rest of
the guide)
Section 3: How to Use this Guide (to be used as a start part for determining
the scope of
the problem and what sections to consult)
Section 4. Basic AQ propagation troubleshooting (applies to both AQ propagation of user enqueued and dequeued messages as well as Oracle Streams propagations)
Section 5. Additional troubleshooting steps for AQ propagation of user enqueued and dequeued messages
Section 6. Additional troubleshooting steps for Oracle Streams propagation
Section 7. Performance issues
2. Definitions and Classifications
Given
the potential scope of issues that can be encountered with AQ propagation,
the first recommended step is to do some basic diagnosis to determine
the type of problem that is being encountered.
2.1. What Type of Propagation is Being Used?
2.1.1. Buffered Messaging
For an advanced queue, messages can be maintained on disk (persistent messaging) or in memory (buffered messaging). To determine if a queue is buffered or not, reference
the GV_$BUFFERED_QUEUES view. If
the queue does not appear in this view, it is persistent.
2.1.2. Propagation mode - queue-to-dblink vs queue-to-queue
As of 10.2, an AQ propagation can also be defined as queue-to-dblink, or queue-to-queue:
queue-to-dblink:
The propagation delivers messages or events from
the source queue to all subscribing queues at
the destination database identified by
the dblink. A single propagation schedule is used to propagate messages to all subscribing queues. Hence any changes made to this schedule will affect message delivery to all
the subscribing queues. This mode does not support multiple propagations from
the same source queue to
the same target database.
queue-to-queue: Added in 10.2, this propagation mode delivers messages or events from
the source queue to a specific destination queue identified on
the database link. This allows
the user to have fine-grained control on
the propagation schedule for message delivery. This new propagation mode also supports transparent failover when propagating to a destination Oracle RAC system. With queue-to-queue propagation, you are no longer required to re-point a database link if
the owner instance of
the queue fails on Oracle RAC. This mode supports multiple propagations to
the same target database if
the target queues are different.
The default is queue-to-dblink. To verify if queue-to-queue propagation is being used, in non-Streams environments query DBA_QUEUE_SCHEDULES.DESTINATION - if a remote queue is listed along with
the remote database link, then queue-to-queue propagation is being used. For Streams environments,
the DBA_PROPAGATION.QUEUE_TO_QUEUE column can be checked.See
the following note for a method to switch between
the two modes:Document 827473.1 How to alter propagation from queue-to-queue to queue-to-dblink
2.1.3. Combined Capture and Apply (CCA) for Streams
In 11g Oracle Streams environments, an optimization called Combined Capture and Apply (CCA) is implemented by default when possible. Although a propagation is configured in this case, Streams does not use it; instead it passes information directly from capture to an apply receiver. To see if CCA is in use:
COLUMN CAPTURE_NAME HEADING 'Capture Name' FORMAT A30COLUMN OPTIMIZATION HEADING 'CCA Mode?' FORMAT A10SELECT CAPTURE_NAME, DECODE(OPTIMIZATION,0, 'No','Yes') OPTIMIZATIONFROM V$STREAMS_CAPTURE;
Also, see
the following note:Document 463820.1 Streams Combined Capture and Apply in 11g
2.2. Queue Table Compatibility
There are three types of queue table compatibility. In more recent databases, queue tables may be present in all three modes of compatibility:
8.0 - earliest version, deprecated in 10.2 onwards
8.1 - support added for RAC, asynchronous notification, secure queues, queue level access control, rule-based subscribers, separate storage of history information
10.0 - if
the database is in 10.1-compatible mode, then
the default value for queue table compatibility is 10.0
2.3. Single vs Multiple Consumer Queue Tables
If more than one recipient can dequeue a message from a queue, then its queue table is multiple consumer. You can propagate messages from a multiple-consumer queue to a single-consumer queue. Propagation from a single-consumer queue to a multiple-consumer queue is not possible.
3. How to Use This Guide
3.1. Are Messages Being Propagated at All, or is
the Propagation Just Slow?
Run
the following query on
the source database for
the propagation (assuming that it is running):
select TOTAL_NUMBER from DBA_QUEUE_SCHEDULES where QNAME='<source_queue_name>';
If TOTAL_NUMBER is increasing, then propagation is most likely functioning, although it may be slow. For performance issues, see Section 7.
3.2. Propagation Between Persistent User-Created Queues
See Sections 4 and 5 (and optionally Section 6 if performance is an issue).
3.3. Propagation Between Buffered User-Created Queues
See Sections 4, 5, and 6 (and optionally Section 7 if performance is an issue).
3.4. Propagation between Oracle Streams Queues (without Combined Capture and Apply (CCA) Optimization)
See Sections 4 and 6 (and optionally Section 7 if performance is an issue).
3.5. Propagation between Oracle Streams Queues (with Combined Capture and Apply (CCA) Optimization)
Although an AQ propagation is not used directly in this case, some characteristics of
the message transfer are inferred from
the propagation parameters used. Some parts of Sections 4 and 6 still apply.
3.6. Messaging Gateway Propagations
This note does not apply to Messaging Gateway propagations.
4. Basic AQ Propagation Troubleshooting
4.1. Double-check Your Code
Make sure that you are consistent in your usage of
the database link(s) names, queue names, etc. It may be useful to plot a diagram of which queues are connected via which database links to make sure that
the logical structure is correct.
4.2. Verify that Job Queue Processes are Running
4.2.1. Versions 10.2 and Lower - DBA_JOBS Package
For versions 10.2 and lower, a scheduled propagation is managed by DBMS_JOB package.
The propagation is performed by job queue process background processes. Therefore we need to verify that there are sufficient processes available for
the propagation process. We should have at least 4 job queue processes running and preferably more depending on
the number of other jobs running in
the database. It should be noted that for AQ specific work, AQ will only ever use half of
the job queue processes available.An issue caused by an inadequate job queue processes parameter setting is described in
the following note:Document 298015.1 Kwqjswproc:Excep After Loop: Assigning To Self
4.2.1.1. Job Queue Processes in Initalization Parameter File
The parameter JOB_QUEUE_PROCESSES in
the init.ora/spfile should be > 0.
The value can be changed dynamically via
connect / as sysdbaalter system set JOB_QUEUE_PROCESSES=10;
4.2.1.2. Job Queue Processes in Memory
The following command will show how many job queue processes are currentlyin use by this instance (this may be different than what is in
the init.ora/spfile):
connect / as sysdbashow parameter job;
4.2.1.3. OS PIDs Corresponding to Job Queue Processes
Identify
the operating system process ids (spids) of job queue processes involved in propagation via
select p.SPID, p.PROGRAM from V$PROCESS p, DBA_JOBS_RUNNING jr, V$SESSION s, DBA_JOBS j where s.SID=jr.SID and s.PADDR=p.ADDR and jr.JOB=j.JOBand j.WHAT like '%sys.dbms_aqadm.aq$_propaq(job)%';
and these SPIDs can be used to check at
the operating system level that they exist.In 8i a job queue process will have a name similar to: ora_snp1_<instance_name>.In 9i onwards you will see a coordinator process: ora_cjq0_ and multiple slave processes: ora_jnnn_<instance_name>, where nnn is an integer between 1 and 999.
4.2.2. Version 11.1 and
Above - Oracle Scheduler
In version 11.1 and
above, Oracle Scheduler is used to perform AQ and Streams propagations. Oracle Scheduler automatically tunes
the number of slave processes for these jobs based on
the load on
the computer system, and
the JOB_QUEUE_PROCESSES initialization parameter is only used to specify
the maximum number of slave processes. Therefore,
the JOB_QUEUE_PROCESSES initialization parameter does not need to be set (it defaults to a very high number), unless you want to limit
the number of slaves that can be created. If JOB_QUEUE_PROCESSES = 0, no propagation jobs will run.See
the following note for a discussion of Oracle Streams 11g and Oracle Scheduler:Document 1083608.1 11g Streams and Oracle Scheduler
4.2.2.1. Job Queue Processes in Initalization Parameter File
The parameter JOB_QUEUE_PROCESSES in
the init.ora/spfile should be > 0, and preferably be left at its default value.
The value can be changed dynamically via
connect / as sysdbaalter system set JOB_QUEUE_PROCESSES=10;
To set
the JOB_QUEUE_PROCESSES parameter to its default value, run:
connect / as sysdbaalter system reset JOB_QUEUE_PROCESSES;
and then bounce
the instance.
4.2.2.2. Job Queue Processes in Memory
The following command will show how many job queue processes are currently in use by this instance (this may be different than what is in
the init.ora/spfile):
connect / as sysdbashow parameter job;
4.2.2.3. OS PIDs Corresponding to Job Queue Processes
Identify
the operating system process ids (SPIDs) of job queue processes involved in propagation via
col PROGRAM for a30select p.SPID, p.PROGRAM, j.JOB_namefrom v$PROCESS p, DBA_SCHEDULER_RUNNING_JOBS jr, V$SESSION s, DBA_SCHEDULER_JOBS j where s.SID=jr.SESSION_ID and s.PADDR=p.ADDRand jr.JOB_name=j.JOB_NAME and j.JOB_NAME like '%AQ_JOB$_%';
and these SPIDs can be used to check at
the operating system level that they exist.You will see a coordinator process: ora_cjq0_ and multiple slave processes: ora_jnnn_<instance_name>, where nnn is an integer between 1 and 999.
4.3. Check
the Alert Log and Any Associated Trace Files
The first place to check for propagation failures is
the alert logs at all sites (local and if relevant all remote sites). When a job queue process attempts to execute a schedule and fails it will always write an error stack to
the alert log. This error stack will also be written in a job queue process trace file, which will be written to
the BACKGROUND_DUMP_DEST location for 10.2 and below, and in
the DIAGNOSTIC_DEST location for 11g.
The fact that errors are written to
the alert log demonstrates that
the schedule is executing. This means that
the problem could be with
the set up of
the schedule. In this example
the ORA-02068 demonstrates that
the failure was at
the remote site. Further investigation revealed that
the remote database was not open, hence
the ORA-03114 error. Starting
the database resolved
the problem.
Thu Feb 14 10:40:05 2002 Propagation Schedule for (AQADM.MULTIPLEQ, SHANE816.WORLD) encountered following error:ORA-04052: error occurred when looking up Remote object
[email protected]: error occurred at recursive SQL level 4ORA-02068: following severe error from SHANE816ORA-03114: not connected to ORACLEORA-06512: at "SYS.DBMS_AQADM_SYS", line 4770ORA-06512: at "SYS.DBMS_AQADM", line 548ORA-06512: at line 1
Other potential errors that may be written to
the alert log can be found in
the following notes:Document 827184.1 AQ Propagation with CLOB data types Fails with ORA-22990 (11.1)Document 846297.1 AQ Propagation Fails : ORA-00600[kope2upic2954] or Ora-00600[Kghsstream_copyn] (10.2, 11.1)Document 731292.1 ORA-25215 Reported on Local Propagation When Using Transformation with ANYDATA queue tables (10.2, 11.1, 11.2)Document 365093.1 ORA-07445 [kwqppay2aqe()+7360] Reported on Propagation of a Transformed Message (10.1, 10.2)Document 219416.1 Advanced Queuing Propagation Fails with ORA-22922 (9.0)Document 1203544.1 AQ Propagation Aborted with ORA-600 [ociksin: invalid status] on SYS.DBMS_AQADM_SYS.AQ$_PROPAGATION_PROCEDURE After Upgrade (11.1, 11.2)Document 1087324.1 ORA-01405 ORA-01422 reported by Advanced Queuing Propagation schedules after RAC reconfiguration (10.2)Document 1079577.1 Advanced Queuing Propagation Fails With "ORA-22370 incorrect usage of method" (9.2, 10.2, 11.1, 11.2)Document 332792.1 ORA-04061 error relating to SYS.DBMS_PRVTAQIP reported when setting up Statspack (8.1, 9.0, 9.2, 10.1)Document 353325.1 ORA-24056: Internal inconsistency for QUEUE <queue_name> and destination <dblink> (8.1, 9.0, 9.2, 10.1, 10.2, 11.1, 11.2)Document 787367.1 ORA-22275 reported on Propagating Messages with LOB component when propagating between 10.1 and 10.2 (10.1, 10.2)Document 566622.1 ORA-22275 when propagating >4K AQ$_JMS_TEXT_MESSAGEs from 9.2.0.8 to 10.2.0.1 (9.2, 10.1)Document 731539.1 ORA-29268: HTTP client error 401 Unauthorized Error when
the AQ Servlet attempts to Propagate a message via HTTP (9.0, 9.2, 10.1, 10.2, 11.1)Document 253131.1 Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555) (9.2)Document 118884.1 How to unschedule a propagation schedule stuck in pending stateDocument 222992.1 DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE Returns ORA-24082Document 282987.1 Propagated Messages marked UNDELIVERABLE after Drop and Recreate Of Remote QueueDocument 1204080.1 AQ Propagation Failing With ORA-25329 After Upgraded From 8i or 9i to 10g or 11g.Document 1233675.1 AQ Propagation stops after upgrade to 11.2.0.1 ORA-30757
4.3.1. Errors Related to Incorrect Network Configuration
The most common propagation errors result from an incorrect network configuration.
The list below contains common errors caused by tnsnames.ora file or database links being configured incorrectly:
- ORA-12154: TNS:could not resolve service name- ORA-12505: TNS:listener does not currently know of SID given in connect descriptor- ORA-12514: TNS:listener could not resolve SERVICE_NAME - ORA-12541: TNS-12541 TNS:no listener
4.4. Check
the Database Links Exist and are Functioning Correctly
For schedules to remote databases confirm
the database link exists via.
SQL> col DBLINK for a45SQL> select QNAME, NVL(REGEXP_SUBSTR(DESTINATION, '[^@]+', 1, 2), DESTINATION) dblink2 from DBA_QUEUE_SCHEDULES3 where MESSAGE_DELIVERY_MODE = 'PERSISTENT';QNAME DBLINK------------------------------ ---------------------------------------------MY_QUEUE ORCL102B.WORLD
Connect as
the owner of
the link and select across it to verify it works and connects to
the database we expect. i.e.
select * from ALL_QUEUES@ ORCL102B.WORLD;
You need to ensure that
the userid that scheduled
the propagation (using DBMS_AQADM.SCHEDULE_PROPAGATION or DBMS_PROPAGATION_ADM.CREATE_PROPAGATION if using Streams) has access to
the database link for
the destination.
4.5. Has Propagation Been Correctly Scheduled?
Check that
the propagation schedule has been created and that a job queue process has been assigned. Look for
the entry in DBA_QUEUE_SCHEDULES and SYS.AQ$_SCHEDULES for your schedule. For 10g and below, check that it has a JOBNO entry in SYS.AQ$_SCHEDULES, and that there is an entry in DBA_JOBS with that JOBNO. For 11g and
above, check that
the schedule has a JOB_NAME entry in SYS.AQ$_SCHEDULES, and that there is an entry in DBA_SCHEDULER_JOBS with that JOB_NAME. Check
the destination is as intended and spelled correctly.
SQL> select SCHEMA, QNAME, DESTINATION, SCHEDULE_DISABLED, PROCESS_NAME from DBA_QUEUE_SCHEDULES;SCHEMA QNAME DESTINATION S PROCESS------- ---------- ------------------ - -----------AQADM MULTIPLEQ AQ$_LOCAL N J000
AQ$_LOCAL in
the destination column shows that
the queue to which we are propagating to is in
the same database as
the source queue. If
the propagation was to a remote (different) database, a database link will be in
the DESTINATION column.
The entry in
the SCHEDULE_DISABLED column, N, means that
the schedule is NOT disabled. If Y (yes) appears in this column, propagation is disabled and
the schedule will not be executed. If not using Oracle Streams, propagation should resume once you have enabled
the schedule by invoking DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE (for 10.2 Oracle Streams and
above,
the DBMS_PROPAGATION_ADM.START_PROPAGATION procedure should be used).
The PROCESS_NAME is
the name of
the job queue process currently allocated to execute
the schedule. This process is allocated dynamically at execution time. If
the PROCESS_NAME column is null (empty)
the schedule is not currently executing. You may need to execute this statement a number of times to verify if a process is being allocated. If a process is at some time allocated to
the schedule, it is attempting to execute.
SQL> select SCHEMA, QNAME, LAST_RUN_DATE, NEXT_RUN_DATE from DBA_QUEUE_SCHEDULES;SCHEMA QNAME LAST_RUN_DATE NEXT_RUN_DATE------ ----- ----------------------- ----------------------- AQADM MULTIPLEQ 13-FEB-2002 13:18:57 13-FEB-2002 13:20:30
In 11g, these dates are expressed in TIMESTAMP WITH TIME ZONE datatypes. If
the NEXT_RUN_DATE and NEXT_RUN_TIME columns are null when this statement is executed,
the scheduled propagation is currently in progress. If they never change it would suggest that
the schedule itself is never executing. If
the next scheduled execution is too far away, change
the NEXT_TIME parameter of
the schedule so that schedules are executed more frequently (assuming that
the window is not set to be infinite). Parameters of a schedule can be changed using
the DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE call. In 10g and below, scheduling propagation posts a job in
the DBA_JOBS view.
The columns are more or less
the same as DBA_QUEUE_SCHEDULES so you just need to recognize
the job and verify that it exists.
SQL> select JOB, WHAT from DBA_JOBS where WHAT like '%sys.dbms_aqadm.aq$_propaq(job)%';JOB WHAT---- ----------------- 720 next_date := sys.dbms_aqadm.aq$_propaq(job);
For 11g, scheduling propagation posts a job in DBA_SCHEDULER_JOBS instead:
SQL> select JOB_NAME from DBA_SCHEDULER_JOBS where JOB_NAME like 'AQ_JOB$_%';JOB_NAME------------------------------AQ_JOB$_41
If no job exists, check DBA_QUEUE_SCHEDULES to make sure that
the schedule has not been disabled. For 10g and below,
the job number is dynamic for AQ propagation schedules.
The procedure that is executed to expedite a propagation schedule runs, removes itself from DBA_JOBS, and then reposts a new job for
the next scheduled propagation.
The job number should therefore always increment unless
the schedule has been set up to run indefinitely.
4.6. Is
the Schedule Executing but Failing to Complete?
Run
the following query:
SQL> select FAILURES, LAST_ERROR_MSG from DBA_QUEUE_SCHEDULES;FAILURES LAST_ERROR_MSG------------ -----------------------1 ORA-25207: enqueue failed, queue AQADM.INQ is disabled from enqueueingORA-02063: preceding line from SHANE816
The failures column shows how many times we have attempted to execute
the schedule and failed. Oracle will attempt to execute
the schedule 16 times after which it will be removed from
the DBA_JOBS or DBA_SCHEDULER_JOBS view and
the schedule will become disabled.
The column DBA_QUEUE_SCHEDULES.SCHEDULE_DISABLED will show 'Y'. For 11g and
above,
the DBA_SCHEDULER_JOBS.STATE column will show 'BROKEN' for
the job corresponding to DBA_QUEUE_SCHEDULES.JOB_NAME. Prior to 10g
the back off algorithm for failures was exponential, whereas from 10g onwards it is linear.
The propagation will become disabled on
the 17th attempt. Only
the last execution failure will be reflected in
the LAST_ERROR_MSG column. That is, if
the schedule fails 5 times for 5 different reasons, only
the last set of errors will be recorded in DBA_QUEUE_SCHEDULES. Any errors need to be resolved to allow propagation to continue. If propagation has also become disabled due to 17 failures, first resolve
the reason for
the error and then re-enable
the schedule using
the DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE procedure, or DBMS_PROPAGATION_ADM.START_PROPAGATION if using 10.2 or
above Oracle Streams. As soon as
the schedule executes successfully
the error message entries will be deleted. Oracle does not keep a history of past failures. However, when using Oracle Streams,
the errors will be retained in
the DBA_PROPAGATION view even after
the schedule resumes successfully. See
the following note for instructions on how to clear out
the errors from
the DBA_PROPAGATION view:Document 808136.1 How to clear
the old errors from DBA_PROPAGATION view?If a schedule is active and no errors are being reported then
the source queue may not have any messages to be propagated.
4.7. Do
the Propagation Notification Queue Table and Queue Exist?
Check to see that
the propagation notification queue table and queue exist and are enabled for enqueue and dequeue. Propagation makes use of
the propagation notification queue for handling propagation run-time events, and
the messages in this queue are stored in a SYS-owned queue table. This queue should never be stopped or dropped and
the corresponding queue table never be dropped. 10g and belowThe propagation notification queue table is of
the format SYS.AQ$_PROP_TABLE_n, where 'n' is
the RAC instance number, i.e. '1' for a non-RAC environment. This queue and queue table are created implicitly when propagation is first scheduled. If propagation has been scheduled and these objects do not exist, try unscheduling and rescheduling propagation. If they still do not exist contact Oracle Support.
SQL> select QUEUE_TABLE from DBA_QUEUE_TABLES2 where QUEUE_TABLE like '%PROP_TABLE%' and OWNER = 'SYS';QUEUE_TABLE------------------------------AQ$_PROP_TABLE_1SQL> select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED2 from DBA_QUEUES where owner='SYS'3 and QUEUE_TABLE like '%PROP_TABLE%';NAME ENQUEUE DEQUEUE------------------------------ ------- -------AQ$_PROP_NOTIFY_1 YES YESAQ$_AQ$_PROP_TABLE_1_E NO NO
If
the AQ$_PROP_NOTIFY_1 queue is not enabled for enqueue or dequeue, it should be so enabled using DBMS_AQADM.START_QUEUE. However,
the exception queue AQ$_AQ$_PROP_TABLE_1_E should not be enabled for enqueue or dequeue.11g and aboveThe propagation notification queue table is of
the format SYS.AQ_PROP_TABLE, and is created when
the database is created. If they do not exist, contact Oracle Support.
SQL> select QUEUE_TABLE from DBA_QUEUE_TABLES2 where QUEUE_TABLE like '%PROP_TABLE%' and OWNER = 'SYS';QUEUE_TABLE------------------------------AQ_PROP_TABLESQL> select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED2 from DBA_QUEUES where owner='SYS'3 and QUEUE_TABLE like '%PROP_TABLE%';NAME ENQUEUE DEQUEUE------------------------------ ------- -------AQ_PROP_NOTIFY YES YESAQ$_AQ_PROP_TABLE_E NO NO
If
the AQ_PROP_NOTIFY queue is not enabled for enqueue or dequeue, it should be so enabled using DBMS_AQADM.START_QUEUE. However,
the exception queue AQ$_AQ$_PROP_TABLE_E should not be enabled for enqueue or dequeue.
4.8. Does
the Remote Queue Exist and is it Enabled for Enqueueing?
Check that
the remote queue
the propagation is transferring messages to exists and is enabled for enqueue:
SQL> select DESTINATION from USER_QUEUE_SCHEDULES where QNAME = 'OUTQ';DESTINATION-----------------------------------------------------------------------------"AQADM"."INQ"@M2V102.ESSQL> select OWNER, NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED from
[email protected];OWNER NAME ENQUEUE DEQUEUE-------- ------ ----------- -----------AQADM INQ YES YES
4.9. Do
the Target and Source Database Charactersets Differ?
If a message fails to propagate, check
the database charactersets of
the source and target databases. Investigate whether
the same message can propagate between
the databases with
the same characterset or it is only a particular combination of charactersets which causes a problem.
4.10. Check
the Queue Table Type Agreement
Propagation is not possible between queue tables which have types that differ in some respect. One way to determine if this is
the case is to run
the DBMS_AQADM.VERIFY_QUEUE_TYPES procedure for
the two queues that
the propagation operates on. If
the types do not agree, DBMS_AQADM.VERIFY_QUEUE_TYPES will return '0'.For AQ propagation between databases which have different NLS_LENGTH_SEMANTICS settings, propagation will not work, unless
the queues are Oracle Streams ANYDATA queues.See
the following notes for issues caused by lack of type agreement:Document 1079577.1 Advanced Queuing Propagation Fails With "ORA-22370: incorrect usage of method"Document 282987.1 Propagated Messages marked UNDELIVERABLE after Drop and Recreate Of Remote QueueDocument 353754.1 Streams Messaging Propagation Fails between Single and Multi-byte Charactersets when using Chararacter Length Semantics in
the ADT
4.11. Enable Propagation Tracing
4.11.1. System Level
This is set it in
the init.ora/spfile as follows:
event="24040 trace name context forever, level 10"
and restart
the instanceThis event cannot be set dynamically with an alter system command until version 10.2:
SQL> alter system set events '24040 trace name context forever, level 10';
To unset
the event:
SQL> alter system set events '24040 trace name context off';
Debugging information will be logged to job queue trace file(s) (jnnn) as propagation takes place. You can check
the trace file for errors, and for statements indicating that messages have been sent. For
the most part
the trace information is understandable. This trace should also be uploaded to Oracle Support if a service request is created.
4.11.2. Attaching to a Specific Process
We can also attach to an existing job queue processes that is running a propagation schedule and trace it individually using
the oradebug utility, as follows:10.2 and below
connect / as sysdbaselect p.SPID, p.PROGRAM from v$PROCESS p, DBA_JOBS_RUNNING jr, V$SESSION s, DBA_JOBS j where s.SID=jr.SID and s.PADDR=p.ADDR and jr.JOB=j.JOB and j.WHAT like '%sys.dbms_aqadm.aq$_propaq(job)%';-- For
the process id (SPID) attach to it via oradebug and generate
the following traceoradebug setospid <SPID>oradebug unlimitoradebug Event 10046 trace name context forever, level 12oradebug Event 24040 trace name context forever, level 10-- Trace
the process for 5 minutesoradebug Event 10046 trace name context offoradebug Event 24040 trace name context off--
The following command returns
the pathname/filename to
the file being written tooradebug tracefile_name
11g
connect / as sysdbacol PROGRAM for a30select p.SPID, p.PROGRAM, j.JOB_NAMEfrom v$PROCESS p, DBA_SCHEDULER_RUNNING_JOBS jr, V$SESSION s, DBA_SCHEDULER_JOBS j where s.SID=jr.SESSION_ID and s.PADDR=p.ADDR and jr.JOB_NAME=j.JOB_NAME and j.JOB_NAME like '%AQ_JOB$_%';-- For
the process id (SPID) attach to it via oradebug and generate
the following traceoradebug setospid <SPID>oradebug unlimitoradebug Event 10046 trace name context forever, level 12oradebug Event 24040 trace name context forever, level 10-- Trace
the process for 5 minutesoradebug Event 10046 trace name context offoradebug Event 24040 trace name context off--
The following command returns
the pathname/filename to
the file being written tooradebug tracefile_name
4.11.3. Further Tracing
The previous tracing steps only trace
the job queue process executing
the propagation on
the source. At times it is useful to trace
the propagation receiver process (the session which is enqueueing
the messages into
the target queue) on
the target database which is associated with
the job queue process on
the source database.These following queries provide ways of identifying
the processes involved in propagation so that you can attach to them via oradebug to generate trace information.In order to identify
the propagation receiver process you need to execute
the query as a user with privileges to access
the v$ views in both
the local and remote databases so
the database link must connect as a user with those privileges in
the remote database.
The <DBLINK> in
the queries should be replaced by
the appropriate database link.
The queries have two forms due to
the differences between operating systems.
The value returned by 'Rem Process' is
the operating system identifier of
the propagation receiver on
the remote database. Once identified, this process can be attached to and traced on
the remote database using
the commands given in Section 4.11.2.10.2 and below - Windows
select pl.SPID "JobQ Process", pl.PROGRAM, sr.PROCESS "Rem Process" from v$PROCESS pl, DBA_JOBS_RUNNING jr, V$SESSION s, DBA_JOBS j, V$SESSION@<DBLINK> sr where s.SID=jr.SID and s.PADDR=pl.ADDR and jr.JOB=j.JOB and j.WHAT like '%sys.dbms_aqadm.aq$_propaq(job)%' and pl.SPID=substr(sr.PROCESS, instr(sr.PROCESS,':')+1);
10.2 and below - Unix
select pl.SPID "JobQ Process", pl.PROGRAM, sr.PROCESS "Rem Process" from V$PROCESS pl, DBA_JOBS_RUNNING jr, V$SESSION s, DBA_JOBS j, V$SESSION@<DBLINK> sr where s.SID=jr.SID and s.PADDR=pl.ADDR and jr.JOB=j.JOB and j.WHAT like '%sys.dbms_aqadm.aq$_propaq(job)%' and pl.SPID=sr.PROCESS;
11g - Windows
select pl.SPID "JobQ Process", pl.PROGRAM, sr.PROCESS "Rem Process" from V$PROCESS pl, DBA_SCHEDULER_RUNNING_JOBS jr, V$SESSION s, DBA_SCHEDULER_JOBS j, V$SESSION@<DBLINK> sr where s.SID=jr.SESSION_ID and s.PADDR=pl.ADDR and jr.JOB_NAME=j.JOB_NAME and j.JOB_NAME like '%AQ_JOB$_%%' and pl.SPID=substr(sr.PROCESS, instr(sr.PROCESS,':')+1);
11g - Unix
select pl.SPID "JobQ Process", pl.PROGRAM, sr.PROCESS "Rem Process" from V$PROCESS pl, DBA_SCHEDULER_RUNNING_JOBS jr, V$SESSION s, DBA_SCHEDULER_JOBS j, V$SESSION@<DBLINK> sr where s.SID=jr.SESSION_ID and s.PADDR=pl.ADDR and jr.JOB_NAME=j.JOB_NAME and j.JOB_NAME like '%AQ_JOB$_%%' and pl.SPID=sr.PROCESS;
5. Additional Troubleshooting Steps for AQ Propagation of User-Enqueued and Dequeued Messages
5.1. Check
the Privileges of All Users Involved
Ensure that
the owner of
the database link has
the necessary privileges on
the aq packages.
SQL> select TABLE_NAME, PRIVILEGE from USER_TAB_PRIVS;TABLE_NAME PRIVILEGE------------------------------ ----------------------------------------DBMS_LOCK EXECUTEDBMS_AQ EXECUTEDBMS_AQADM EXECUTEDBMS_AQ_BQVIEW EXECUTEQT52814_BUFFER SELECT
Note that when queue table is created, a view called QT<nnn>_BUFFER is created in
the SYS schema, and
the queue table owner is given SELECT privileges on it.
The <nnn> corresponds to
the object_id of
the associated queue table.
SQL> select * from USER_ROLE_PRIVS;USERNAME GRANTED_ROLE ADM DEF OS_------------------------------ ------------------------------ ---- ---- ---AQ_USER1 AQ_ADMINISTRATOR_ROLE NO YES NOAQ_USER1 CONNECT NO YES NOAQ_USER1 RESOURCE NO YES NO
It is good practice to configure central AQ administrative user. All admin and processing jobs are created, executed and administered as this user. This configuration is not mandatory however, and
the database link can be owned by any existing queue user. If this latter configuration is used, ensure that
the connecting user has
the necessary privileges on
the AQ packages and objects involved. Privileges for an AQ Administrative user
Execute on DBMS_AQADM
Execute on DBMS_AQ
Granted
the AQ_ADMINISTRATOR_ROLE
Privileges for an AQ user
Execute on DBMS_AQ
Execute on
the message payload
Enqueue privileges on
the remote queue
Dequeue privileges on
the originating queue
Privileges need to be confirmed on both sites when propagation is scheduled to remote destinations. Verify that
the user ID used to login to
the destination through
the database link has been granted privileges to use AQ.
5.2. Verify Queue Payload Types
AQ will not propagate messages from one queue to another if
the payload types of
the two queues are not verified to be equivalent. An AQ administrator can verify if
the source and destination's payload types match by executing
the DBMS_AQADM.VERIFY_QUEUE_TYPES procedure.
The results of
the type checking will be stored in
the SYS.AQ$_MESSAGE_TYPES table. This table can be accessed using
the object identifier OID of
the source queue and
the address database link of
the destination queue, i.e. [schema.]queue_name[@destination]. Prior to Oracle 9i
the payload (message type) had to be
the same for all
the queue tables involved in propagation. From Oracle9i onwards a transformation can be used so that payloads can be converted from one type to another.
The following procedural call made on
the source database can verify whether we can propagate between
the source and
the destination queue tables.
connect aq_user1/
[email protected] serverout onDECLARErc_value number;BEGINDBMS_AQADM.VERIFY_QUEUE_TYPES(src_queue_name => 'AQ_USER1.Q_1', dest_queue_name => 'AQ_USER2.Q_2',destination => 'dbl_aq_user2.es',rc => rc_value);dbms_output.put_line('rc_value code is '||rc_value);END;/
If propagation is possible then
the return code value will be 1. If it is 0 then propagation is not possible and further investigation of
the types and transformations used by and in conjunction with
the queue tables is required. With regard to comparison of
the types
the following sql can be used to extract
the DDL for a specific type with' %' changed appropriately on
the source and target. This can then be compared for
the source and target.
SET LONG 20000 set pagesize 50 EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',false); SELECT DBMS_METADATA.GET_DDL('TYPE',t.type_name) from user_types t WHERE t.type_name like '%'; EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT');
5.3. Check Message State and Destination
The first step in this process is to identify
the queue table associated with
the problem source queue. Although you schedule propagation for a specific queue, most of
the meta-data associated with that queue is stored in
the underlying queue table.
The following statement finds
the queue table for a given queue (note that this is a multiple-consumer queue table).
SQL> select QUEUE_TABLE from DBA_QUEUES where NAME = 'MULTIPLEQ';QUEUE_TABLE --------------------MULTIPLEQTABLE
For a small amount of messages in a multiple-consumer queue table,
the following query can be run:
SQL> select MSG_STATE, CONSUMER_NAME, ADDRESS from AQ$MULTIPLEQTABLE where QUEUE = 'MULTIPLEQ';MSG_STATE CONSUMER_NAME ADDRESS-------------- ----------------------- -------------READY AQUSER2
[email protected] AQUSER1READY AQUSER3 AQADM.INQ
In this example we see 2 messages ready to be propagated to remote queues and 1 that is not. If
the address column is blank,
the message is not scheduled for propagation and can only be dequeued from
the queue upon which it was enqueued.
The MSG_STATE column values are discussed in Document 102330.1 Advanced Queueing MSG_STATE Values and their Interpretation. If
the address column has a value,
the message has been enqueued for propagation to another queue.
The first row in
the example includes a database link (@M2V102.ES). This demonstrates that
the message should be propagated to a queue at a remote database.
The third row does not include a database link so will be propagated to a queue that resides on
the same database as
the source queue.
The consumer name is
the intended recipient at
the target queue. Note that we are not querying
the base queue table directly; rather, we are querying a view that is available on top of every queue table, AQ$<queue_table_name>.A more realistic query in an environment where
the queue table contains thousands of messages is8.0.3-compatible multiple-consumer queue table and all compatibility single-consumer queue tables
select count(*), MSG_STATE, QUEUE from AQ$<queue_table_name> group by MSG_STATE, QUEUE;
8.1.3 and 10.0-compatible queue tables
select count(*), MSG_STATE, QUEUE, CONSUMER_NAME from AQ$<queue_table_name>group by MSG_STATE, QUEUE, CONSUMER_NAME;
For multiple-consumer queue tables, if you did not see
the expected CONSUMER_NAME , check
the syntax of
the enqueue code and verify
the recipients are declared correctly. If a recipients list is not used on enqueue, check
the subscriber list in
the AQ$_<queue_table_name>_S view (note that a single-consumer queue table does not have a subscriber view. This view records all members of
the default subscription list which were added using
the DBMS_AQADM.ADD_SUBSCRIBER procedure and also those enqueued using a recipient list.
SQL> select QUEUE, NAME, ADDRESS from AQ$MULTIPLEQTABLE_S;QUEUE NAME ADDRESS---------- ----------- -------------MULTIPLEQ AQUSER2
[email protected] AQUSER1
In this example we have 2 subscribers registered with
the queue. We have a local subscriber AQUSER1, and a remote subscriber AQUSER2, on
the queue INQ, owned by AQADM, at M2V102.ES. Unless overridden with a recipient list during enqueue every message enqueued to this queue will be propagated to INQ at M2V102.ES.For 8.1 style and
above multiple consumer queue tables, you can also check
the following information at
the target:
select CONSUMER_NAME, DEQ_TXN_ID, DEQ_TIME, DEQ_USER_ID, PROPAGATED_MSGID from AQ$<queue_table_name> where QUEUE = '<QUEUE_NAME>';
For 8.0 style queues, if
the queue table supports multiple consumers you can obtain
the same information from
the history column of
the queue table:
select h.CONSUMER, h.TRANSACTION_ID, h.DEQ_TIME, h.DEQ_USER, h.PROPAGATED_MSGIDfrom AQ$<queue_table_name> t, table(t.history) h where t.Q_NAME = '<QUEUE_NAME>';
A non-NULL TRANSACTION_ID indicates that
the message was successfully propagated. Further,
the DEQ_TIME indicates
the time of propagation,
the DEQ_USER indicates
the userid used for propagation, and
the PROPAGATED_MSGID indicates
the message ID of
the message that was enqueued at
the destination.
6. Additional Troubleshooting Steps for Propagation in an Oracle Streams Environment
6.1. Is
the Propagation Enabled?
For a propagation job to propagate messages,
the propagation must be enabled. For Streams, a special view called DBA_PROPAGATION exists to convey information about Streams propagations. If messages are not being propagated by a propagation as expected, then
the propagation might not be enabled. To query for this:
SELECT p.PROPAGATION_NAME, DECODE(s.SCHEDULE_DISABLED, 'Y', 'Disabled','N', 'Enabled') SCHEDULE_DISABLED, s.PROCESS_NAME, s.FAILURES, s.LAST_ERROR_MSGFROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION pWHERE p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(s.DESTINATION, '[^@]+', 1, 2), s.DESTINATION) AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME AND MESSAGE_DELIVERY_MODE = 'PERSISTENT' order by PROPAGATION_NAME;
At times,
the propagation job may become "broken" or fail to start after an error has been encountered or after a database restart. If an error is indicated by
the above query, an attempt to disable
the propagation and then re-enable it can be made. In
the examples below, for
the propagation named STRMADMIN_PROPAGATE where
the queue name is STREAMS_QUEUE owned by STRMADMIN and
the destination database link is ORCL2.WORLD,
the commands would be:10.2 and
above
exec dbms_propagation_adm.stop_propagation('STRMADMIN_PROPAGATE'); exec dbms_propagation_adm.start_propagation('STRMADMIN_PROPAGATE');
If
the above does not fix
the problem, stop
the propagation specifying
the force parameter (2nd parameter on stop_propagation) as TRUE:
exec dbms_propagation_adm.stop_propagation('STRMADMIN_PROPAGATE',true); exec dbms_propagation_adm.start_propagation('STRMADMIN_PROPAGATE');
The statistics for
the propagation as well as any old error messages are cleared when
the force parameter is set to TRUE. Therefore if
the propagation schedule is stopped with FORCE set to TRUE, and upon restart there is still an error message in DBA_PROPAGATION, then
the error message is current.9.2 or 10.1
exec dbms_aqadm.disable_propagation_schedule('STRMADMIN.STREAMS_QUEUE','ORCL2.WORLD'); exec dbms.aqadm.enable_propagation_schedule('STRMADMIN.STREAMS_QUEUE','ORCL2.WORLD');
If
the above does not fix
the problem, perform an unschedule of propagation and then schedule_propagation:
exec dbms_aqadm.unschedule_propagation('STRMADMIN.STREAMS_QUEUE','ORCL2.WORLD'); exec dbms_aqadm.schedule_propagation('STRMADMIN.STREAMS_QUEUE','ORCL2.WORLD');
Typically if
the error from
the first query in Section 6.1 recurs after restarting
the propagation as shown
above, further troubleshooting of
the error is needed.
6.2. Check Propagation Rule Sets and Transformations
Inspect
the configuration of
the rules in
the rule set that is associated with
the propagation process to make sure that they evaluate to TRUE as expected. If not, then
the object or schema will not be propagated. Remember that when a negative rule evaluates to TRUE,
the specified object or schema will not be propagated. Finally inspect any rule-based transformations that are implemented with propagation to make sure they are changing
the data in
the intended way.
The following query shows what rule sets are assigned to a propagation:
select PROPAGATION_NAME, RULE_SET_OWNER||'.'||RULE_SET_NAME "Positive Rule Set",NEGATIVE_RULE_SET_OWNER||'.'||NEGATIVE_RULE_SET_NAME "Negative Rule Set"from DBA_PROPAGATION;
The next two queries list
the propagation rules and their conditions.
The first is for
the positive rule set,
the second is for
the negative rule set:
set long 4000select rsr.RULE_SET_OWNER||'.'||rsr.RULE_SET_NAME RULE_SET ,rsr.RULE_OWNER||'.'||rsr.RULE_NAME RULE_NAME,r.RULE_CONDITION CONDITION fromDBA_RULE_SET_RULES rsr, DBA_RULES rwhere rsr.RULE_NAME = r.RULE_NAME and rsr.RULE_OWNER = r.RULE_OWNER and RULE_SET_NAME in(select RULE_SET_NAME from DBA_PROPAGATION) order by rsr.RULE_SET_OWNER, rsr.RULE_SET_NAME;
set long 4000select c.PROPAGATION_NAME, rsr.RULE_SET_OWNER||'.'||rsr.RULE_SET_NAME RULE_SET ,rsr.RULE_OWNER||'.'||rsr.RULE_NAME RULE_NAME,r.RULE_CONDITION CONDITION fromDBA_RULE_SET_RULES rsr, DBA_RULES r ,DBA_PROPAGATION cwhere rsr.RULE_NAME = r.RULE_NAME and rsr.RULE_OWNER = r.RULE_OWNER andrsr.RULE_SET_OWNER=c.NEGATIVE_RULE_SET_OWNER and rsr.RULE_SET_NAME=c.NEGATIVE_RULE_SET_NAMEand rsr.RULE_SET_NAME in(select NEGATIVE_RULE_SET_NAME from DBA_PROPAGATION) order by rsr.RULE_SET_OWNER, rsr.RULE_SET_NAME;
6.3. Determining
the Total Number of Messages and Bytes Propagated
As in Section 3.1, determining if messages are flowing can be instructive to see whether
the propagation is entirely hung or just slow. If
the propagation is not in flow control (see Section 6.5.2), but
the statistics are incrementing slowly, there may be a performance issue. For Streams implementations two views are available that can assist with this that can show
the number of messages sent by a propagation, as well as
the number of acknowledgements being returned from
the target site:
the V$PROPAGATION_SENDER view at
the Source site and
the V$PROPAGATION_RECEIVER view at
the destination site. It is helpful to query both to determine if messages are being delivered to
the target. Look for
the statistics to increase.Source:
select QUEUE_SCHEMA, QUEUE_NAME, DBLINK,HIGH_WATER_MARK, ACKNOWLEDGEMENT, TOTAL_MSGS, TOTAL_BYTESfrom V$PROPAGATION_SENDER;
Target:
select SRC_QUEUE_SCHEMA, SRC_QUEUE_NAME, SRC_DBNAME, DST_QUEUE_SCHEMA, DST_QUEUE_NAME, HIGH_WATER_MARK, ACKNOWLEDGEMENT, TOTAL_MSGS from V$PROPAGATION_RECEIVER;
6.4. Check Buffered Subscribers
The V$BUFFERED_SUBSCRIBERS view displays information about subscribers for all buffered queues in
the instance. This view can be queried to make sure that
the site that
the propagation is propagating to is listed as a subscriber address for
the site being propagated from:
select QUEUE_SCHEMA, QUEUE_NAME, SUBSCRIBER_ADDRESS from V$BUFFERED_SUBSCRIBERS;
The SUBSCRIBER_ADDRESS column will not be populated when
the propagation is local (between queues on
the same database).
6.5. Common Streams Propagation Errors
6.5.1. ORA-02082: A loopback database link must have a connection qualifier.
This error can occur if you use
the Streams Setup Wizard in Oracle Enterprise Manager without first configuring
the GLOBAL_NAME for your database.
6.5.2. ORA-25307: Enqueue rate too high. Enable flow control
DBA_QUEUE_SCHEDULES will display this informational message for propagation when
the automatic flow control (10g feature of Streams) has been invoked.Similar to Streams capture processes, a Streams propagation process can also go into a state of 'flow control. This is an informative message that indicates flow control has been automatically enabled to reduce
the rate at which messages are being enqueued into at target queue.This typically occurs when
the target site is unable to keep up with
the rate of messages flowing from
the source site. Other than checking that
the apply process is running normally on
the target site, usually no action is required by
the DBA. Propagation and
the capture process will be resumed automatically when
the target site is able to accept more messages.
The following document contains more information:Document 302109.1 Streams Propagation Error: ORA-25307 Enqueue rate too high. Enable flow controlSee
the following document for one potential cause of this situation:Document 1097115.1 Oracle Streams Apply Reader is in 'Paused' State
6.5.3. ORA-25315 unsupported configuration for propagation of buffered messages
This error typically occurs when
the target database is RAC and usually indicates that an attempt was made to propagate buffered messages with
the database link pointing to an instance in
the destination database which is not
the owner instance of
the destination queue. To resolve
the problem, use queue-to-queue propagation for buffered messages.
6.5.4. ORA-600 [KWQBMCRCPTS101] after dropping / recreating propagation
For cause/fixes refer to:Document 421237.1 ORA-600 [KWQBMCRCPTS101] reported by a Qmon slave process after dropping a Streams Propagation
6.5.5. Stopping or Dropping a Streams Propagation Hangs
See
the following note:Document 1159787.1 Troubleshooting Streams Propagation When It is Not Functioning and Attempts to Stop It Hang
6.6. Streams Propagation-Related Notes for Common Issues
Document 437838.1 Streams Specific PatchesDocument 749181.1 How to Recover Streams After Dropping PropagationDocument 368912.1 Queue to Queue Propagation Schedule encountered ORA-12514 in a RAC environmentDocument 564649.1 ORA-02068/ORA-03114/ORA-03113 Errors From Streams Propagation Process - Remote Database is Available and Unschedule/Reschedule Does Not ResolveDocument 553017.1 Stream Propagation Process Errors Ora-4052 Ora-6554 From 11g To 10201Document 944846.1 Streams Propagation Fails Ora-7445 [kohrsmc]Document 745601.1 ORA-23603 'STREAMS enqueue aborted due to low SGA' Error from Streams Propagation, and V$STREAMS_CAPTURE.STATE Hanging on 'Enqueuing Message'Document 333068.1 ORA-23603: Streams Enqueue Aborted Eue To Low SGADocument 363496.1 Ora-25315 Propagating on RAC StreamsDocument 368237.1 Unable to Unschedule Propagation. Streams Queue is InvalidDocument 436332.1 dbms_propagation_adm.stop_propagation hangsDocument 727389.1 Propagation Fails With ORA-12528Document 730911.1 ORA-4063 Is Reported After Dropping Negative Prop.RulesetDocument 460471.1 Propagation Blocked by Qmon Process - Streams_queue_table / 'library cache lock' waitsDocument 1165583.1 ORA-600 [kwqpuspse0-ack] In Streams EnvironmentDocument 1059029.1 Combined Capture and Apply (CCA) : Capture aborts : ORA-1422 after schedule_propagationDocument 556309.1 Changing Propagation/ queue_to_queue : false -> true does does not work; no LCRs propagatedDocument 839568.1 Propagation failing with error: ORA-01536: space quota exceeded for tablespace ''Document 311021.1 Streams Propagation Process : Ora 12154 After Reboot with Transparent Application Failover TAF configuredDocument 359971.1 STREAMS propagation to Primary of physical Standby configuation errors with Ora-01033, Ora-02068Document 1101616.1 DBMS_PROPAGATION_ADM.DROP_PROPAGATION FAILS WITH ORA-1747
7. Performance Issues
A propagation may seem to be slow if
the queries from Sections 3.1 and 6.3 show that
the message statistics are not changing quickly. In Oracle Streams, this more usually is due to a slow apply process at
the target rather than a slow propagation. Propagation could be inferred to be slow if
the message statistics are changing, and
the state of a capture process according to V$STREAMS_CAPTURE.STATE is PAUSED FOR FLOW CONTROL, but an ORA-25307 'Enqueue rate too high. Enable flow control' warning is NOT observed in DBA_QUEUE_SCHEDULES per Section 6.5.2. If this is
the case, see
the following notes / white papers for suggestions to increase performance:Document 335516.1 Master Note for Streams Performance RecommendationsDocument 730036.1 Overview for Troubleshooting Streams Performance IssuesDocument 780733.1 Streams Propagation Tuning with Network ParametersWhite Paper: http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-streams-performance-130059.pdfWhite Paper: Oracle Streams Configuration Best Practices: Oracle Database 10g Release 10.2, http://www.oracle.com/technetwork/database/features/availability/maa-10gr2-streams-configuration-132039.pdf, See APPENDIX A: USING STREAMS CONFIGURATIONS OVER A NETWORKFor basic AQ propagation,
the network tuning in
the aforementioned Appendix A of
the white paper 'Oracle Streams Configuration Best Practices: Oracle Database 10g Release 10.2' is applicable.
References
NOTE:102330.1 - Advanced Queueing MSG_STATE Values and their InterpretationNOTE:102771.1 - Advanced Queueing Propagation using PL/SQLNOTE:1059029.1 - Combined Capture and Apply (CCA) : Capture aborts : ORA-1422 after schedule_propagationNOTE:1079577.1 - Advanced Queuing Propagation Fails With "ORA-22370: incorrect usage of method"NOTE:1083608.1 - 11g Streams and Oracle SchedulerNOTE:1087324.1 - ORA-01405 ORA-01422 reported by Adavanced Queueing Propagation schedules after RAC reconfigurationNOTE:1097115.1 - Oracle Streams Apply Reader is in 'Paused' StateNOTE:1101616.1 - DBMS_PROPAGATION_ADM.DROP_PROPAGATION FAILS WITH ORA-1747NOTE:1159787.1 - Troubleshooting Streams Propagation When It is Not Functioning and Attempts to Stop It HangNOTE:1165583.1 - ORA-600 [kwqpuspse0-ack] In Streams EnvironmentNOTE:118884.1 - How to unschedule a propagation schedule stuck in pending stateNOTE:1203544.1 - AQ PROPAGATION ABORTED WITH ORA-600[OCIKSIN: INVALID STATUS] ON SYS.DBMS_AQADM_SYS.AQ$_PROPAGATION_PROCEDURE AFTER UPGRADENOTE:1204080.1 - AQ Propagation Failing With ORA-25329 After Upgraded From 8i or 9i to 10g or 11g.NOTE:219416.1 - Advanced Queuing Propagation fails with ORA-22922NOTE:222992.1 - DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE Returns ORA-24082NOTE:253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555)NOTE:282987.1 - Propagated Messages marked UNDELIVERABLE after Drop and Recreate Of Remote QueueNOTE:298015.1 - Kwqjswproc:Excep After Loop: Assigning To SelfNOTE:302109.1 - Streams Propagation Error: ORA-25307 Enqueue rate too high. Enable flow controlNOTE:311021.1 - Streams Propagation Process : Ora 12154 After Reboot with Transparent Application Failover TAF configuredNOTE:332792.1 - ORA-04061 error relating to SYS.DBMS_PRVTAQIP reported when setting up StatspackNOTE:333068.1 - ORA-23603: Streams Enqueue Aborted Eue To Low SGANOTE:335516.1 - Master Note for Streams Performance RecommendationsNOTE:353325.1 - ORA-24056: Internal inconsistency for QUEUE and destination NOTE:353754.1 - Streams Messaging Propagation Fails between Single and Multi-byte Charactersets when using Chararacter Length Semantics in
the ADT.NOTE:359971.1 - STREAMS propagation to Primary of physical Standby configuation errors with Ora-01033, Ora-02068NOTE:363496.1 - Ora-25315 Propagating on RAC StreamsNOTE:365093.1 - ORA-07445 [kwqppay2aqe()+7360] reported on Propagation of a Transformed MessageNOTE:368237.1 - Unable to Unschedule Propagation. Streams Queue is InvalidNOTE:368912.1 - Queue to Queue Propagation Schedule encountered ORA-12514 in a RAC environmentNOTE:421237.1 - ORA-600 [KWQBMCRCPTS101] reported by a Qmon slave process after dropping a Streams PropagationNOTE:436332.1 - dbms_propagation_adm.stop_propagation hangsNOTE:437838.1 - Streams Specific PatchesNOTE:460471.1 - Propagation Blocked by Qmon Process - Streams_queue_table / 'library cache lock' waitsNOTE:463820.1 - Streams Combined Capture and Apply in 11gNOTE:553017.1 - Stream Propagation Process Errors Ora-4052 Ora-6554 From 11g To 10201NOTE:556309.1 - Changing Propagation/ queue_to_queue : false -> true does does not work; no LCRs propagatedNOTE:564649.1 - ORA-02068/ORA-03114/ORA-03113 Errors From Streams Propagation Process - Remote Database is Available and Unschedule/Reschedule Does Not ResolveNOTE:566622.1 - ORA-22275 when propagating >4K AQ$_JMS_TEXT_MESSAGEs from 9.2.0.8 to 10.2.0.1NOTE:727389.1 - Propagation Fails With ORA-12528NOTE:730036.1 - Overview for Troubleshooting Streams Performance IssuesNOTE:730911.1 - ORA-4063 Is Reported After Dropping Negative Prop.RulesetNOTE:731292.1 - ORA-25215 Reported On Local Propagation When Using Transformation with ANYDATA queue tablesNOTE:731539.1 - ORA-29268: HTTP client error 401 Unauthorized Error when
the AQ Servlet attempts to Propagate a message via HTTPNOTE:745601.1 - ORA-23603 'STREAMS enqueue aborted due to low SGA' Error from Streams Propagation, and V$STREAMS_CAPTURE.STATE Hanging on 'Enqueuing Message'NOTE:749181.1 - How to Recover Streams After Dropping PropagationNOTE:780733.1 - Streams Propagation Tuning with Network ParametersNOTE:787367.1 - ORA-22275 reported on Propagating Messages with LOB component when propagating between 10.1 and 10.2NOTE:808136.1 - How to clear
the old errors from DBA_PROPAGATION view ?NOTE:827184.1 - AQ Propagation with CLOB data types Fails with ORA-22990NOTE:827473.1 - How to alter propagation from queue_to_queue to queue_to_dblinkNOTE:839568.1 - Propagation failing with error: ORA-01536: space quota exceeded for tablespace ''NOTE:846297.1 - AQ Propagation Fails : ORA-00600[kope2upic2954] or Ora-00600[Kghsstream_copyn]NOTE:944846.1 - Streams Propagation Fails Ora-7445 [kohrsmc]
