Introduction

For every technology I learn and use, I start with a simple setup and build on top of it utilizing ‘docker compose’. We are going to spin up an Oracle Database Free (23c) where we run some init-scripts. Now we can play with all the exciting new features the database provides.

Setup

Docker Compose is a convenient way to spin up a service. Declare your configuration and issue ‘docker compose up’. You are running a very sophisticated database locally also used by the largest banks in the world.

docker compose

Create a folder ‘oracle-free’ and move in there

mkdir oracle-free && cd "$_"

Next, create the docker-compose YAML file where we configure app_user and init_scripts

cat > docker-compose.yml <<EOF
version: "3.9"

services:

  oracle:
    container_name: oracle-free
    image: gvenzl/oracle-free:latest
    environment:
      ORACLE_PASSWORD: Welcome_#1
      APP_USER: dev
      APP_USER_PASSWORD: dev
      TARGET_PDB: FREEPDB1
    ports:
      - 1521:1521
    volumes:
      - ./init_scripts:/container-entrypoint-initdb.d
    healthcheck:
      test: healthcheck.sh
      interval: 10s
      timeout: 5s
EOF

Add your custom init scripts

# directory where to keep our init scripts

mkdir init_scripts

# utilize the new 23c dev role 'db_developer_role'

cat > init_scripts/01_grant_db_developer_role.sql <<EOF
alter session set container=freepdb1;
grant db_developer_role to dev;
EOF

The Oracle Database startup routine is configured to eat all scripts in this folder

tree init_scripts/
init_scripts/
└── 01_grant_db_developer_role.sql

1 directory, 1 file

Run

First time it will take a while, because the image has to be pulled from Docker Hub. We are ready to bring up the Oracle Database Free service in detached mode (-d)!

docker compose up -d

[+] Building 0.0s (0/0)                                                                                                                                      docker:desktop-linux
[+] Running 1/1
 ✔ Container oracle-free  Started

Connect

It is time to connect to the Oracle Database. And now it is starting to getting complicated. If you do not understand the Oracle Database architecture, you will just think other databases are so much easier to connect to and leave right here. Please hang on! I will explain the different login options.

First install a client side developer tool like ‘sqlplus’ (CLI), ‘sqlcl’ (CLI), ‘Oracle SQL Developer’ (IDE), ‘DBeaver’ (IDE) or something similar. Before I try any IDE tool I always verify the connection utilizing a CLI tool.

  • Note #1: There is no need to specify default port 1521. Just leave it out

  • Note #2: Oracle Database 23c is a multitenant architecture where a container database ‘FREE’ can host multiple pluggable databases. ‘FREEPDB1’ is already available and this is the one we will connect to.

  1. Login to the pluggable database ‘FREEPDB1’ using ‘sqlplus’ with the ‘dev’ user
sqlplus dev/dev@localhost/FREEPDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 8 19:49:30 2023
Version 19.8.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

dev@FREEPDB1>
  1. Login to the pluggable database ‘FREEPDB’ using ‘sqlplus’ with the ‘sys’ user
sqlplus sys/Welcome_#1@localhost/FREEPDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 8 19:51:03 2023
Version 19.8.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

sys@FREEPDB1>
  1. Login to the container database ‘FREE’ using ‘sqlplus’ with the ‘sys’ user
sqlplus sys/Welcome_#1@localhost/FREE as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 8 19:52:10 2023
Version 19.8.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

sys@FREE>
sys@FREE>
sys@FREE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
  1. Tip - set the connection string for once and switch between users
# sqlplus reads the TWO_TASK environment variable

export TWO_TASK=localhost/FREEPDB1

# tell sqlplus to connect as 'dev' and issue 'select user;'

echo -e "connect dev/dev\nselect user;" | sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 8 20:10:50 2023
Version 19.8.0.0.0

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

idle> Connected.
dev@FREEPDB1>
USER
---------
DEV

# tell sqlplus to connect as 'sys as sysdba' and issue 'select user;'

echo -e 'connect sys/Welcome_#1 as sysdba\nselect user;' | sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 8 20:11:14 2023
Version 19.8.0.0.0

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

idle> Connected.
sys@FREEPDB1>
USER
---------
SYS

Conclusion

The purpose of this post is straight to the point. Bring up an Oracle Database Free utilizing Docker Compose and being able to connect to the database. You may add additional init-scripts for your use cases. There are tons of new features in version 23c of the Oracle Database. These features deserve their own posts. Features like ‘JSON Relational Duality View’, ‘Kafka API’, ‘MongoDB API’ and the whole concept of a ‘Converged Database’.