Problem installing Jchem cartridge 3.0.13

Hello


I have had a couple of problems installing Jchem cartridge 3.0.13 on a server. There was previously 3.0.11 running on there, which I uninstalled first. See below for my steps. The step I am currently stuck on is step 8, at the bottom of this message, so you may wish to look at that first, and then read the rest to see what other problems I had in case they are related.





This is the first time we have tried to install 3.0.13 on any machine. We have 3.0.12 running successfully on another server, but I have not tried that on this server.





Thanks


Mark





Info -


Red Hat Enterprise Linux AS release 3 (Taroon)


Kernel 2.4.21-4.ELsmp on an i686





Oracle 10.1.0





Tomcat 4.1.30





JChem version: 3.0.13


JChem Streams version: 3.0.13





So, steps


1. Copied across jchem3[1].0.13.tar.gz file to server. Renamed existing jchem folder in Oracle user home directory to jchem3.0.11. Extracted tar, resulting in new jchem directory.





2. Shutdown tomcat.


3. Deleted webapps/jchemstreams directory from tomcat home using rm -rf and deleted jchemstreams.war from tomcat home.


4. The next step was to drop indexes which used Jchem, as well as PL/SQL procedures which used Jchem.





Indexes


Ran this SQL, which generated a list of SQL statements to drop indexes


select 'drop index ' || di.OWNER || '.' || di.INDEX_NAME || ';' from dba_indexes di where di.ITYP_OWNER = 'JCHEM';





However, this did not work - the uninstall script later complained that it could not drop an indextype which had dependent indexes. So I dropped the Jchem user altogether.





PL/SQL procedures. I don’t know how to find all PL/SQl which uses Jchem. I did try querying dba_source using like, but it was not satisfactory. I was satisfied that since Jchem user was dropped, these PL/SQL procs were simply invalid and would need recompiling later.





As a matter of suggestion, it is extremely inconvenient to drop all PL/SQl source which uses Jchem, as of course it first has to be backed up. If this is not absolutely necessary, you might suggest an alternative.





5. The next step was to run uninstall.sh. As mentioned above, I had dropped Jchem, so I recreated it to run the script. I know this was pointless, but I did it to be sure.





CREATE USER JCHEM


IDENTIFIED BY VALUES xxx


DEFAULT TABLESPACE DATA_JCHEM


TEMPORARY TABLESPACE TEMP


PROFILE DEFAULT


ACCOUNT UNLOCK;


-- 3 Roles for JCHEM


GRANT DBA TO JCHEM;


GRANT CONNECT TO JCHEM;


GRANT RESOURCE TO JCHEM;


ALTER USER JCHEM DEFAULT ROLE ALL;


-- 13 System Privileges for JCHEM


GRANT DROP ANY TABLE TO JCHEM;


GRANT CREATE ANY INDEX TO JCHEM;


GRANT CREATE ANY TABLE TO JCHEM;


GRANT DELETE ANY TABLE TO JCHEM;


GRANT DROP ANY TRIGGER TO JCHEM;


GRANT INSERT ANY TABLE TO JCHEM;


GRANT SELECT ANY TABLE TO JCHEM;


GRANT UPDATE ANY TABLE TO JCHEM;


GRANT DROP ANY SEQUENCE TO JCHEM;


GRANT CREATE ANY TRIGGER TO JCHEM;


GRANT CREATE ANY SEQUENCE TO JCHEM;


GRANT SELECT ANY SEQUENCE TO JCHEM;


GRANT UNLIMITED TABLESPACE TO JCHEM;





Then I ran uninstall.sh, which obviously had lots of error messages about stuff that did not exist.





6. Then, copy the new jchemstreams.war to tomcat/webapps and all the jar files in jchem/lib to tomcat/shared/lib. Did that, as verified below





[oracle@uksap12 lib]$ pwd


/home/oracle/tomcat/jakarta-tomcat-4.1.30/shared/lib


[oracle@uksap12 lib]$ ls -l


total 10788


-rw-r--r-- 1 oracle oinstall 218630 Jul 6 15:55 batik-core.jar


-rw-r--r-- 1 oracle oinstall 87991 Jul 6 15:55 chart.jar


-rw-r--r-- 1 oracle oinstall 1461081 Nov 28 2004 classes12.jar


-rw-r--r-- 1 oracle oinstall 456914 Jul 6 15:55 dom4j.jar


-rw-r--r-- 1 oracle oinstall 8780486 Jul 6 15:55 jchem.jar





[oracle@uksap12 webapps]$ ls -l


total 36


-rw-r--r-- 1 oracle oinstall 697 Jan 25 2004 admin.xml


drwxr-xr-x 6 oracle oinstall 4096 Jan 25 2004 examples


drwxr-xr-x 4 oracle oinstall 4096 Jul 6 15:56 jchemstreams


-rw-r--r-- 1 oracle oinstall 5826 Jul 6 15:49 jchemstreams.war


-rw-r--r-- 1 oracle oinstall 435 Jan 25 2004 manager.xml


drwxr-xr-x 3 oracle oinstall 4096 Dec 7 2004 ROOT


drwxr-xr-x 11 oracle oinstall 4096 Dec 7 2004 tomcat-docs


drwxr-xr-x 3 oracle oinstall 4096 Dec 7 2004 webdav


[oracle@uksap12 webapps]$ pwd


/home/oracle/tomcat/jakarta-tomcat-4.1.30/webapps





7. Start tomcat and navigate to the url for jchemstreams.


Did that. The first time, there was an Apache error. I thought I would try the whole installation from the start, including extracting the tar and dropping the jchem user again, and this went away. But I have attached it in case it gives you a clue. (attached html file)





Anyway, after this problem was resolved by reinstallation, I can now see the correct Jchem version in this web site.





8. Run the install.sh, supplying the Jchem user and the url of the jchemstreams web site.


This is where I am stuck. The script seems to run for about 15 minutes, outputting the following few lines only





[oracle@uksap12 cartridge]$ ./install.sh jchem/jchem@evskdev http://uksap12:808


9/jchemstreams


Loading Java classes into Oracle Server.


This process can take several minutes.


+ /u01/app/oracle/product/10.1.0/db_1/bin/loadjava -force -user jchem/jchem@evskdev ../lib/dom4j.jar





However, then it fails, outputting several hundred lines as shown in the attached txt file.

Mark,





As a general remark: in order to upgrade JChem Cartridge, you normally should uninstall the previous version and then install the new version. (See http://www.chemaxon.hu/jchem/doc/admin/cartridge.html#uiandup .) Uninstallation includes ("manual") uninstallation of JChem Cartridge related objects in cartridge users' schemata (synonyms, privileges, etc.) first, then uninstallation (using the uninstall.sh script) from the cartridge owner's schema.

Quote:

However, this did not work - the uninstall script later complained that it could not drop an indextype which had dependent indexes. So I dropped the Jchem user altogether.

Anything referring to JChem Cartridge related objects in the cartridge owner's schema will also prevent you from dropping the indextype. This includes objects in other users' schemata referring to JChem Cartridge objects. As you write later in your posting, your users have their own PL/SQL procedures referring to cartridge packages/procedures. These dependencies should also be removed to be able to cleanly uninstall.

Quote:

PL/SQL procedures. I don’t know how to find all PL/SQl which uses Jchem. I did try querying dba_source using like, but it was not satisfactory.

In the OEM Console for 9i there is a way to find out all objects dependent on another. I seem to remember that the 10g EM Console provides something similar. The ultimate source, I guess, is the dba_dependencies view.

Quote:

As a matter of suggestion, it is extremely inconvenient to drop all PL/SQl source which uses Jchem, as of course it first has to be backed up. If this is not absolutely necessary, you might suggest an alternative.

Unfortunately, I do not have no better alternative to offer for the moment.

Quote:

The first time, there was an Apache error. I thought I would try the whole installation from the start, including extracting the tar and dropping the jchem user again, and this went away. But I have attached it in case it gives you a clue. (attached html file)

Based on the attachment, the best guess I can make is that the new jchem.jar was somehow not properly installed in Tomcat.

Quote:

then it fails, outputting several hundred lines as shown in the attached txt file.

As the error messages suggest there must be a problem with the connection between the loadjava client and Oracle. I guess you have checked to see if Oracle is running, have you not? If you can connect successfully with sqlplus to Oracle using the same connect string, then loadjava should be able to execute cleanly -- regardless of what kind of mess your schema is eventually in.





Even if you have not removed the jars from cartridge owner's schema (using uninstall.sh), the new jars should, in most of the cases, upload and overwrite the old ones without problem. In one instance, I remember having trouble overwriting the old jchem.jar. Looking at v$session I found that a mysterious session blocked the upload, but I was to test something else so I did not investigate further and simply restarted Oracle.





The error messages in your case do not seem to be consistent with the scenario of something blocking the loadjava client. Still, if after a minute or so nothing happens as dom.jar is apparently being loaded into Oracle, you can try and check with v$session to see if there are sessions blocking.





Peter

Hello Peter


I am still stuck and need help


I have some new information.


First, the same problem I saw in step 8 above now happens with both 3.0.12 and 3.0.11 installations on this server, so it is not specific to 3.0.13, and since 3.0.11 was previously installed on there, I am obviously doing something wrong, or perhaps it is not possible to go back from a partially installed 13 to earlier versions.

Quote:

As a general remark: in order to upgrade JChem Cartridge, you normally should uninstall the previous version and then install the new version. (See http://www.chemaxon.hu/jchem/doc/admin/cartridge.html#uiandup .) Uninstallation includes ("manual") uninstallation of JChem Cartridge related objects in cartridge users' schemata (synonyms, privileges, etc.) first, then uninstallation (using the uninstall.sh script) from the cartridge owner's schema.

I have reviewed the page you referenced again.





I tried the additional steps before running install.sh





call dbms_java.grant_permission( 'JCHEM', 'SYS:java.net.SocketPermission', '127.0.0.1:8080', 'connect,resolve' ) (since I dropped the Jchem user each time, this would probably be necessary).





Also, I checked the JDBC connection in the jchemstreams web site.





The problem persists.

Quote:

Anything referring to JChem Cartridge related objects in the cartridge owner's schema will also prevent you from dropping the indextype. This includes objects in other users' schemata referring to JChem Cartridge objects. As you write later in your posting, your users have their own PL/SQL procedures referring to cartridge packages/procedures. These dependencies should also be removed to be able to cleanly uninstall.

I first located all synonyms which were pointing to Jchem objects, and then dropped them all.





select 'drop synonym ' || sa.OWNER || '.' || sa.SYNONYM_NAME || ';' from all_synonyms sa where


sa.TABLE_OWNER = 'JCHEM';





then, I went manually through all the schemas and dropped anything that looked like it might even be thinking of using JChem





select * from all_source a where a.TEXT like '%jc\_%' escape '\' or a.TEXT like '%JC\_%' escape '\'; - no rows returned.





This still left the same problem.

Quote:

As the error messages suggest there must be a problem with the connection between the loadjava client and Oracle. I guess you have checked to see if Oracle is running, have you not? If you can connect successfully with sqlplus to Oracle using the same connect string, then loadjava should be able to execute cleanly -- regardless of what kind of mess your schema is eventually in.

Yes - Oracle is running, and continues to run after the problem occurs.

Quote:

Even if you have not removed the jars from cartridge owner's schema (using uninstall.sh), the new jars should, in most of the cases, upload and overwrite the old ones without problem. In one instance, I remember having trouble overwriting the old jchem.jar. Looking at v$session I found that a mysterious session blocked the upload, but I was to test something else so I did not investigate further and simply restarted Oracle.

Actually, to drop the Jchem user after aborting the install.sh, I have to restart the Oracle database anyway, as the Jchem user is left with an Oracle session running from the install.sh.

Quote:

The error messages in your case do not seem to be consistent with the scenario of something blocking the loadjava client. Still, if after a minute or so nothing happens as dom.jar is apparently being loaded into Oracle, you can try and check with v$session to see if there are sessions blocking.

I am not sure what you mean, but I have enclosed a text file which is a query of V$session from system, when the problem occurs.





By the way, running test.sh after the aborted installation does not seem to produce anything useful





SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jul 7 15:41:31 2005





Copyright (c) 1982, 2004, Oracle. All rights reserved.





Enter user-name:


Connected to:


Oracle Database 10g Release 10.1.0.3.0 - Production





SQL> select jchem_core_pkg.getenvironment() from dual


*


ERROR at line 1:


ORA-00904: "JCHEM_CORE_PKG"."GETENVIRONMENT": invalid identifier








Disconnected from Oracle Database 10g Release 10.1.0.3.0 - Production

the java grant above should have read





call dbms_java.grant_permission( 'JCHEM', 'SYS:java.net.SocketPermission', '127.0.0.1:8089', 'connect,resolve' );





since in our case tomcat is running on 8089

In case anyone has the same problem, the problem in this case was with the loadjava utility. It seems that rather than spit out a useful error message, this just hangs if the java pool size is less than what it needs to be (20MB at least).





This is kind of hinted at at the bottom of the following page





http://oraclesvca2.oracle.com/docs/cd/B14117_01/java.101/b12021/perf.htm#sthref962





Although you need an Oracle account to access this.





Oracle® Database Java Developer's Guide


10g Release 1 (10.1)


Part Number B12021-02