Connect Dockerised Instant Client to Autonomous Data Warehouse

With all the recent exciting releases of Oracle Autonomous PaaS Services, I wanted to explore some of the client connectivity options to work against the Oracle Autonomous Data Warehouse (ADW).
In my cloud subscription I provisioned an instance of ADW which took less than two minutes from start to finish – terrific, now I am ready to leverage the functionality. If you want to know the steps to provision an ADW instance check out this blog post – https://redthunder.blog/2018/07/02/teaching-how-to-get-started-with-oracle-autonomous-data-warehouse-cloud-service/

Obviously an empty data warehouse isn’t particularly useful so one of the first things I wanted to do was to connect a SQL client to the ADW instance so that I can load some data. Initially I used Oracle SQLDeveloper to load data into my ADW instance. I had staged my Excel data files into an Oracle Cloud Object Storage container and then referenced them in my SQL code as External Tables. Carlos has already blogged the steps required for this in https://redthunder.blog/2018/07/02/teaching-how-to-get-started-with-oracle-autonomous-data-warehouse-cloud-service/ . If you follow the steps you will quickly get your ADW instance populated with your data. In fact for the demo scenario my ADW instance was now populated with some data (approx. 1 Million rows of Sales data and associated related dimensions (Product, Customers etc).

My next step (and the subject of this blog post) was to use the Oracle Instant Client in order to query the loaded data. Of course I could easily have viewed the data inside SQLDeveloper but I wanted to try some other approaches. Often in Proof of Concepts there is a need to quickly spin up a tool to create, retrieve, update and delete data. Anyone who has used the Oracle Database would be familiar with the SQL*Plus client which is included as part of the Oracle Instant Client. For those who are not familiar with Oracle Instant client, the Oracle website describes it as follows,

“Free, light-weight, and easily installed Oracle Database tools, libraries and SDKs for building and connecting applications to an Oracle Database instance. Oracle Instant Client enables applications to connect to a local or remote Oracle Database for development and production deployment. The Instant Client libraries provide the necessary network connectivity, as well as basic and high end data features, to make full use of Oracle Database. It underlies the Oracle APIs of popular languages and environments including Node.js, Python and PHP, as well as providing access for OCI, OCCI, JDBC, ODBC and Pro*C applications. Tools included in Instant Client, such as SQL*Plus and Oracle Data Pump, provide quick and convenient data access.”

I used a Vagrant-Box / VirtualBox to avoid having to install development tools such as the Oracle Instant Client directly on my laptop operating system. I found an existing vagrant box that provided me with an Oracle Linux base that also included Docker. This vagrant-box image allowed me to quickly spin up a base environment which in turn allowed me to focus on steps to run the Oracle Instant Client inside a Docker container inside the Virtual Box environment (sounds like a cheesecake recipe – lots of layers). The Dockerfile I used was based on the Oracle Instant Client forked from the official Oracle DockerImages project with some modifications for specifics around connecting to an Oracle Data Warehouse Instance.

If you want to try it out then follow the steps below.

• Install Oracle VM VirtualBox

• Install Vagrant – see also https://redthunder.blog/2018/02/13/teaching-how-to-use-vagrant-to-simplify-building-local-dev-and-test-environments/ 

• In a terminal window on your laptop host do the following;
  • Make / Change directory to a suitable location. For my environment I did the followingcd /Users/dmr/projects/solutionsanz/

     

  • From github.com/solutionsanz clone the Vagrant Box project for Oracle Linux 7 + DockerEngine .This image has Oracle Linux 7 base, it also includes the docker Engine and it also has the git clone of the solutionsanz/docker-images project which includes the OracleInstantClient-ADW that we will run as a containerised application.The README.md file has the instructions but we will also download the required rpm files for InstantClient and the credential wallet for Autonomous Data Warehouse before we run up the VirtualBox image.
    • Clone the repository
      git clone https://github.com/solutionsanz/vagrant-boxes.git
• Change directory to the vagrant-boxes/DockerEngine folder
  
  cd /Users/dmr/projects/solutionsanz/vagrant-boxes/DockerEngine
• The Dockerfile for OracleInstantClient-ADW expects that you have downloaded some OracleInstantClient rpm files and also a credential wallet for the Autonomous Data Warehouse instance.For this reason,  before we attempt to do a vagrant up and then a vagrant ssh we will first download the required files.

  Download the following required files into the DockerEngine folder on your host. For my environment this directory was /Users/dmr/projects/solutionsanz/vagrant-boxes/DockerEngine .

  Note: the reason for this is that there is a default shared folder mapping in the Virtual Box Image. You will find that /vagrant will map to the location of the Vagrantfile in the DockerEngine vagrant project.

  Once the files are downloaded we will reference them through the shared folder in order to copy them from the host operating system, through the Vagrant VirtualBox and into the Docker Image that we will build.

  From http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

  first accept the terms and conditions and download the following files – oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm , oracle-instantclient18.3-devel-18.3.0.0.0-1.x86_64.rpm and oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm.

• We also need to download the ADW Credential Wallet from your ADW environment into same directory as your Vagrantfile (for my environment it was /Users/dmr/projects/solutionsanz/vagrant-boxes/DockerEngine).In order to do this you will need to login to the Oracle cloud environment and click into the Administration section of your ADW Instance and then download the ADW credential wallet.The Oracle Documentation provides the detailed steps.Note: The Oracle client credentials (wallet files) are typically downloaded from the Autonomous Data Warehouse administrative service console by a service administrator. If you are not an Autonomous Data Warehouse administrator, your administrator should provide you with the client credentials.
  To download client credentials, do the following:
  – Navigate to the Service Console for Autonomous Data Warehouse.
  – Choose Administration.
  – On the Administration page choose Download Client Credentials.
  – On the Client Credentials dialog, enter a wallet password and confirm the password.
  – Click Download to save the client security credentials zip file.
  You must protect this file to prevent unauthorized database access.
  
  Note: Wallet files, along with the Database user ID and password provide access to data in your Autonomous Data Warehouse. Store wallet files in a secure location. Share wallet files only with authorized users. If wallet files are transmitted in a way that might be accessed by unauthorized users (for example, over public email), transmit the wallet password separately and securely. Autonomous Data Warehouse uses strong password complexity rules for all users based on Oracle Cloud security standards. For more information on the password complexity rules see Creating Users with Autonomous Data Warehouse.

• With the required files downloaded we can now run vagrant up to start the VirtualBox image.

  vagrant up

  Note the above command will start up VirtualBox in a headless manner.
  To confirm this you can launch your VirtualBox and you should see that the Oracle Linux / DockerEngine VM is already running.
  Now run the following command to create an ssh session to the VirtualBox image that you just started.

  vagrant ssh

   

• Next change directory into the OracleInstantClient-ADW docker project
  The solutionsanz/docker-images project that includes the OracleInstantClient-ADW subproject was git cloned into the VirtualBox image during the vagrant startup of the image.

  cd /Users/dmr/myprojects/solutionsanz/docker-images/OracleInstantClient-ADW/dockerfiles/18.3.0
  
  

• Build the docker imagedocker build -t oracle/instantclient-adw:18.3.0 .

• Run the docker image / container using either

  docker run -ti –rm oracle/instantclient-adw:18.3.0 bash

   

  then launch the sqlplus utility

  sqlplus admin@ db201807201207_medium

  OR alternatively
  
  docker run -ti –rm oracle/instantclient-adw:18.3.0 sqlplus admin@yourADWservicename

  Note: To determine your available services, view the /home/adw_wallet/tnsnames.ora file.

  For my ADW instance the tnsnames.ora file shows 3 related services : db201807201207_high , db201807201207_medium, db201807201207_low.

  I used db201807201207_medium as per the following SQL*Plus connect command. Note: you will be prompted for your database password.

  docker run -ti –rm oracle/instantclient-adw:18.3.0 sqlplus admin@db201807201207_medium

• Once connected to the database using SQL*Plus you can then execute your SQL commands such as the following.

   

  select table_name from user_tables;
  

  select count(1) from sales;

  Of course you could easily add your project specific files into your copy of the repositories so that they are made available inside the docker image.

• To shut down / close everything in a clean state

  Inside the running docker container, type exit to close the SQL*Plus client connection, and then exit to close the interactive shell session to the docker container.

  In the Vagrant/VirtualBox session, type exit to close the ssh session to the VirtualBox image.

  Now run the following command to shut down the VirtualBox image.
  
  vagrant halt

   

• Whenever you want to start your environment again you can simply run

  vagrant up
  
  and then

  vagrant ssh

   

  And then you can run up the docker container using the following

  docker run -ti –rm oracle/instantclient-adw:18.3.0 sqlplus yourdbuser@yourADWInstanceservicename
  

  I hope you found this blog useful. If you have any questions or comments, feel free to contact me directly at https://www.linkedin.com/in/davidmreid/

Author: David Reid

Cloud Platform Architect View all posts by David Reid