Oracle Data Integrator Cheat Sheet

This blog post contains some useful commands and tasks for the lifecycle of Oracle Data Integrator. They don’t fit in any other post so I collect them here.

General

# show the contents of a keystore
keytool -list -v -keystore /home/oracle/Oracle/Middleware/Oracle_Home/wlserver/server/lib/DemoTrust.jks
# enable ODI Studio Debugging for certificate or https errors (restart ODI Studio)
cat >>/home/oracle/Oracle/Middleware/Oracle_Home/odi/studio/bin/odi.conf <<EOF
AddVMOption -Djavax.net.debug=all
AddVMOption -Djava.security.debug=certpath
EOF
# refresh built-in KMs (via groovy script in ODI Studio)
OdiAdapter.getInstance(odiInstance).refreshRepositorySeededObjects (odiInstance, true);

Recreate DemoTrust.jks

Sometimes these keystores need to be recreated either when they are corrupt, or when they have been removed from the system.

# run as user oracle
echo yes|keytool -importcert -trustcacerts -alias wlscertgenca -storetype JKS -keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase -file /home/oracle/Oracle/Middleware/Oracle_Home/wlserver/server/lib/CertGenCA.der
cp -f DemoTrust.jks  /home/oracle/Oracle/Middleware/Oracle_Home/wlserver/server/lib/

[oracle@lin7 ~]$ # run as user oracle
[oracle@lin7 ~]$ echo yes|keytool -importcert -trustcacerts -alias wlscertgenca -keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase -file /home/oracle/Oracle/Middleware/Oracle_Home/wlserver/server/lib/CertGenCA.der
Owner: CN=CertGenCA, OU=FOR TESTING ONLY, O=MyOrganization, L=MyTown, ST=MyState, C=US
Issuer: CN=CertGenCA, OU=FOR TESTING ONLY, O=MyOrganization, L=MyTown, ST=MyState, C=US
Serial number: 40044886c441ef3b643a8066409afca0
Valid from: Sat Dec 01 04:07:51 CET 2012 until: Thu Dec 02 04:07:51 CET 2032
Certificate fingerprints:
         SHA1: CA:61:71:5B:64:6B:02:63:C6:FB:83:B1:71:F0:99:D3:54:6A:F7:C8
         SHA256: 57:10:7C:2C:B3:07:B9:8B:F8:FD:EB:69:99:36:53:03:7A:E1:E7:CB:D3:7A:E7:CF:30:F3:B3:ED:F3:42:0A:D7
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 2048-bit RSA key
Version: 3

Extensions:

#1: ObjectId: 2.5.29.19 Criticality=true
BasicConstraints:[
  CA:true
  PathLen:1
]

#2: ObjectId: 2.5.29.15 Criticality=true
KeyUsage [
  Key_CertSign
]

#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 34 38 FD 45 D8 80 CF C7   D2 E8 DF 1D F8 A1 39 B0  48.E..........9.
0010: 11 88 00 6A                                        ...j
]
]

Trust this certificate? [no]:  Certificate was added to keystore
[oracle@lin7 ~]$ cp -f DemoTrust.jks  /home/oracle/Oracle/Middleware/Oracle_Home/wlserver/server/lib/
[oracle@lin7 ~]$

See also Doc ID 1392455.1 on how to recreate these keys.

Adding certificates to the Trust Store

To connect to https servers (for example with a RESTful Service Data Server), the certificate needs to be trusted. You might get an error similar to the following if the certificate is not trusted:

2025-09-02 20:04:27.873 NOTIFICATION Testing URL : https://openlibrary.org
javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

With the following script it is easy to add a certificate of a https server to the trust store used by Oracle Data Integrator (DemoTrust.jks):

cat > /usr/local/bin/cacerts.sh <<EOF
#!/bin/sh
/usr/bin/openssl s_client -showcerts -connect \$1:443 </dev/null 2>/dev/null | /usr/bin/openssl x509 -outform PEM > /tmp/\$1.pem
echo 'DemoTrustKeyStorePassPhrase
yes'|/usr/lib/jvm/jdk-17.0.16-oracle-x64/bin/keytool -import -trustcacerts -file /tmp/\$1.pem -alias \$1 -keystore /home/oracle/Oracle/Middleware/Oracle_Home/wlserver/server/lib/DemoTrust.jks
rm /tmp/\$1.pem
EOF
chmod 700 /usr/local/bin/cacerts.sh

To add a certificate (for example for openlibrary.org) run the script as follows:

# as root:
cacerts.sh openlibrary.org

[root@lin7 ~]# cacerts.sh openlibrary.org
Enter keystore password:  Owner: CN=www.openlibrary.org
Issuer: CN=R10, O=Let's Encrypt, C=US
Serial number: 5fd3ebb341bf2f2bf6457481968943cc1ea
Valid from: Sat Jul 05 01:06:33 CEST 2025 until: Fri Oct 03 01:06:32 CEST 2025
Certificate fingerprints:
         SHA1: FD:86:C9:5A:DF:C4:E9:D3:EE:C4:DA:7D:75:96:31:CE:49:AF:F6:AC
         SHA256: E6:47:84:FC:BC:A6:27:75:19:32:6A:AB:C1:A1:C0:68:13:2F:51:FC:1D:C3:F1:F5:34:37:6F:EF:9D:A1:A6:6A
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 2048-bit RSA key
Version: 3

Extensions:

#1: ObjectId: 1.3.6.1.4.1.11129.2.4.2 Criticality=false
0000: 04 81 F2 00 F0 00 76 00   0D E1 F2 30 2B D3 0D C1  ......v....0+...
0010: 40 62 12 09 EA 55 2E FC   47 74 7C B1 D7 E9 30 EF  @b...U..Gt....0.
0020: 0E 42 1E B4 7E 4E AA 34   00 00 01 97 D7 E6 9A 3E  .B...N.4.......>
0030: 00 00 04 03 00 47 30 45   02 21 00 C7 18 E4 75 69  .....G0E.!....ui
0040: A8 1C 69 5B DB FF DF EF   94 14 12 6F F5 FB A7 81  ..i[.......o....
0050: 3C 69 A6 C7 96 D8 A1 CF   B4 16 ED 02 20 66 56 3C  <i.......... fV<
0060: 04 CA EF 4D AD 51 80 41   79 28 F3 5C 0C 04 F8 D6  ...M.Q.Ay(.\....
0070: CE E7 F6 10 E0 45 C4 F6   17 78 62 A6 60 00 76 00  .....E...xb.`.v.
0080: A4 42 C5 06 49 60 61 54   8F 0F D4 EA 9C FB 7A 2D  .B..I`aT......z-
0090: 26 45 4D 87 A9 7F 2F DF   45 59 F6 27 4F 3A 84 54  &EM.../.EY.'O:.T
00A0: 00 00 01 97 D7 E6 9A 3E   00 00 04 03 00 47 30 45  .......>.....G0E
00B0: 02 20 19 DD F9 E8 C4 EF   E7 A4 DC 8A 8A D4 D5 C5  . ..............
00C0: 10 3D A2 1D 44 A5 D8 6C   0B 33 34 B8 9F 2C C9 8D  .=..D..l.34..,..
00D0: 35 E5 02 21 00 B0 04 36   63 20 EF 50 6C 21 EB E0  5..!...6c .Pl!..
00E0: A8 46 D6 75 7C 66 7E 4C   DE 9E 68 DD DF 85 4E 6F  .F.u.f.L..h...No
00F0: 18 60 32 09 BD                                     .`2..


#2: ObjectId: 1.3.6.1.5.5.7.1.1 Criticality=false
AuthorityInfoAccess [
  [
   accessMethod: caIssuers
   accessLocation: URIName: http://r10.i.lencr.org/
]
]

#3: ObjectId: 2.5.29.35 Criticality=false
AuthorityKeyIdentifier [
KeyIdentifier [
0000: BB BC C3 47 A5 E4 BC A9   C6 C3 A4 72 0C 10 8D A2  ...G.......r....
0010: 35 E1 C8 E8                                        5...
]
]

#4: ObjectId: 2.5.29.19 Criticality=true
BasicConstraints:[
  CA:false
  PathLen: undefined
]

#5: ObjectId: 2.5.29.31 Criticality=false
CRLDistributionPoints [
  [DistributionPoint:
     [URIName: http://r10.c.lencr.org/107.crl]
]]

#6: ObjectId: 2.5.29.32 Criticality=false
CertificatePolicies [
  [CertificatePolicyId: [2.23.140.1.2.1]
[]  ]
]

#7: ObjectId: 2.5.29.37 Criticality=false
ExtendedKeyUsages [
  serverAuth
  clientAuth
]

#8: ObjectId: 2.5.29.15 Criticality=true
KeyUsage [
  DigitalSignature
  Key_Encipherment
]

#9: ObjectId: 2.5.29.17 Criticality=false
SubjectAlternativeName [
  DNSName: openlibrary.org
  DNSName: www.openlibrary.org
]

#10: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: DD 2A 29 18 56 88 A5 98   35 C4 63 67 C0 D4 E2 D8  .*).V...5.cg....
0010: 63 72 55 3B                                        crU;
]
]

Trust this certificate? [no]:  Certificate was added to keystore
[root@lin7 ~]#

Afterwards ODI Studio needs to be restarted and the connection test with the RESTful Service Data Server should be successful.

Adding a Standalone Collocated Agent

A Standalone Collocated Agent is part of the WebLogic Server and can be added as follows:

Add the Agent to the existing WebLogic Domain

# start the Configuration Wizard (as oracle in a X terminal)
cd /home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/common/bin
./config.sh

Select “Update an existing domain” and click Next. Select “Oracle Data Integrator – Standalone Collocated Agent” in the Templates Window. Click Get RCU Configuration. Click Next 3 times. In “Advanced Configuration” select “System Components” and click Next two times. Fill the “ODI Server Configuration” Window as follows:

The SUPERVISOR password is changeme#. In the Machines window remove LocalODIMachine from Machines. Click Next. Add the OracleDIAgent1 to the ODI_machine1. Click Next and then Update.

Configure the new Agent in ODI Studio

Start the ODI Studio, connect to the Master Repository and add the new Agent. Go to Topology => right click on Agents => New Agent. Fill the fields as follows (OracleDIAgent1, lin7.fritz.box):

Save the data entered and exit ODI Studio.

Creating start and stop scripts for the Agent

# run as root
cat >/usr/local/bin/startagent1.sh <<EOF
#!/bin/bash
su - oracle -c '
export JAVA_HOME=/usr/lib/jvm/jdk-17.0.16-oracle-x64/
export PATH=\$JAVA_HOME/bin:\$PATH
cd /home/oracle/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain/bin/
echo changeme#|./startComponent.sh OracleDIAgent1'
EOF
cat >/usr/local/bin/stopagent1.sh <<EOF
#!/bin/bash
su - oracle -c '
export JAVA_HOME=/usr/lib/jvm/jdk-17.0.16-oracle-x64/
export PATH=\$JAVA_HOME/bin:\$PATH
cd /home/oracle/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain/bin/
echo changeme#|./stopComponent.sh OracleDIAgent1'
EOF
chmod 700 /usr/local/bin/startagent1.sh
chmod 700 /usr/local/bin/stopagent1.sh

Now the Agent can be started and stopped by running:

# run as root
# start OracleDIAgent1
startagent1.sh
# stop OracleDIAgent1
stopagent1.sh

Verify the status of the Agent

We can use ODI Studio to verify if the Agent is running (Topology => Agents => right click on OracleDIAgent1 => Test):

Adding jar files to ODI Studio

Sometimes when using ODI Studio we get class not found errors like the following:

java.lang.NoClassDefFoundError: com/jayway/jsonpath/Configuration
Caused by: java.lang.ClassNotFoundException: com.jayway.jsonpath.Configuration cannot be found by oracle.odi.navigator_14.1.2

The solution is to add the correct jar files to the ODI Studio config. In order to make this process easy, we can use the following steps.

# this creates the script used to search for the jarfiles
# run as root
cat >/usr/local/bin/searchodijars.sh <<EOF
#!/bin/bash

while read line; do
  if [[ \$line == JARFILENAMEANDPATH* ]] ; then
    jarfilename=\`echo \$line|cut -d ' ' -f 2\`
  fi
  if [[ \$line == *\$1* ]] ; then
    echo 'AddJavaLibFile ../../../'\$jarfilename
    break
  fi
done <~/jarcontents.txt
EOF
chmod 700 /usr/local/bin/searchodijars.sh

# create a text file containing the contents of the jar files
# this needs to be done only once, but should be repeated after an ODI upgrade
# run as root
cd /home/oracle/Oracle/Middleware/Oracle_Home
for i in `ls oracle_common/modules/thirdparty/*jar` ; do echo JARFILENAMEANDPATH $i; unzip -t $i ; done > ~/jarcontents.txt
for i in `ls oracle_common/modules/thirdparty/features/*jar` ; do echo JARFILENAMEANDPATH $i; unzip -t $i ; done >> ~/jarcontents.txt
for i in `ls oracle_common/modules/*jar` ; do echo JARFILENAMEANDPATH $i; unzip -t $i ; done >> ~/jarcontents.txt

To use the script we can use it to find the jar file containing for example: com/jayway/jsonpath/Configuration.class as follows:

searchodijars.sh com/jayway/jsonpath/Configuration.class

This gives us the following line that can directly be added to the ODI Studio conf file (/home/oracle/Oracle/Middleware/Oracle_Home/odi/studio/bin/odi.conf):

AddJavaLibFile ../../../oracle_common/modules/thirdparty/json-path-2.9.0.jar

Don’t forget to restart ODI Studio for the changes to take effect.