JChem Oracle Cartridge doesn't work on Oracle databases (11gR2,12cR1,12cR2) patched with OJVM_UPDATE_171018 (2017 Oct).
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.
install-interact.bat have been deprecated in favor of
JAVA_HOMEto the home directory of a Oracle Java Development Kit version 1.6 or newer.
/opt/chemaxon/jchem-cartridgeon Unix/Linux or
C:\ChemAxon\JChem-Cartridgeon 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.
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
on Unix or
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
on Unix or
on Windows. The
config-util program accepts the following options for both listing commands:
The options which are not specified on the command line will be prompted for interactively.
localhostas host name can only be applied if Oracle JChem Cartridge and JChem Server will be located on the same host.
The following steps have to be performed on the host where the JChem Cartridge server will be running:
cartridgedirectory 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.
install.shshell script or
?), a more detailed description of the property will be displayed.
Sidconnection type, you will be asked for host, port and Oracle sid to identify the connection.
Service nameconnection type, you will be asked for host, port and the service name to identify the connection.
Urlconnection type, you can enter the whole connection string of the connection.
y, you will be prompted for the name of the service.
chkconfigconfigruation file for RedHat-based distributions. To use it:
jcc_userin the downloaded file to values appropriate for your environment. (The service will be started using the identity of the user pointed to by the
jccas root into the
chmod +x jcc
chkconfig jcc on(For more information, see the following Forum post: https://forum.chemaxon.com/viewpost16398.html#16398)
The following two solutions are provided for installing 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.
JChem Cartridge has been tested on Oracle RAC with
mydbAssume that JChem Server will be hosted by a computer called
install.sh shell script on
url, when prompted for Type of the connection.
jdbc:oracle:thin:@10.0.0.83:1521/mydb1, when prompted for Name of the Oracle database (url).
shiraz, when prompted for The host name of the JChem Server.
urlproperty in the
jchem/cartridge/conf/jcart.propertiesfile of the JChem Server with the following value:
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.
JChem Cartridge has been tested on Oracle RAC with
urlconnection type and entering the full connection string. It can be changed later if necessary.
a clustered service called
mydb On each node there must be JChem Server installed applying
localhost as host name of JChem Server.
install.sh shell script on the first computer (clu0)
--jcserver-only switch on the second one (clu1) (and on all other computers present in the cluster)
url, when prompted for Type of the connection.
jdbc:oracle:thin:@localhost:1521/mydb0on clu0, and
jdbc:oracle:thin:@localhost:1521/mydb1on clu1, when prompted for Name of the Oracle database (url).
localhost, 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 on both nodes with the following value:
See our FAQ comment regarding the outage of a 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:
cartridgedirectory your current working directory
server.sh on Unix/Linux
server.bat on Windows) with the
Executing the following command in the jchem/cartridge directory on the JChem Server host
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 ).
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:
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
reservedMemoryproperty set in the jchem/cartridge/conf/jcart.properties file.
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.
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.
jcserver.maxmem property in the
Make sure the letter
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.
Assuming that the
jc_idxtype index called JCXCDDR_0 has been created
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
jcserver.maxmemproperty in jchem/cartridge/conf/jcart.properties must therefore be set to 2.25m.
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:
for Windows. (If you enter a question mark a more detailed help messages will be displayed on the input parameter required.)
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
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.
If JChem Server is using the default data source implementation (oracle.jdbc.pool.OracleDataSource), the connection cache properties can be specified in the jchem/jchemsite/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/jchemsite/cartridge/conf/jcart.properties file, the following:
(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.)
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.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
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
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
command (for reloading certain configuration parameters of JChem Server without having to stop it), you might also consider adding:
Connect to the database as the JChem-owner (or the JChem-user) to be tested and execute the following:
where <passwd> is the JChem-owner's (or the JChem-user's) password;
If the JChem Cartridge is correctly working, the last call returns an output similar to the following:
The following sql script can be used for testing:
The license file(s) received from ChemAxon's Sales should be dropped into the directory
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:
conf/jcart.propertiesfile). This means that you may have to store them under different name – for example as
license2.cxl, etc. – to avoid overwriting each other.
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());
After putting the license file(s) into their directory (or after changing their location), the JChem Server must always be restarted
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.
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.
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:
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.
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:
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:
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.
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.
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:
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.
Reusing the parent session's identity:
Defaulting to a pre-configured identity
the users start to work with grants to the already existing jchem objects
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:
on Unix or
on Windows. The
config-util program accepts the following options for this listing command:
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.
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.
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:
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.)
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:
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);
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);
privman_pkg.grants_on_jcidxprocedure in the JChem Cartridge owner's schema to grant the desired privilege on the index. This procedure accepts the following parameters:
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.
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:
(If you enter a question mark a more detailed help messages will be displayed on the input parameter required.)
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.
The following example grants the
CCDR19_READER Oracle user "search permission" on the index
CCDR19 owned by the user
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.
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:
The memory resources are:
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:
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
Tasks started to create or rebuild indexes can also be tracked via the
v$session_longops table. To do this, the
chemaxon.jchem.cartridge.indexingIsAynch has to be set to true in the jchem/cartridge/conf/jcart.properties file. See the
refreshPeriod index parameter for more information.
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:
Search tasks running on the JChem server can be killed by executing the following SQL:
<task-id> can be obtained from the listing of running tasks. The following strategy can be used to programmatically kill search tasks:
Before executing the search, obtain the JCC session id by executing
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.)
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
will dump the current execution stack of the JCC server's threads to the standard error.
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:
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.
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.
To remove JChem Cartridge
jc_idxtypeindexes 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).
The following upgrade instructions apply to JChem Cartridge version 5.0 or later. See our FAQ comment regarding JChem Cartridge upgrade test.
upgrade-evol.bat have been deprecated in favor of
JAVA_HOMEenvironment variable to the home directory of a Oracle Java Development Kit version 1.6 or newer.
(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
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.
cartridgedirectory of old JChem version The second step must be performed even if the service appeared to stop correctly in the first step.
cartridgedirectory of the new JChem version in a command interpreter
cartridgedirectory 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.)
upgrade.baton 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.)
?), a more detailed description of the property will be displayed.
rebuild JChem indexes on plain structure tables by executing the following SQL statement:
Execute the same steps as for JChem Servers without 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.bat on Windows) without any parameter as described in the 4th step in the Upgrade section, add the