How to Resolve: Upgrade of the Oracle database keeps failing due to connection issue when installing a Maximo 7.5 fix pack

Body

I hope that this will be helpful when you experience this kind of issue during an installation of a Maximo 7.5 fix pack. The title of this blog speaks for its content and this is can be a very specific issue. This issue happens when you are trying to install Maximo 7.5.0.x fix pack in an environment that has Oracle database.

For a brief background of the environment, this is an environment where you have a separate Maximo Administrative Workstation and a separate Oracle Database Server.

The installation issue that we are going to troubleshoot is encountered during upgrade the database of Maximo (during the run of the updatedb command). In the event that the updatedb fails, you will see error messages like these:

  dbchange#ScriptFailure;  A statement failed to execute inside a freeform statement;  sql#17002;  Io exception: Software caused connection abort: recv failed  psdi.dbmanage.MXExceptionWithDefault: scriptrun#freeformfail          at psdi.dbmanage.statement.FreeFormStatement.executeFreeFormStatement(Unknown Source)          at psdi.dbmanage.statement.FreeFormStatement.run(Unknown Source)          at psdi.dbmanage.statement.Script.runStatement(Unknown Source)          at psdi.dbmanage.statement.Script.run(Unknown Source)          at psdi.dbmanage.ScriptRun.run(Unknown Source)          at psdi.dbmanage.Utility.runDbcScriptLastGoodLine(Unknown Source)          at psdi.tools.UpdateDB.runDbcScript(Unknown Source)          at psdi.tools.UpdateDB.runDbcFileInfo(Unknown Source)          at psdi.tools.UpdateDB.runScripts(Unknown Source)          at psdi.tools.UpdateDB.startProcess(Unknown Source)          at psdi.tools.UpdateDB.startProcess(Unknown Source)          at psdi.tools.UpdateDB.loadScriptInfo(Unknown Source)          at psdi.tools.UpdateDB.main(Unknown Source)

OR

  dbchange#ScriptFailure;  Closed Connection  java.sql.SQLException: Closed Connection          at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)          at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)          at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)          at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)          at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)          at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)          at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:3404)          at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:3438)

Troubleshooting:

1. Check the connection between the two servers.

I have described the environment having two servers, one for the Maximo Administrative Workstation and one for the Oracle Database Server. Check the physical connection between the two servers. Make sure that the two servers can communicate and can ping with each other. If the issue still persists after checking that there is no problem with the connection, proceed with number 2.

NOTE: If you had connection issues detectable by pinging the two servers, the updated command would not reach this point of failure. Please also check if there is a possibility of a firewall dropping the connection between the Oracle client and the database server.

2. Test updatedb locally.

To test the updatedb locally, install an Oracle database in your Maximo administrative workstation and then create a new database so that you can import your existing Maximo 7.5 database to this newly created one. Verify and modify that the maximo.properties file has the new database information. Run updatedb!

If the updatedb will be successful after running it locally, then one of the reason why updatedb is failing earlier was because of an outdated Oracle driver and it would be a good idea to update the Oracle driver to see if it will resolve the issue.

NOTE: This is a complex and time consuming process so we recommend that you only perform this if you have a lot of time to troubleshoot the issue.

3. Check your current Oracle Driver if it is outdated.

To check your current Oracle Driver version, follow the instructions below:

a) Download and expand TDVclass.zip and move it to the following directory: \IBM\SMP\maximo\tools\maximo\classes\psdi\script\en
b) Download and expand TDVsql.zipand move it in the following directory:\IBM\SMP\maximo\tools\maximo\en\script
c) Open a command prompt and run the following command: \IBM\SMP\maximo\tools\maximo\internal>runscriptfile -fTDV
 
Note: This command will check what Oracle driver is currently in your environment. A log file will be created for your reference: \IBM\SMP\maximo\tools\maximo\log\MXServer_RUNSCRIPT_TDV.log

How to know if your Oracle JDBC Driver is outdated?
For example the output of the “runscriptfile –fTDV” is the following:
Log file: MXServer_RUNSCRIPT_TDV.log
getDatabaseProductName(): Oracle
getDatabaseProductVersion(): Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
getDatabaseMajorVersion(): 11
getDatabaseMinorVersion(): 2
getDriverName(): Oracle JDBC driver
getDriverVersion(): 11.1.0.7.0-Production
getDriverMajorVersion() : 11
getDriverMinorVersion() : 1

The green colored text shows the Oracle database server version which: 11.0.2.

The blue colored text shows the Oracle JDBC driver version: 11.0.1.

In this example, you should download the latest Oracle JDBC driver version for Oracle 11.0.2 to match the database server.

If your Oracle Driver version is outdated, proceed with number 4 to update your Oracle driver.

4. Download and use the latest Oracle driver for your Oracle database version.

a) Move the IBM\SMP\maximo\applications\maximo\lib\oraclethin.jar in any location outside the \IBM\SMP folder to create a backup.
b) Delete the file c:\IBM\SMP\maximo\tools\maximo\iscintegration\libs\oraclethin.jar
c) Download the latest available client jdbc driver for your Oracle database version:

For example your database version is Oracle 11.2.0.4.

i) Go to the Oracle driver download website.

ii) And look under:
       Oracle Database 11g Release 2 JDBC Drivers
       Oracle Database 11g Release 2 (11.2.0.4) JDBC Drivers 

       Download the latest Oracle driver (e.g. ojdbc6.jar) available.

d) Copy the ojdbc6.jar to the following directories and then rename them to oraclethin.jar.
        \IBM\SMP\maximo\applications\maximo\lib
        \IBM\SMP\maximo\tools\maximo\iscintegration\libs

After updating your Oracle driver, restore your database to the state prior to running the updatedb command and then go ahead and try to run the updatedb command and hopefully it will now complete to a success. If the upgrade of the database will be successful, you can now build and redeploy the Maximo EAR file and use your upgraded application.

But remember that this is only one way to resolve the issue and another solution may work for you. Well, I think that's it for today's entry! I hope that this will be helpful when you encounter a similar issue described to this blog entry. Thanks for reading!