Installation And Administration of JChem Cartridge for Oracle

Software requirements
Before you begin
Installing JChem Cartridge
- How to setup JChem Server and Oracle on two different hosts?
- Installing the JChem Server as a Windows NT service
Managing JChem Server
Using cartridge
Removing JChem Cartridge
Upgrading JChem Cartridge

Software requirements

JDK 1.4.2 or later is supported. Java 1.5 or above is recommended for performance reasons.
9i R2 or above or later
- Oracle Express Edition is not supported.
- Oracle 10gR2 users must either
  - install Oracle Database 10g Products from the Oracle Database Companion CD;
  - apply Oracle patch 4655283, if available on their platform;
  - upgrade to Oracle patch level 10.2.0.3.

Before you begin

Before you begin installing JChem Cartridge, please, read the Introduction and the High Level Overview of the JChem Cartridge architecture.

If you encounter any problem during installation, do not hesitate to contact our technical support for assistance.

Installation of JChem Cartridge for Oracle

Configuring the JChem Server
1. Setting environment variables
  
  Environment variables specify "static" settings: for these settings to take effect JChem Server has to be restarted.
  
  Setting the Java runtime
  Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.4.2 or newer.
  Setting the size of the Java heap memory
  
  Set the JAVA_HEAP_SIZE variable which indicates the maximum amount of heap memory the Java runtime will be allowed to use. The amount of free physical memory on the JChemServer host should be large enough to accommodate the Java heap memory.
  
  The amount of Java heap memory should be large enough to accommodate
  - the structure cache of the Search Engine and
  - the objects associated with individual (concurrent) searches
  The rule of thumb is that the structure cache needs 100 MB of memory for 1 million molecular structures. (For a detailed description of how to calculate the exact memory requirement of the search cache, see the FAQ.)
  
  The memory overhead for each concurrent search is about 1.7MB per 100k hits. For example: if a particular JChem Cartridge installation is supposed to support 10 truly concurrent searches returning each (at the same time) a maximum of 1 million hits, JChem Server needs 170MB memory in addition to what is required for the structure cache.
  
  Example: In order to allow the Java runtime to use up to 700 megabyte of heap memory, set the JAVA_HEAP_SIZE environment variable as follows:
  on Windows:
```
            SET JAVA_HEAP_SIZE=700m
            
```
  on Unix/Linux (bash):
```
            export JAVA_HEAP_SIZE=700m
            
```
2. Setting the path to the Oracle JDBC driver
  
  If JChem Server host has an Oracle installation on it, set the ORACLE_HOME environment variable to the home of the Oracle installation like:
  on windows:
```
            set ORACLE_HOME="c:\oracle\ora92"
            
```
  on Linux:
```
            export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
            
```
  and the Oracle JDBC driver (the ojdbc14.jar file) bundled with your Oracle installation will be used.
  
  If you want JChem Server to use a JDBC driver different from (newer than) what comes with the Oracle installation, set the ORACLE_JDBC_DRIVER environment variable to the path of the desired ojdbc14.jar file like:
  on Windows:
```
            SET ORACLE_JDBC_DRIVER=C:\chemaxon\oem_libs\ojdbc14.jar
            
```
  on Unix/Linux (bash):
```
            export ORACLE_JDBC_DRIVER=/opt/chemaxon/oem_libs/ojdbc14.jar
            
```
  If the JChem Server host doesn't have an Oracle installation, the instructions in the section How to setup JChem Server and Oracle on two different hosts? apply.
3. Telling JChem Server how to connect to the Oracle Database
  
  Set the oracle.server.* parameters in the cartridge/conf/jcart.properties file as appropriate. You typically need to change only the oracle.server.instance property, setting it to the database instance name (aka system identifier or SID).
  
  Please, see also How to setup JChem Server and Oracle on two different hosts?, if applicable.
Registering license file(s)

License files issued for JChem 5.0 or later

Create a directory licenses in the cartridge directory (in the working directory of the JChem Server) and drop the license files received from ChemAxon's Sales into the licenses directory. If you want JChem Server to retrieve the license files from a different directory, set the license.dir property in the jcart.properties file to the path of that directory.

The cartridge/licenses directory is supposed to hold the newly issued license files which are XML files.

License codes issued for JChem versions prior to 5.0

License codes issued for JChem versions prior to 5.0 are supposed to work in the same manner as with JChem versions prior to 5.0:
- the licensing.dat file must be stored in the .chemaxon (chemaxon without the leading dot character on Windows) sub-directory of the home directory of the user who started JChem Server; (one simple way to achieve this is to leave the licenses.dat file in its old place and start JChem Server with the same user as Tomcat used to be started for JChem Cartridge versions prior to 5.0.
- JChem Base and JChem Cartridge registration codes have to be stored in the database (using JChem Manager). (They can be left in place when upgrading.)
Starting JChem Server

Using a command interpreter available on your platform (terminal on Unix/Linux, Command Prompt on Windows), make the cartridge directory your current working directory and call the server script (server.sh on Unix/Linux server.bat on Windows) with the start parameter:
```
                bash server.sh start
            
```
Please, refer to Managing JChem Server for how to stop JChem Server. In order to create system services for production use, please, use the facilities provided on your platform for this purpose. (Windows users will want to refer to Installing the JChem Server as a Windows NT service. Linux/Unix users may want to look at the following Forum post: http://www.chemaxon.com/forum/viewpost16398.html#16398)

Installing JChem Data Cartridge on Oracle

Make sure that your Oracle database is JVM-enabled and properly configured for use of Java Stored Procedures. To do so, follow the instructions in the sections Initializing a Java-Enabled Database and Configuring Oracle JVM in the chapter Java Installation and Configuration of Oracle's Java Developer's Guide.
Oracle 10gR2 users must make sure that either
- Oracle Database 10g Products from the Oracle Database Companion CD is installed for their database or
- Oracle patch No 4655283 is applied
- Oracle is upgraded to patchlevel 10.2.0.3
Select/create an Oracle user to own the JChem Cartridge installation. It is recommended that this schema is dedicated exlusively to the database object created by JChem Cartrdige so that these objects are cleanly separated from users' objects.
Make sure that the cartridge owner has the following roles granted to him:
- CONNECT
- RESOURCE
Grant connect and resolve permissions (java.net.SocketPermission) to the cartridge owner. Assuming that
- JChem Server is located on the same host as the Oracle RDBMS,
- JChem Server uses the default port (1099) and
- the cartridge owner is called JCHEM
you can do it as follows: execute the following the PL/SQL command to do this is:
```
        call dbms_java.grant_permission( 'JCHEM', 'SYS:java.net.SocketPermission', 'localhost', 'resolve' )
        call dbms_java.grant_permission( 'JCHEM', 'SYS:java.net.SocketPermission', 'localhost', 'connect,resolve' )
```
Note that if Oracle and JChem Server are on different machines, you have to replace localhost with the name of the JChem Server host.

Set the ORACLE_HOME environment variable to the directory where the Oracle Database is installed. (You may have performed this step already while Setting the path to the Oracle JDBC driver for JChem Server.) Eg:

On windows:	`set ORACLE_HOME="c:\oracle\ora92"`
On Linux:	`export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1`

Set the environment variable JAVA_HOME to the home directory of a Sun Microsystems Java Development Kit version 1.4.2 or newer.
Make sure that the cartridge directory in the uncompressed JChem package is writable by you.
Change your current working directory to the cartridge directory.
Install the cartridge with the install script suitable for your platform (install.sh for UNIX and Linux or install.bat for Windows). (The Oracle RDBMS must be running.)
The script loads the jcart.jar file into the Oracle Database and creates PL/SQL functions, procedures, and other PL/SQL objects needed by the cartridge.
The name and password of the cartridge owner have to be specified as the first parameter of the script. The install script assumes that JChem Server is running on the same host as Oracle and listens on the 1099 port by default. If any of the JChem Server connection parameters differs from the default, a third argument must also be specified to the install script indicating the JChem Server host and port:
```
install.sh username/password[@connect_string] [<jchem.server.host>:<jchem.server.port>]
```
For example:
```
install.sh jchem/tiger@db
```

Testing the JChem Cartridge installation

Connect to the database as the JChem-owner and execute the following:

call jchem_core_pkg.use_password('<passwd>');

where <passwd> is JChem-owner's password;

 select jchem_core_pkg.getenvironment() from dual;

If JChem Cartridge is correctly working, the last call returns an output similar to the following:

            JCHEM_CORE_PKG.GETENVIRONMENT()
            --------------------------------------------------------------------------------
            Oracle environment:
            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
            PL/SQL Release 10.2.0.3.0 - Production
            CORE    10.2.0.3.0      Production
            TNS for Linux: Version 10.2.0.3.0 - Production
            NLSRTL Version 10.2.0.3.0 - Production

            JChem Server environment:
            Java VM vendor: Sun Microsystems Inc.
            Java VM version: 1.5.0_14-b03
            JChem version: 5.0.0alpha14

            JCHEM_CORE_PKG.GETENVIRONMENT()
            --------------------------------------------------------------------------------
            JDBC driver version: 10.2.0.3.0

Configuring JChem Cartridge users

If your JChem Cartridge is correctly working with the cartridge owner, you can proceed to configure Oracle users to use JChem Cartridge.
1. Create a public synonym for the jchem_core_pkg package in the JChem owner's schema. (If you have special considerations (e.g. you want to use multiple JChem Cartridge versions in the same Oracle instance), you will have to create a private synonym(s) for jchem_core_pkg and/or the other objects.)
2. Create an Oracle role that will be granted the privileges required to use JChem Cartridge. In the following, we assume that this role is called JCHEMUSER_ROLE.
3. Grant this role the same system privileges as you did for the JChem owner (this step requires SYS' or SYSTEM's privileges):
```
grant connect to jchemuser_role;
grant resource to jchemuser_role;
grant create synonym to jchemuser_role;
call dbms_java.grant_permission( 'JCHEMUSER_ROLE', 'SYS:java.net.SocketPermission', 'localhost', 'resolve');
call dbms_java.grant_permission( 'JCHEMUSER_ROLE', 'SYS:java.net.SocketPermission', 'localhost', 'connect,resolve');
```
  Note that if Oracle and JChem Server are on different machines, you have to replace localhost with the name of the JChem Server host.
4. In order to grant necessary privileges to users on objects in the JChem Cartridge schema (jchem in the above example) connect as the JChem-owner and execute:
```
call privman_pkg.grants_on_jcobjs('JCHEM', 'JCHEMUSER_ROLE');
```
5. Grant the JCHEMUSER_ROLE to the first JChem Cartridge user candidate and test the configuration with this user as you did with the JChem-owner.

How to setup JChem Server and Oracle on two different hosts?

Set the ORACLE_JDBC_DRIVER environment variable to the path of the ojdbc14.jar file (with the file name itself included), like:
on Windows:
```
SET ORACLE_JDBC_DRIVER=C:\chemaxon\oem_libs\ojdbc14.jar
```
on Unix/Linux (bash):
```
export ORACLE_JDBC_DRIVER=/opt/chemaxon/oem_libs/ojdbc14.jar
```
Comment out the line starting with jchem.server.host in cartridge/conf/jcart.properties
Set the property oracle.server.host in cartridge/conf/jcart.properties to the host name as the Oracle host is visible from the JChem Server host.
When installing JChem Cartridge into the Oracle Server specify the connection descriptor (the host and port) of the JChem Server like:
```
bash install.sh jchem/tiger@mydb apollo:1099
```
where apollo is the JChem Server host name and 1099 is the JChem Server port as specified by the property jchem.server.port in jcart.properties.

Installing the JChem Server as a Windows NT service

Windows users may want to install JChem Server as a Windows NT service, if they want JChem Server to automatically start whenever Windows is (re)started. Microsoft offers a simple way to create a Windows NT user-defined service for Windows: http://support.microsoft.com/kb/137890 .

Please, follow the instructions up to step 6. in the referenced knowledge base article. In step 5., use the path to the server.bat file as the <path> component and server.bat as the <application.ext> component. In addition to the Application value, you also have to add an AppDirectory and an AppParameters value under the Parameters key created in step 3. The AppDirectory value must be the path to the server.bat file. The AppParameters value must be start.

For the first time after you created the service, the service has to be started manually using the Services program in the Administrative Tools program group (or Windows has to be restarted). It will be automatically restarted after each Windows restart.

Note that this procedure will not give you a true Windows NT service: it will only make sure that JChem Server is started at each restart. You still should call server.bat stop in the command line, if you want to stop the service.

Using JChem Cartridge

SQL operators of the Cartridge can be used to search for chemical data. SQL procedures are also defined to perform DML operations on chemical tables. To increase the speed of the searching process, the jc_idxtype index type has to be applied.

See using JChem Cartridge.

User credentials and privileges for JChem Server

The structure tables and domain indexes of a given user initiating a given cartridge operation must be accessible via the JDBC connection(s) opened in the JChem Server for use by the fingerprint caching engine. Beginning with JChem version 3.0.12 the recommended way to achieve this is for users to call the PL/SQL procedure jchem_core_pkg.use_password(password VARCHAR2) with their password as the parameter at the beginning of each database session (see a brief description of the mechanism at work here). The function jchem_core_pkg.set_password(password VARCHAR2) has a similar effect; it returns 0, if the password is valid for the user, -1 otherwise.

If the password of a given user has not been set for JChem Server via the use_password PL/SQL procedure, the oracle.server.login and oracle.server.password JChem Cartridge properties will be used to open database connections in JChem Server.

If the jchem_core_pkg.use_password(password VARCHAR2) call cannot be used by users or applications for some unlikely reason, the user specified by the oracle.server.login property must have the following privileges:


        
            SELECT ANY TABLE
        
        
            SELECT ANY SEQUENCE
        
        
            DELETE ANY TABLE
        
        
            CREATE ANY TABLE
        
        
            CREATE ANY INDEX
        
        
            CREATE ANY SEQUENCE
        
        
            CREATE ANY TRIGGER
        
        
            INSERT ANY TABLE
        
        
            UPDATE ANY TABLE
        
        
            DROP ANY TABLE
        
        
            DROP ANY SEQUENCE
        
        
            DROP ANY TRIGGER

Please, note that the JChem Cartridge components which are executed in Oracle currently communicate with JChem Server using an open RMI protocol. This means that some kind of protection (e.g. a simple firewall) must be setup on the machine which hosts JChem Server to prevent unauthorized searches in the structure tables.

Managing JChem Server

The server.{sh,bat} script can be used for managing the JChem Server. It accepts one of the following commands at a time:

start starts the JChem Server
stop stops the JChem Server
reload re-reads the following files
- the master JChem Server configuration file ( conf/jcart.properties by default),
- the license files in the licenses directory (or whatever directory has been specified using the license.dir property int the master configuration file),
- the logging configuration file (conf/logging.properties by default)
and updates the internal state/variables of JChem Server based on the newly retrieved values -- without the JChem Server having to stop.
setpassword sets the password that will be required for administering JChem Server.

The script also accepts any the following options:

--no-pwprompt: do not prompt for a password.
--config-file <config-file>: use the configuration file <config-file> instead of the default conf/jcart.properties.

Each time it is run, the server script prompts for the administrative password unless you specify the --no-pwprompt option. One exception is the start command, for which you will never be prompted for a password. Initially, no password is set. Until a password is set with the setpassword command, you can just hit <enter> when prompted for the password (or use the --no-pwprompt for non-interactive use).

By default, JChem Server does not accept remote administrative calls. The server script must therefore be run on the JChem Server host.

(In order to allow the JChem Server to be managed remotely using the server script, you have to set the property remote.admin.allowed to true. In case of executing the server script on a remote JChem Server, the jchem.server.host and jchem.server.port properties must be appropriately set on the client machine where where the script is executed. Note that you cannot start JChem Server remotely using the server script.)

Removing JChem Cartridge

To remove JChem Cartridge drop all jc_idxtype indexes or other pl/sql objects that have been created after the installation (including user defined operators added in support of user defined functions and supporting PL/SQL functions) and run the uninstall (uninstall.sh, uninstall.bat) script with the user/password[@connect_string] argument. This script drops every pl/sql type, function and other pl/sql objects that were created by the install script and drops the JFunctions.class, jchem.jar and dom4j.jar files.

Upgrading JChem Cartridge

Upgrading from JChem version 3.1.1 or later

Uncompress the installation package for the new JChem version.
Make sure that no user can access JChem Cartridge. (See Restricting Access to an Open Database of the Starting Up and Shutting Down section in the Oracle Database Administrator's Guide.)
If your JChem Server is running (or Tomcat for versions prior to JChem 5.0), shut it down.
Make sure you have write permissions on the cartridge subdirectory of the new uncompressed JChem installation package.
Change your current working directory into the cartridge subdirectory of the new uncompressed JChem installation package.
Execute the shell script upgrade.sh (Windows users upgrade.bat) passing the script the SQLPlus connection string as the first (and sole) parameter. For example:
bash upgrade.sh jchem/tiger@mydb
on Unix/Linux if the schema where JChem Cartridge is installed is jchem, the password is tiger and the TNS name of the database is mydb. If you see DROP statements (or ALTER statements with a DROP clause) complaining of non-existent database objects, you can safely ignore these error messages. The reason is that the upgrade procedure tries to drop certain objects or object attributes only to recreate them with their new signature or implementation. These objects will be created as required even if they did not exist in the JChem Cartridge version that is being upgraded from.
If the Oracle database and the JChem Server are on different machines, you need to specify the connection descriptor of the JChem Server as the second argument to the upgrade script similarly to the install script as described here. For example:
```
bash upgrade.sh jchem/tiger@mydb
```
(Re)create a public synonym for the jchem_core_pkg package in the JChem owner's schema.
Re-grant necessary privileges to users on objects in the JChem owner's schema (jchem in the above example).

If you are upgrading from a JChem Cartridge version prior to 5.0,

grant Java socket privileges without restrictions to specific ports:

call dbms_java.grant_permission( 'JCHEMUSER_ROLE', 'SYS:java.net.SocketPermission', 'localhost', 'resolve');
call dbms_java.grant_permission( 'JCHEMUSER_ROLE', 'SYS:java.net.SocketPermission', 'localhost', 'connect,resolve');

Configure the JChem Server
Register license files

(Re)start the JChem Server
Connect to Oracle and use either of the jchem_core_pkg.use_password stored procedure or the jchem_core_pkg.set_password function to initiate sessions in JChem Server for the current user.
Regenerate the JChem structure tables (if you have any) with JChemManager and/or drop and recreate jc_idxtype indexes on you structure tables.
Upgrade from JChem versions prior to 3.1.1
Remove the current version of JChem Streams and JChem Cartridge then install the new version.

Copyright © 1999-2008 ChemAxon Ltd. All rights reserved.

Installation And Administration of JChem Cartridge for Oracle

Software requirements

Before you begin

Installation of JChem Cartridge for Oracle

Configuring the JChem Server

Setting environment variables

Setting the Java runtime

Setting the size of the Java heap memory

Setting the path to the Oracle JDBC driver

Telling JChem Server how to connect to the Oracle Database

Registering license file(s)

License files issued for JChem 5.0 or later

License codes issued for JChem versions prior to 5.0

Starting JChem Server