info.michael-simons.eu

In this part of the series you’ll learn several things:

1. How to build official Oracle Database Docker images
2. How to use Docker Maven plugin to run and customize them
3. How to use the official Oracle Maven Repository to get the lastest JDBC driver
4. Configure a common connection for the Flyway, jOOQ and Spring Boot

How to build official Oracle Database Docker images

Oracle provides official scripts for creating Docker containers running Oracle Software. Bruno Borges and Gerald Venzl have done a lot of work to make this an easy task.

First step is certainly the installation of Docker itself. Please figure how to do this on your machine.

I use Oracle 12c in my demo. So next step is going to the Oracle Database 12c Release 1 download and getting the distribution files for 12.1.0.2.0. Those are certainly not free for production but for evaluation purpose. So, get yourself an Oracle account (which is free). You’ll need it later for the Maven Repository as well!

Then go to Oracles docker images repository, download the OracleDatabase scripts and follow the instructions. Remember to add the two files from step 2 to “dockerfiles/12.1.0.2”. I used the following command: ./buildDockerImage.sh -v 12.1.0.2 -e to build my images. This results in “oracle/database:12.1.0.2-ee”. Be aware that you’ll need at last 2, better 4Gb Ram for the Docker process.

Depending on your machine building the image takes between 10 and 30 minutes. Bruno and Gerald changed the process a lot from the first versions: The image just installs the needed software inside the container but doesn’t initialize a database at all, so that the user has the opportunity to map a local directory into the container that contains the database files.

But that also means, the first start of the container takes some time. After that, it’s fine. For reference: MacBook Pro with Core i7 and SSD: 10 Minutes, iMac with Core i5 and Fusion Drive: 15 Minutes and some old Core 2 something with a HDD: About an hour.

Be aware: If you stop and remove the container, you’ll remove your database as well if you haven’t used a local directory during startup. Also, you blow up your docker base file for no reason.

How to use Docker Maven plugin to create a database instance

The database instance I’m gonna use is for development use! The project is designed that way, that I’ll always use migrations which are part of the project to be on the same level as all other committed code. This database instance isn’t meant to be an integration test instance. One could maybe use another schema for unit tests, but I wouldn’t fire it up on each run but instead keep the container running. Starting Oracle instances is faster than it was 10 years ago but nowhere as fast as PostgreSQL or others.

I use the Docker Maven plugin maven plugin to achieve the following task:

• Fire up the image from step one
• Map a local directory into the container that will contain the database files
• Use a combination of shell and SQL script to create a tablespace and a user into the default pluggable database ORCLPDB1 that I will used for the functional part of the demo in the following post

The Docker Maven plugin has an incredible good documentation (which is a feature that ranks in my top 5 wether I use a project or not in these days) that you’ll find here: fabric8io/docker-maven-plugin.

In commit #43f32ba of my demo project DOAG2016 I have pushed the code that makes use of this image and this is the core of it, pom.xml:

<plugin>
	<groupId>io.fabric8</groupId>
	<artifactId>docker-maven-plugin</artifactId>
	<version>0.17.1</version>
	<configuration>
		<images>
			<image>
				<name>oracle/database:12.1.0.2-ee</name>
				<run>
					<volumes>
						<bind>
							<volume>${project.build.scriptSourceDirectory}:/var/tmp/scripts</volume>
							<volume>${basedir}/var/oradata:/opt/oracle/oradata</volume>
						</bind>
					</volumes>
					<ports>
						<port>1521:1521</port>
						<port>5500:5500</port>
						<port>5501:5501</port>
					</ports>
					<wait>
						<log>DATABASE IS READY TO USE!</log>
						<time>3600000</time>
						<exec>
							<postStart>/var/tmp/scripts/initdb.sh</postStart>
						</exec>
					</wait>
				</run>
			</image>
		</images>
	</configuration>
</plugin>

This reads: Run “oracle/database:12.1.0.2-ee”, don’t create a custom image. Bind “${basedir}/var/oradata” as /opt/oracle/oradata into the container (${basedir} is the root of the Maven project). Also bind “${project.build.scriptSourceDirectory}” into an arbitrary folder inside the container. This folder contains my script that will modify the database to my needs.

Another option would have been to copy those scripts into a new image derived from the Oracle image, but than I would have to deal with at least 11Gb additional image (the Oracle image is that large and therefor each custom one is as large) so I decided against it.

Then expose some ports. It’s a demo, so I happily assume that those local ports are free. If I’d use this for integration testing or such I advice random ports as described here. Port 1521 is import for SQL*Plus, the other ones are Oracle management ports.

Then, wait until “DATABASE IS READY TO USE!” appears in the containers logs but maximum an hour. After that, execute my “initdb.sh” script.

This script looks like this:

#!/bin/bash
 
echo 'Initialising database'
 
export ORACLE_SID=ORCLCDB
 
sqlplus / as sysdba @/var/tmp/scripts/create_tablespace_and_user.sql
 
echo 'Tablespace and user created'

calling create_tablespace_and_user.sql in the same folder:

-- Change to PDB which was created inside parent iamge
ALTER SESSION SET CONTAINER = ORCLPDB1;
 
-- Enable Enterprise Manager Express on port 5501
EXEC dbms_xdb_config.sethttpsport(5501);
 
-- Create table space for demo application 
DECLARE
  v_ts_id V$TABLESPACE.TS#%TYPE;
BEGIN
  SELECT TS# INTO v_ts_id FROM V$TABLESPACE WHERE name = 'DOAG2016';
EXCEPTION
  WHEN no_data_found THEN
    EXECUTE IMMEDIATE 
      'CREATE SMALLFILE TABLESPACE "DOAG2016" ' ||
      '  DATAFILE ''/opt/oracle/oradata/ORCLCDB/ORCLPDB1/doag2016-1.dbf'' SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE 1024M ' ||
      '  LOGGING ' ||
      '  DEFAULT NOCOMPRESS ' ||
      '  ONLINE ' ||
      '  EXTENT MANAGEMENT LOCAL AUTOALLOCATE ' ||
      '  SEGMENT SPACE MANAGEMENT AUTO';
END;
/
 
-- Create doag2016 user
DECLARE
  v_id DBA_USERS.USER_ID%TYPE;
BEGIN
  SELECT USER_ID INTO v_id FROM DBA_USERS WHERE username = 'DOAG2016';
EXCEPTION
  WHEN no_data_found THEN
    EXECUTE IMMEDIATE
      'CREATE USER doag2016 IDENTIFIED BY doag2016 PROFILE "DEFAULT" ACCOUNT UNLOCK DEFAULT TABLESPACE  "DOAG2016" TEMPORARY TABLESPACE "TEMP"';
END;
/
 
GRANT "CONNECT" TO doag2016;
GRANT "DBA" TO doag2016;
 
EXIT;

The shell script is standard. I export the Oracle SID which I didn’t change while firing up the image as container so I know it is ORCLCDB. Through “sqlplus /” I can use OS authentication for starting my script. The @-notation is the SQL*Plus way of saying “run that sql script.”

The SQL script does some more “magic”. As it gets executed everytime the container starts, I have to make sure that I don’t create the tablespace and user several times. Therefore the PL/SQL blocks with dynamic SQL execution inside. Notice how I change the session to the pluggable database as first action!

First it creates the tablespace and then the user, if either one doesn’t exist. This way you could also provide another schema / user for unit testing.

Having an “EXIT” statement is important because otherwise SQL*Plus will keep running and the following maven command will hang.

If you start the whole thing with mvn docker:start, drink some coffee the first time, you can connect with sqlplus doag2016/doag2016@//localhost:1521/ORCLPDB1 after a while.

Make sure to stop the container with mvn docker:stop to stop the database gracefully so that your database files in “${basedir}/var/oradata” don’t get corrupted.

Inside the demo, “var” is excluded from git and you shouldn’t change that 🙂

Use the official Oracle Maven Repository to get the lastest JDBC driver

So you have already connect from SQL plus but we are bound to write a Spring Boot application using jOOQ to speak to the database.

Oracle offers a Maven repository for quite some time now to get the driver.

First step is adding this to the pom.xml

<repositories>
	<repository>
		<id>maven.oracle.com</id>
		<name>oracle-maven-repo</name>
		<url>https://maven.oracle.com</url>
		<layout>default</layout>
		<releases>
			<enabled>true</enabled>
			<updatePolicy>always</updatePolicy>
		</releases>
	</repository>
</repositories>
<pluginRepositories>
	<pluginRepository>
		<id>maven.oracle.com</id>
		<name>oracle-maven-repo</name>
		<url>https://maven.oracle.com</url>
		<layout>default</layout>
		<releases>
			<enabled>true</enabled>
			<updatePolicy>always</updatePolicy>
		</releases>
	</pluginRepository>
</pluginRepositories>

You are not quite there yet, as those repository is password protected and you need an Oracle account to access it. The account is free and you’ll get it here.

The following part is important: If you have the account, register the access to the Oracle Maven Repository here https://www.oracle.com/webapps/maven/register/license.html.

You then have to configure your Maven installation itself (or the one of your CI server) to use that account. This is done in the central settings.xml file of Maven. Please refer to both the Maven documentation and then especially Oracles manual on Configuring the Oracle Maven Repository: Configuring the HTTP Wagon.

If you have done this, you can access the official Oracle JBDC driver under the following coordinates:

<properties>
	<oracle.version>12.1.0.2</oracle.version>
</properties>
<dependency>
	<groupId>com.oracle.jdbc</groupId>
	<artifactId>ojdbc7</artifactId>
	<version>${oracle.version}</version>
</dependency>
<dependency>
	<groupId>com.oracle.jdbc</groupId>
	<artifactId>orai18n</artifactId>
	<version>${oracle.version}</version>
</dependency>

Configure a common connection for the Flyway, jOOQ and Spring Boot

The project should migrate a database during build, then run the jOOQ code generator (you’ll learn about that later) and than start the application for development purpose. This should be done against the same database instance.

Every developer on this project should have the same container as described above so we can safely assume that the connection properties are the same for every developer in the pom:

<properties>
	<db.url>jdbc:oracle:thin:@//localhost:1521/ORCLPDB1</db.url>
	<db.username>doag2016</db.username>
	<db.password>doag2016</db.password>
	<db.schema>DOAG2016</db.schema>
</properties>

Those are the properties inside Maven that will be based to the Flyway and jOOQ code generator configuration later. As those are configured in the build process we have nothing to take off.

But what about Spring Boot? It’s database configuration will be configured through the application.properties file. Luckily, you can use Maven placeholders there:

spring.datasource.platform = oracle
spring.datasource.driverClassName = oracle.jdbc.OracleDriver
spring.datasource.removeAbandoned= true
spring.datasource.testOnBorrow = true
spring.datasource.validation-query = select 1 from dual
spring.datasource.url = @db.url@
spring.datasource.username = @db.username@
spring.datasource.password = @db.password@

That is the Spring Boot configuration I recommend against a single Oracle instance. The URL, username and password are the Maven properties configured above. In case you haven’t done this, enable filtering for the properties file in your build description as shown here.

Summary

In the first post we set up a basic Spring Boot project that contained dependencies to three starters: web, jOOQ and H2. In this post we used the official Oracle scripts to build a Docker image containing Oracle 12c Enterprise Edition. We than used the Docker Maven plugin to run that image and than customize the container instance by adding another Oracle tablespace and user. Those changes are not persisted in the container itself but life inside a var folder of the project.

To use this from the application we configured the Oracle Maven repository to get the official JDBC driver. The database connection to use was configured in the build descriptor.

The next post will deal with the workflow of a database centric, analytic application. I’ll introduce database migrations based on Flyway that will be run before the jOOQ code generator is executed. I’ll show you how to configure the later and create a first jOOQ query.