How to connect to the database using OIC
OIC Connectivity Agent Installation
As not all applications run on the cloud, the agent framework can help us build hybrid cloud solutions, allowing on-premise and cloud applications to communicate with each other. Connectivity Agents are required for Oracle Integration Cloud to exchange messages with on-premise applications like third party applications/Database and E-Business Suite, etc.
The purpose of the document is to provide the steps for installing Oracle Integration Cloud (OIC) agent on on-premise Linux host and Oracle Linux server running on OCI
High-Level Installation Steps:
- Create an Agent Group in the OIC Console
- Download the Connectivity Agent zip file from the OIC Console
- Unzip the contents of the zip file on the on-premise agent machine
- Update the InstallerProfile.cfg with the details of the OIC environment and on-premise network
- Run the agent using java -jar connectivityagent.jar
- Installing Agent in on-premise Linux server
Detailed steps for installing the agent on on-premise Linux server to use any available on-premise database in OIC
- Create new agent group Ex: DB_AGENT_GROUP
- Click Download > Connectivity Agent
- Download the connectivity agent installer to the directory on the on-premise Linux host
Unzip oic_connectivity_agent.zip.
Check for the binaries extracted after oic_connectivity_agent.zip is unzipped.
- Modify InstallerProfile.cfg, include the necessary details.
- Set the JAVA_HOME property to the location of the JDK installation, for executing the connectivityagent.jar.
Note: execute the above-mentioned jar command in nohup mode.
nohup java -jar connectivityagent.jar
After successful installation of the agent, verify in OIC.
Use the on-premise database details to create a DB connection in OIC to integrate
- Installing Agent in Oracle Linux server running on OCI
To connect to Oracle DBaaS from OIC, the agent installation should be done on the VM compartment in which the DBaaS is provisioned.
- Create new agent group Ex: DB_AGENT_GROUP
- Click Download > Connectivity Agent
- Download the connectivity agent installer to the directory on a local machine and then transferred it to the specific directory
- Navigate to the Compute instance details
- Using the Public IP Address of the VM and the key pair generated at the time of creating the instance, use PuTTy tool to connect to the VM
- Login as opc user using the private key file in the SSH authentication
- Login to VM using WinSCP tool to move the oic_connectivity_agent.zip file to the VM server. Use the same private key which was used while provisioning the VM instance
- Copy all the files inside the oic_connectivity_agent.zip into the VM. We can also copy the entire zip file and unzip using the Unix commands.
- Issue the command ‘ls -ltr’ in the PuTTY to verify the files in copied
- Verify the URL, agent group name and username for the OIC instance
How to install Oracle Java on Oracle Linux running in an OCI compute shape by using RPMs available yum servers available within OCI.
Oracle Cloud Infrastructure includes Oracle Java —with support and updates— at no additional cost. By providing Oracle Java RPMs in OCI’s yum servers, installation is greatly simplified
Installing Oracle Java
The Oracle Java RPMs are in the ol7_oci_included repository on Oracle Linux yum server accessible from within OCI.
To enable this repository:
| $ sudo yum install -y –enablerepo=ol7_ociyum_config oci-included-release-el7 |
The repository contains Oracle Java 8, 11 and 12.
| $ yum list jdk*Loaded plugins: langpacks, ulninfoAvailable Packagesjdk-11.0.3.x86_64 2000:11.0.3-ga ol7_oci_includedjdk-12.0.1.x86_64 2000:12.0.1-ga ol7_oci_includedjdk1.8.x86_64 2000:1.8.0_211-fcs ol7_oci_included |
To install Oracle Java 12, version 12.0.1:
| $ sudo yum install jdk-12.0.1 |
To confirm the Java version:
| $ java -versionjava version “12.0.1” 2019-04-16Java(TM) SE Runtime Environment (build 12.0.1+12)Java HotSpot(TM) 64-Bit Server VM (build 12.0.1+12, mixed mode, sharing) |
Multiple JDK versions and setting the default
If you install multiple version of the JDK, you may want to set the default version using alternatives. For example, let’s first install Oracle Java 8:
| $ sudo yum install -y jdk1.8 |
The alternatives command shows that two programs provide java:
| $ sudo alternatives –config java There are 2 programs which provide ‘java’. Selection Command———————————————–*+ 1 /usr/java/jdk-12.0.1/bin/java 2 /usr/java/jdk1.8.0_211-amd64/jre/bin/java |
Choosing selection 2 sets the default to JDK 1.8 (Oracle Java 8):
| $ java -versionjava version “1.8.0_211″Java(TM) SE Runtime Environment (build 1.8.0_211-b12)Java HotSpot(TM) 64-Bit Server VM (build 25.211-b12, mixed mode) |
Run the below command in PuTTY instance to start the agent in the VM
java -jar connectivityagent.jar
The Oracle Database Adapter enables you to integrate the Oracle database residing behind the firewall of your on-premise environment or Oracle Cloud Infrastructure with Oracle Integration Cloud Service through use of the on-premise connectivity agent
How to verify the Oracle DBaaS connection in OIC:
Use the public IP of the host and Connect String of the DBaaS instance to connect to DB using SQL Developer and Oracle DBaaS Connection.
Connect String & Port Number:
Connect to the DBaaS using SQL Developer:
Create a sample table and test
Create a new connection in OIC using Oracle DBaaS Adapter
Build a simple integration to accept input string and insert into the sample table
Test the integration using SOAP UI by providing an input string
Verify using SQL developer whether the input provided is inserted into the table
The key take away from this blog is to remember that the InstallerProfile.cfg is a single use configuration file (with the exception of the OIC user/password) for agent installation. If any changes need to be made to the initial installation configuration there are two options:
1. Reinstall the agent with the updated InstallerProfile.cfg, or
2. manually update the CpiAgent.properties
