Update instructions for Oracle-XE to create a new image after first-run of Oracle XE

[gdubya]

When using Oracle in a docker container, the first time the container is run it takes a long time to startup as lots of files are unpacked, etc.

The suggestion here is to create an Oracle-XE container using the base image from Oracle and then run docker commit to create an updated image based on the unpacked XE installation. The following startup times are then much faster (seconds as opposed to many minutes)

(This may be related to the long startup time reported in #1292)

[rnorth]

Hi @gdubya, I think that's a great suggestion.

If you or anybody else would like to contribute a documentation change, I'm sure we'd be happy to accept it. Thanks Richard

[AnEmortalKid]

We were able to defer this to the BUILD image phase (instead of on startup) by calling the setup twice, something like:

# The first [RUN] will import the base image data and the second [CMD] will run on startup to import more tables.
RUN /usr/sbin/startup.sh
CMD /usr/sbin/startup.sh && tail -f /dev/null

[CamilYed]

@AnEmortalKid can you provide some Dockerfile :)

[AnEmortalKid]

Sure @KamilJedrzejuk

Dockerfile

FROM wnameless/oracle-xe-11g-r2

ADD init.sql /docker-entrypoint-initdb.d/

# The first [RUN] will import the base image data
RUN /usr/sbin/startup.sh
RUN rm /docker-entrypoint-initdb.d/init.sql

# The second [CMD] will run on startup to import more tables from whoever extends this image
CMD /usr/sbin/startup.sh && tail -f /dev/null

init.sql

create table CATS (
    NAME VARCHAR2(20)
);

insert into CATS(
    NAME
)
VALUES ('foo');

Build

$ docker build . -t localdb --no-cache
Sending build context to Docker daemon   5.12kB
Step 1/5 : FROM wnameless/oracle-xe-11g-r2
 ---> 0d19fd2e072e
Step 2/5 : ADD init.sql /docker-entrypoint-initdb.d/
 ---> 49d3f2ec3b38
Step 3/5 : RUN /usr/sbin/startup.sh
 ---> Running in ce971fbfcd53
Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.

/usr/sbin/startup.sh: running /docker-entrypoint-initdb.d/init.sql

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 14 18:17:13 2020

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production


Table created.


1 row created.

SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production


Removing intermediate container ce971fbfcd53
 ---> 1949682a771c
Step 4/5 : RUN rm /docker-entrypoint-initdb.d/init.sql
 ---> Running in faa0a0369571
Removing intermediate container faa0a0369571
 ---> 806dcc0d4304
Step 5/5 : CMD /usr/sbin/startup.sh && tail -f /dev/null
 ---> Running in 35c43bffc231
Removing intermediate container 35c43bffc231
 ---> 357006645d32
Successfully built 357006645d32
Successfully tagged localdb:latest

Run

docker run -p 49161:1521 localdb

Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.

Inspect

 docker exec -it ac5f /bin/bash                                                                 root@ac5fc821c840:/# which sqlplus
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus
root@ac5fc821c840:/# sqlplus system/oracle@xe

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 14 18:18:38 2020

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select owner,table_name from all_tables where table_name='CATS';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            CATS

SQL> select * from SYS.CATS;

NAME
--------------------
foo

[CamilYed]

@AnEmortalKid Ok I Am using a 18.c version and maybe this will be walk around https://github.com/KamilJedrzejuk/oracle18c-xe

[Jacopo47]

Hi, is this still open to contributes?

I'm asking because in these days I was working in this direction and I found this open issue. I tried to implement it as suggested in previous comments but base images are not compatible since I'm working on top of: gvenzl/oracle-xe:21-slim-faststart

But similarly to how it's described in this comment I was able to achieve this during the image's build phase.

I can share quickly some "pseudo-code". I'm saying "pseudo-code" because it's not a working example but I can work on it if needed.

Dockerfile

FROM gvenzl/oracle-xe:21-slim-faststart

COPY testcontainers/oracle/init /container-entrypoint-initdb.d

USER root

RUN mkdir ./workspace &&\
  cd ./workspace &&\
  microdnf upgrade \
    --refresh \
    --best \
    --nodocs \
    --noplugins \
    --setopt=install_weak_deps=0 &&\
  microdnf install wget tar &&\
  wget -O installer.jar https://github.com/path/to/your/installer.jar &&\
  wget https://github.com/adoptium/temurin17-binaries/releases/download/jdk-17.0.5%2B8/OpenJDK17U-jre_x64_linux_hotspot_17.0.5_8.tar.gz &&\
  tar -xf OpenJDK17U-jre_x64_linux_hotspot_17.0.5_8.tar.gz &&\
  ln -s `pwd`/jdk-17.0.5+8-jre/bin/java /usr/local/bin &&\
  mv installer.jar /opt/installer.jar


ENV ORACLE_PASSWORD=Th3_P4ssw0rd_th4t_yu0_pr3f3r3s!
USER oracle

# Running oracle entrypoint in background with "container-entrypoint.sh $".
# Schema initialization is achieved through initialization script placed in /container-entrypoint-initdb.d
# Here we have just to wait that oracle finalize its setup.
# Sleep it's just in order to let him finish it setup. 300 (5 minutes) it's and heuristic value
RUN nohup bash -c "container-entrypoint.sh &" && echo "Waiting..." && sleep 300

This dockerfile is implemented around my needs.

What I mean is that in my case the schema initialization is run through a pre-packed .jar file. This is why I had the need of downloading these artifacts.

wget -O installer.jar https://github.com/path/to/your/installer.jar &&\
  wget https://github.com/adoptium/temurin17-binaries/releases/download/jdk-17.0.5%2B8/OpenJDK17U-jre_x64_linux_hotspot_17.0.5_8.tar.gz

Indeed inside my: testcontainers/oracle/init i have the following files:

1_create_user.sql

ALTER SESSION SET CONTAINER=XEPDB1;

CREATE USER TEST_USER IDENTIFIED BY "T3st_us3r_pwd!";

-- Do your stuff..
GRANT CREATE SESSION TO TEST_USER;

GRANT CONNECT
, CREATE TABLE
, ..... TO TEST_USER

ALTER USER TEST_USER DEFAULT ROLE ALL;
ALTER USER TEST_USER quota unlimited on USERS;

2_init_schema.sh

#!/bin/bash

java -jar /opt/installer.jar -Durl=jdbc:oracle:thin:@localhost:1521/XEPDB1 -Duser=TEST_USER -Dpassword=T3st_us3r_pwd!

[eddumelendez]

I think this issue is old. Currently, testcontainers is using gvenzl/oracle-xe and OracleContainer implementation is based on it and also tested. IMO this documentation should be on https://github.com/gvenzl/oci-oracle-xe, so every user can benefit from it. In Testcontainers we can add a reference.

[kiview]

I agree with @eddumelendez here. Just summoning @gvenzl, since he could help us with incorporating it into the Docker Hub image docs 🙂

Afterwards, we would resolve this issue, by referencing this specific section of the Docker Hub image docs in our docs.

[Jacopo47]

Hi @kiview , @eddumelendez , thanks for your feedback!

I found that there is a similar thread here: https://github.com/gvenzl/oci-oracle-xe/issues/125 I'll move here the discussion in order to see if it could land in @gvenzl's project's documentation :)

[kiview]

I'll close this issue for now, since it was considering the legacy Oracle XE image and the new image has different facilities already in place trim down the startup time.

We can consider re-opening or raising a new issue if something more specific occurs or if there is a chance to leverage certain documentation of the https://github.com/gvenzl/oci-oracle-xe image within our docs.