Friday, November 25, 2011

How to fix "schema has already been upgraded" issue

I had similar problem when applying last oracle SOA/BPM FP.
To patch db schema I used psa tool:

%ORACLE_HOME%\bin>psa -dbType Oracle -dbConnectString  //dbhost:1521/tst -dbaUserName sys -schemaUserName MY_SOAINFRA

Psa utility threw an error but after removed cause of this error, still I could not run upgrade procedure once again. The cause was next error:

[2011-11-24T17:23:37.467+01:00] [SOAINFRA] [ERROR] [] [upgrade.SOAINFRA] [tid: 10] [ecid: 0000JFRKfjmCKu85njt1iZ1EnvBW000000,0] SOAINFRA schema has already been upgraded.

It looked like schema version was altered despite the fact that the upgrade script was not executed properly.

To check actually version of my SOA schema I used following query:

SELECT version, status FROM schema_version_registry WHERE owner='MY_SOAINFRA';

In the fact, schema version was altered from version to

To reset this invalid version value i used following clause:

Update SCHEMA_VERSION_REGISTRY set version='' where comp_id='SOAINFRA' and owner='MY_SOAINFRA'

No comments:

Post a Comment