jchem upgrade problem with Oracle 11g

Hi.


I had a working oracle 10GR1 with jchem 5.0.2.


I first upgraded the oracle to version 11.1.0.6, which went fine.


After copying the ojdbc14.jar to the $ORACLE_HOME/jdbc/lib/ojdbc14.jar, I could start the jchem server and connect and run the select jchem_core_pkg.getenvironment() from dual;


That worked.


The first trouble I ran into was when I the tried to run jcman. It complained something about wrong method called (don't remember the exact message).


I then decided to download the latest jchem version, since I found posts on the forum about other people who runs oracle 11g.


The software-guided upgrade failed with "no such indextype" error, so I tried the manual instead.


The following error was created.


Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production


With the Partitioning, OLAP, Data Mining and Real Application Testing options


Loading Java classes into Oracle Server.


This process can take several minutes.


+ /u01/oracle/product/11R1_64bit/bin/loadjava -force -user jchem/chemical@beehive ./jcart.jar


Error while creating resource META-INF/MANIFEST.MF


ORA-29546: badly formed resource: java.lang.ExceptionInInitializerError


ORA-06512: at line 1


Error while creating resource META-INF/CHEMAXON.SF


ORA-29546: badly formed resource: java.lang.NoClassDefFoundError


ORA-06512: at line 1


Error while creating resource META-INF/CHEMAXON.RSA


ORA-29546: badly formed resource: java.lang.NoClassDefFoundError


ORA-06512: at line 1


Error while creating class chemaxon/jchem/VersionInfo


ORA-29545: badly formed class: java.lang.NoClassDefFoundError


ORA-06512: at line 1


Error while creating class chemaxon/jchem/cartridge/CallCleanUp


ORA-29545: badly formed class: java.lang.NoClassDefFoundError


etc,etc down to the last line.


It seems that I miss something fundamental.


Can you please shed some light on what I need to do to make this work.


Regards,


Mikael

Hi Mikael,


I couldn't reproduce so far any problem upgrading from JChem 5.0.2 to JChem 5.1.4 on Oracle 11gR1 with patch set 1 (11.1.0.7).


I suggest to try loading jcart.jar in an empty test schema:

Code:

/u01/oracle/product/11R1_64bit/bin/loadjava -force -user testschema/testpasswd@beehive ./jcart.jar

        





If you have the same error messages, please attach your jcart.jar .





Thanks


Peter

Ok, I will try this and get back to you.





Regards,





Mikael

I have now upgraded to Oracle 11.1.0.7 and applied the needed patch.

I downloaded the latest release (5.2) of jchem.

 

When I run the upgrade-evo.sh I get the folowing output/error.

oracle@fett:~/jchem/cartridge> ./upgrade-evol.sh
+ /usr/java/jdk1.5.0_12/bin/java -classpath ../lib/jchem.jar:/home/oracle/product/11R1/jdbc/lib/ojdbc5.jar:/home/oracle/product/11R1/jdbc/lib/ojdbc14.jar -Djava.util.logging.config.file=conf/logging.properties chemaxon.jchem.cartridge.install.UpgradeCmdLine --oracle-home /home/oracle/product/11R1

The path to installation package of the old JChem version: /home/oracle/jchem501

====> The directory /home/oracle/jchem501/cartridge/licenses, where the old JChem Cartridge version expected the licenses files, doesn't exist.

Name of the Oracle host. [localhost]:

Oracle listener port [1521]:

Name of the Oracle instance [star]:

DBA login [SYSTEM]:
DBA password:

Name of the cartridge owner: jchem
Password of the cartridge owner:

The host name of the JChem Server [localhost]:

The port at which the JChem Server accepts connections [1099]:

maximum memory allocated to JChem Server [700m]:
INFO: Checking JChem Server at localhost:1099...

====> Trying to start JChem Server...
INFO: Using classpath: /home/oracle/jchem/lib/jchem.jar:
INFO: Server process start-ed.
INFO: Directory licenses does not exist. Leaving it to LicenseHandler to check the default location for licenses.
INFO: Listening on network interface 0.0.0.0/0.0.0.0 at port 1099...

Local TNS name of the Oracle instance. [star]: fett_star

The role for basic JChem Cartridge user privileges [JCC_BASIC_ROLE]:

Staging schema for the version [JCC_UPGR_STAGE]:
The password for the staging schema:

Rebuild jc_idxtype indexes (none, ask, all) [none]: none


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

The path to installation package of the old JChem version: /home/oracle/jchem501
Name of the Oracle host.: localhost
Oracle listener port: 1521
Name of the Oracle instance: star
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 JChem Server: 700m
Local TNS name of the Oracle instance.: fett_star
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: Creating staging schema...
INFO: Populating the staging schema with the objects of the new JChem Cartridge version...
INFO: Loading PL/SQL script jchem_cart.sql_u 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: JC_IDXTYPE_IM: 21 methods in the old JChem Cartridge version, 23 methods in the new JChem Cartridge version ; 3 method(s) to add, 1 method(s) to drop
INFO: Dropping staging schema...
java.lang.RuntimeException: No source for JC_IDXTYPE of PL/SQL type INDEXTYPE (chemaxon.jchem.cartridge.install.Schema$PlSqlIndexType)
        at chemaxon.jchem.cartridge.install.Schema$PlSqlEntity.prepareCreatePlan(Schema.java:230)
        at chemaxon.jchem.cartridge.install.Schema.prepareEntityTypeUpgrade(Schema.java:1214)
        at chemaxon.jchem.cartridge.install.Schema.prepareUpgrade(Schema.java:1202)
        at chemaxon.jchem.cartridge.install.Schema.upgrade(Schema.java:1182)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:51)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:257)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:54)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:530)
INFO:
Shutting down...
java.io.IOException: Bad file descriptor
+ set +x
oracle@fett:~/jchem/cartridge>

 

What have I missed ?

 

Regards,

Mikael

Please, could you execute

select indextype_name, implementation_name, partitioning from all_indextypes where owner = upper('jchem')

as JCHEM? What does it return?

Thanks

Peter

Hi.

Strange.

From the cloned database I get the following.

 

SQL> select indextype_name, implementation_name, partitioning from all_indextypes where owner = upper('jchem');

no rows selected

SQL>

 

But from the original I get.

SQL> select indextype_name, implementation_name, partitioning from all_indextypes where owner = upper('jchem');

INDEXTYPE_NAME                 IMPLEMENTATION_NAME            PARTITIONI
------------------------------ ------------------------------ ----------
JC_IDXTYPE                     JC_IDXTYPE_IM                  RANGE

SQL>

 

So, how I guess I have to recreate the indextype, how do I do that ?

 

Regards,

Mikael

Mikael,

I guess you will be better off reinstalling JChem Cartridge altogether on your cloned database. The upgrade procedure assumes that you have a working JChem Cartridge version in place. Your cloned database is certainly not working without the index type. (Also, we don't know how it went missing and what else went wrong during cloning, do we?)

Thanks

Peter

I tried this by upgrading an Oracle 10.1.0.6 to Oracle 11.1.0.7 but with the same result.

Is there any way to do a re-install of the cartridge but to keep all rows in the jchemproperties table,

or do I do a new install and copy all the rows from the old jchemproperties tables ?

Regards,

Mikael

 

There is no easy way. You have to reindex your structure columns. You can retrieve the index parameters from the PARAMETERS column in the DBA_INDEXES data dictionary view.

Regards,

Peter

Reindexing is no problem, but will that also populate the jchemproperties table

with the lines beginning with table.<schema>.<table name>.<property> ?

 

or is that table populated byt the regenerate function in jcman ?

Yes, you can regenerate them with JChem Manager.

Well, that only works if the jchemproperties table has the information about the structure table. I was asking for a way to populate the jchemproperties table with information about jchem tables where you was forced to do a new install since the upgrade fails.

I solved it by doing the way I said, ie take the values from the old jchemproperties table and insert them using sqlplus (or some other tool) into the new jchemproperties table.

So: Before doing a new fresh install, copy your old jchemproperties to some other schema and then do a new fresh install.

Maybe you can add that as an option in the install procedure to rename the old jchemproperties, drop and install and then copy back the data. As it is now, the install procedure stops when it detects that the schema is not empty.

Regards, Mikael

Mikael,

Thank you for your input.

I think the best thing is not to put anything in the JChem Cartridge owner's schema which is not maintained by JChem Cartridge. JChem-table properties (those having names starting with "table.") are typically maintained by JChem Base tools.

Regards,

Peter

True, but your tools does not work with the above scenario.

Maybe I did not explain clearly enough what the problem is.

With my working 10G I had the cartridge installed in the scema jchem.

I have about 20 other schemas containing jchem tables that was created by jcman and everyone is pointing to the jchem.jchemproperties and can be found in that table.

After I had upgraded Oracle to 11G (11.1.0.7), the upgrade of the cartridge fails (see above for error message and cause).

I did then a new install, and becuase of the way the install works, I had do empty the current cartridge owner and thus loosing all the information about jchem tables in the instance.

After manually populating the new jchem.jchemproperties table with information about what schemas and what tables are jchem tables, the regenerate function in jcman works.

Regards

Mikael

Thank you, Mikael, for the clarification.

Since the license code was moved (in JChem 5.1, I guess) from the jchemproperties tables to license files, there appears to be no more rationale for keeping a centralized jchemproperties table. You can simply use the default, which is the schema of the JChem table owner.

Thanks

Peter