Use the ecxver.sql script to verify the health of your Oracle XML Gateway installation. It can be run at any time. Outlined below are instructions for executing the script and interpreting the output.
Log in to your database applications account and execute the following:
SQL> @$ECX_TOP/patch/115/sql/ecxver.sql
The output of the script will appear on your screen.
| Component | OBJECT_NAME | LOCKED_MODE |
|---|---|---|
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| OTA | ECX_OUTQUEUE | Normal |
| WEBMETHODS | ECX_OUTQUEUE | Normal |
| WEBMETHODS | ECX_OUTQUEUE | Normal |
| WEBMETHODS | ECX_OUTQUEUE | Normal |
| WEBMETHODS | ECX_OUTQUEUE | Normal |
| WEBMETHODS | ECX_OUTQUEUE | Normal |
ECX_UTL_XSLT_DIR Profile : /sqlcom/log/ecx115 ECX_OAG_LOGICALID Profile : www.oracle.com ECX_SERVER_TIMEZONE Profile: ECX_SYS_ADMIN_EMAIL Profile: fname_lname@oracle.com ECX_UTL_LOG_DIR Profile : /sqlcom/log/ecx115 ECX_XML_VALIDATE_FLAG Profile: Y ECX_XML_MAXIMUM_SIZE Profile: 2000000 utl_file_dir: /sqlcom/log/ecx115 Oracle XML Parser: 2.0.2.9.0 Production Parser Version OK
--------------------------------------- XML Gateway Status Summary ---------------------------------------
| Log Profile/utl_file_dir | OK |
| XML Parser Version | OK |
| All ECX Objects Valid? | OK |
| All XML Parser Objects Valid? | OK |
| webMethods Running? | OK |
| OTA Running? | OK |
| Total Messages on Outbound Queue | 6 |
| OTA Msgs on Outbound Queue | 4 |
| webMethods Msgs on Outbound Queue | 1 |
| Messages on Inbound Queue | 8 |
---------------------------------- End of Summary ----------------------------------
The following table explains each line of the example output shown above. Some of the output is informational only and does not require further action.
| Output | Explanation/Troubleshooting Tip |
|---|---|
| Transport Software | The "LOCKED_MODE" value should be "Normal", but may appear as "DEADLOCK". Deadlocks are normal if they remain for a short period of time. If a deadlock persists for more than 10 minutes, there may be a serious problem. Note: webMethods is used in Oracle Exchange environments only. It is not used in ERP environments |
| System Profiles: ECX_UTL_XSLT_DIR ECX_OAG_LOGICALID ECX_SERVER_TIMEZONE ECX_SYS_ADMIN_EMAIL ECX_UTL_LOG_DIR ECX_XML_MAXIMUM_SIZE ECX_XML_VALIDATE_FLAG | The value associated with each XML Gateway System Profile is displayed for review. |
| ERROR: Parser Version Wrong | The XML Parser version associated with the environment is displayed. XML Parser version 2.0.2.9 or higher is required. |
| ERROR: Some Invalid ECX Objects | Informational message indicating some invalid ECX database objects were identified. |
| Log Profile/utl_file_dir | If value displayed is anything other than "OK", verify the following: The physical directories associated with the ECX_UTL_XSLT_DIR and ECX_UTL_LOG_DIR system profiles must be assigned to the utl_file_dir parameter in the INIT.ORA file. Also, make sure the physical directories are write-enabled. If necessary, make the changes and bounce the database. |
| XML Parser Version | If value displayed is "FAIL", upgrade XML Parser to version 2.0.2.9 or higher. The XML Parser version mentioned here is from the java component. This value is used to find out only whether the previous version is installed in the environment. The versions of plxml parser (plsql component of XML Parser) and corresponding PL/SQL wrappers created by load.sql could not be determined. Therefore, please check for any invalid XML Parser objects and invalid ECX objects. Oracle XML Gateway will not function properly, if the installation of XML Parser is not correct. If you get "ORA-00932: inconsistent datatypes" error, then you need to reinstall XML Parser (java component and PL/SQL component) in the environment. Only XML Parser 2.0.2.9 is certified by Oracle E-Business Suite. |
| All ECX Objects Valid? | If value displayed is "NO", the invalid objects must by recompiled. Recompile the invalid objects or reapply the XML Gateway patch to resolve the invalid ECX database objects. |
| All XML Parser Objects Valid? | If value displayed is "NO", the XML Parser must be reinstalled. Reinstall the XML Parser to resolve the invalid XML Parser objects. |
| webMethods Running? | If value displayed is anything other than "OK", restart webMethods. For an Oracle-hosted environment: contact Operations For self-hosted environments: contact webMethods Note: webMethods is used in Oracle Exchange environments only. It is not used in ERP environments. |
| OTA Running? | If value displayed is anything other than "OK", restart Oracle Transport Agent (OTA). Follow instructions in OTA patch to start the Apache web server. Ensure the required JSERV properties are set. |
| Total Messages on Outbound Queue | Number reported represents the number of messages on the outbound queue awaiting processing by OTA or webMethods. If number is unusually high, check the individual totals in the OTA and webMethods queues. |
| OTA Messages on Outbound Queue | Number reported represents the number of messages on the outbound queue awaiting processing by OTA. This is a subset of the total. If number is unusually high, ensure OTA is running. |
| webMethods Messages on Outbound Queue | Number reported represents the number of messages on the outbound queue awaiting processing by webMethods. This is a subset of the total. If number is unusually high, ensure webMethods is running. |
| Messages on Inbound Queue | Number of messages on inbound queue (informational). As long as the agent for the inbound queue is enabled, the agent listener is running, and OTA/webMethods is running, these messages will be processed. See Start Agent Listeners for information on enabling agents and starting agent listeners. |
Additional Information: There are some common mistakes while using webMethods in Oracle Exchange 6.2.2 environment:
Shutdown webMethods first before shutting down the Oracle Exchange database and start webMethods after starting up the database. Otherwise messages will not be delivered because of stale database connections.
Set NLS_LANG for webMethods to AMERICAN_AMERICA.UTF8.
Values for dbUser, dbPass and dbinstance have not been updated in the exchange_config.prop file.
Packages have been copied in the replicate/inbound directory but not loaded into webMethods. Login into the webMethods and verify the version of packages (Exchange.zip 115.14 and OracleExchange.zip 115.14).
If you see an error message like following on the TxnMonitor.jsp - 'Job Failed: [Tx] Expired transaction', it indicates that either URL provided by user is not correct or the customer's webMethods Server is down.
Any 'Guranteed Delivery disabled for webMethods' error should be resolved by the standard instructions given by webMethods support to reset the guranteed delivery. The error might be due to wrong version of webMethods.
In Oracle Exchange 6.2.2 environment, you will not receive XML messages if transactions are not set up. However, your system administrator should get an email indicating this error.
Before running the patch/115/sql/ECXTEST.sqlscript to test Oracle XML Gateway, you need to run the patch/115/sql/ECXLGINI.sql script which does the necessary initialization for fnd logging for that particular sql session first. This step is necessary even if you have Enabled FND Logging for ECX from configuration screens.
Execute the following SQL program as the application user in SQL*Plus to test Oracle XML Gateway:
SQL> $ECX_TOP/patch/115/sql/ECXTEST.sql
This creates an XML file and log file in the FND_LOG infrastructure (the fnd_log_attachment table).
This is applicable if the database is upgraded from 8.1.6 to 8.1.7x. You may receive the following error messages in the FND_LOG:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission */OUT9.xml write) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(APPS|PolicyTableProxy(APPS)). Exit ECX_OUTBOUND.PROCESS_OUTBOUND_DOCUMENTS
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission */OUT9.xml write) has not been granted by dbms_java.grant_permission to SchemaProtect ionDomain (APPS|PolicyTableProxy(APPS)) at ECX_OUTBOUND.PROCESS_OUTBOUND_DOCUMENTS Exit ECX_OUTBOUND.GETXML
This is because the Java security has not been set properly in the database. Use the following instructions to correct this error:
SQL> exec dbms_java.grant_permission ('PUBLIC', 'java.io.FilePermission','<dir name>/*','write');
Note: The <dir name> is the ECX: Log File Path.
If this does not resolve the issue, follow these steps. Copy $ORACLE_HOME (8.1.6)/javavm/install/init_security.sql to a temp file. Replace the word call with exec (this is a known bug in the script). Login to the database as the SYS user and run the modified SQL script.
The ECXOTAPing.html file included with the Oracle Transport Agent (OTA) allows you to test the OTA installation and configuration by sending a "ping" to it. This ping only tests whether the OTA server is running and validates the username/password.
To use the ECXOTAPing.html, open a web browser and enter the following URL:
http://<applications apache url with port> OA_HTML/US/ECXOTAPing.htm
A web page will appear prompting you for the username and password. You must enter a valid applications username and password. Click the Ping button and you should see a status HTML page in your browser. If it is successful, the web page will look like the following:
STATUS_CODE 1000
STATUS_DESCRIPTION OK
MESSAGE_RECEIPT_ID
Note: If you received an error response (anything other than STATUS_CODE 1000), check the Apache error log file in the Apache_top/Apache/logs directory for more specific information on the error.
The ECXOTAInbound.html file included with the Oracle Transport Agent allows you to send XML documents inbound from a web browser. The document will be received by OTA and placed on the ECX_INBOUND queue. This HTML file is included for testing purposes and should not be used in a production environment. To use the ECXOTAInbound.html, open a web browser and enter the following URL:
http://<applications apache url with port> OA_HTML/US/ECXOTAInbound.htm
A web page will appear prompting you for the input parameters used by the OTA messaging protocol. Refer to the Oracle Transport Agent chapter for information on OTA messaging protocol parameters.
When sending any document through Oracle Transport Agent without explicitly mentioning the port number (even for default ports 80 and 443), this error is reported in the Apache error log file. After this error, OTA retries to send the document indicated by OXTAOutMaxAttempts parameter. Please specify the port number explicitly for the Outbound documents.
All the activities by Oracle Transport Agent is recorded in the ECX_OXTA_LOGMSG table. Please query the table to find more information about OTA errors.