Using SQL*Plus to Seed your Dockerized Oracle Database

In my last blog, you learned  to create a containerized Oracle database for your development/testing purpose. You also know how to connect to your container and run command in the container.

Most applications require some reference data e.g. example,my  OrderApp application based on Apache Tom EE  requires catalog data to be pre-populated before I can test my application. 

One of the readers asked me how can we run a SQL script on his local or shared drive to seed the containerized database.

In this blog, I will show how you can execute scripts with SQL*Plus inside the container to seed your dockerized Oracle database.

Connecting to SQLPlus

In the last blog, we learned that ORACLE_HOME for the database running in the container is /u01/app/oracle/product/12.1.0/dbhome_1.

I can connect to the database by running the following command:

docker exec -it orcldb /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus system/welcome1@ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 20 06:22:58 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 14 2018 03:09:54 +00:00

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL>

You might remember ORCL was the instance name that provided for my database.

Also note that when I run the command, SQL*Plus is getting executed inside the container.

Running a SQL script Using SQLPlus

As the command is getting executed inside the container running the Oracle database, the SQL script has to be accessible from the container.

My Script

My application depends upon a user in the PDB. My script creates the user, creates tables in that user's schema and populates data in those tables. 

I have a script named user.sql that I want to execute and here are the contents of /Users/dpanda/orderapp2/orcl/sql/user.sql script.

create user orderapp identified by orderapp2

default tablespace users temporary tablespace temp

/

alter user orderapp quota unlimited on users

/

grant connect, resource to orderapp

/

connect orderapp/orderapp@pdb1

@/u04/app/sql/sample_oow_tomcat_create.sql

@/u04/app/sql/sample_oow_productline.sql

commit;

exit;

As I am invoking the SQL*Plus inside the container, I have to specify the drive inside the container.

Mapping Volume from the Container to Local or Shared Drive

You might remember from the last blog that when I started the Database container, I mapped the drive in /u04/app in the container to /Users/dpanda/orderapp2/orcl by using –v option as below:

docker run -d --env-file db.properties -p 1521:1521 -p 5500:5500 --name orcldb --net appnet  --shm-size="4g" -v /Users/dpanda/orderapp2/orcl:/u04/app:/u04/app container-registry.oracle.com/database/standard

The script directory has to be specified as /u04/app/sql as my script is located in /Users/dpanda/orderapp2/orcl/sql directory on my MAC .

Here is the docker command I can use to run my script:

docker exec -it orcldb

/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus system/welcome1@PDB1 @/u04/app/sql/user

As you can see, I can connecting to the pdb1 database by executing SQLPlus command and running the user.sql script.

Here is the output you will get

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 13 06:16:32 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Jan 13 2018 06:16:19 +00:00

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

User created.

User altered.

Grant succeeded.

…..

1 row created.

1 row created.

1 row created.

Commit complete.

Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Hope this helps to automate your script to seed your containerized Oracle database.

In a future blog, I will demonstrate how can you Oracle Instant Client in a Docker container to automate your scripts.

Dockerizing Your Development and Test Oracle databases

You are probably reading this blog because your application depends on Oracle database. Most enterprises in the world depend on Oracle database to run their business. If you are a developer using Oracle database and getting started with Docker, you must be wondering how can you use a containerized Oracle database. 

In my last blog, I outlined how you can use a Dockerized Tomcat /Tom EE with an Oracle database. In this blog, I will describe how you can use Dockerized Oracle database for your development or test activities.

If you want to get started with Docker, review their getting started guide.

Getting Docker Images

Oracle provides Docker images for Oracle Database and you don't have to build using Dockerfile. You can get Oracle Database Docker images either from Docker store or Oracle Container Registry.

You have to register and accept licenses in Docker store or Oracle Container Registry.

In this blog, I will outline the steps required for the Docker image downloaded from Oracle Container Registry.

Login to Container-Registry 

You can log in to the Oracle Container Registry as follows:

docker login container-registry.oracle.com

Username :      

Password:

The Oracle Container Registry provides option to  download images for Oracle Standard or Enterprise Edition (12.2.0.1).

Note that the download may take several minutes or up to couple of hours based on your internet bandwidth. 

Download Oracle Database EE

If you want to download the docker image for Oracle Database Enterprise Edition, you can use the Docker pull command as follows:

docker pull container-registry.oracle.com/database/enterprise:12.2.0.1

You will get output as below if the your command is successful:

12.2.0.1: Pulling from database/enterprise

cbb9821ba51c: Downloading [>                                                  ]  1.599MB/81.5MB

9bd4d110366e: Downloading [>                                                  ]  1.067MB/143MB

af8b29651e27: Download complete 

4c242ab1add4: Download complete 

7bda1e55bd08: Downloading [>                                                  ]  1.599MB/2.737GB

Download Oracle Database SE

In my example, I am going to use Oracle Database Standard Edition. You can download the image for Oracle DB SE as below:

docker pull container-registry.oracle.com/database/standard

You will see output as below:

Using default tag: latest

latest: Pulling from database/standard

Digest: sha256:fad41f7b4b885f13943872218a73c7f051e2caed0b5d5620d8f6f1287cf44918

Status: Image is up to date for container-registry.oracle.com/database/standard:latest

Download Issues

You will get authentication errors if you have not logged to the Docker registry.

Ensure that you accepted the Oracle license agreement in the Oracle Container Registry, otherwise you will get an error message as below:

Error response from daemon: pull access denied for database/standard, repository does not exist or may require 'docker login'

Checking Docker Images

You can check Docker images available by using the following command:

docker images | grep oracle

container-registry.oracle.com/java/serverjre           8                   daea2cf635d1        5 weeks ago         280MB

container-registry.oracle.com/database/instantclient   latest              fda46de41de3        4 months ago        407MB

container-registry.oracle.com/database/standard        latest              faa877d7fbdd        7 months ago        5.16GB

DB Config file

The Oracle DB container requires a configuration file where you can specify few parameters such as Database SID, Password, etc.

Here is the db.properties file that I used. As you can see I changed the default password and the domain for my database.

DB_SID=ORCL

## db passwd

## default : Oracle

DB_PASSWD=welcome1

## db domain

## default : localdomain

DB_DOMAIN=us.oracle.com

## db bundle

## default : basic

## valid : basic / high / extreme

## (high and extreme are only available for enterprise edition)

DB_BUNDLE=basic

Starting Database Container

You can start the Database container by using the command as shown below. If you have not downloaded the database image, then database image will be automatically pulled from the container repository. 

            

docker run -d --env-file db.properties -p 1521:1521 -p 5500:5500 --name orcldb --net appnet  --shm-size="4g" -v /Users/dpanda/orderapp2/orcl:/u04/app container-registry.oracle.com/database/standard

The container will start and database will be ready to use within few minutes.

Reviewing Key Parameters 

Let’s review some of the key parameters I specified.

·       The --shm-size="4g" parameter sets the size of shared memory i.e. /dev/shm for the container to 4GB. 

·       The --name orcldb parameter sets the name of the container to orcldb. You can login to the container with that name or other containers can communicate to this container in that name when using SQLNet or JDBC. You can use this name to stop or remove the container.

·       The --net appnet is connecting the container to the bridge network named appnet.

·       The -v /Users/dpanda/orderapp2/orcl:/u04/app option lets the container map the /u04/app drive to the local volume (/Users/dpanda/orderapp2/orcl) of my MAC. This mapping allows the database to create the redo logs into my local drive. Also this will enable me run the SQL scripts that I have in my local drive to run inside the container.

You can check the status of the running containers by using the docker ps command as below:

CONTAINER ID        IMAGE                                             COMMAND                  CREATED             STATUS              PORTS                                            NAMES

9101006044e9        container-registry.oracle.com/database/standard   "/bin/sh -c '/bin/..."   2 minutes ago       Up 2 minutes        0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcldb

fccce8035b91        orderapp                                          "catalina.sh run"        46 hours ago        Up 46 hours         0.0.0.0:8080->8080/tcp

As you can see, the orcldb container running my Oracle database started up 2 minutes ago.                           

Executing Commands in the Container

Now that the container is running you can run commands inside the container by executing docker exec command

You can login to the container as below and check whether things are set properly.

Note that these are purely optional steps and are not required.

1. Login to the Container

docker exec -it orcldb /bin/bash

[root@9101006044e9 /]# 

2. Switch Linux user to oracle user from root

su – oracle

Last login: Sat Jan 13 05:50:33 UTC 2018 on pts/0

3. You can check few things such as Oracle Environment Variables

 echo $ORACLE_SID

ORCL

 echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0/dbhome_1

4. Connect to SQLPlus 

sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 13 07:03:14 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> 

Accessing the Oracle Enterprise Manager Database Console

You can access the EM Console as https://localhost:5500/em

You can login with the user id as sys or system and password you specified in the db.properties file while starting the container. 

After you login you will see the Database Home Page as below. You can see the name of your container as your database host.

Your Dockerized Oracle Database is now ready for use! 

Try exploring until I next time !  We will see how you can run use SQLPlus with the Dockerized database.