Installation and administration of JChem Cartridge for Oracle

Software requirements
Before you begin
Installation of JChem Cartridge for Oracle
- Installing JChem Oracle Cartridge (without RAC)
- Installing JChem Server with Oracle Real Application Cluster (RAC)
  - Installing single JChem Server with RAC
  - Installing multiple JChem Servers with RAC
Administrative tasks
Using JChem Cartridge
Removing JChem Cartridge
Upgrading JChem Cartridge
- Upgrading JChem Oracle Cartridge (without RAC) installations
- Upgrading JChem Server with RAC
  - Upgrading single JChem Server with RAC
  - Upgrading multiple JChem Servers with RAC

Software requirements

Starting from version 23.10, JDK 17 is needed. We support AdoptOpenJDK 17 as well. In the case of AdoptOpenJDK libfontconfig package must be included.
Oracle 19c; JChem Oracle Cartridge can also be installed in multitenant Oracle database.
Oracle Express Edition is not supported.

Before you begin

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

If you encounter any problem during installation, do not hesitate to post an enquiry on our technical support forum or contact our technical support for assistance.

Installation of JChem Cartridge for Oracle

Installing JChem Oracle Cartridge (without RAC)

Deprecation notice: install-interact.sh and install-interact.bat have been deprecated in favor of install.sh and install.bat

Pre-installation tasks

Enable JVM for the Oracle database Make sure that your Oracle database is JVM-enabled and properly configured to use Java Stored Procedures. A database created with default settings typically meets this requirement. (If in doubt, read the sections Initializing a Java-Enabled Database and Configuring Oracle JVM in the chapter Java Installation and Configuration of your Oracle version's Java Developer's Guide.)
Set the JAVA_HOME environment variable Set the environment variable JAVA_HOME to the home directory of a Java Development Kit version 17.
Download the installation package Download the JChem Cartridge installation package and unpack into a permanent location (such as /opt/chemaxon/jchem-cartridge on Unix/Linux or C:\Chemaxon\JChem-Cartridge on Windows), because its files will not be used exclusively during the installation process of JChem Cartridge, but they will also back the operation of the JChem Cartridge server (associated with the JChem Cartridge instance to be installed). The location of the uncompressed package will be the location of the JChem Server.
(Advanced use) Have your DBA set up schema and role For Data Vault users and for organizations where the roles of the DBA and the application administrator are strictly separated. Some of the installation steps which are executed by the JChem Cartridge installation application by default, may need to be executed manually.
1. Some steps of the installation require elevated (DBA) privileges. In organizations where the function of the Application Administrator and Database Administrator is separated, DBA privileges may not be available during installation. In such cases, the JChem Cartridge owner and a basic JChem Cartridge user role has to be set up by the DBA prior the installation. The commands requiring elevated privileges can be listed by executing
```
[/opt/chemaxon/jchem/cartridge]$ config-util.sh list-dba-sqls [options]
```
on Unix or
```
C:\chemaxon\jchem\cartridge> config-util.bat list-dba-sqls [options]
```
on Windows.

{success} Installation Guide - Example

Here you find an example of the installation steps to be executed when the roles of the DBA and the application administrator are strictly separated.
1. You may want to have more control over the creation of the JChem Cartridge owner's schema than is provided by the installation application — for example, you want to assign a specific default table space to the JChem Cartridge owner. In such cases, the JChem Cartridge owner has to be created in advance. The JChem Cartridge installer application will look for an empty JChem Cartridge Owner's schema with the appropriate privileges. If found, it will use this schema, otherwise it will create and configure a new schema. The SQL statements needed to grant the required privileges to the JChem Cartirdge owner can be listed by executing
```
[/opt/chemaxon/jchem/cartridge]$ config-util.sh list-sqls-for-jcc-owner-privs [options]
```
on Unix or
```
C:\chemaxon\jchem\cartridge> config-util.bat list-sqls-for-jcc-owner-privs [options]
```
on Windows. The config-util program accepts the following options for both listing commands:
```
--jcc-owner <jchem-owner>
--jchem-server-host <jchem-server-host>
--jchem-server-port <jchem-server-port>
--oracle-version <target-db-major-version>.<target-db-minor-version>
```
The options which are not specified on the command line will be prompted for interactively.
Copy license file(s) to the machine that will be used as JChem Server After the installation of JChem Cartridge is completed, the license file(s) should be copied into a dedicated directory.
Collect information to be prepared to answer the questions that will be asked during the next installation process You will be asked for the
1. Oracle host name and port;
2. Oracle database name;
3. Host name, port, and maximum allocated memory of the JChem Server. Note : localhost as host name can only be applied if Oracle JChem Cartridge and JChem Server will be located on the same host.
4. DBA login name and password;
5. Name and password of the Cartridge owner;
6. Name of the role for basic JChem Cartridge user privileges (optional);
7. Name for JChemServer as an operating system service (optional).

Installation

The following steps have to be performed on the host where the JChem Cartridge server will be running:

Start the command interpreter available on your platform (console/terminal on Unix/Linux, Command Prompt on Windows) and make the cartridge directory your current working directory. Windows users should note that creating a Windows Service requires Administrator (or equivalent) privileges; therefore, they will need a Command Prompt with Administrator privileges. Note also that with recent Windows Servers, the Command Prompt program started by Administrators will not be running with full Administrator privileges by default.
Start the installer by the install.sh shell script or install.bat batch file.
Provide the input requested by the script
1. If you answer a prompt for a property with a question mark (?), a more detailed description of the property will be displayed.
2. The connection to the database can be given mutiple ways.
  1. If you choose Sid connection type, you will be asked for host, port and Oracle sid to identify the connection.
  2. If you choose Service name connection type, you will be asked for host, port and the service name to identify the connection.
  3. If you choose Url connection type, you can enter the whole connection string of the connection.
3. When prompted for the DBA login, enter an exclamation mark (!), if you don't want the installer program to use DBA account. See this pre-installation task for steps required before installation in such cases. Installation using non-DBA account is not recommended, please, apply it only in compulsory cases.
4. After all required input has been provided, you will be asked to confirm them and only after your confirmation will the database and configuration files be changed.
5. On Windows, you will be asked whether you want to install JChemServer as an operating system service, and if you answer y, you will be prompted for the name of the service.

Post-installation tasks

(Linux/Unix users) Create system service control script The installer (optionally) creates a Windows service on Windows. Linux/Unix users may want to create a system service for JChem Server after the installation program completes successfully. We created an example chkconfig configruation file for RedHat-based distributions. To use it:
1. Download the configuration file.
2. Edit jchem_home and jcc_user in the downloaded file to values appropriate for your environment. (The service will be started using the identity of the user pointed to by the jcc_user variable.)
3. Copy the file jcc as root into the /etc/init.d directory
4. Make the file executable: chmod +x jcc
5. Add file to chkconfig: chkconfig jcc on (For more information, see the following Forum post: https://forum.chemaxon.com/viewpost16398.html#16398)
Start JChem Server After the installation program start JChem Server
Register your license file(s)
Enable Oracle users to use JChem Cartridge
Execute cost estimation for the Oracle Optimizer
Set Oracle parameters described here to ensure JChem Cartridge runs as fast as possible

Installing JChem Server with Oracle Real Application Cluster (RAC)

The following two solutions are provided for installing JChem Server with RAC:

Installing single JChem Server with RAC

The figure below displays the simplified schematic architecture and operation of single JChem Server with RAC system. The Load Balancer distributes the jobs between JChem Cartridge instances, while each JChem Cartridge instance communicates with the same JChem Server. Each JChem Cartridge instance and the JChem Server are located on different hosts.

images/download/attachments/1803465/singleserver_rac.png

JChem Cartridge has been tested on Oracle RAC with

Clusterware 10.2.0.4
Oracle 10.2.0.4
the JChem Server running on a host "outside" the RAC and connecting to the RAC's nodes using their public virtual IP addresses. Installation is similar to installing JChem Server and Oracle on two different hosts. We recommend using urlconnection type and entering the full connection string. It can be changed later if necessary. Example: Assume that the cluster has two nodes with

management interfaces reachable as clu0 and clu1
virtual IP addresses of 10.0.0.81 and 10.0.0.83
one Oracle instance with two nodes with service name mydb0 and mydb1, respectively
a clustered service called mydb Assume that JChem Server will be hosted by a computer called shiraz.

Make sure the JAVA_HOME environment variable points to a Java Development Kit 17 on shiraz.
Start the install.sh shell script on shiraz:

   bash install.sh

Enter url, when prompted for Type of the connection.
Enter jdbc:oracle:thin:@10.0.0.81:1521/mydb0 or jdbc:oracle:thin:@10.0.0.83:1521/mydb1, when prompted for Name of the Oracle database (url).
Enter shiraz, when prompted for The host name of the JChem Server.
After JChem Cartridge installation is complete, you can change the url manually (it requires restart of the JChem server). For example, to modify JChem Server configuration to have its traffic load-balanced across the entire RAC: Edit the url property in the jchem/cartridge/conf/jcart.properties file of the JChem Server with the following value:

url=jdbc\:oracle\:thin\:@(DESCRIPTION=(LOAD_BALANCE=on)\
(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.81)(PORT=1521))\
(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.83)(PORT=1521))\
(CONNECT_DATA=(SERVICE_NAME=mydb)))

Installing multiple JChem Servers with RAC

The figure below displays the simplified schematic architecture and operation of multiple JChem Server with RAC system. The Load Balancer distributes the jobs between JChem Cartridge instances, while each JChem Cartridge instance communicates only with that JChem Server which is located on same host where the JChem Cartridge instance is. The JChem Cartridge instances are located on different hosts.

images/download/attachments/1803465/multipleserver_rac.png

JChem Cartridge has been tested on Oracle RAC with

Clusterware 10.2.0.4
Oracle 10.2.0.4 Installation is similar to installing JChem Server and Oracle on one host. We recommend using urlconnection type and entering the full connection string. It can be changed later if necessary. Example: Assume that the cluster has two nodes with

management interfaces reachable as clu0 and clu1
virtual IP addresses of 10.0.0.81 and 10.0.0.83
one Oracle instance with two nodes with service name mydb0 and mydb1, respectively
a clustered service called mydb On each node there must be JChem Server installed applying localhost as host name of JChem Server.

Make sure the JAVA_HOME environment variable points to a Java Development Kit 17 on clu0 and on clu1.
Start the install.sh shell script on the first computer (clu0)

   bash install.sh

and with --jcserver-only switch on the second one (clu1) (and on all other computers present in the cluster)

   bash install.sh --jcserver-only

Enter url, when prompted for Type of the connection.
Enter jdbc:oracle:thin:@localhost:1521/mydb0 on clu0, and jdbc:oracle:thin:@localhost:1521/mydb1 on clu1, when prompted for Name of the Oracle database (url).
Enter localhost, when prompted for The host name of the JChem Server.
Specify different structure cache IDs for clu0 and clu1 (e.g., JCC0 and JCC1, respectively)
After JChem Cartridge installation is complete, you can change the url manually (it requires restart of the JChem server). For example, to modify JChem Server configuration to have its traffic load-balanced across the entire RAC: Edit the url property in the jchem/cartridge/conf/jcart.properties file on both nodes with the following value:

url=jdbc\:oracle\:thin\:@(DESCRIPTION=(LOAD_BALANCE=on)\
(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.81)(PORT=1521))\
(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.83)(PORT=1521))\
(CONNECT_DATA=(SERVICE_NAME=mydb)))

See our FAQ comment regarding the outage of a JChem Server.

Administrative tasks

Starting and stopping JChem Server

The preferred way to start and stop JChem Server is through the system service facility provided on the host operating system. On Windows, a service for JChem Cartridge is created during installation. Linux/Unix users may do it manually after installation.

You also can start and stop using a command interpreter available on your platform (terminal on Unix/Linux, Command Prompt on Windows) in the following way:

Make the cartridge directory your current working directory
call the server script(server.sh on Unix/Linux server.bat on Windows) with the start or stop parameter:

   bash server.sh start

   bash server.sh stop

Purging connection caches

Executing the following command in the jchem/cartridge directory on the JChem Server host

$ ./server.sh purge-connection-caches

will result in purging of the connection caches in JChem Server so that new connections will be opened by JChem Server on the next JChem Cartridge operations (without having to re-execute jchem_core_pkg.use_password for the users ).

Clearing server references in client connections

JChem Cartridge stored procedures cache references to RMI server objects created and running in the JChem Cartridge Server. If the JChem Cartridge Server is restarted, these RMI references become stale. If closing and reopening the client connections is not an option, the following SQL command can be executed (in each client connection affected) to force refetching new server references:

call rmi.clear_directory_cache();

Configuring the JChem Server

Setting the maximum size of the Java heap memory

Determining the maximum amount of memory required by the JChem Cartridge server application

For best performance, 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, in turn, should be large enough to accommodate

the Structure Cache of the search engine and
the objects associated with individual (concurrent) searches and other user operations (such as indexing).

Structure Cache memory The rule of thumb is that the structure cache needs about 130 MB of memory for 1 million molecular structures with 512 bit fingerprint length. The longer the fingerprint length, the larger the required memory is. (For a detailed description of how to calculate an estimation of the memory requirement of the search cache, see the FAQ. The Structure Cache will not use the entire Java heap size. It will try to reserve the amount of memory specified by the value (in megabytes) of the reservedMemory property set in the jchem/cartridge/conf/jcart.properties file.
Operation-dependent memory The operation-dependent memory requirement can be further subdivided into two categories:
1. Temporary memory for the search hits 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.
2. General memory overhead A difficult to quantify component which basically reflects the usage pattern of JChem Cartridge observable at the specific site. Still, assuming that user activity is evenly spread over time, it can be typically regarded fairly constant. A good initial value could be 200MB. It can be further adjusted depending on the number of concurrent users, the type of operations they do and the kind of (input) structures they operate on.

Although not always effective (and therefore not strictly necessary), it is a good practice to specify the total expected amount of the operation-dependent memory (in megabytes) through the reservedMemory property in the jchem/cartridge/conf/jcart.properties file.

Determining the maximum size of the Java heap memory

The Java runtime process - in which JChem Cartridge Server program is executed - works with a memory overhead of about 12.5% over the JChem Cartridge server's calculated total memory requirement. This means that the JChem Cartridge server's calculated total memory requirement has to be multiplied by 1.125 to obtain the value for the jcserver.maxmem property in jchem/cartridge/conf/jcart.properties. This property is used to set the maximum heap space of the JVM process.

Setting the maximum size of the Java heap memory

Set the jcserver.maxmem property in the jchem/cartrdige/conf/jcart.properties file:

jcserver.maxmem=700m

Make sure the letter m or M is appended to the value to denote the megabyte measurement unit. A restart of the JChem Cartridge server is required for the new value to take effect.

Example:

Assuming that the jc_idxtype index called JCXCDDR_0 has been created

on 10 million structures,
with default fingerprint properties (512 bit long fingerprints),
and an average cd_smiles length of 46.3 (obtained by executing: select avg(length(cd_smiles)) from CCDR_OWNER.JCXCDDR_0_JCX; ), you estimate the structure cache memory requirement (10000000*(0.5*46.3+512/8+74)) at 1.6 gigabyte. (Repeat this calculation for all indexes which this particular JChem Cartridge instance has to service and add the results to get the total structure cache memory requirement. We assume for this example that we have a single JChem index for this JChem Cartridge instance to service, so the total memory required by the structure cache is 1,006 megabyte.) You further estimate
the temporary memory required for search hits at 200 megabyte
the general memory overhead of the JChem Cartridge server application at 200 megabyte which gives 2 gigabyte memory required by the application, which gives a memory requirement of roughly 2.25 gigabyte by the Java runtime (2*1.125). The jcserver.maxmem property in jchem/cartridge/conf/jcart.properties must therefore be set to 2.25m.

Changing the network configuration

The network address of the Oracle server or JChem Cartridge server may change. A command-line tool is provided to help you execute the steps required to make the JChem Cartridge configuration reflect such changes. In the jchem/cartridge directory, execute the following command and enter the input you're prompted for:

bash config-util.sh config-server-connection

for Unix/Linux,

config-util.bat config-server-connection

for Windows. (If you enter a question mark a more detailed help messages will be displayed on the input parameter required.)

Disabling screening during Markush search

With current JChem Cartridge versions, screening during Markush search may be counter-productive on two-host installations with relatively slow network connections (rule of thumb: 0.5 ms ping time or longer). In such cases add the following line to jchem/cartridge/conf/jcart.properties:

markush.screening.enabled = false

Configuring Marvin Services

Marvin Services are available in e tJChem Cartridge through the jc_evaluate and jc_evaluate_x operators as well as their corresponding functions in the JCF package (and the equivalent functions in the JCF package). Marvin Services can be globally configured by setting the marvin.services.config.file property in the jchem/cartridge/conf/jcart.properties file to the path of the corresponding configuration file.

Setting the Oracle cached connection pool properties

If JChem Server is using the default data source implementation (oracle.jdbc.pool.OracleDataSource), the connection cache properties can be specified in the jchem/cartridge/conf/jcart.properties file, by prefixing their names with the oracle.connection.cache. character string. For example, in order to close idle connections after 10 seconds you have to specify in the jchem/cartridge/conf/jcart.properties file, the following:

oracle.connection.cache.inactiveConnectionTimeout = 10
oracle.connection.cache.timeoutCheckInterval = 10

(The name of the timeoutCheckInterval property is somewhat misleading: in addition to the interval between checks on property values, it also specifies the interval for enforcing the cache properties' settings. Its default value is 900 seconds, so it should be adjusted for any Oracle-connection-cache-related time-out settings specifying a time-out less then 900 seconds.)

Ignoring JChem Server properties for specific commands

Some custom properties you might have set for a running JChem Server might be interfering with other JChem Server related tasks which are executed through the server.sh or server.bat scripts. For example, you might fancy to enable remote JMX management by setting the sysprop.com.sun.management.jmxremote.port property in jchem/conf/jcart.properties . When you execute

server.sh stop

to stop the running server, the command will fail by complaining about the already used JMX port. You can prevent this from happening by adding the following line to the jchem/cartridge/conf/jcart.properties file:

ignore.properties.for.stop=sysprop.com.sun.management.jmxremote.port

More generally, specifying a comma-separated list of property names (which are found in the jcart.properties file) as the value of the ignore.properties.for.<command-string> property allows you to exclude that list of properties from evaluation for a specific server.sh command. So if you also use the

server.sh reload

command (for reloading certain configuration parameters of JChem Server without having to stop it), you might also consider adding:

ignore.properties.for.reload = sysprop.com.sun.management.jmxremote.port

Testing the JChem Cartridge installation

Connect to the database as the JChem-owner (or the JChem-user) to be tested and execute the following:

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

where is the JChem-owner's (or the JChem-user's) password; 2.

 select jchem_core_pkg.getenvironment() from dual;

If the 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 version: 1.6.0_23
    Java VM version: 1.6.0_23-b03
    JChem version: 5.0.0alpha14

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

The following sql script can be used for testing:

        set echo on
        call jchem_core_pkg.use_password('<cartridge owner's password>');
        create table wombat (
        id number primary key,
            structure varchar2(4000)
            );
        insert into wombat (id, structure) values(1, 'Brc1ccccc1');
        insert into wombat (id, structure) values(2, 'Clc1ccccc1');
        insert into wombat (id, structure) values(3, 'c1ccccc1');
        create index jcxwombat on wombat(structure) indextype is <cartridge owner's name>.jc_idxtype;
        select id from wombat where jc_compare(structure, 'c1ccccc1', 't:s') = 1;
        select jchem_core_pkg.getenvironment from dual;
        quit

Registering the license file(s)

The license file(s) received from Chemaxon's Sales should be dropped into the directory licenses under cartridge directory (the working directory of the JChem Server). If you want JChem Server to retrieve the license files from a different directory (e.g. because multiple applications or hosts share a common license "repository"), set the license.dir property in the jcart.properties file to the path of that directory.

Multiple license files:

If your licenses are stored in multiple license files (i.e. you receive an additional license file from Chemaxon Sales with, for example, a renewed license or an evaluation license for an additional feature), you have to drop all license files into the licenses directory (wherever it is configured to be in the conf/jcart.properties file). This means that you may have to store them under different name – for example as license0.cxl, license1.cxl, license2.cxl, etc. – to avoid overwriting each other.
There should be only one entry for any licensed feature in the license files. For example, if you receive a license file with a renewed license, remove the entry for the corresponding expired license from the old license file, so that only the renewed, valid license entry is found for the given feature in the files.
You can also merge license files into a single one

You can view the available licenses using the following query, which returns the list of licensed products and features:

select * from table(jchem_core_pkg.getLicenses());

{primary} After putting the license file(s) into their directory (or after changing their location), the JChem Server must always be restarted

{primary} On October 1st, 2008, the old license files (issued for JChem versions prior to 5.0.0) will become unusable with releases JChem 5.0.0 and above. In case you have such files, you need to request Chemaxon Sales to re-issue the licenses in the new, XML-based format.

Configuring JChem Cartridge users

It is recommended that the Oracle user created during JChem Cartridge installation is exlusively dedicated to owning the core database objects of JChem Cartridge (e.g. jchem_core_pkg ), so that these objects are cleanly separated from users' objects.

Types of JCC users

Users need to have appropriate system and object privileges as well as synonyms in order to successfully use JChem Cartridge.

From an administrative prespective, users can be divided into two categories:

those who own the jc_idxtype indexes they use as well as those who don't need such indexes at all because they use functions and operators with literal (string constant) input structures;
those who use jc_idxtype indexes owned by a different user.

Users of both categories need a basic set of privileges in order to execute JChem Cartridge functions and operators. In addition to this basic set of privileges, users of the second category also need privileges on the objects associated with the index not owned by them. The following figure shows the various groups of privileges.

images/download/attachments/1803465/UserPrivileges.png

Certain privileges or roles (such as CONNECT, RESOURCE) must be granted directly to users. Other privileges may be granted both directly and indirectly through roles which already have been granted the necessary privileges. (The benefit of roles is obvious with a large number of users.) One single role is sufficient for the basic privileges that all users must have (represented by the JCC_BASIC_ROLE role on the figure). In addition to the basic role, zero or more futher roles are required for members of the second user category depending on how many groups of such users there are in your organization or project. Each group of secondary users will need a set of extra privileges (specific to the given group) corresponding to the set of not-owned indexes which members of the group need to access. The roles for these groups of "secondary" users are represented by the JCC_IDXxxxx_ROLE role.

A typical user creating script:

create user <jchem_user> identified by <jchem_user_password>;       
grant connect, resource to <jchem_user>;        
grant JCC_BASIC_ROLE to <jchem_user>;
grant unlimited tablespace to <jchem_user>;
--if public synonyms were not granted during install you also have to grant them manually:      
--call <jchem_owner>.PRIVMAN_PKG.SYNS_FOR_JCOBJS('<jchem_owner>','<jchem_user>');

When the roles of the DBA and the application administrator (JChem Owner) are strictly separated, the privileges cannot be granted through roles. The following grants must be applied:

call JCHEM.privman_pkg.grants_on_jcobjs('<jchem_owner>', '<jchem_user>');
exec dbms_java.grant_permission( '<jchem_user>', 'SYS:java.net.SocketPermission', '<host>:<port>', 'connect,resolve' );

{success} Installation Guide - Example

Here you find an example of the installation steps to be executed when the roles of the DBA and the application administrator are strictly separated.

User credentials and privileges for JChem Server

Reusing the parent session's identity

A user initiating an operation on structure tables and domain indexes must be able to access them via the JDBC connection(s) opened in the JChem Server for use by the fingerprint caching engine. 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.

The two-argument variant of jchem_core_pkg.use_password(login VARCHAR2, password VARCHAR2) can be used to instruct JChem Server to use a different identity to connect back to Oracle – different from the real user. This may be useful in the case when a non-definer is transferred the privileges of the stored procedure definer when the stored procedure code is executed.

The passwords specified through jchem_core_pkg.use_password are, by default, cached in the JChem Server and reused for subsequent requests. For heightened security, the password.always.required property can be specified. When set to true , this property will instruct JChem Server to require a password (in addition to the database login) for each RMI request involving database access. In this non-default case, the password is cached in the parent Oracle session and passed along with each request to JChem Server. This mode of operation thus requires the call to jchem_core_pkg.use_password at the beginning of each database session. (As opposed to the default behaviour, which requires this call only once between restarts of the JChem Server.)

This method can't be used if JChem Cartridge is installed on RAC with multiple JChem Servers. The reason for this is that use_password must be called on all JChem Servers, which can't be guaranteed, because the current JChem Server instance, which the use_password call runs on, is decided by a load balancer. A workaround for this issue is that after starting or restarting the JChem Servers, the users have to log in to each RAC node (and not the whole RAC service) and run the use_password call on them. After doing this step, they can log on to the RAC service and use JChem Cartridge as the user credentials are set.

Defaulting to a pre-configured identity

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

If the use of parent sessions' identities is combined with the use of the default identity, the need may arise for preventing users who are supposed to use the default credentials in JChem Server for their database operations from accidently using their parent session identities. The allowed.to.specify.credentials property in jchem/cartridge/conf/jcart.properties can be set for a comma separated list for this purpose. When it is specified, only users whose database login names appear in the list are allowed to set their parent sessions' identities for use in the 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 table.

Comparison of the methods applicable for handling the user credentials and privileges for JChem Server

Method	Advantages	Disadvantages
Reusing the parent session's identity: use_password(‘pw’) and use_password(‘loginname’,’pw’)	* user privileges are strictly limited to jchem objects	* use_password() should be run after each server restart * password is handled in non-obfuscated mode
Defaulting to a pre-configured identity	* no need to run use_password()	* wide range of privileges must be given to the user (e.g., drop any table)
Combined method: the users start to work with grants to the already existing jchem objects	* no need to run use_password() * user privileges are strictly limited to jchem objects	* when new jc_idxtype indexes are created, the privileges relating the new indexes must be granted to the users accordingly, it is necessary to be continuously informed about the CREATE INDEX statements * not implemented in JChem Oracle Cartridge; must be solved by the customer

Accessing JChem Cartridge functionality through PL/SQL procedures defined by other users (with definer's right):

For users who need to access JChem Cartridge functionality through PL/SQL programs, the same privileges that have been granted to the JCC_BASIC_ROLE (described here) must be also directly granted to these users. The reason is that privileges obtained through a role are ignored by Oracle for access control excercised in a PL/SQL program. The following command can be used to list the SQL statements for granting the required privileges:

[/opt/chemaxon/jchem/cartridge]$ config-util.sh list-sqls-for-jcc-user-privs [options]

on Unix or

C:\chemaxon\jchem\cartridge> config-util.bat list-sqls-for-jcc-user-privs [options]

on Windows. The config-util program accepts the following options for this listing command:

--jcc-owner <jchem-owner>
--jchem-server-host <jchem-server-host> 
--jchem-server-port <jchem-server-port> 
--oracle-version <target-db-major-version>.<target-db-minor-version>
--user-to-configure <the-user-to-configure>

In addition, if users are also to create jc_idxtype indices from within PL/SQL programs, they will need to have CREATE TABLE and CREATE SEQUENCE privileges directly granted to them. (Privileges associated with RESOURCE role will be ignored as well.) Symetrically, users will need DROP TABLE and DROP SEQUENCE privileges directly granted to them in order to successfully drop indices in their schemata.

Setting up users to access JChem index(es) created by other users

If applicable, create privileges for those users who need to use jc_idxtype index(es) owned by another user:

Depending on how JChem Server is set up to connect back to Oracle, this step can be as simple as granting the appropriate privilege (the required combination of SELECT, INSERT, UPDATE and DELETE privileges) on the structure table to the non-owner user.

JChem Cartridge "super user" configured in JChem Server

One possible configuration is to set the "oracle.server.login" and "oracle.server.password" in the cartridge/conf/jcart.properties configuration file to the credentials of a user whose privileges cover the required privileges of all JChem Cartridge users. The jcart-util.sh script can be used with the the encrypt argument to convert login names and passwords into an encrypted form which JChem Server can subsequently decrypt:

     ./jcart-util.sh encrypt

With this configuration, the appropriate privilege (the required combination of SELECT, INSERT, UPDATE and DELETE privileges) must be granted on the structure table to the non-owner user.

This approach (let's call it "the JChem Cartridge super-user" approach) has the advantage of a simpler privilege configuration for "second-level" JChem Cartridge users (those who use other users' indexes such as ABC_DATA_READER), but has the disadvantage of "having around" a relatively highly privileged user. Some organizations perceive this disadvantage as prohibitive. (The JChem Cartridge super-user approach is also recommended if you want to use Oracle's built-in fine grained (row-level) access control mechanism.)

JChem Server uses initiators' identities

The other configuration consists of JChem Server using the initiator user's identity to connect back to Oracle. With this configuration you have to perform the following steps to let non-owner users access JChem structure indexes:

As the JChem Cartridge owner execute:
```
privman\_pkg.grants\_on\_jcobjs('', '');    
```
to make sure the index owner is directly granted privileges on objects in the JChem Cartridge owner's schema (as opposed to being granted through a role);

privman\_pkg.grants\_on\_jcobjs('', '');

to make sure the index user is directly granted privileges on objects in the JChem Cartridge owner's schema (as opposed to being granted through a role);

As the index owner execute the privman_pkg.grants_on_jcidx procedure in the JChem Cartridge owner's schema to grant the desired privilege on the index. This procedure accepts the following parameters:
- The name of the role (or user) which needs to access the given index
- The name of the index
- The name of the index
- "search" flag, 1 if the user needs to access the index for searching, 0 otherwise.
- "insert" flag, 1 if the user needs to access the index for inserting new structures, 0 otherwise.
- "update" flag, 1 if the user needs to access the index for updating structures, 0 otherwise.
- "delete" flag, 1 if the user needs to access the index for deleting structures, 0 otherwise.

Note: You may need to use a fresh database session for all the newly granted privileges to take effect. Depending on which tool you use to execute structure searches, this may involve restarting IJC or SQL*Plus – or even restarting the JChem Cartridge server, in case the user already did unsuccessful searches with an incomplete privilege set. Also, rarely, you may need to commit (or close) the SQL sessions used to grant the privileges.

Command-line tool

A command-line tool is provided to help you execute the steps required. In the jchem/cartridge directory, execute the following command and enter the input you're prompted for:

config-util.sh config-index-user

(If you enter a question mark a more detailed help messages will be displayed on the input parameter required.)

Consider using roles

If multiple users need to use the same index with the same access privileges, you may want to create a role for the particular privilege set on the particular index and execute the above steps on the role (instead of concrete users) and grant the role to the users.

Example:

The following example grants the CCDR19_READER Oracle user "search permission" on the index CCDR19 owned by the user CCDR19_OWNER :

    -- As JCHEM:
    privman_pkg.grants_on_jcobjs('JCHEM', 'CCDR19_OWNER');
    privman_pkg.grants_on_jcobjs('JCHEM', 'CCDR19_READER');
    -- ----------------------------------------------------
    -- As CCDR19_OWNER:
    call jchem.privman_pkg.grants_on_jcidx('CCDR19_READER', 'CCDR19_OWNER', 'CCDR19', 1, 0, 0, 0);

For the cases when the roles of the DBA and the application administrator (JChem Owner) are strictly separated, the privileges cannot be granted through roles. See privilege grants example here.

Tracking JChem Server activity

Tracking memory utilization

Tracking tasks in progress

Tracking long running operations

Killing "runaway" searches

Producing thread dump

Tracking memory utilization

The jcf.t_get_gmem_util() function can be used to obtain global memory utilization data. The call

select * from table(jcf.t_get_gmem_util())

will return two columns:

DESCRIPTION describing the memory resources and
UTIL_MB giving the value for the memory resource in megabytes.

The memory resources are:

Total JVM memory: the amount of memory currently reserved by the JChem Server process.
Free JVM memory: the amount of memory currently reserved but not allocated to Java program objects.
Max JVM memory: the total amount of memory which the JChem Server process is allowed to eventually reserve.
<index-owner-name>.<index-name>: the amount of memory used for caching data associated with the specified index.

Tracking tasks in progress

The jcf.t_get_taskinfo() function can be used to list outstanding tasks in the JChem Server. The call

select * from table(jcf.t_get_taskinfo());

will return the following columns:

ID specifying the internal identifier of the task;
DESCRIPTION specifying various attributes of the task such as the kind of operation, the options specified for the operation and the index being worked on
START_TIME specifying the time when the task was submitted
USER_NAME specifying the user who submitted the tasks (in whose session the operation has been started)
ESTIM_MEMORY_USE specifying an approximation of the memory (in bytes) used by the task
TIMEOUT specifying the amount of time (in seconds) which the task can be sitting idle. (Idle timeout can be specified for domain index scans and is useful in cases where hits found during a search are fetched piece-meal by the Oracle resident JCC parts for early retrieval without waiting for all the hits to be retrieved. The task is normally completed when all hits have been fed to Oracle. On completion, the resources associated with the task are released. The idle time out makes resource release time-bound if the database session which submitted the tasks has crashed, for example)
LAST_RESCHEDULED specifying the time when the task last was accessed by the Oracle-resident JCC part

When executed by the JChem Cartridge Owner, jcf.t_get_taskinfo() returns all outstanding tasks. When executed by non-JChem Cartridge Owners the function lists only the tasks which they have started.

Currently only index scans are tracked by the jcf.t_get_taskinfo() function.

Tasks started to create or rebuild indexes can also be tracked via the v$session_longops table. To do this, the chemaxon.jchem.cartridge.indexingIsAsynch has to be set to true in the jchem/cartridge/conf/jcart.properties file. See the refreshPeriod index parameter for more information.

Tracking long running operations

Oracle Databases provide a view called V$SESSION_LONGOPS to display the status of long running operations. This view is available for the DBA, but rights to access this view can be granted to other users as well. Oracle developing and administrating GUI tools usually have menu items to show information extracted from this view. The currently supported tasks that can be monitored are:

Creating index: opname field begins with "Creating index "
Rebuilding index: opname field begins with "Rebuilding index "

Killing "runaway" searches

Search tasks running on the JChem server can be killed by executing the following SQL:

select jcc_admin.kill_task(<task-id>) from dual

where <task-id> can be obtained from the listing of running tasks. The following strategy can be used to programmatically kill search tasks:

assign an operation ID to the searches unique in the current session.
Before executing the search, obtain the JCC session id by executing

select jchem_core_pkg.get_jcc_session_id from dual

identify the task in the task list by the JCC session id and the operation ID.

When killing the task, the associated SQL query will fail with either ORA-29902, ORA-29903, or ORA-29904 and the error message will contain 'ORA-20107'. (With Oracle 10.2.0.4 the killed SQL query may abort with ORA-00600, in which case closing and reopening the connection is recommended.)

Current limitations:

Only search tasks started for domain index scans are covered. Searches started as part of search filters are not accounted for as tasks.
This command works only randomly on Oracle RAC configurations with multiple JChem Servers because of the randomness of the Load Balancer.

Producing thread dump

If the JCC server appears to hang (the server in general or any of the outstanding tasks in particular), retrieving samples of the execution state might be helpful to Chemaxon's technical support for diagnosing the problem. Executing the following command in the jchem/cartridge directory on the JChem Server host

$ ./server.sh thread-dump

will dump the current execution stack of the JCC server's threads to the standard error.

In case of windows OS the corresponding command .bat version should be used.

Logging

Activity of the JChem Server and Oracle side processes can be logged by configuring the logging.properties file which is available in the \<installed JChem's home directory>/cartridge/conf/ folder. See the available options in the logging.properties file. Modification of the logging.properties file will be taken into account after JChem Server restart. The log files, by default, will be generated in the \<installed JChem's home directory>/cartridge/logs/ folder. Log files starting with jcart refer to the activity of the JChem Server, while log files starting with trace refer to the Oracle side processes. The errors are always seen in the log files, independently of the applied log level.

Furthermore, errors are always logged in Oracle trace log files available for the DBA. Logging level of oracle resident Chemaxon codes - for all sessions - can be set by:

call jcart_logger.set_log_level('chemaxon', 3);
--3 = debug
--0 = error

This log level setting also influences the level of logging into the trace files in \<installed JChem's home directory>/cartridge/logs/ if the *logging.properties * contains chemaxon.jchem.cartridge.oracleTraceLog.level = FINE.

The stacktrace of an exception of a JChem Server side process can always be monitored on the dbms output using dbms_java.set_output(32000) by any cartridge user.

Example:

exec dbms_java.set_output(32000);   --turns tracing on
select jc_molconvert('yyyyxx52342354%%%%', 'mrv') from dual;  --erroneous statement raising the exception
exec dbms_java.set_output(32000);   --shows the stacktrace of the previously raised exception

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.

Removing JChem Cartridge

To remove JChem Cartridge

drop all jc_idxtype indexes and other PL/SQL objects that have been created after installation (including user defined operators added in support of user defined functions and supporting PL/SQL functions).
drop the JChem Cartride owner's schema (the schema where JChem Cartridge has been installed)
Windows users: remove the Windows service for the JChem Cartridge server by executing in the jchem\cartridge directory:

.\prunsrv.exe //DS//JChemCartridgeService

Upgrading JChem Cartridge

The following upgrade instructions apply to JChem Cartridge version 5.0 or later. See our FAQ comment regarding JChem Cartridge upgrade test.

Deprecation notice: upgrade-evol.sh and upgrade-evol.bat have been deprecated in favor of upgrade.sh and upgrade.bat

Upgrading JChem Oracle Cartridge (without RAC) installations

Pre-upgrade tasks

Set the JAVA_HOME environment variable to the home directory of a Java Development Kit version 17.
Download the installation package of the new JChem Cartridge version.
Unpack the new JChem installation package Typcially, you may want to unpack the new version next to the location where the old version was unpacked (i.e. in a sibling directory of the old version).
(Optional) Make sure an empty schema for staging the upgrade exists For the upgrade of Oracle objects in the JChem Cartridge owner's schema, an additional, empty schema will be required where the objects of the new version will be loaded and used as a blue-print for the upgrade of the JChem Cartridge owner's schema. If you provide DBA credentials for the upgrade program, the staging schema will be created for you with the required privileges. If, for some reason, DBA privileges cannot be entered to the upgrade program (for example, because the upgrade is essentially performed by an application adminstrator, who is a distinct person from the database administrator), the staging schema with the required privileges must be created manually. The privileges required by the staging schema can be listed by executing the config-util program in the jchem/cartridge directory with the list-sqls-for-staging-schema argument:

config-util list-sqls-for-staging-schema

Starting with JChem Cartridge version 5.5.1, the staging schema will be emptied by the upgrade program after it is no longer needed, so it is ready for use during subsequent upgrades. With prior versions the schema can be emptied by executing the script published under this forum post.

Upgrade

Stop users from using JChem Cartridge Restrict access to the Oracle instance and/or to the applications accessing the JChem Cartridge so you can safely upgrade the JChem Cartridge without user requests and the installation procedure interfering with each other.
Stop the old JChem Server version
- If you use your operating system's service facility for automatically starting and stopping JChem Server, use that facility to stop it first
- Execute server.sh stop (or server.bat stop) in the cartridge directory of old JChem version The second step must be performed even if the service appeared to stop correctly in the first step.
Navigate to the cartridge directory of the new JChem version in a command interpreter Change your current working directory to the cartridge directory of the new JChem version. (Users with multiple JChem Cartridge instances reusing the same JChem package, may want to look at this forum topic at this point.)
Start the upgrade application Start the script upgrade.sh (or upgrade.bat on Windows). (The main purpose of the script is to upgrade the PL/SQL objects associated with the JChem Cartridge implementation. But it will also try migrating JChem Server settings, license files, rebuilding indexes, etc.)
Provide the input requested by the script
- If you answer a prompt for a property with a question mark (?), a more detailed description of the property will be displayed.
- When prompted for the DBA login, enter an exclamation mark (!), if you don't want the installer program to use DBA account. See this pre-upgrade task for steps required before installation in such cases.
- As the last part of this step, Windows user will be offered the option to upgrade the JChem Cartridge windows service. Select this option only if the windows service was created by the JChem Cartridge installer during the original installation.

Post-upgrade tasks

On Windows, the upgrade application will offer to upgrade the Windows service for JChem Cartridge server. On other operating systems, you must make sure to update the service configuration to point to the new JChem package.
Start JChem Server.
If you ran the upgrader application without DBA privileges, you have to
- rebuild JChem indexes on plain structure tables by executing the following SQL statement:
```
alter index <index-name> rebuild parameters('upgradeOnly=y')
```
Upgrade JChem structure tables (if you use any) using JChemManager.

Upgrading JChem Server with RAC

Upgrading single JChem Server with RAC

Execute the same steps as for JChem Servers without RAC.

Upgrading multiple JChem Servers with RAC

On the first node execute the same steps as for JChem Servers without RAC.

On all the other nodes follow the steps described for JChem Servers without RAC but instead of starting the script upgrade.sh (or upgrade.bat on Windows) without any parameter as described in the 4th step in the Upgrade section, add the --jcserver-only parameter.

upgrade.sh --jcserver-only