Page History
...
- The wallet does not necessarily have to be created on the machines where JS7 Agents are located. The wallet preferably consists of a number of keystore and truststore files that can be copied from a remote machine to the servers that hosts the JS7 Agents.
Typical commands to create a wallet include for example:
Code Block title Example how to set up a wallet linenumbers true # create the wallet in an arbitrary location mkstore -wrl /home/js7/wallet -create # add credentials to the wallet; specify key, user account and password for database access mkstore -wrl /home/js7/wallet/ -createCredential js7 some_account some_password
...
Anchor | ||||
---|---|---|---|---|
|
The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJob make use of the Oracle PKI Libraries.
- The Oracle PKI A number of Oracle Java libraries are required that have to match the version of the Oracle DBMS and Oracle JDBC Driver.
- The .jar files are available from an Oracle Client installation and are offered by Oracle for download:
ORACLE_HOME/jlib/oraclepki.jar
ORACLE_HOME/jlib/osdt_cert.jar
ORACLE_HOME/jlib/osdt_core.jar
- For on premises installations store the libraries in the
JS7_AGENT_HOME/lib/user_lib
directory of the Agent installation directory. - When running Agent containers for Docker® consider to store the Oracle JDBC Driver and libraries in the
JS7_AGENT_CONFIG_DIR/lib
directory.
Anchor | ||||
---|---|---|---|---|
|
Som JITL configured to connect to an Oracle database by use of Hibernate. In addition the locations of Oracle configuration files and of the wallet have to be specified.
...
Anchor | ||||
---|---|---|---|---|
|
The JS7 - JITL SQLExecutorJob makes use of a Hibernate configuration file. A Hibernate file by default is looked up from JS7_AGENT_CONFIG_DIR/hibernate.cfg.xml
...
- Location:
JETTY_BASE/resources/joc/hibernate.cfg.xml
, see JS7 - Database.
...
title | Hibernate configuration file for Oracle® database |
---|
.
The JS7 - JITL PLSQLJob does not use a Hibernate configuration file but the job argument db_url
for the database URL.
- The below explanations about use of a URL such as
jdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet
similarly apply. - For use with Oracle Wallet® the job arguments
db_user
anddb_password
are omitted.
The Hibernate configuration file is located in JETTY_BASE/resources/joc/hibernate.cfg.xml
, see JS7 - Database. The hibernate configuration can look like this:
Code Block | ||
---|---|---|
| ||
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver< |
...
/property>
<property name="hibernate.connection. |
...
password"></property> <property name="hibernate.connection. |
...
url">jdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet</property> <property name="hibernate.connection.username"></property> <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property> <property name="hibernate.show_sql">false</property> <property name="hibernate.connection.autocommit">false</property> <property name="hibernate.format_sql">true</property> <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property> <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property> <property name="hibernate.hikari.maximumPoolSize">10</property> </session-factory> </hibernate-configuration> |
- Consider the empty elements that are used for the account and password. Do not delete the respective elements from the hibernate Hibernate configuration file.
- The connection URL specifies
js7
as the key to an entry in the wallet. - The URL parameter
tns_admin
is used to specify the directory of thetnsnames.ora
configuration file. JDBC Connections usually would do not need this configuration file as connection details (Listener, Service Name, Service ID) are specified with the URL. However, due to use of thejs7
key to the wallet in the URL it is preferable to manage connection details from atnsnames.ora
configuration file. - In the above example this file is located in the
/home/js7/wallet
directory that in fact is the directory where the wallet is located. This location is not required as the file can reside in any directory that is accessible to JOC Cockpit. - Consider that an
sqlnet.ora
configuration file is not used with the above setup of a JDBC connection.
Anchor | ||||
---|---|---|---|---|
|
Use of the tnsnames.ora
file applies to all JITL Jobs.
The following example is not authoritative but is intended to explain a few basic settings:
...
- Line 4: The name
JS7
of the first entry in this file corresponds to the key for which credentials have been stored to the wallet. - Line 5-9: The settings indicate the Listener's host and port and the database Service Name or Service ID.
...
- Configure the location of the wallet by use of a Java define like this:
-Doracle.net.wallet_location=/home/js7/wallet
. This setting should point to the directory where the wallet files are located. This setting can be specified with one of the following options:- specify the Java define with the
jettyOptions
setting of thejoc_install_xml
installer response file like this:<entry key="jettyOptions" value="-Doracle.net.wallet_location=/home/js7/wallet"/>
- alternatively, for Unix use one of the following options:
- specify the
JAVA_OPTIONS
environment variable before running the JOC Cockpitjetty.sh
start script. - create/modify and make executable the
/home/js7/.jocrc
file, assuming thatjs7
is the JOC Cockpit run-time account. This file should export theJAVA_OPTIONS
environment variable like this:export JAVA_OPTIONS="-Doracle.net.wallet_location=/home/js7/wallet"
- add the
JAVA_OPTIONS
environment variable to thesystemd
service file, see JS7 - systemd Service Files for automated Startup / Shutdown with Unix Systems
- specify the
- Find further details from JS7 - How To - Apply Java Options.
- specify the Java define with the
Using Oracle Wallet® for the JITL SQLExecutorJob and PLSQLJob with Agents
The JS7 offers the following job templates for use with Oracle Wallet®:
- JS7 - JITL SQLExecutorJob : Standard JDBC Job for any DBMS
- JS7 - JITL PLSQLJob : PL/SQL JDBC job for Oracle DBMS
Both template jobs are running with Agents, therefore the wallet configuration is applied to the respective Agent.
Prerequisites
- The Oracle Wallet® is required, see Prerequisites: Oracle Wallet
- The JDBC Driver is required, see Prerequisites: Oracle JDBC Driver
- To apply a version of the JDBC Driver that is different from the version that ships with JS7 Agents, see JS7 - Database, chapter: Individual JDBC Driver Versions.
- The Oracle PKI Libraries are requires, see Prerequisites: Oracle PKI Libraries
- Store the libraries in the
JS7_AGENT_HOME/lib/user_lib
directory of the Agent installation directory. When running Agents for Docker® consider to store the JDBC Driver and libraries in theJS7_AGENT_CONFIG_DIR/lib
directory.
- Store the libraries in the
Configuration
Hibernate hibernate.cfg.xml configuration file
- The JS7 - JITL SQLExecutorJob optionally makes use of a Hibernate configuration file. The explanations above from chapter Hibernate hibernate.cfg.xml Configuration File apply. A Hibernate file by default is looked up from
JS7_AGENT_CONFIG_DIR/hibernate.cfg.xml
. - The JS7 - JITL PLSQLJob does not use a Hibernate file but the job argument
db_url
for the database URL. Above explanations about use of a URL such asjdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet
similarly apply. - For use with Oracle Wallet® the job arguments
db_user
anddb_password
are omitted.
Oracle tnsnames.ora configuration file
...
- this file corresponds to the key for which credentials have been stored to the wallet.
- Line 5-9: The settings indicate the Listener's host and port and the database Service Name or Service ID.
Anchor | ||||
---|---|---|---|---|
|
- Configure the location of the wallet by use of a Java define like this:
-Doracle.net.wallet_location=/home/js7/wallet
. This setting should point to the directory where the wallet files are located. This setting can be specified for an Agent with one of the following options:- specify the
JAVA_OPTIONS
environment variable before running theagent_<port>.sh|.cmd
Instance Start Script. - for Unix add the
JAVA_OPTIONS
environment variable to thesystemd
service file, see JS7 - systemd Service Files for automated Startup / Shutdown with Unix Systems - Find further details from JS7 - How To - Apply Java Options.
- specify the
...
The template job is running with Agents and makes use of the sqlplus
Command Line Client, therefore the wallet configuration is applied to the respective Agent.
Prerequisites
Prerequisites to execute SQL*Plus with Oracle Wallet® include that
...
- Consider that the
mkstore
command will add the location of the wallet to yoursqlnet.ora
configuration file.- This file is required by SQL*Plus and allows to execute the command line client like this:
sqlplus /@js7.
- The
js7
is the key for thetnsnames.ora
configuration file to identify the database connection settings and for the wallet to identify the matching credentials.
- This file is required by SQL*Plus and allows to execute the command line client like this:
Configuration
Environment Variables
The prerequisites to set environment variables for use of SQL*Plus with Oracle Wallet® can be met
...