Update: I've moved my setup process to Dockerize Oracle and APEX to Github. It will be maintained with the latest steps I'm using it. The repository is docker-oracle-setup.
Last week I needed to test an Oracle 12.2 feature and got hooked on Docker. I wrote an article on how to setup an Oracle DB 12.2 Docker container. This solved my goal to test the 12.2 feature. Roel Hartman then wrote a followup article about how to setup an Oracle database in a Docker container but preserve the data on his laptop. I.e. if the container was deleted or needed to be rebuilt his database data wouldn't be lost.
Roel's article inspired me to setup an entire Dockerized Oracle and APEX environment, thus replacing my current local VM infrastructure. I took a lot of notes about the process. The result is this blog post which contains everything I did to create my containers and link them together. To get it all working I used the following articles and all my scripts are a result of a combination of the code found in the links.
Background
A few things to keep in mind that will help when reading the rest of this article:
- All my scripts are Linux / MacOS focused. If you use a Windows machine you'll need to translate
- I specifically made reference to "your laptop" to emphasize what was run "on your machine" vs "in a docker container"
The following key configurations were used for the containers:
Port Mapping
| Container | Port | Description |
oracle | 32712 | TNS listener |
ords | 32713 | ORDS |
Passwords
| Container | Username | Password | Description |
oracle | sys | Oradoc_db1 | |
oracle | admin | oracle | Workspace Internal for APEX admin |
Download Files
Due to licensing restrictions I can't host/provide these files in Github or elsewhere. As such you'll need to download them manually. Download the following files and store them in your Downloads folder
Laptop Folder Structure
The following script will create a folder structure that looks like:
| Path | Description |
~/docker | root |
~/docker/apex | To host APEX installation and images for each version |
~/docker/apex/5.1.3 | APEX 5.1.3 installation files |
~/docker/oracle | Hold Oracle 12.2 data files |
~/docker/ords | ORDS Dockerfile (to build ORDS image) |
~/docker/tmp | Temp folder |
mkdir ~/docker
mkdir ~/docker/apex
mkdir ~/docker/apex/5.1.3
mkdir ~/docker/oracle
mkdir ~/docker/ords
Move Downloaded Files
cd ~/docker/apex/
mv ~/Downloads/apex_5.1.3.zip .
unzip apex_5.1.3.zip
mv apex 5.1.3
mkdir ~/docker/tmp
cd ~/docker/tmp
mv ~/Downloads/docker-images-master.zip .
mv ~/Downloads/linuxx64_12201_database.zip .
Docker
Build Oracle Docker Image
This will be a different setup then I previously blogged about in that this docker container will separate the data from the app. I.e. we can remove the container and rebuild it while keeping all the data in place. This allows for us to
cd ~/docker/tmp
unzip docker-images-master.zip
mv linuxx64_12201_database.zip docker-images-master/OracleDatabase/dockerfiles/12.2.0.1
cd docker-images-master/OracleDatabase/dockerfiles
./buildDockerImage.sh -v 12.2.0.1 -e
Setup Docker Network
In order for the containers to "talk" to each other we need to setup a Docker network and associate all the containers on this network. Containers can reference each other by their respective container names. When referencing another container on the same Docker network the port used is the container's native port not the mapped port on your laptop.
docker network create oracle_network
docker network inspect oracle_network
Create Oracle Docker Container
The following command will create and run the Oracle Docker container. It's TNS listener will be accesible via port 32712 on your laptop. The reference to the APEX installation files are necessary only whe installing APEX.
Adding the -e TZ will set the appropriate timezine for the OS and the database. A full list of timezones can be found here. If excluded it will default to UTC.
docker run \
--name oracle \
--network=oracle_network \
-e TZ=America/Edmonton \
-p 32712:1521 \
-v ~/docker/oracle:/opt/oracle/oradata \
-v ~/docker/apex/5.1.3:/tmp/apex-install \
oracle/database:12.2.0.1-ee
In another terminal tab, set the sys password to Oradoc_db1 and install APEX
docker exec oracle ./setPassword.sh Oradoc_db1
Install APEX
In a terminal tab on your laptop run:
docker exec -it oracle bash -c "source /home/oracle/.bashrc; bash"
cd /tmp/apex-install
sqlplus sys/Oradoc_db1@localhost/orclpdb1 as sysdba
The above command will open a SQL prompt. Run the following scripts:
@apexins.sql SYSAUX SYSAUX TEMP /i/
@apex_rest_config_core.sql oracle oracle
alter user apex_public_user identified by oracle account unlock;
declare
l_acl_path varchar2(4000);
l_apex_schema varchar2(100);
begin
for c1 in (select schema
from sys.dba_registry
where comp_id = 'APEX') loop
l_apex_schema := c1.schema;
end loop;
sys.dbms_network_acl_admin.append_host_ace(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => l_apex_schema,
principal_type => xs_acl.ptype_db));
commit;
end;
/
begin
apex_util.set_security_group_id( 10 );
apex_util.create_user(
p_user_name => 'ADMIN',
p_email_address => 'martin@talkapex.com',
p_web_password => 'oracle',
p_developer_privs => 'ADMIN' );
apex_util.set_security_group_id( null );
commit;
end;
/
exit
Now exit bash:
exit
Create ORDS Container
The following assumes that you've downloaded ORDS 3.0.12. Referencing the ORDS version so that can create ORDS images for each ORDS release.
The scripts below will first create the ORDS Docker image then create the containers.
cd ~/docker/ords
git clone git@github.com:martindsouza/docker-ords.git .
mv ~/Downloads/ords.3.0.12.263.15.32.zip ~/docker/ords
unzip ~/docker/ords/ords.3.0.12.263.15.32.zip ords.war
docker build -t ords:3.0.12 .
docker run -t -i \
--name ords \
--network=oracle_network \
-e DB_HOSTNAME=oracle \
-e DB_PORT=1521 \
-e DB_SERVICENAME=ORCLPDB1 \
-e APEX_PUBLIC_USER_PASS=oracle \
-e APEX_LISTENER_PASS=oracle \
-e APEX_REST_PASS=oracle \
-e ORDS_PASS=oracle \
-e SYS_PASS=Oradoc_db1 \
--volume /Users/giffy/docker/apex/5.1.3/images:/usr/local/tomcat/webapps/i \
-p 32713:8080 \
ords:3.0.12
You should now be able to go to localhost:32713/ords/ on your laptop to run APEX
Useful Commands
Docker Start/Stop Containers
docker stop -t 200 oracle
docker stop -t 200 ords
docker start oracle
docker start ords
Connect to DB from laptop
sqlcl is my alias for SQLcl. More information on how to install SQLcl on MacOS here.
sqlcl sys/Oradoc_db1@localhost:32712:orclcdb as sysdba
sqlcl sys/Oradoc_db1@localhost:32712/orclpdb1 as sysdba
sqlcl martin/martin@localhost:32712/orclpdb1
Create New DB User
define new_user = 'martin'
create user &new_user. identified by &new_user. container = current;
grant connect, resource, create any context to &new_user;
alter user &new_user quota unlimited on users;