Thursday, 30 August 2018

Connect DBaaS instance from SQL Developer

In the previous article, we have provisioned a Oracle Database (DBaaS) instance. This article will show how to connect DBaaS instance from SQL developer from public network.

By default, the rule to access the Database instance from SQL Developer is disabled.

Let's see what happens when we try to connect DB instance using SQL developer before we enable the rule.

Find out the DB details by clicking the DB instance which we have created

See the Public IP and try to connect with SQL developer. Will get the error "Test failed: IO Error: The Network Adapter could not establish the connection"

The port 1521 is not allowed from the public network. Let's enable the port and try again.

When a database instance is created a number of Oracle Compute Cloud Service access rules are created, but set to a disabled status. When we enable one of these predefined access rules, the given port on the compute node is opened to the public internet.

In order to connect the DB with SQL developer one predefined rule "ora_p2_dblistener" need to enable
To enable the rule "ora_p2_dblistener", Click on the hamburger menu beside the database service name and Click "Access Rules"

To enable SQL Developer access, click the menu  hamburger menu beside  the "ora_p2_dblistener" access rule, and select Enable

Click Enable from the Enable Access rule popup

Once the rule is enabled, a red cross sign will go away which indicates the rule has been enabled successfully

Now try again to make connection using SQL developer, this time connection should be successful.

Wednesday, 29 August 2018

Migrate Integration Cloud Service (ICS) Integrations to Integration Cloud (OIC)

This article is written to show how to migrate ICS artifacts including Integrations, Connections, Lookups, packages, libraries etc. to OIC. Since, ICS /OIC provides the export and import feature but this is limited to single integration at a time. What about, if there are 100's of integration which are there in the ICS and wanted to migrate all integrations once.

To do so, there is Clone utility which is provided for bulk export all the integrations, connections, packages, libraries and other artifacts from ICS to OIC. This clone utility is the set of REST APIs in ICS / OIC. ICS provides REST APIs to bulk export ICS artifacts and OIC provides REST APIs to import these exported artifacts.

  • Oracle Cloud Storage Container with create / update permissions
  • Existing ICS service with Admin role access to the service
  • OIC service with Admin role access to the service
  • A feature flag has to be enabled on OIC to enable clone utility. To turn on the feature flag, open a Service Request with Oracle support
Artifacts, which can be exported using the Clone Utility:
  • Integrations
  • Connections including credentials
  • Lookups
  • Libararies
  • Certificates
  • Packages
Note: Agent groups can not be exported, this need to be created manually

Below is the pictorial representation, which will give a high level, thought on how migration works

Let me explain the diagram in more detail
  • Execute the export API to export ICS artifacts
  • The first steps will create the archive file automatically and save into Oracle Storage Cloud container
  • Execute the Import API which will import the archive file from Oracle Storage Cloud container into OIC instance
  • Once archive file imported successfully, ICS artifacts will be imported into OIC
Let's execute the step one by one and achieve the migration

Step-1: Create Oracle Storage Container

Let's create the container if not exist already. Before we create a container let's identify the Storage URL which will be used in the next step to save the exported archive file. To see the Storage URL, login into the Oracle Cloud my services and navigate to the Dashboard. Click on the Storage Classic (if not found, customized the dashboard screen using Customize Dashboard tile)

Note the value of REST Endpoint (Permanent) which will be used while running the import/export API. This is the storage URL.

Storage URL will be in the below format


Now, create the storage container
  • Login to my services
  • Click on the Storage Classic from left Menu
  • Click on Create Container button. Enter Name (ankur) and Click Create button

A container will be created with the name "ankur"

Step-2: Export ICS artifacts

ICS provides an API which will be used to export the ICS artifacts. Below are the API details which will be used to export






Authorization: Basic <ICS username:password>


                "storageInfo":   {
                                "storageUrl":" https://Storage-URL",
                                "storagePassword":" Storage_Password"

Note: Storage URL will be constructed by appending the container name (ankur) which we created in the previous step with the Storage-URL which we noted from the value of REST Endpoint (Permanent). 

For example:


Below will be the response of the API upon successful


    "archiveFilename": "",
    "jobID": "a3279de9-af56-49e6-978b-7194af719e8a",
    "location": "",
    "status": "Starting"

Note the archiveFileName response parameter, this is the archive file which will be saved into the storage container and will be used while importing the artifacts into OIC

Sample curl command

curl -X POST https://ICSURL:PORT/icsapis/v2/clonepod/exportArtifacts  -H 'authorization: Basic sddewwsdGSDWweweGFjdC5jb206R2VucGFjdEAxMjM0NQ=='  -H 'content-type: application/json'   -d '{"storageInfo":{"storageUrl":"","storageUser":"","storagePassword":"example@1234"}}'

To check the export status, use below GET API


Either "RUNNING" or "COMPLETED" status will be fetched from the above API. In the worst case, we may see the status as "Failed" in case the job fails. Download the diagnostic logs from ICS and see the Admin server logs to diagnose the issue.

Once the job is completed, the archive file can be seen in the storage container by clicking on the container (ankur)

Step-3: Import artifacts into OIC

Once the archive file generated successfully, the archive file can be imported into OIC via the REST API "https://OICSserver/ic/api/common/v1/importServiceInstanceArchive"

The import API has three different parameters either can be used to import and activate the integration

All integrations/flows are imported and after successfully importing the integrations/flows are activated
All integrations/flows are only imported but not activated
Can be used if the ImportOnly mode was used and all integrations/flows need to be activated






Authorization: Basic <OIC username:password>






Sample curl command of an import API

curl -X POST https://oicinstance/ic/api/common/v1/importServiceInstanceArchive -H 'authorization: Basic sddsds121xdsddtYWlsLmNvbTpXZWxjb21lQDEyMzQ=' -d '{

Use the jobId response parameter to check the status of the import job. Use below GET API to check the import status


Once the job is completed, login into OIC instance and sees the artifacts should be imported successfully.

Tuesday, 28 August 2018

Provisioning the Oracle Integration Cloud (OIC) Instance

This article will explain how to create the Oracle Integration Cloud instance and access it.

Pre-requisite to Create an OIC instance

The pre-requisite to create an OIC instance is just to Oracle database (DBaaS) instance up and running. If you don't have already a DBaaS instance, kindly go through the article and setup the DBaaS instance

Once we have setup the cloud DBaaS instance, we can move forward and create the OIC instance.

Provision an OIC instance
  • Login into my dashboard page and navigate to the Service -> Integration Cloud page
  • Click on Create Instance button
  • Enter below information and click Next button
*Instance Name
Name of the unique instance name of Integration Cloud
Optionally add a description to help identify the purpose of this new instance
Notification Email
Enter an email address at which to be notified of instance provisioning progress
Region in which the instance will be provisioned
Define tags to mark the instance (DEV, TEST, PROD etc..)
*SSH Public Key
Select an option for accessing service host access. This key is used for authentication when connecting to the instance using a secure shell (SSH) client.

Click on Edit button in front of SSH public key and Click on Create a New Key radio button. Click Enter and Download Keys

  • Specify details as below
Selection for this Instance
Feature Set 
* Feature Set 
Select the one of the following:

Integration: Installs the Standard Edition of Oracle Integration Cloud. This feature set includes Integrations and Visual Builder

Integration Analytics: Installs Integration Analytics, which includes Stream Analytics and Integration Insight.

Integration and Process: Installs the Enterprise Edition of Oracle Integration Cloud. This feature set includes Integrations, Processes, and Visual Builder
Node Configuration
Initial Number of Nodes
Enter the number of node which you want configure as part of instance. Default is 2
Reserved Ips

Assigned Automatically
Backup and Recovery Configuration
Cloud Storage Container
Oracle Storage Cloud Service container in which to store backups
Cloud Storage User Name
Specify the username
Cloud Storage User Password
Specify the password
Create Cloud Storage Container
Select this check box to automatically create a storage container during Oracle Integration Cloud provisioning.

Select this checkbox if you wanted to create new storage container
Select checkbox
Database Configuration
*Service Name
Select Database service name which we wanted to use

This drop down will show all the DBaaS instance which are there in the services.
Pluggable Database Service name
Specify the service name of the pluggable database (PDB).
This field is only for version 12c databases
*Database Administrator User Name
Specify the user name. This value must be set to a database user with SYSDBA system privileges
*Database Administrator User Password
Specify the password for the administration user
Instance Responsibility
*Instance Administration Responsibility Acknowledgment
Click to acknowledge that you are responsible for provisioning and administering your own instance, including:
* Patching
* Starting stopping instances and individual VMs
* Starting/stopping Oracle WebLogic Server
* Scaling in/scaling out
* Backups
* Database management
* Operating system management, including ensuring that you have enough swap space
Select checkbox

Note: Fields which are marked in Red are mandatory
  • On Integration Cloud Details Confirmation, click on Create button

Provisioning the instance will take at-least 1 hour. Once created successfully, you will receive the notification.

Check the Oracle Integration cloud for the status of newly created OIC instance

Once OIC instance ready, click on the Action menu and click on Open Integration Cloud Home page

This will take us to the Integration Cloud Home page


Monday, 27 August 2018

"Internal Server Error" while invoking https service from Oracle API CS platform

In most of the cases, we configure back-end services in API CS which are SSL enabled. So while invoking such service via browser or POSTMAN tool, we face a generic error "Internal Server Error" which is very difficult to figure out the root cause of the error.

Important Blogs to learn more about API CS

In this blog, we are going to handle such a situation and show how to resolve this.

Issue: "Internal Server Error" while invoking the SSL enabled services in API CS

So before we get into the detailed solution, we'll describe the problem statement

Consider the https enabled service (Image-1) which we have configured in API CS and the same has been deployed on API CS physical gateway. While hitting the service either from the browser (Image-2) or POSTMAN the error will be occurred as shown in the below screenshot:



SSL handshake exception ("") exception can be found in the log {GW_Install_DIR}/domain/gateway1/servers/managedServer1/trace/default.log


The solution of the issue is to import the back-end service SSL certificate chain into the WebLogic Keystore of  API CS physical Gateway node.

So first is to export the back-end service SSL certificate chain via the browser. To export the certificate of back-end service follow the below steps:

  • Hit the back-end service using firefox browser
  • Click on Security Report and More Information
  • Go to Security Tab and Click on View Certificate button
  • Click on Details tab and click on the Export button
  • Select X.509 Certificate with Chain (PEM) (*.crt, *.pem) from Save as type list
  • Click on Save button
Import Certificate into physical gateway node

Login into the Gateway WebLogic gateway node and see the path of WebLogic Keystore path. Navigate to Servers -> managedServer1 -> Configuration -> Keystores 

In above screenshot we'll notice that WebLogic is using two keystore files to validate what is cert Weblogic needs to trust. We can import downloaded cert into either of DemoTrust.jks or cacerts file.

In that case, we are importing the downloaded certificate into DemoTrust.jks file.

  • Login into the gateway node via Putty and navigate to the path of DemoTrust.jks
  • Take a backup of the original file DemoTrust.jks
  • keytool command will be used to import the certificate into the keystore. Use below command to import certificate
 keytool -keystore DemoTrust.jks -import -alias servicecert -file /home/oracle/certificatechain.crt -storepass DemoTrustKeyStorePassPhrase
Where DemoTrustKeyStorePassPhrase is the default password of DemoTrust.jks file
  • After importing the certificate, just restart the GW node. Please see the article how to restart GW node
After successful, hit the service again. That time error will not come and back-end service will serve the request

This concludes that, if we need to invoke any SSL service via API CS, the associated back-end SSL certificate must be imported into the WebLogic keystore.

Friday, 24 August 2018

Date Time by adding / subtracting n number of days / hours / minutes in ICS

There are scenarios where we wanted to add or subtract n number of days / hours / minutes in current date and time in Oracle Integration Cloud Service.

For example:
  • Subtract 30 days from Calendar date
  • Add 5 hours 30 minutes in the current date
And so and so-on...

So to meet the requirement, we are writing this article which will help user to get the expected date and time.

In this article we'll create a Scheduler integration and print the calculated date and time in logger.

This article assumes that we have one Scheduler integration in place

Subtract 30 days from Calendar date

Drop an Assign activity below the Schedule activity and create a variable with name Date1. Open the Expression Builder.  Create the expression as below:


fn:current-DateTime function can be found under Function -> Date
xsd:dayTimeDuration function is under Functions -> Conversion

Note: "30D" means 30 days. Notice the minus (-) operator between two functions which will minus the number of days.

Just Save the expression and come out from the Assign activity. Now add a Logger activity below the Assign activity and print variable Date1.

Run the scheduler and see the date should be printed which is 30 days prior of current date and time.

Add 05 hours 30 minutes in the current date

To add 05 hours and 30 minutes in current date and time, modify the previous variable Date1 and assign below expression


Note: "5H30M" means 5 hours and 30 minutes. Notice the plus(+) operator between two functions which will plus the number of hours in current date and time.

Run the scheduler and see the date and time should be printed which is 5 hours 30 minutes ahead.

Similarly, we can add / subtract number of days or hours in current date and time.