Introduction

• JS7 JS1 offers two levels of integration with an Oracle DBMS:
  • JS7 JS1 supports use of Oracle as the JS7 - Database, for the database for this scenario see JS7 - How to make JOC Cockpit connect to an Oracle database using Wallet®.
  • JS7 JS1 offers job templates from JS7 - JITL Database Jobs that can be used to access Oracle databases.
    • Such jobs are preferably executed with Agents, therefore the below explanations apply to servers for which Agents are operated.

• For both scenarios users might prefer not to provide a user account and password for authentication with the DBMS from readable files.

  • Use of passwords is considered insecure when passwords are stored in clear text in external files or in job parameters.
  • JS7 offers JS7 - Use of JS1 offers Using a Credential Store with JITL for Jobs as an alternative way to store and to retrieve passwords.
  • The Oracle Wallet® provides a credential store to connect to an Oracle database without specifying a user account and password from parameters or from readable files.
• The following JITL Jobs can be used with Oracle Wallet®:
  • JS7 - JITL SQLExecutorJobJob JobSchedulerManagedDatabaseJobSOSHibernate: Standard JDBC Job for any DBMS
  • JS7 - JITL PLSQLJobJob JobSchedulerPLSQLJob: PL/SQL JDBC job for Oracle DBMS
  • JS7 - JITL SQLPLUSJobJob SOSSQLPlusJob: SQL*Plus Command Line Client

...

Anchor
wallet wallet

Oracle Wallet®

Except for use of the JS7 - JITL SQLPLUSJobthe Job SOSSQLPlusJob no Oracle Client installation is required at run-time for use of a wallet with JS7 JS1 Agents.

However, users need an Oracle Client to set up and to configure the wallet.

• The wallet does not necessarily have to be created on the machines where JS7 JS1 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 host the JS7 JS1 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/js7js1/wallet -create # add credentials to the wallet; specify key, user account and password for database access mkstore -wrl /home/js7js1/wallet/ -createCredential js7js1 some_account some_password # check that the key has been added to the wallet mkstore -wrl /home/js7js1/wallet/ -listCredential

Anchor
jdbc jdbc

Oracle JDBC Driver

The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJobJob JobSchedulerManagedDatabaseJobSOSHibernate and Job JobSchedulerPLSQLJob make use of the Oracle JDBC Driver.

• Check the Oracle JDBC Driver version that ships with the JS7 JS1 release, see JS7 - Database, chapter: Individual JDBC Driver VersionsWhich common Database Management Systems are supported by JobScheduler. A newer JDBC Driver might be available for download from Oracle.
• Oracle JDBC Drivers that ship for release 18c of the DBMS are reported to work. Previous Oracle JDBC Driver releases, for example 12c, are reported not to work with Oracle Wallet® when used by JS7 JS1. If in doubt use the Oracle JDBC Driver version that matches the version of the DBMS.
• Users who want to use a specific version of the Oracle JDBC Driver can apply the following steps:
  • For on premises installations store the Oracle JDBC Driver's .jar file in the JS7SCHEDULER_AGENT_HOME/lib/user_lib directory of the Agent installation directory.
  • When running JS7 Agent containers for Docker® consider to store the Oracle JDBC Driver's .jar file in the JS7_AGENT_CONFIG_DIR/lib directory.

Anchor
pki pki

AnchorpkipkiOracle PKI Libraries

The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJobJob JobSchedulerManagedDatabaseJobSOSHibernate and Job JobSchedulerPLSQLJob make use of Oracle PKI Libraries.

• The Oracle PKI libraries are required and have to match the version of the Oracle DBMS and Oracle JDBC Driver.
• The .jar files are offered by Oracle for download and are available from an Oracle Client installation for example from:
  • ORACLE_HOME/jlib/oraclepki.jar
  • ORACLE_HOME/jlib/osdt_cert.jar
  • ORACLE_HOME/jlib/osdt_core.jar
• For on premises installations store the Oracle PKI libraries to the JS7_AGENTSCHEDULER_HOME/lib/user_lib directory of the JS7 JS1 Agent installation directory.
• When running JS7 Agent containers for Docker® consider to store the Oracle PKI libraries in the JS7_AGENT_CONFIG_DIR/lib directory.

Anchor
configuration configuration

AnchorconfigurationconfigurationConfiguration

Anchor
hibernate_cfg_xml hibernate_cfg_xml

Hibernate hibernate.cfg.xml Configuration File

The JS7 - JITL SQLExecutorJobJob JobSchedulerManagedDatabaseJobSOSHibernate makes use of a Hibernate configuration file.

The JS7 - JITL PLSQLJobJob JobSchedulerPLSQLJob does not use a Hibernate configuration file but uses the job argument db_url for the database URL.

• The below explanations about use of a URL such as jdbc:oracle:thin:@/js7js1?tns_admin=/home/js7js1/wallet similarly apply.
• For use with Oracle Wallet® the job arguments db_user and db_password are omitted.

A Hibernate configuration file by default is looked up in JS7_AGENT_CONFIG_DIR/from SCHEDULER_DATA/config/hibernate.cfg.xml, see JS7 - Database. The Hibernate configuration can look like this:

<?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:@/js7js1?tns_admin=/home/js7js1/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 configuration file.
• The connection URL specifies js7 js1 as the key to an entry in the wallet.
• The URL parameter tns_admin is used to specify the directory of the tnsnames.ora configuration file. JDBC Connections usually do not need this configuration file as connection details (Listener, Service Name, Service ID) are specified with the URL. However, due to use of the js7 key js1 key to the wallet in the URL it is preferable to manage connection details from a tnsnames.ora configuration file.
• In the above example this file is located in the /home/js7js1/wallet directory that in fact is the directory where the wallet is located. This location is not authoritative 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.

...

# tnsnames.ora Network Configuration File: /home/js7js1/product/18.0.0/dbhomeXE/NETWORK/ADMIN/tnsnames.ora
# Generated by Oracle configuration tools.

JS7JS1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.11.0.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JS7JS1)
    )
  )

LISTENER_JS7JS1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.11.0.99)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

...

Explanation:

• Line 4: The name JS7 of JS1 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.

Anchor
wallet_location wallet_location

Wallet Location for Java

The JS7 - JITL SQLPLUSJobJob SOSSQLPlusJob identifies the wallet location from its sqlnet.ora configuration file.

The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJobJob JobSchedulerManagedDatabaseJobSOSHibernate and Job JobSchedulerPLSQLJob identify the wallet location from a Java define.

• Configure the location of the wallet by use of a Java define like this: 
  -Doracle.net.wallet_location=/home/js7js1/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 the the jobscheduler_agent_<port>.sh|.cmd Instance Start Script.
  • for Unix add the JAVA_OPTIONS environment variable to the systemd service file, see JS7 - systemd Service Files for automated Startup / Shutdown with Unix Systems
  • Find further details from JS7 - How To - Apply Java Options.

Using Oracle Wallet® for the JITL SQLExecutorJob

• • .

Using Oracle Wallet® for the JITL JobSchedulerManagedDatabaseJobSOSHibernate

JS1 offers the Job JobSchedulerManagedDatabaseJobSOSHibernateJS7 offers the JS7 - JITL SQLExecutorJob template for use with Oracle Wallet®.

The job template is running with JS7 JS1 Agents and performs standard SQL operations for any DBMS including Oracle. This job template cannot be used to execute PL/SQL code that is specific for Oracle.

...

All of the above explained Configuration items apply.

Using Oracle Wallet® for the JITL

...

JobSchedulerPLSQLJob

JS7 JS1 offers the JS7 - JITL PLSQLJob template Job JobSchedulerPLSQLJob template for use with Oracle Wallet®.

The job template is running with JS7 JS1 Agents and can be used to execute PL/SQL code that is specific for Oracle. This job template can be used for the Oracle DBMS only.

...

All of the above explained Configuration items apply.

Using Oracle Wallet® for the JITL

...

SOSSQLPlusJob

JS7 JS1 offers the JS7 - JITL SQLPLUSJob Job SOSSQLPlusJob template for use with Oracle Wallet®.

The job template is running with JS7 JS1 Agents and makes use of the sqlplus Command Line Client. This job template requires prior installation of an Oracle Client that includes the SQL*Plus Command Line Client.

...

• Add the location of the wallet to your sqlnet.ora configuration file, for example:
  
  • WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/js7js1/wallet)))
  • Additional entries will be required for this file, please check Oracle's documentation.
• This file is required by SQL*Plus and allows to execute the command line client like this: sqlplus /@js7@js1. 
• The  js7 js1 is the key for the tnsnames.ora configuration file to identify the database connection settings and for the wallet to identify the matching credentials.

...

• by adding environment variable to the Agent Instance Start Script orby setting up JS7 - Job Resources to inject environment variables to workflows and jobs.

Environment Variables from the Agent Instance Start Script

• Adjust Agent Instance Start Script
  • For Unix add environment variables to the Agent Instance Start Script .SCHEDULER_HOME/bin/jobscheduler_agent_<port>.sh
    • ORACLE_HOME=/some_location
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=/some_location
export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN
  • For Windows add environment variables to the Agent Instance Start Script ./bin/SCHEDULER_HOME\bin\jobscheduler_agent_<port>.cmd
    • set ORACLE_HOME=C:\some_location
set LD_LIBRARY_PATH=%ORACLE_HOME%\lib
set TNS_ADMIN=C:\some_location
  • The Instance Start Script is executed on startup of the Agent in the context of the user account that the Agent is operated for. The environment variables are forwarded to subsequent jobs in a workflow.
• Restart the Agent.

Environment Variables from Job Resources

...

• Agent.

Oracle sqlnet.ora Configuration File

...

# sqlnet.ora Network Configuration File: /home/js7js1/product/18.0.0/dbhomeXE/NETWORK/ADMIN/sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/var/sos-berlin.com/js7/agent/var_4445/config/home/js1/wallet)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

...

• The wallet location specified from /var/sos-berlin.com/js7/agent/var_4445/config/home/js1/wallet is a possible location that corresponds to JS7_AGENT_CONFIG_DIR/config/wallet. Any location that is within reach of the JS7 JS1 Agent and that allows to read the wallet's files can be used.

...