OTN Discussion Forums : error while enqueueing into queue ...

hrishy

Posts: 35
Registered: 01/10/01

error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Jan 2, 2008 11:24 AM

Hi

I have setup a streams environment and also a queue handler with a user defined procedure however when i test the environment i get the following error in dba_apply_error

APPLY_EMP  ORA-26694: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
           ORA-24033: no recipients for message
 
APPLY_EMP  ORA-26694: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
           ORA-24033: no recipients for message
 
APPLY_EMP  ORA-26694: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
           ORA-24033: no recipients for message
 
APPLY_EMP  ORA-26694: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
           ORA-24033: no recipients for message

I do have a custom deque handler and that is valid.

  1  select object_name,object_type,status
  2  from user_objects
  3* where object_type='PROCEDURE'
SQL> / 
 
OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
EMP_DML_HANDLER      PROCEDURE           VALID

Version of my database is 10.2.0.1.0.

I configured a very simple streams environment by following the instructions at

http://blogs.ittoolbox.com/oracle/guide/archives/oracle-streams-configuration-change-data-capture-13501

Any ideas whats wrong or
How do i go about troubleshooting this environment ?

regards
Hrishy

Culjak

Posts: 7
Registered: 11/04/07

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Jan 2, 2008 12:55 PM

in response to: hrishy

Reply

Hi Hrishy,

I had the same problem before I had set global names to true.

Some of my favourite trouble shooting queries:

--1. Check if queues created.
SELECT name, queue_table FROM user_queues;

--2. Supplemental logging:
SELECT always, table_name, log_group_type FROM dba_log_groups order by table_name;

--3. Capture process was created:
SELECT capture_name, queue_name, queue_owner, status FROM all_capture;

--4. Propagation was created:
SELECT propagation_name, source_queue_name, destination_queue_name, status FROM dba_propagation;

--5. INSTANTIATION_SCN was set up:
SELECT source_object_owner,source_object_name, source_object_type, instantiation_scn FROM dba_apply_instantiated_objects

--6. Apply process was created:
SELECT * FROM all_apply;

--7. Check handlers exist
SELECT OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME, USER_PROCEDURE, APPLY_NAME FROM DBA_APPLY_DML_HANDLERS WHERE APPLY_DATABASE_LINK IS NULL ORDER BY OBJECT_OWNER, OBJECT_NAME;

--8. Any data sitting in the queue?
select * from streams_QUEUE_TABLE;
SELECT * FROM AQ$streams_queue_table;

hrishy

Posts: 35
Registered: 01/10/01

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Jan 3, 2008 4:50 AM

in response to: Culjak

Reply

Hi

Thanks for taking a look at this.

Here is the output of the diagnostic queries.

SQL> SELECT name, queue_table FROM user_queues;
 
NAME                           QUEUE_TABLE
------------------------------ ------------------------------
STREAMS_QUEUE                  STREAMS_QUEUE_TABLE
AQ$_STREAMS_QUEUE_TABLE_E      STREAMS_QUEUE_TABLE
 
SQL> SELECT always, table_name, log_group_type FROM dba_log_groups order by table_name
  2
SQL> / 
 
ALWAYS      TABLE_NAME                     LOG_GROUP_TYPE
----------- ------------------------------ -------------------
ALWAYS      EMPLOYEES                      PRIMARY KEY LOGGING
CONDITIONAL EMPLOYEES                      FOREIGN KEY LOGGING
CONDITIONAL EMPLOYEES                      UNIQUE KEY LOGGING
 
  1* SELECT capture_name, queue_name, queue_owner, status FROM all_capture
SQL> / 
 
CAPTURE_NAME                   QUEUE_NAME                     QUEUE_OWNER                    STATUS
------------------------------ ------------------------------ ------------------------------ --------
CAPTURE_EMP                    STREAMS_QUEUE                  STRMADMIN                      ENABLED
 
I have not created a propogation as i am basically replicating on the same database
 
SOURCE_OBJECT_OWNER            SOURCE_OBJECT_NAME             SOURCE_OBJE INSTANTIATION_SCN
------------------------------ ------------------------------ ----------- -----------------
HR                             EMPLOYEES                      TABLE                  599422
 
SQL> select apply_name,status
  2  from all_apply
  3
SQL> / 
 
APPLY_NAME                     STATUS
------------------------------ --------
APPLY_EMP                      ENABLED
 
SQL> SELECT OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME, USER_PROCEDURE, APPLY_NAME FROM DBA_APPLY_DML_HANDLERS WHERE APPLY_D
ATABASE_LINK IS NULL ORDER BY OBJECT_OWNER, OBJECT_NAME
  2
SQL> / 
 
OBJECT_OWNER                   OBJECT_NAME                    OPERATION_NAM USER_PROCEDURE
                                       APPLY_NAME
------------------------------ ------------------------------ ------------- -----------------------------------------------
--------------------------------------------------- ------------------------------
HR                             EMPLOYEES                      INSERT        "STRMADMIN"."EMP_DML_HANDLER"
HR                             EMPLOYEES                      UPDATE        "STRMADMIN"."EMP_DML_HANDLER"
HR                             EMPLOYEES                      DELETE        "STRMADMIN"."EMP_DML_HANDLER"
SQL> select q_name,RECIPIENT_KEY
  2  from streams_QUEUE_TABLE
  3
SQL> / 
 
Q_NAME                         RECIPIENT_KEY
------------------------------ -------------
AQ$_STREAMS_QUEUE_TABLE_E                  0
AQ$_STREAMS_QUEUE_TABLE_E                  0
AQ$_STREAMS_QUEUE_TABLE_E                  0
AQ$_STREAMS_QUEUE_TABLE_E                  0

regards
Hrishy

Culjak

Posts: 7
Registered: 11/04/07

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Jan 3, 2008 12:35 PM

in response to: hrishy

Reply

Hi Hrishi,

I also had problems with that article you used. I am not 100% sure but the way I got it working was to:
1. not execute the lcr in the handler; and
2. create a messaging client for the queue (you should drop your apply, run this, then recreate the apply)
DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => 'HR.EMPLOYEES',streams_type => 'dequeue', streams_name => 'EMP_dq', queue_name => 'strmadmin.STREAMS_QUEUE',include_dml => true, include_ddl => false, inclusion_rule => true

If this doesn't work please include the handler and commands you used to configure apply and capture.

Mike

anoopS

Posts: 86
Registered: 01/16/08

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Jan 17, 2008 11:06 PM

in response to: Culjak

Reply

The handler that you have created is prohibiting the message you have inserted to be dequeued.
or
The subscriber that you have created is not valid.

user571269

Posts: 157
Registered: 04/24/07

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Apr 9, 2008 10:54 AM

in response to: anoopS

Reply

Hi All,

I followed the same example above and my streams are working.

I have a problem when i insert multiple rows in the table and commit data, only the first row inserted is captured by streams. I am unable to capture all the rows.

Is it that after every update\insert\delete we need to commit so that streams work ?

Any help would be appreciated

Thanks,
Sam.

user571269

Posts: 157
Registered: 04/24/07

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Apr 10, 2008 11:59 AM

in response to: user571269

Reply

I got it working.

The blog (http://blogs.ittoolbox.com/oracle/guide/archives/oracle-streams-configuration-change-data-capture-13501) gives a very good example of streams.

After adding the dequeue logic it will allow multiple DML transcations together and will resolve the error message below
APPLY_EMP ORA-26694: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
ORA-24033: no recipients for message

Login as STRMADIM and execute the code below

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.employees',
streams_type => 'dequeue',
streams_name => 'emp_deq',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => false,
inclusion_rule => true);
END;

Thanks,
Sam.

user571269

Posts: 157
Registered: 04/24/07

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Apr 10, 2008 12:05 PM

in response to: user571269

Reply

I got it working.

The blog (http://blogs.ittoolbox.com/oracle/guide/archives/oracle-streams-configuration-change-data-capture-13501) gives a very good example of streams.

After adding the dequeue logic it will allow multiple DML transcations together and will resolve the error message below
APPLY_EMP ORA-26694: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
ORA-24033: no recipients for message

Login as STRMADIM and execute the code below

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.employees',
streams_type => 'dequeue',
streams_name => 'emp_deq',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => false,
inclusion_rule => true);
END;

Thanks,
Sam.

lewisc

Posts: 123
Registered: 04/07/98

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: May 2, 2008 10:12 AM

in response to: user571269

Reply

Hi.

Thanks to all for this note.

My blog entry was based on an actual streams implementation and I pared the example down as far as I could and still have it work (to simplify it). Apparently, I pared it down a bit too far.

The real implementation involved many tables across multiple schemas as well as quite a bit of data conversion.

I'll add the fix to the original post. Thanks for pointing this out.

LewisC

user522

Posts: 53
Registered: 02/04/08

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: May 16, 2008 1:32 PM

in response to: hrishy

Reply

Hi All,

I have also used this example to implement CDC.

For Update I want to see New values as well as old (post image).

Can somebody please provide me an example for Update dml handler for local capture and apply. Following is the objective.

If the column value has been updated, I want to see the NEW value.

For all other columns I want to see the existing or old value ( I don't want to see NULL for them).

source table hr.employees

target table hr.employee_audit

Supplemental Log has been enable for ALL columns.

Thanks in advance.

lewisc

Posts: 123
Registered: 04/07/98

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: May 18, 2008 8:38 AM

in response to: user522

Reply

user522,

I added a new blog entry that saves off new and old values. You can modify this to give you exactly what you're looking for.

http://blogs.ittoolbox.com/oracle/guide/archives/oracle-streams-configuration-capture-new-and-old-values-24740

I have to say though, that if you are doing this for auditing reasons, fine grained auditing is a better choice.

LewisC

user522

Posts: 53
Registered: 02/04/08

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: May 21, 2008 1:22 PM

in response to: hrishy

Reply

Thanks Lewis, You have made our life easier, there are tons of information available on web, but nothing is as precise as your articles.

-Ravi

user480471

Posts: 71
Registered: 01/17/06

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Sep 15, 2008 1:06 AM

in response to: lewisc

Reply

hi,

i am a new bie and had hard time configuring streams... luckly i got urs and it is working fine. Atleaset its calling the dml hanler. I am geting the following error when the handler is getting invloked. Any help??????

i am getting the error:
SQLERRM:: ORA-31021: Element definition not found
ORA-06512: at "SYS.DBMS_STREAMS_LCR_INT", line 13
ORA-06512: at "SYS.DBMS_STREAMS", line 195

and debugging showed that its due to : DBMS_STREAMS.CONVERT_LCR_TO_XML(in_any)

btw is there any way to trace what, how and to what extent the message are getting captured. Like how can i confirm that log miner had read, capture process had captured... but enqueuing/ apply process couldnt pickup??

-- ravi

Edited by: user480471 on 15 Sep, 2008 1:37 PM

aldofad

Posts: 188
Registered: 05/17/01

Re: error while enqueueing into queue STRMADMIN.STREAMS_QUEUE
Posted: Sep 17, 2008 4:55 AM

in response to: user522

Reply

Hi user522,
here is how to see the existing or old value, not updated, togheter with the updated.
Normally you use this to create an history database:

  lcr          SYS.LCR$_ROW_RECORD;
  old_values   SYS.LCR$_ROW_LIST;
  new_values   SYS.LCR$_ROW_LIST;
  rc           PLS_INTEGER;  
  command      VARCHAR2(30);
  aVERIFY_DATE ANYDATA;
  operation varchar2(1);
  currTimestamp timestamp;
begin
  rc := in_any.GETOBJECT(lcr);
  command:=lcr.GET_COMMAND_TYPE();
  lcr.SET_COMMAND_TYPE('INSERT');
  lcr.set_object_owner('MTS_OWNER2');
  --lcr.SET_OBJECT_NAME(destTable);
  new_values := lcr.get_values('new');
  currTimestamp := systimestamp;
  IF command IN ('UPDATE') THEN
    FOR i IN 1.. new_values.COUNT LOOP
      lcr.set_value('old', new_values(i).column_name, new_values(i).data);
    END LOOP;
    lcr.set_values('new', lcr.GET_VALUES('old'));
    lcr.set_values('old', null);
  ...

This is part of my DML handler in an history destination database.

bye