Cartridge upgrade

Hello,

I'm in a work to updrade our cartridge from 5.6.0.2 to 5.10.2. Following the upgrade guide found here: http://www.chemaxon.com/jchem/doc/admin/cartridge.html#upgrade I get the this error:

You have entered the following parameters:
==========================================

The path to the home directory of the old JChem version: /home/oracle/jchem-5.6.0.2
Name of the Oracle host.: localhost
Oracle listener port: 1521
Name of the Oracle instance: hts2
DBA login: SYSTEM
Name of the cartridge owner: jchem
The host name of the JChem Server: localhost
The port at which the JChem Server accepts connections: 1099
maximum memory allocated to the JChem Server process: 700m
Local TNS name of the Oracle instance.: hts2
Do you want to use a role for basic JChem Cartridge user privileges? (This role will be granted to jchem WITH ADMIN OPTION. DataVault users, please, answer with with "n"): y
The role for basic JChem Cartridge user privileges: JCC_BASIC_ROLE
Staging schema for the version: JCC_UPGR_STAGE
Rebuild jc_idxtype indexes: none

Proceed with the Upgrade? (y, n) [n]: y
INFO: Setting up staging schema...
INFO: Populating the staging schema with the objects of the new JChem Cartridge version...
INFO: Loading jar file /home/oracle/jchem-5.10.2/cartridge/jcart.jar into JCC_UPGR_STAGE's schema...
INFO: Loading PL/SQL script /home/oracle/jchem-5.10.2/cartridge/jchem_cart.sql_i into JCC_UPGR_STAGE's schema...
INFO: Loading PL/SQL script /home/oracle/jchem-5.10.2/cartridge/jchem_util.sql into JCC_UPGR_STAGE's schema...
INFO: Loading PL/SQL script /home/oracle/jchem-5.10.2/cartridge/jchem_opti.sql into JCC_UPGR_STAGE's schema...
INFO: Loading PL/SQL script /home/oracle/jchem-5.10.2/cartridge/idx_analyse.sql into JCC_UPGR_STAGE's schema...
INFO: Loading PL/SQL script /home/oracle/jchem-5.10.2/cartridge/privman.sql into JCC_UPGR_STAGE's schema...
INFO: Building schema model for JCC_UPGR_STAGE...
INFO: Building schema model for JCHEM...
INFO: Preparing upgrade of JChem Cartridge in JCHEM based on JCC_UPGR_STAGE...
INFO: Skipping dropping sequence BIOL_PROJECTS_SQ
INFO: Skipping dropping sequence JCHEMIDX_SEQ
INFO: Skipping dropping sequence JC_STAGING_SQ
INFO: Skipping dropping sequence JC_STAGING_USQ
INFO: Skipping dropping sequence JC_STRUCTURES_JCX1_JCX_SQ
INFO: Skipping dropping sequence JC_STRUCTURES_JCX1_JCX_USQ
INFO: Skipping dropping sequence JC_STRUCTURES_SQ
INFO: Skipping dropping sequence JC_STRUCTURES_USQ
INFO: Skipping dropping sequence MOVEMENTS_SQ
INFO: Skipping dropping sequence PARENTS_7_SQ
INFO: Skipping dropping sequence PARENTS_8_SQ
INFO: Skipping dropping sequence SALTS_SOLVATES_SQ
INFO: Skipping dropping sequence SCREENS_SQ
INFO: Upgrade plan has been stored in /home/oracle/jchem-5.10.2/cartridge/upgrade-plan.txt
INFO: Dropping staging schema...
INFO: Using classpath: /home/oracle/jchem-5.10.2/lib/jchem.jar:/home/oracle/cartridge
INFO: Connecting to localhost:1099
AdminImpl: shutdown called...
INFO: shutdown called
Exiting...
INFO:
Shutting down...
INFO: Server process stop-ed.
SEVERE: error
java.sql.SQLException: ORA-00933: SQL command not properly ended

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
        at chemaxon.jchem.cartridge.install.schema.Schema.executeSql(Schema.java:450)
        at chemaxon.jchem.cartridge.install.schema.UpgradeStep.perform(UpgradeStep.java:44)
        at chemaxon.jchem.cartridge.install.schema.UpgradePlan.execute(UpgradePlan.java:16)
        at chemaxon.jchem.cartridge.install.Upgrader.upgradeSchemaObjects(Upgrader.java:109)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:69)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:62)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:332)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.interactiveUpgrade(UpgradeCmdLine.java:99)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:53)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:685)
+ set +x

Please help.

Istvan

Hello Istvan,

Did you create "manually" any database objects in the JCHEM schema?

Please, could you send us the following files:

• /home/oracle/jchem-5.10.2/cartridge/upgrade-plan.txt


• /home/oracle/jchem-5.10.2/cartridge/*.log


• /home/oracle/jchem-5.10.2/cartridge/logs/*.log

Thanks,

Peter

PS:

Did you create "manually" any database objects in the JCHEM schema?

If you did, the way to upgrade JChem Cartridge is to remove it and reinstall it. And then also no need to send the files.

Peter

Hello Peter!

Yes I created tables, views, functions etc. in the jchem schema.

Let say I drop the jchem schema and recreate it with the new cartridge, what will happen if I restore this scheme from my backup? Will the restore overwrite parts of the new version? If yes, then should I restore only the objects I created before?

Thanks,

Istvan

Did you create "manually" any database objects in the JCHEM schema?

Hello Istvan,

I think that you should restore only the objects you have created. Restoring the entire schema will restore the problem as well, I am afraid.

If feasible, I suggest to restore the objects you created to a separate schema (separate from where JChem Cartridge is installed). This would make it much easier to manage your JChem Cartridge instance.

Peter

Hello Peter,

I looked into jcart0.log and find that it breaks on our triggers, so I removed them by hand.After it the upgrade process goes well till the point where it breaks again with this:

[main/10]: 2012-08-07 09:13:01.061 chemaxon.jchem.cartridge.install.schema.Schema executeSql
FINEST: About to execute `call jchem.privman_pkg.grants_on_jcobjs('jchem', 'JCHEM')`...
[main/10]: 2012-08-07 09:13:01.065 chemaxon.jchem.cartridge.install.schema.Schema executeSql
FINE: Sql failed: call jchem.privman_pkg.grants_on_jcobjs('jchem', 'JCHEM')
java.sql.SQLException: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
ORA-06512: at "JCHEM.PRIVMAN_PKG", line 40
ORA-06512: at "JCHEM.PRIVMAN_PKG", line 77
ORA-06512: at "JCHEM.PRIVMAN_PKG", line 46

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
        at chemaxon.jchem.cartridge.install.schema.Schema.executeSql(Schema.java:450)
        at chemaxon.jchem.cartridge.install.schema.Schema.executeSqls(Schema.java:419)
        at chemaxon.jchem.cartridge.install.Installer.grantBasicJccPrivilegesByJCartOwner(Installer.java:330)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.rebuildIndices(UpgradeCmdLine.java:503)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:339)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.interactiveUpgrade(UpgradeCmdLine.java:99)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:53)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:685)

Hello István,

Are your indexes in the JCHEM schema?

Thanks

Péter

Yes.

If I chosed to not rebuild any of my indexes, the upgrade finished fine. I rebuild my JX_IDXTYPE index manually. I only had to readd the triggers I removed in the first step.

Istvan

Do I understand correctly that you are able to rebuild the indexes manually?

Peter

I managed to upgrade my schema without dropping and recreating it as you suggested. After the upgrade I lost all my triggers, functions and procedures, but I restored them from a backup.

Regarding the indexes, I reduilt them with alter index rebuild without any problem.

Thanks,

Istvan

Hello Peter,

How I can check that my jchem tables, indexes are up to date?

Thanks,
Istvan

Hello István,

When you connect with JChem Manager (of JChem version 5.10.2) to the JChemProperties table associated with the JChem Base tables in question, JChem Manager checks the tables on their version. If it founds any of them not up to the current version, it pops up a dialog box offering to recalculate them. If you can connect without being presented such a dialog box, the tables are up-to-date.

Also, if you can succesufully do structure-search in a table, the table is up-to-date. Searches in tables with versions incompatible with the JChem version executing the search will abort with a corresponding error message.

Péter