ORA-24018: STOP_QUEUE on RMI_PIPE failed, outstanding transactions found

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

Starting the secondary RmiServer may sometimes throw the below error in the secondary RmiServer's log file. 

ErrorMsg: AwE-5001 Database Query Error
Details: ORA-24018: STOP_QUEUE on AM.RMI_PIPE944105729 failed, outstanding transactions found
ORA-06512: at "AM.AM_AQ", line 151
ORA-06512: at "AM.AM_AQ", line 788
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8398
ORA-06512: at "SYS.DBMS_AQADM", line 783
ORA-06512: at "AM.AM_AQ", line 139
ORA-06512: at "AM.AM_AQ", line 170
ORA-06512: at "AM.SO_SESSION_DEL", line 27
ORA-04088: error during execution of trigger 'AM.SO_SESSION_DEL'
ORA-06512: at "AM.AW_RMI_SERVERS_NOTIFY", line 12
ORA-04088: error during execution of trigger 'AM.AW_RMI_SERVERS_NOTIFY'
ORA-06512: at "AM.AW5", line 1341
ORA-06512: at "AM.AW5", line 1270
ORA-06512: at line 1

When this error occurs, Agents may not failover to the secondary RmiServer if the Primary is stopped or becomes unavailable.

Environment

Applications Manager version 9.6 and 9.6.1

Cause

needed to add exception to handle the STOP_QUEUE when there is an outstanding transaction

Resolution

Fixed in Applications Manager 9.6.2 and/or 9.7

Workaround:

A fix will be included in Applications Manager version 9.6.2 and/or 9.7

Workaround for 9.6 and 9.6.1:

A patch can be applied by running two sql scripts that are attached to this article, am_aq_aq_fix.sql and aw_operation_body_aq_fix.sql.

1. Download attached aq_fix.zip
2. Extract am_aq_aq_fix.sql and aw_operation_body_aq_fix.sql from zip and move to the master's $AW_HOME/sql directory
3. Stop Applications Manager processes
4. From the $AW_HOME/sql directory, run a sqlplus command and connect to the AM database as the AM Oracle User
5. Run the commands from the sqlplus command line and exit:
   
   @am_aq_aq_fix.sql
   @aw_operation_body_aq_fix.sql
   @aw_post_checks.sql  (this already exist in sql directory and needs to run after the above to recreate AQ)
   
   
6. Restart Applications Manager processes

To revert changes:

1. From the same sql directory, follow the same steps replacing the commands with the below commands:
   
   @am_aq.sql
   @aw_operation_body.sql
   @aw_post_checks.sql  (this already exist in sql directory and needs to run after the above to recreate AQ)