Checking out Oracle Database 23c
What We Are Doing
Today, I will be guiding you through the process of setting up a dockerized instance of an Oracle Database 23c for free, as well as delving into the general overall structure of how the database functions.
What It Is
It’s exactly what you expect—a database designed for storing data, much like any other SQL-based database. Imagine it as a large Excel document.
What I really want to explore are the exciting capabilities that Oracle Database 23c offers. By examining the diagram below, you can visualize the logical map of how it would appear.
(go here if you want to interact with the diagram)
The thing I find most interesting is that you can have separate plugable databases within the root database that can share data across each other and easily migrate from one database to another. I can’t wait to start experimenting with this! Just need to come up with a reason for implementing it in the homelab XD.
How I Will Be Setting It Up
I will be setting this up in Docker, as I find it to be the easiest deployment method to follow along with, and I simply like Docker. So, without further ado, let’s get to it!
Docker Setup
First things first, we need to get Docker installed. I will be using Ubuntu 22.04 LTS as my base system; however, you can use whatever distro you like, provided you know how to set up Docker.
The easy way to get this done on Ubuntu is by following these steps:
1
sudo apt update sudo apt install docker.io
Once this is complete, we can pull down our container from DockerHub.
To pull down the container and run it, I will be using the following command:
1
sudo docker run -itd --name oracledb -e ORACLE_PWD='oracledb' -p 1521:1521 container-registry.oracle.com/database/free:latest
Let’s break it down.
We start with a normal docker run command and give it the flags -itd.
i- Keep STDIN open even if not attachedt- Allocate a pseudo-ttyd- Detached mode: Run container in the background
We want to keep the standard input open for our container while running it in detached mode. We also want to have an available tty for interactions.
I am naming the container oracledb and need to set a password for the root user. For testing purposes, I will be setting this to the container’s name.
After this, we need to map the internal port 1521 to the external port 1521, which is the default port used by OracleDB TNS listener, so that the database is accessible outside the container.
After this setup, we pull it down from the hub.
Once it is installed, check and see if it is up with a docker ps. If it is up, you’re good to go! You’re all done!
Just kidding. We want to play around and learn a few things, right? Well, let’s get a shell on the container.
Accessing the Database
1
sudo docker exec -it oracledb bash
This command will provide us with a bash shell on the tty we had specified earlier within our oracledb.
To access the database, we can plug in using the sqlplus command. This is what we will be using to interact with the database via the CLI.
To log in as the root or system user, we will do the following:
1
sqlplus sys/oracledb@localhost:1521 as sysdba
Let’s break this down as well.
We first call our command sqlplus. As stated, this command lets us interact with the database.
Next, we do something similar to how we would SSH into a system but with a few twists.
We will log in as the user sys or system and specify the password we had set when we ran the container, which is oracledb.
Next, we point it at localhost since the database is on our current container, and then give it the default port for the TNS Listener service (1521) that we specified earlier and mapped to our host port.
Now you should be presented with a new prompt, and you are in the database!
Syntax
Now, since Oracle likes to be different, they have a slightly different syntax for interacting with the database.
If you want to know the full syntax, here are the docs.
Here, you will find flow charts for just about all the commands you can think of to interact with and create databases.
Cool Useful Things
The coolest thing, in my opinion, is the JSON Relational Duality feature. Coming from a Python dev background, being able to simply provide the database with JSON and having it convert to a SQL database with all the search and filter features is pretty cool! For instance, if you have large datasets or outputs from an API or output from some AI/ML program, it is incredibly useful for filtering and editing these outputs.
Conclusion
There you have it—the bare basics of setting up and interacting with an Oracle database. If you want to learn more, I highly recommend checking out the docs here. For additional insights on Docker, feel free to explore my Docker post, along with some of my Ansible playbooks post or visit my GitHub.