Issue with AQ Propagation
Date: Thu, 22 Jun 2017 02:06:33 +0000
Message-ID: <BY2PR0101MB0887F9284F4DBBE4050F815EDDDB0_at_BY2PR0101MB0887.prod.exchangelabs.com>
We have an application that uses AQ propagation to transmit messages to a remote queue. It has been working fine - until it stopped propagating.
When I checked dba_queue_schedules I found the queue in question had the SCHEDULE_DISABLED column set to 'Y'. I had a look at the alert log and found this:
Mon Jun 19 20:28:11 2017
Errors in file /u01/app/oracle/diag/rdbms/oplevbet/OPLEVBET1/trace/OPLEVBET1_j003_12436.trc:
ORA-00028: your session has been killed
opidrv aborting process J003 ospid (12436) as a result of ORA-28
Looking at the trace file mentioned was not helpful (to me). It just contained:
- 2017-06-19 20:28:11.550
- SESSION ID:(506.5) 2017-06-19 20:28:11.550
- CLIENT ID:(48101) 2017-06-19 20:28:11.550
- SERVICE NAME:(SYS$USERS) 2017-06-19 20:28:11.550
- MODULE NAME:(Streams) 2017-06-19 20:28:11.550
- ACTION NAME:(Propagation Sender) 2017-06-19 20:28:11.550
kwqjaq: Exception 28 Occured
ORA-00028: your session has been killed
kwqjschupd: Exception Occured 604
kwqjwdropall:Exception, freeing all windows
error 28 in job queue process
ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8729 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9133OPIRIP: Uncaught error 28. Error stack:
ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8729 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9133
I checked in aud$ and there was no evidence that the sessions were killed manually by a DBA.
(We're using 11.2.0.4 SE RAC and are up to date on PSU's.)
I'm trying to find out why these processes were killed, and what other folks are doing to monitor the propagation mechanism. In my naivety I assumed once you established a propagation schedule, Oracle would ensure the processes are running. I guess not.
If anyone has any clues, or can offer best practises on monitoring or ideally automatic recovery, I'd be very grateful.
Thanks,
Steve
This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 22 2017 - 04:06:33 CEST