Cleanup Message Queue (MQ) Gateway Agent

Message queue terminated as DB was terminated unexpectedly. Once db is up, MQ won't start with following error.

SQL> exec dbms_mgwadm.shutdown
PL/SQL procedure successfully completed.

SQL> exec dbms_mgwadm.startup
BEGIN dbms_mgwadm.startup; END;
*
ERROR at line 1:
ORA-32846: Messaging Gateway agent cannot be started; status is BROKEN
ORA-06512: at "SYS.DBMS_MGWADM", line 88
ORA-06512: at "SYS.DBMS_MGWADM", line 66
ORA-06512: at line 1

No new LOG is genrating while restarting MQ.

SQL> select agent_status,agent_ping, last_error_date, last_error_msg, max_connections, max_memory, max_threads, agent_status,agent_ping from mgw_gateway;

AGENT_STATUS AGENT_PING LAST_ERROR_MSG MAX_CONNECTIONS AGENT_STATUS AGENT_PING
BROKEN UNREACHABLE ORA-32845: Messaging Gateway agent is already running 100 64 1 BROKEN UNREACHABLE

SOLUTION:

1.    Shutdown Message Queue

exec dbms_mgwadm.shutdown

2.    Check for invalid packages -

select owner, object_name, object_type  from dba_objects where status = 'INVALID'

3.    Recompile any invalid objects.  Either run UTLRP.SQL or recompile individually.

The dbms_mgwadm and mgwi_amdin packages should be valid.

4.    If the Messaging Gateway shutdown still does not work, then issue the following to cleanup the gateway -

Exec DBMS_MGWADM.CLEANUP_GATEWAY(1);

OR

exec dbms_mgwadm.cleanup_gateway(dbms_mgwadm.CLEAN_STARTUP_STATE);

This runs recovery tasks that set the gateway to a known state when the gateway agent has crashed or some other abnormal event occurs so that the gateway cannot be started.

NOTE: This command will only work if a previous shutdown command has been attempted otherwise error messages will be displayed.