Docker-images: Building containers on top of OracleDatabase

[x97mdr]

Hi,

I'd like to use a Dockerfile to add data into the database of the oracle/database:12.1.0.2-ee container that I've built. The idea is to have a container that has data pre-populated to allow developers to get up and running quickly.

Note: we won't be using volumes, rather baking the sourced tables directly into the container image.

Here is my dockerfile

FROM oracle/database:12.1.0.2-ee
USER oracle
WORKDIR /home/oracle
COPY *.sql /home/oracle
RUN setPassword.sh dummy
RUN sqlplus sys/dummy//localhost:1521/$ORACLE_SID @script.sql

and the output ends with

/home/oracle/setPassword.sh: line 9: sqlplus: command not found

yet, if I execute bash on a running container, sqlplus is available?

Thoughts? Help?

[gvenzl]

The image itself doesn't contain a database yet, just the binaries. A database will be created on container startup or restarted if one already exists.

That's why setPassword.sh fails. It cannot find a SID and hence not set the ORACLE_HOME nor PATH and therefore not find sqlplus. Not that it would make sense, there is no database yet to set the password for.

So in order to build an image that does contain a database with starter data you would first have to invoke dbca to create a new database. There is a dbca repsonse file template at /opt/oracle/dbca.rsp.tmpl.

Have a look at function createDB in runOracle.sh on how a DB is created at container startup.

[brunoborges]

Hey @gvenzl, what if you moved the createDB function to a second shell script, so users can call that right from the inherited Dockerfile? Would that make sense to this use case?

FROM oracle/database:12.1.0.2-ee
USER oracle
WORKDIR /home/oracle
COPY *.sql /home/oracle
RUN createDB.sh && \
    setPassword.sh dummy && \
    sqlplus sys/dummy//localhost:1521/$ORACLE_SID @script.sql

CMD ["startDB.sh"]

[gvenzl]

Yeah, I'm thinking towards the same direction but have to think about it in a bit more detail. There still would have to be one script that decides whether to start the DB or create it and linking some files accordingly for the data container to work. And some variables probably need to be passed around. It can soon get a bit messy. But a wrapper script around those functions may not be a bad thing.

Let me think about it a bit more.

For now one can just copy/paste the instructions into a separate file and use that one if the DB should reside in the image as well.

[x97mdr]

Thanks guys, I'll give that a try and let you know how it goes.

[clemSeveillac]

Hi folks, doesn't "burning" the created database, including SYSTEM/SYSAUX tablespaces, redo logs... into a separate Docker image layer use up quite some space, for files that will be overwritten (via Copy on Write) at the first start i.e. never reused?

I've witnessed that by originally creating many "stacked" flavors of my DB content via image inheritance, and realizing I would take around 2 GB more with each layer. That is why I'm considering tweaking/extending what the CMD command does via environment variables now -- even though you have to wait a few more seconds on startup before your container is fully ready.

[x97mdr]

Hi @clemSeveillac,

In our case, we are willing to pay the cost of disk space/RAM for startup time. Our use case here is to provide a pre-configured database for integration testing to reduce startup time required to allow the tests to start.

I don't think we would use a docker container in this fashion for a production scenario.

[gvenzl]

While I fully understand the use case @x97mdr (the original build files were built for exactly that one) I would like to understand why using the volume is not an option for you.
The volume would achieve the very same goal of a quick startup time, all you have to do is to create the DB once.

Is there a technical reason that I have overlooked or is it just a matter of making automation of integration testing simpler?

How are you getting on with your Dockerfile, btw?

[x97mdr]

For us we really care about:

• Starting off fresh with every run
• Ease of maintenance (volumes can get a bit messier)
• Always going from the last GA version to the current version. For example: GA -> x, GA -> y, GA -> z, etc.

I am making some progress by slightly modifying the runOracle.sh script for the various runs. However, my latest problem is this one:

Step 9 : RUN ls -la $ORACLE_BASE/oradata &&     ./startDb.sh &&     sqlplus / as sysdba @setup_haloadmin.sql
 ---> Running in 7d8f6ee9f800
total 8
drwxr-xr-x  2 oracle dba 4096 Nov  7 02:09 .
drwxr-xr-x 21 oracle dba 4096 Nov  6 22:08 ..

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-NOV-2016 02:09:21

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

Starting /opt/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/299ed46fd636/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-NOV-2016 02:09:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/299ed46fd636/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 7 02:09:23 2016

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

Connected to an idle instance.

SQL> ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/12.1.0.2/dbhome_1/dbs/initORCL.ora'
SQL> Disconnected

Note that $ORACLE_BASE/oradata is empty where I'd expect to see $ORACLE_BASE/oradata/dbconfig/ORCL with some files under it. Also, I don't quite see why Oracle is looking for initORCL.ora to start up? I understand now why I saw no files ... noob mistake of looking before the creation. However, I see init.ora instead of initORCL.ora. Not sure why the start command is looking for initORCL.ora?

[gvenzl]

You need to invoke dbca - short for database configuration assistant - before starting the database. The initORCL.ora is most likely the spfile that the database is using.

[gvenzl]

I kind of got to a hard stop on this because of the VOLUME instruction used within the original Dockerfile. It seems there is no way to unset that instruction in a derived Dockerfile and hence the database created during the image build ends up in an anonymous data volume which won't be part of the resulting image.

In short: Due to the VOLUME instruction in the base Dockerfile the DB is created but the data files never make it into the image. There is no way to unset this in a derived image. Although it has been proposed already, see Docker Issue 3639 and Docker PR 8177, there is no such instruction as a NOVOLUME today.

The only workaround I can see it to provide another example Dockerfile that builds a DB from scratch, pretty much what we had in the beginning of this project. @Djelibeybi, do you see any other feasible workaround or approach for this?

[x97mdr]

@gvenzl I ran into this problem as well. I stripped the VOLUME instruction (and the symlink and move instructions meant to support it) from the Dockerfile and supporting .sh files.

This weekend I was able to successfully create my container with the pdb already in it. The trick is to remember that for every statement in the Dockerfile a new container is spun up. This is important because if you add a new RUN command, you need to start the listener and the database. before it. I modified your runOracle.sh into a createDb.sh, startDb.sh and startDbAndWait.sh. You need to run startDb.sh every time you want to use a utility to connect to the database (sqlplus, impdp, expdp, etc.). Now my Dockerfile looks like:

FROM oracle/database:12.1.0.2-ee

ENV ORACLE_SID=ORCL
ENV ORACLE_PDB=ORCLPDB1

USER oracle
WORKDIR /home/oracle/
COPY createDb.sh /home/oracle/
RUN ./createDb.sh

COPY startDb.sh your_script_here.sql /home/oracle/
RUN ./startDb.sh && \
    sqlplus / as sysdba @your_script_here.sql

COPY startDbAndWait.sh /home/oracle/
CMD /home/oracle/startDb.sh

[dbernheisel]

@x97mdr would you mind posting your working example (the modified 12c-ee dockerfile, and your shell scripts)? I presume the snippet above is your complete Dockerfile for getting a test database ready. I have a gist here of my unsuccessful attempt so far: https://gist.github.com/dbernheisel/85ad468661e24e79584a2711c0b7745b

EDIT: Nevermind. got it to work. Here's a sample repo: https://github.com/dbernheisel/oracle-testdb-ci-docker

[jcbrasuell]

Adding a similar use case to the thread. I have a 12.1.0.2 EE container initialized with test data. I want to commit that container into a new image and use the new image in a build slave for automated software testing.

When I run the new image, the container log looks fine. But when trying to verify instance from SQL Developer I get a:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The same connect string works fine when I access the original container from SQLDeveloper

Here are my commands and Connect String info.:

docker run -d --name oracle-1221-test -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCLCDB -e ORACLE_PDB=ORCLPDB1  -e ORACLE_TESTDATA=load3.sql -e ORACLE_TESTDATA_OUT=oratestdata.out -v logslave:/logslave cbras/oracle-ee12102:1.0   '/logslave/myRunOracle.sh'

docker commit --change "CMD /opt/oracle/runOracle.sh"  04f cbras/oracle-ee12102:1.1

docker run -d --name oracle-1221-test-2 -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORCLCDB -e ORACLE_PDB=ORCLPDB1 cbras/oracle-ee12102:1.1

SQLDeveloper connection string: ALPHA@//x.x.x.x:1521/ORCLPDB1
myRunOracle - Copy.txt

[clemSeveillac]

@jcbrasuell did you strip the VOLUME from the Dockerfile before building it? Otherwise you'll have to reattach the old volume to your new container, as the committed image will not contain the modified database...

[jcbrasuell]

Hi @clemSeveillac - The volume is not a host volume for the database storage. It is used for sharing database initialization data and sql plus log output. Anyway, went ahead and added the volume to the new container runtime just to validate. Same results.

[lasjen]

Hi Gerald

Sorry for disturbing, but I have been working with the same issue.

If I remember correctly the original issue was to get an Oracle DB image working with docker-compose. Because of this there was a need for building an image with an Oracle database pre-created.

I also wanted to do this, and ended up with removing the VOLUME from the Dockerfile.

It would be very nice if VOLUME not is set by default. One way to fix this would be to set this as an option to buildDockerImage.sh:
…
USE_VOLUME=0

while getopts "hesxiv:V" optname; do
  case "$optname" in
    "h")
      usage
      ;;
    …
    "V")
      USE_VOLUME=1
      ;;
    *)
    # Should not occur
      echo "Unknown error while processing options inside buildDockerImage.sh"
      ;;
  esac
done
…
# Oracle Database Image Name
IMAGE_NAME="oracle/database-t:$VERSION-$EDITION"
VOLUME_PATH="/opt/oracle/oradata"
…
# BUILD THE IMAGE (replace all environment variables)
BUILD_START=$(date '+%s')
docker build --force-rm=true --no-cache=true $DOCKEROPS $PROXY_SETTINGS -t $IMAGE_NAME -f Dockerfile.$EDITION . || {
  echo "There was an error building the image."
 exit 1
}

if [  "$USE_VOLUME" -eq 1 ]; then
  cat < FROM $IMAGE_NAME
VOLUME ["$VOLUME_PATH"]
EOF
  docker build --force-rm=true --no-cache=true $DOCKEROPS $PROXY_SETTINGS -t $IMAGE_NAME -f Dockerfile.vol . || {
    echo "There was an error building the image."
  exit 1
}
rm -f Dockerfile.vol
fi

BUILD_END=$(date '+%s')
BUILD_ELAPSED=expr $BUILD_END - $BUILD_START

This way you can choose when you build your image. Just a thought :-)

[gvenzl]

@lasjen provided an example of how to create a Docker container with a prebuilt database inside, see OracleDatabase/samples/prebuiltdb