Discussion
Cognizant
IN
Last activity: 6 Apr 2018 19:26 EDT
ORA-00904 Invalid identifier error when running upgrade.sh for 7.1.9
We are facing an issue while performing a "rules upgrade" from 6.2.1 to 7.1.9 ( Unix + Oracle setup ).
Our issue is similar to what has been published in the following articles ...
https://pdn.pega.com/support-articles/ora-00904-invalid-identifier-reported-during-migrate-step (7.1.7)
https://pdn.pega.com/support-articles/invalid-identifier-error-when-running-generateddl-script (7.1.9)
We have already granted "all the privileges and a role" as specified in the 7.1.9 upgrade guide yet we got this error.
Does every "Version Upgrade / ML Update" to 7.1.9 requires granting execute privilege for DBMS_METADATA package.
Kindly post why this privilege is relevant to the upgrade? Was this privilege left out from the 7.1.9 upgrade guide ?
Please find the error we received below ...
_callDDLUtilityUpgrade:
[echo] Call DDL Generation Upgrade Utility...
[echo] /apps/abc/p7/scripts/lib/xercesImpl.jar;/apps/abc/p7/scripts/lib/xml-apis.jar;/apps/abc/p7/scripts/lib/resolver.jar;
[echo] archive_dir: /apps/abc/p7/scripts/lib
[java] 2016-04-28 03:19:53,777 ( SchemaGenerator) SEVERE - Error generating ddl for oracledate, PEGA_RULES
[java] ORA-00904: : invalid identifier
[java]
[java]
[java] java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier
[java]
[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
[java] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
[java] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
[java] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
[java] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
[java] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
[java] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
[java] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
[java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
[java] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
[java] at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
[java] at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
[java] at com.pega.pegarules.data.ddl.SchemaManager.readObjectBodies(Unknown Source)
[java] at com.pega.pegarules.data.ddl.SchemaManager.readViews(Unknown Source)
[java] at com.pega.pegarules.data.ddl.SchemaManager.<init>(Unknown Source)
[java] at com.pega.pegarules.data.ddl.SchemaManager.<init>(Unknown Source)
[java] at com.pega.pegarules.data.ddl.SchemaGenerator.saveCurrentSchemaState(SchemaGenerator.java:547)
[java] at com.pega.pegarules.data.ddl.SchemaGenerator.main(SchemaGenerator.java:1638)
generate_upgrade_ddl:
Check zOS Upgrade:
[echo] ***setting resume checkpoint***
[propertyfile] Updating property file: /apps/abc/p7/scripts/resume.properties
Schema Upgrade:
Schema Upgrade Inner:
[java] 2016-04-28 03:19:54,350 ( SQLLoader) INFO - Starting logging to : 'logs/SQLinstaller.log'
[java] Exception in thread "main" com.pega.pegarules.install.SQLLoader$SQLLoaderException: Invalid directory: /apps/abc/p7/scripts/../schema/generated/oracledate/update/statements
[java] at com.pega.pegarules.install.SQLLoader.fail(SQLLoader.java:359)
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:110)
Pegasystems Inc.
IN
The DBMS_METADATA package is used to retrieve metadata from the PRPC 6.x database dictionary as XML and creates the delta DDL between OOTB Pega 7 shipped schema nad Pega 6 schema xmls.
Yes this privilege is required for generating the DDL any of the upgrades/updates.
Cognizant
IN
Hello Dhev,
The 7.3.0 upgrade guide does not have any reference to the privilege EXECUTE DBMS_METADATA PACKAGE
We are preparing to perform a 6.2.2 upgrade to 7.3.0
Will we face the issue again ?
-Vignesh
Updated: 28 Apr 2016 10:35 EDT
Cognizant
IN
Thanks Dhev.
I suggest we update the P7.1.9 Upgrade Guide accordingly and republish it in PDN.