Errors Encountered if OIM 11g (11.1.2.3.0) Setup with JMS Persistent Store is Database Based Instead of File Based

In a ready-to-use OIM 11g (11.1.2.3.0) setup, JMS persistent store is typically file based. However, you can move the JMS persistent store to DB, if required.

If the JMS persistent store is database based in the OIM 11g (11.1.2.3.0) setup prior to one-hop upgrade, then after the one-hop upgrade process, you will encounter errors around the JMS resources, such as unavailability of JMS queues, and so on.

The OIM and SOA server logs will show errors similar to the following:

<Error> <Cluster> <BEA-000179> <WLS_SOA1 failed while altering its lease.
Could not acquire lease [service.WLS_SOA1] by [-4552382865043770721/WLS_SOA1]
    at weblogic.cluster.singleton.DatabaseLeasingBasis.updateOrInsertLease(DatabaseLeasingBasis.java:204)
    at weblogic.cluster.singleton.DatabaseLeasingBasis.acquire(DatabaseLeasingBasis.java:133)
    at weblogic.cluster.singleton.LeaseManager.tryAcquire(LeaseManager.java:129)
    at weblogic.cluster.migration.MigratableGroup.activate(MigratableGroup.java:424)
    at weblogic.cluster.migration.MigrationManagerService.privateRegister(MigrationManagerService.java:379)
    Truncated. see log file for complete stacktrace
Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:510)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:462)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1105)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:551)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
    Truncated. see log file for complete stacktrace
Caused By: Error : 942, Position : 21, Sql = SELECT INSTANCE FROM ACTIVE WHERE ( TIMEOUT >= SYS_EXTRACT_UTC(SYSTIMESTAMP)) AND SERVER = :1   AND DOMAINNAME='IAMGovernanceDomain' AND CLUSTERNAME='SOA_Cluster', OriginalSql = SELECT INSTANCE FROM ACTIVE WHERE ( TIMEOUT >= SYS_EXTRACT_UTC(SYSTIMESTAMP)) AND SERVER = ?  AND DOMAINNAME='IAMGovernanceDomain' AND CLUSTERNAME='SOA_Cluster', Error Msg = ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:514)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:462)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1105)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:551)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
    Truncated. see log file for complete stacktrace

When performing different use-cases such as self registration, user creation, and so on, the following error message will be displayed on the OIM user interface when the operation fails:

Queue not available

Solution

To resolve this issue:

1. Stop all the servers of the newly installed OIM 12c (12.2.1.4) setup.
2. From the <12c (12.2.1.4)_prefix>_WLS_RUNTIME schema of the newly installed OIM 12c (12.2.1.4) setup, export the contents of the ACTIVE database table.
3. Import the above contents to the ACTIVE database table of the <11g (11.1.2.3.0)_prefix>_WLS_RUNTIME schema of the upgraded 11g (11.1.2.3.0) database table.
4. Restart all the servers of the newly installed OIM 12c (12.2.1.4) setup.