2
Installing Messaging Gateway

1. SID_NAME: provide the SID specified in the net service name in tnsnames.ora, for example, "mgwextproc".

2. ORACLE_HOME: provide your ORACLE_HOME directory.

3. PROGRAM: provide the name of the external procedure agent, which is "extproc".

4. ENVS: set up the LD_LIBRARY_PATH environment needed for the external procedure to run.

   The LD_LIBRARY_PATH must contain the following paths:

   [JDK_HOME]/jre/lib/[PLATFORM_TYPE]

   [ORACLE_HOME]/lib

   Replace the bracketed item with the appropriate, spelled-out value (using $ORACLE_HOME does not work, for example). PLATFORM_TYPE is your platform type, for example, sparc.

Adding Static Service Information for the Listener: Example

# Add a SID_DESC
SID_LIST_LISTENER= (SID_LIST=
(SID_DESC =
   (SID_NAME= mgwextproc)
   (ENVS="LD_LIBRARY_PATH=/private/oracle/orcl9i/Apache/jdk/jre/lib/
      sparc:/private/oracle/orcl9i/lib")
   (ORACLE_HOME=/private/oracle/orcl9i)
   (PROGRAM = extproc))
      .
      .
      .

Modifying tnsnames.ora for the External Procedure

For the external procedure, configure a net service name MGW_AGENT in tnsnames.ora whose connect descriptor matches the information configured in listener.ora. The net service name must be MGW_AGENT (this value is fixed). The KEY value must match the KEY value specified for the IPC protocol in listener.ora. The SID value must match the value specified for SID_NAME of the SID_DESC entry in listener.ora.

Modifying tnsnames.ora to Create the MGW_AGENT Net Service Name: Example

MGW_AGENT = 
(DESCRIPTION= 
   (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC))) 
   (CONNECT_DATA= (SID=mgwextproc) (PRESENTATION=RO)))

Modifying mgw.ora for the External Procedure

The Messaging Gateway configuration file $ORACLE_HOME/mgw/admin/mgw.ora is a TEXT file that the gateway external procedure uses to get further configuration information. Copy $ORACLE_HOME/mgw/admin/sample_mgw.ora to mgw.ora and modify it according to your situation.

The following procedure sets environment variables and other parameters:

1. Set environment variables for the external procedure to start the gateway agent.

   The ENVS parameter in listener.ora sets only enough environment to run the external procedure. The environment needed to start the gateway agent is set in mgw.ora.

   Set the following environment variables:

   1. LD_LIBRARY_PATH must contain at least the following paths.
      • [JDK_HOME]/jre/lib/[PLATFORM_TYPE]

      • [ORACLE_HOME]/rdbms/lib

      • [ORACLE_HOME]/oracle/lib

      • [ORACLE_HOME]/mgw/lib

      • Any additional libraries needed for the Messaging Gateway agent to access non-Oracle messaging systems, for example, the MQSeries libraries, must be included in LD_LIBRARY_PATH.

      Replace the brackets with appropriate, spelled-out values (using $ORACLE_HOME does not work, for example). PLATFORM_TYPE is your platform type, for example, sparc.

   2. CLASSPATH must contain at least the following:
      • Messaging Gateway classes:

        [ORACLE_HOME]/mgw/classes/mgw.jar

      • JDK internationalization classes: [JDK_HOME]/jre/lib/i18n.jar

      • JDK runtime classes: [JDK_HOME]/jre/lib/rt.jar

      • Oracle JDBC classes: [ORACLE_HOME]/jdbc/lib/classes12.zip

      • Oracle internationalization classes: [ORACLE_HOME]/jdbc/lib/nls_charset12.zip

      • SQLJ classes

      • [ORACLE_HOME]/sqlj/lib/translator.zip

      • [ORACLE_HOME]/sqlj/lib/runtime.zip

      • Any additional classes needed for Messaging Gateway to access non-Oracle messaging systems, for example, MQSeries classes

      Replace the brackets with appropriate, spelled-out values (using $ORACLE_HOME does not work, for example).

2. Set the log_directory and log_level parameters.

   Setting these parameters is not required. They influence the logging of Messaging Gateway. If they are not set, the default values are used. For log_directory, the default value is $ORACLE_HOME/mgw/log. For log_level, the default value is 0 for basic logging.

The mgw.ora File: Example

#an example of mgw.ora file
log_directory=/private/mgwlog
log_level=2
set CLASSPATH=<proper value>
set LD_LIBRARY_PATH=<proper value>

Modifying tnsnames.ora for the Gateway Agent's Connection to the Database

Configure a net service name in tnsnames.ora for the gateway agent to connect back to the database. Use this service name for the procedure in "Configuring Messaging Gateway Connection Information".

Modifying tnsnames.ora to Create a Service Name: Example

AGENT_SERVICE = 
      (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC)) 
      (CONNECT_DATA= (SID=mgwsid)))

Creating an Administration User

To perform gateway administration work, a database user with MGW_ADMINISTRATOR_ROLE privileges must be created.

Creating an Administration User: Example

CREATE USER <admin_user> IDENTIFED BY <admin_password>;
GRANT CONNECT, RESOURCE to <admin_user>;
GRANT MGW_ADMINISTRATOR_ROLE to <admin_user>;

Creating an Agent User

To establish the gateway agent's connection back to the database, a database user with MGW_AGENT_ROLE privileges must be created.

Creating an Agent User: Example

CREATE USER <agent_user> IDENTIFED BY <agent_password>;
GRANT CONNECT, RESOURCE to <agent_user>;
GRANT MGW_AGENT_ROLE to <agent_user>;

Configuring Messaging Gateway Connection Information

After the agent user is created, the administration user uses DBMS_MGWADM.DB_CONNECT_INFO to configure Messaging Gateway with the user name, password, and database connect string used by the gateway agent to connect back to database. Refer to "Modifying tnsnames.ora for the Gateway Agent's Connection to the Database".

Using DBMS_MGWADM.DB_CONNECT_INFO: Example

connect <admin_user>/<admin_password>
exec dbms_mgwadm.db_connect_info('<agent_user>','<agent_password>', '<agent_
database>');

Installation Verification

The following procedure verifies the installation and includes a simple startup and shutdown of the Messaging Gateway agent.

1. Start the database listeners.

   Start the listener for the external procedure and other listeners for the regular database connection.

2. Test the database connect string for the gateway agent user.

   Run sqlplus <agent_user>/<agent_password>@<agent_database>.

   If successful, the gateway agent is able to connect to the database.

3. Start the gateway agent.
   1. Connect as <admin_user> and call DBMS_MGWADM.STARTUP to start the gateway agent.

   2. Using the MGW_GATEWAY view, wait for AGENT_STATUS to change to RUNNING and AGENT_PING to change to REACHABLE.

4. Shut down the gateway agent.
   1. Connect as <admin_user> and call DBMS_MGWADM.SHUTDOWN.

   2. Using the MGW_GATEWAY view, wait for AGENT_STATUS to change to NOT_STARTED.

Messaging Gateway Uninstall

Before uninstalling Messaging Gateway, do the following:

1. Shut down Messaging Gateway.

2. Remove any user-created queues whose payload is a Messaging Gateway canonical type (for example, MGW_BASIC_MSG_T).

The following procedure uninstalls Messaging Gateway:

1. Using SQL*Plus, as user SYS as SYSDBA, run catnomgw.sql, located in the $ORACLE_HOME/mgw/admin directory.

   This drops the database objects used by Messaging Gateway, including the roles, tables, views, packages, object types, and synonyms.

2. Run Oracle Universal Installer to uninstall Messaging Gateway.

   This removes the Messaging Gateway files.

3. (Optional) Change listener.ora and tnsnames.ora back to the original state.

Troubleshooting

The following problems and solutions may help you during installation troubleshooting.

Problem

Messaging Gateway has been started but the MGW_GATEWAY view always shows an AGENT_STATUS value of START_SCHEDULED.

Explanation

Messaging Gateway uses job queues in the Oracle database to start the agent process. SNP background processes execute jobs in job queues. At least one SNP process must be configured to execute queued jobs in the background. The gateway job is scheduled to execute immediately, but will not do so until an SNP process is available. If the gateway status remains START_SCHEDULED for an extended period of time, it may indicate that the database instance has been started with no or too few SNP background processes.

Solution

Verify that the database instances have been started, with one SNP process for Messaging Gateway.

init.ora parameters:

• JOB_QUEUE_PROCESSES specifies the number of job queue processes per instance.

• JOB_QUEUE_INTERVAL specifies the interval between wake-ups for SNP processes.

Dynamic parameters: ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>;

Problem

Messaging Gateway has been started but the MGW_GATEWAY view shows an AGENT_STATUS value of NOT_STARTED, and LAST_ERROR_MSG has no value.

Explanation

This may indicate that the gateway agent process encountered a fatal error and has terminated. Whenever the agent process terminates, the agent status is set to NOT_STARTED and the queued job used to start the gateway agent ends.

Normally, the agent status will progress through the following values when the gateway is started:

1. NOT_STARTED

2. START_SCHEDULED

3. STARTING

4. INITIALIZING

5. RUNNING

Solution

Check the Messaging Gateway log file for errors. By default, the log file is in the $ORACLE_HOME/mgw/log directory.

Problem

Messaging Gateway has been started but the MGW_GATEWAY view shows a nonnull error message for LAST_ERROR_MSG.

Explanation

The MGW_GATEWAY view provides status information about the gateway agent. A nonnull value for LAST_ERROR_MSG indicates that the listener was unable to start the gateway agent process or that some other error occurred when attempting to call the external procedure that starts the gateway agent.

Solution:

1. Verify that the listener has been started.

2. Verify that the values specified in tnsnames.ora and listener.ora are correct.

   Incorrect or mismatched values will prevent the listener from starting the gateway agent process.

   For example, if LAST_ERROR_MSG is:

   ORA-28575: unable to open RPC connection to external procedure agent

   Then check the following:

   • tnsnames.ora must have a net service name entry named MGW_AGENT.

   • The SID value specified for CONNECT_DATA of the MGW_AGENT net service name in tnsnames.ora must match the SID_NAME value of the SID_DESC entry in listener.ora.

   • If the MGW_AGENT net service name is set up for an IPC connection, the KEY values for ADDRESS in tnsnames.ora and listener.ora must match.

   • Other values in tnsnames.ora or listener.ora may be incorrect or not match.

3. Correct the problem indicated by the error and start Messaging Gateway.