SOA PSA Failed When Upgrading PROD_SOAINFRA schema While Upgrading OIM From R2PS1 To R2PS3

Server Details:

OIM Upgrade from Oracle Identity Manager 11g Release 2 (11.1.2.1.0) to 11g Release 2 (11.1.2.3.0).
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -64bit Producation

Error:

"[2016-09-16T00:16:46.793-04:00] [RCU] [ERROR] [] [upgrade.RCU.jdbcEngine] [tid: 13] [ecid: 0000LSlDRudATOLLuQH7iX1Nqr7V000004,0] Error encountered executing SQL statement  FileName: '/u01/oracle/middleware/Oracle_SOA1/rcu/integration/soainfra//sql/upgrade_soainfra_111161_111170_oracle.tsql' LineNumber: '2278' Script log file: null
[2016-09-16T00:16:46.794-04:00] [SOA] [ERROR] [] [upgrade.SOA.SOA1] [tid: 13] [ecid: 0000LSlDRudATOLLuQH7iX1Nqr7V000004,0] UPGAST-00221: unexpected error uprading schema
[2016-09-16T00:16:46.794-04:00] [SOA] [ERROR] [] [upgrade.SOA.SOA1] [tid: 13] [ecid: 0000LSlDRudATOLLuQH7iX1Nqr7V000004,0] [[
oracle.sysman.assistants.common.dbutil.SQLFatalErrorException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.onException(JDBCEngine.java:869)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:833)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:773)
        at oracle.sysman.assistants.common.dbutil.jdbc.OracleDDLStatement.execute(ANSISQLStatementType.java:711)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeNextSQLStatement(JDBCEngine.java:1468)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.parseNexecuteScript(JDBCEngine.java:1372)
        at oracle.ias.update.plugin.UpgradePlugin.executeJDBCEngineScript(UpgradePlugin.java:971)
        at oracle.ias.update.plugin.UpgradePlugin.executeJDBCEngineScript(UpgradePlugin.java:846)
        at oracle.ias.update.plugin.soa.SOAINFRAPlugin.upgrade_soainfra_one_patchset(SOAINFRAPlugin.java:390)
        at oracle.ias.update.plugin.soa.SOAINFRAPlugin.upgrade_soainfra(SOAINFRAPlugin.java:331)
        at oracle.ias.update.plugin.soa.SOAINFRAPlugin.upgrade(SOAINFRAPlugin.java:226)
        at oracle.ias.update.plugin.Plugin.upgrade(Plugin.java:352)
        at oracle.ias.update.plan.PlanStep.upgrade(PlanStep.java:294)
        at oracle.ias.update.UpgradeDriver.doUpgrades(UpgradeDriver.java:476)
        at oracle.ias.update.gui.UAUpgradeThread.run(UAUpgradeThread.java:41)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.runSqlStatement(JDBCEngine.java:1090)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.callRunSqlStatement(JDBCEngine.java:779)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:794)
        ... 13 more


"
----------------------------------------------------------------------------------------------------------------------

While upgrading the PROD_SOAINFRA schema its failed with above error.

Below steps need to be performed in order to resolve the issue.

1. Restore SOAINFRA from backup (the one taken before the upgrade) --No need to restore the other   schemas

2. Provide the below permission

Permissions:

grant dba to PROD_SOAINFRA;
grant execute on sys.dbms_lob to PROD_SOAINFRA;
CREATE OR REPLACE PUBLIC SYNONYM "SCHEMA_VERSION_REGISTRY" FOR "SYSTEM"."SCHEMA_VERSION_REGISTRY";
GRANT execute on utl_file to PROD_SOAINFRA;
GRANT execute on sys.dbms_lob to PROD_SOAINFRA;
GRANT EXECUTE ON DBMS_LOCK TO PROD_SOAINFRA;
GRANT CREATE JOB TO PROD_SOAINFRA;
GRANT CREATE EXTERNAL JOB TO PROD_SOAINFRA;

grant dba to FMW;
grant execute on DBMSLOB to FMW with grant option;
grant execute on DBMS_OUTPUT to FMW with grant option;
grant execute on DBMS_STATS to FMW with grant option;
grant execute on sys.dbms_aq to FMW with grant option;
grant execute on sys.dbms_aqadm to FMW with grant option;
grant execute on sys.dbms_aqin to FMW with grant option;
grant execute on sys.dbms_aqjms to FMW with grant option;
grant execute on sys.dbms_aqadm to FMW with grant option;
grant execute on sys.dbms_aq to FMW with grant option;
grant execute on utl_file to FMW with grant option;
grant execute on dbms_lock to FMW with grant option;
grant select on sys.V$INSTANCE to iamupgrade with grant option;
grant select on sys.GV$INSTANCE to iamupgrade with grant option;
grant select on sys.V$SESSION to iamupgrade with grant option;
grant select on sys.GV$SESSION to iamupgrade with grant option;
grant select on dba_scheduler_jobs to FMW with grant option;
grant select on dba_scheduler_job_run_details to FMW with grant option;
grant select on dba_scheduler_running_jobs to FMW with grant option;
grant select on dba_aq_agents to FMW with grant option;
grant execute on sys.DBMS_SHARED_POOL to FMW with grant option;
grant select on dba_2pc_pending to FMW with grant option;
grant select on dba_pending_transactions to FMW with grant option;
grant execute on DBMS_FLASHBACK to FMW with grant option;
grant execute on dbms_crypto to FMW with grant option;
grant execute on DBMS_REPUTIL to FMW with grant option;
grant execute on dbms_job to FMW with grant option;
grant select on pending_trans$ to FMW with grant option;
grant select on dba_scheduler_job_classes to FMW with grant option;
grant select on SYS.DBA_DATA_FILES to FMW with grant option;
grant select on SYS.V$ASM_DISKGROUP to FMW with grant option;
grant select on v$xatrans$ to FMW with grant option;
grant execute on sys.dbms_system to FMW with grant option;
grant execute on DBMS_SCHEDULER to FMW with grant option;
grant select on dba_data_files to FMW with grant option;
grant execute on UTL_RAW to FMW with grant option;
grant execute on DBMS_XMLDOM to FMW with grant option;
grant execute on DBMS_APPLICATION_INFO to FMW with grant option;
grant execute on DBMS_UTILITY to FMW with grant option;
grant execute on DBMS_SESSION to FMW with grant option;
grant execute on DBMS_METADATA to FMW with grant option;
grant execute on DBMS_XMLGEN to FMW with grant option;
grant execute on DBMS_DATAPUMP to FMW with grant option;
grant execute on DBMS_MVIEW to FMW with grant option;

3. Run PSA again

Ref: https://docs.oracle.com/middleware/11119/core/PATCH/patch_set_assistant.htm#PATCH678

Thanks,
Arihant