The other day, I wrote about how I had to start from scratch on my podman containers 😢. I’m now at the step where I need to reinstall ORDS in these two new database containers (21c and 23c). And since I’m doing this install yet again, I figured I would point out some things I’ve learned while doing this with podman containers. This post isn’t meant to be all-inclusive; I’m simply highlighting the areas that gave me the most trouble.
Lettuce begin
My assumptions are that you’ve downloaded the ORDS zip file or from a Yum repository (how-to article here). You’ve also set the ORDS configuration folder path and the ORDS product folder path (both are necessary steps for ORDS pre-installation). You can read up on that step here.
The ORDS Interactive Installer
Here, I’m installing ORDS with the Interactive Installer.
Do this with the ords install command
The fine print
NOTE: For a vanilla installation, most of the default prompts are correct. But for working with a podman container, I do not believe all the default settings will work (at least, this has been my experience).
The ORDS Interactive Installer will prompt you with the default settings, where appropriate. You’ll notice the Choose [value]: convention. These settings are okay to use in many steps, but if you mindlessly follow them in specific steps, you might end up with the incorrect ORDS installation for your particular use case.
Select the type of installation
For instance, in the “Enter a number to select the type of installation” step, I’m prompted with the [1] option. For me, that is incorrect; I need to choose [2].
Option [2] I choose you!
Database pool to update or create
Things can get tricky here, too. In this step, I WILL choose option [1], but in the next step, I WILL NOT selectthe default settings (read on about host names, ports, and service names).
This default string is simply an example; with podman you may not be using 1521 as the port.
Selecting the database connection type
And here’s why I won’t use the default settings. It’s because I have mapped the ports to/from my podman containers like this:
My 21entdb container is set up such that my computer sends and receives podman container traffic on port 41465. Meanwhile, my podman container is set up so that it will send and receive data on port 1521 (which is the default port for Oracle’s TNS Listener).
Another way of looking at this is to imagine port 41465 is sort of spoofing port 1521. Ehh..maybe it’s better to think of it like a pass-through, a proxy, a go-between if you will…but more on this in a second.
Demystifying the connection string
Here, I’ll test both ports, the Container (ORCLCDB) and Pluggable (ORCLPDB1) databases, with various connection strings.
QUESTION: How do I even know my options are ORCLCDB or ORCLPDB1? Well, I learned about them in the container registry documentation.
Using port 1521
First, let’s see what happens when I try to log into my database with SQLcl, using 1521 as the port:
Attempting in the Container databaseAttempting in the Pluggable database
Nothing! Initially, for me, this made no sense! And that’s because, in my mind 1521is the port that you would expect to connect with! This whole network business was confounding! That was until I realized that you have to use your computer’s port to connect to the podman container (which is listening on port 1521).
Using the port podman assigned to you
Ah-ha! Now, if you make that slight change to the ports, you can connect to your Container (ORCLCDB) and Pluggable (ORCLPDB1) databases.
We’re sys-dba’ing now, babyyyy 🍾!
Does this help? Do you have a better understanding of why your port might not be 1521?
Host, port, and service names
You can probably keep the localhost default selection. When it comes to the listen port selection, I must choose 41465 and not1521. And for the database service name, you could choose ORCLCDB (i.e. Oracle Container Database), but we recommend installing ORDS into a Pluggable Database (read up on this in our ORDS Best Practices). Here you see me do just that; ORCLPDB1 is the ORDS default, but I wanted to highlight why this is the default.
Provide database user
In this step, I’m supplying the username and password of a user with the necessary privileges to log into the database to complete the ORDS installation. In this case, unsurprisingly, it is the SYS user (the default). We also have an ORDS Installer Privileges script you can execute if you’d rather grant another user privileges to install, upgrade, repair, and uninstall ORDS. You can find more details on that script here.
A great example showing how the JDBC driver is using the host, port, and service name values you provided.
Enabling features
This isn’t the final ORDS install step; this is just the last step I wanted to point out. The default here is also [1]. And I think you should keep it like that, here’s why. When you select [1], you’ll also give users access to Database Actions – the Graphical User Interface (which shares much in common with the SQL Developer desktop client). Once you start using Database Actions, it’s hard to stop.
Don’t ask any questions; select option [1].
NOTE: You'll also be enabling the REST-enabled SQL service (as well as the Database API). I recently wrote about the ORDS REST-Enabled SQL Service; it's very cool, and you should check out that article here.
The end
Aaanndd, that’s my time. They’re flashing the lights, so I have to get off the stage 🙁. Hopefully, this note will be helpful when you create some containers from images on the Oracle Container Registry. And if you think you might like to tinker with ORDS, bookmark this post so you can refer to it later!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
You’re working with podman containers (maybe like me – the ones from the Oracle Container Registry), and when you execute the podman ps command, you see something like this in the standard output:
A container status of unhealthy
In this case, I already had another container with an Oracle 21c database; that one washealthy. I previously wrote up a tutorial on how I did that here. But when I attempted to create another container with the Oracle 23c Free database, this is where things went sideways. This new container would constantly display an unhealthy status 😭 (not working).
Why am I even doing this? Well, we have a bunch of cool new ORDS features that take advantage of the 23c database, and we want to start showcasing that more. It would be nice to be able to show this in containers.
Digging deeper
Issue the podman logs (not log, duh) command for this particular container. Very few details are revealed (technically, it does reveal relevant information, I just need to figure out what I’m looking at here).
That ORA-12752 error message is…an error message.
You can clearly see that the database starts to provision, and then it just craps out1. I’ll spare you most of how we (I write “we” because this stuff is never really resolved by just one person) fixed this issue. But we finally figured it out; I’ll include some brief background. If you don’t care and want to see the resolution, then 👇🏼
1craps out is a technical term used by only the upper-most echelon of technical masters and internet luminaries.
Looking back, that Using default pga_aggregate_limit of 2048 MB line makes A LOT more sense now.
About podman machines
When you initiate a Podman machine, the default CPU and memory settings are 1 and 2048 MB (or 2,147,483,648 Bytes), respectively. I don’t see this in the documentation anywhere, but it is consistent with my assumptions when I created a second test podman machine with the default settings.
The test machine with default settings
After a ton of reading online, tinkering with podman volumes, pouring through the open issues in the podman and Oracle container GitHub repositories, and bugging the hell out of Gerald, we finally figured out the problem. Gerald, quite astutely, asked to see my output from the podman info command.
REMEMBER...this is the output from the original default configuration of my podman machine. The one where I already had a 21c database container. So, briefly ignore that test podman machine.
I included line numbers so you could more easily scan. Again, this output is from when I had a default podman machine. With this machine, I also had a 21c database container with a volume attached to it. I HAVE NO IDEA what the implications are of attaching volumes to containers (as far as memory is concerned)! I also don’t know what it does to the memory of the Linux virtual machine (what your podman machine actually is) 😬.
A closer look at the machine configuration
Take a look at lines 39 and 40; you’ll see
memFree: 1351737344
memTotal: 2048716800
1351737344 Bytes equals 1.35 GB, while 2048716800 is equivalent to 2 GB. That is consistent with what you see in the podman machine’s default settings. And given that I have a 21c database container with a volume attached, that used memory (696979456 or 0.7 GB) could, at least partly, be attributed to the existing container.
Aaaand…that earlier default pga_aggregate_limit of 2048 MB (read more here) line further supports the assumption that insufficient memory (in the podman machine) is the culprit. The way I read it, that database could consume as much as 2 GB of memory.
So, how could I expect to create another container of equal size in a machine that is only large enough to support one container?!
Myself
Resolving the unhealthy issue
Well, after completely removing my podman machine, I re-initiated a new one with the following parameters (docs here):
podman machine init --cpus=2 --memory=4096
NOTE: podman memory allocation is done in Megabytes. So 4096 Megabytes is equal to 4 Gigabytes.
I then recreated two separate volumes, 21cvol, and 23freevol. From there, I created a container for the 21c database using the following command (sorry, I didn’t get a screenshot of this one):
podman run -d --name 21entbd -p :1521 -v 21cvol:/opt/oracle/oradata container-registry.oracle.com/database/enterprise:latest
And then another for the 23c database:
podman run -d --name 23freedb -p :1521 -v 23freevol:/opt/oracle/oradata container-registry.oracle.com/database/free:latest
And FINALLY, as you can see in the following image, both containers show a healthy status!
NOTE: I've yet to sign into either of these databases, and I still have to reinstall ORDS in each. So if you are following along, this is where I leave you.
Inspecting the new machine
And just for fun, I inspected the sole podman machine (after removing that temporary test machine) to review its configuration.
In conclusion
After about a week, I’m NOW ready to start working in the 23c container. We have some great new functionality and other ORDS stuff planned, too. I plan to start sharing that here!
One more thing
I’m bringing these findings up with the Oracle Container Registry team. I’ll probably share with the podman squad too. I feel I’m not alone in having trouble figuring out what the hell is going on under the covers.
If you found this useful, please share the post! This one is more of a Public Service Announcement than anything else. I can’t tell you how frustrating it is when you can’t figure out why something isn’t working.
Hey, I hope this expedites your misery, though 🙃!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
The title says it all. I’ve run through this about ten times now. But I’ll show you how to start a Podman container (with a volume attached) and install ORDS on your local machine. And then, once installed, we’ll create and REST-enable a user so that the user can take full advantage of Oracle REST APIs. (aka ORDS). I’ll finally show you how to log into a SQL Worksheet as that new user. Ready? Let’s go!
Oracle Container Registry
First, visit the registry. Navigate to the Database product category, then select Enterprise. Even better, just navigate to the 21cor23ai images directly (thanks Killian 😉! Ensure you’ve also signed into the site (otherwise, you won’t be able to pull this image).
For Podman, I’ll review the preferred way to start this container (with a volume; for persisting your data across sessions).
Volumes
Start your Podman machine with the podman machine startcommand. Then create a volume with the podman volume create command (that way, you can save data locally and use that volume each time you start your container). Now that we have the volume, we can create a new container and attach that volume simultaneously (more Podman volume info here).
There are a few ways you can attach volumes when starting a container, but I like the one I found in this video:
Ironically, this is an Oracle video. But it is one of the most straightforward ones I found. To start the container, here is the command I used:
podman run -d --name entdb213 -p 1521:1521 --mount=type=volume,source=entdb213vol,destination=/opt/oracle/oradata container-registry.oracle.com/database/enterprise:21.3.0.0
About port mapping
You’ll notice that I used the following port mapping -p 1521:1521. You can remove that leading 1521. If you do, Podman will bind any exposed port to a random port on your host (a MacBook, in my case) within an ephemeral port range. Ephemeral?? (I guess that means all/any available ports, assuming none of these).
At this point, I have created a volume and started a container (with an Oracle Enterprise database inside).
PRO TIP: If this is your first time starting a container with a database of this size, it will take several minutes. So go do some chores while you're waiting 🤪
Altering the db password
I’ll change the database Administrator password to something I can easily remember (“oracle“) using this command:
# Original command
# docker exec <oracle-db> ./setPassword.sh <your_password>
# My modified command
podman exec entdb213 ./setPassword.sh oracle
Changing the password to something I can easily remember.
Note: There are several shell scripts included in this container; one of which is the change password script. There are more details on the Oracle Container Registry > Oracle Database Enterprise Edition page (redirects prevent me from linking directly to that page).
Downloading ORDS
Next, I’ll head to the Oracle REST Data Services download page. And download the latest ORDS build (I’ll be setting this up shortly, I’m just gathering and configuring everything now).
Once that ZIP file is in my downloads folder, I’ll unzip it. At this point, this folder will still be named ords latest. You can certainly keep it like that, but I’ve renamed it to ords_product_folder. This is similar to how we refer to it in our installation/configuration documentation (changing it might make it easier to follow along).
ORDS Configuration
There are two configuration steps I need to perform before I can begin the ORDS installation. You’ll need to set an Environment Variable to the binaries (these are in the bin folder, you should see that in the above image) found in the ords_product_folder. Secondly, you’ll need to create an ORDS Configuration folder.
WAIT: If you're still reading this, how is my approach? After some research, placing these two folders in the "Library" seemed to make the most sense. I'm not sure what the analog on a Windows machine would be though. Drop a comment below if you know!
At this point, I’m nearly finished with this initial configuration. I next opened my .zprofile file (this is the file where I’m keeping most of my environment variables) and added the following paths:
Pourquoi? I can't seem to find a definitive answer as to where these paths should be saved, but this thread on Stack Exchange does a great job explaining all these files (and when/where they are used).
ORDS Installation
You’ll want to exit out of all your Terminal sessions so that the next session can pick up those changes to the .zprofile file. Podman will still keep doing its thing in the background, and hopefully, by this time, the database container will display as “healthy.”
🛑 STOP: This may be obvious to you, but it wasn't to me, the database needs to be ready (healthy), online, and active (whatever you want to call it) for ORDS to install. You can always issue the podman ps command to check the status of the container.
Remember this; you’ll need it shortly.
ORDS install, the first attempt
In a new Terminal, I’ll issue the ords installcommand. If you’ve set up your bin and config environmental variables like me, then you shouldn’t have any issues. Moving through the first few steps is easy.
The ORDS interactive installer will default to recommended settings. Most of the time, these will be correct. Since this is my first time installing ORDS, I’ll choose Option 2 in that first step. I can use “localhost” as the database hostname and 1521 as the port.
When you get to the database service name, that’s where you might get hung up. The ORDS installer assumes default configuration settings. But here, if you select “orcl” as the database service name, it will install ORDS in the entire database. This is not technically incorrect, but our ORDS Best Practices recommends you install ORDS in a Pluggable Database (PDB). So I’ll issue the podman logscommand (in my case: podman logs entdb213) to find the name of the PDB; ORCLPDB1 (that’s the default for this container, it’s well-documented in the container registry docs, I’m just an idiot).
This is a neat trick, but it’s also documented in the Container Registry docs.
The ORDS interactive installer is very forgiving. I’ve noticed I can exit out of the installation process pretty much anywhere. Given that fact, I’ll restart my Terminal and start the ORDS install over (now that I have the correct database service name).
ORDS install, for real this time
In these images, you can better see the ords installcommand (it was slightly grayed out in the previous image). And you’ll also see all the steps completed. You can probably keep everything default as I did. You might want to since our documentation refers to that 8080 port (as seen in the images) in most of our tutorials and examples. I find it easier to follow along with the docs when everything matches.
🗒️ NOTE: Make sure you choose to run ORDS in standalone mode. That way you can continue to follow along in later steps.
Here you can see the paths to the bin and config folders.Conditions are perfect.
The rest of the installation is largely unremarkable. You will notice a few things, though:
The paths we’ve pointed to for the config and bin folders
The “settings” names(no action required by you, it’s just cool to see)
once the installation finishes, the text “Oracle REST Data Services initialized” will appear
That final image confirms that ORDS is now running in standalone mode. You can visit the following:
localhost:8080/ords/sql-developer
Logging into the SQL Worksheet, the first attempt
And try to log in with the SYS credentials.
One does not simply log into Database Actions…you must REST-enable a user first.
SPOILER ALERT: You can't 😕! Muahahahahaha!
That was a dirty trick. While ORDS is installed in ORCLPDB1, we must first create and REST-enable a user. I will make up for this dirty trick by sharing one of my favorite cheat codes for learning SQL and PL/SQL.
🗒️ NOTE: You cannot REST-enable the SYSTEM or SYS user.
If lucky enough, you have an Oracle Cloud Free Tier account with at least one Autonomous database provisioned.
CORPORATE SHILL ALERT: You can sign up for one here 😘.
Code cheating with the Autonomous database
Login to Database Actions as the Administrator. Navigate to User Management. In that dashboard, select the + Create User button.
In the Administration section.Click the Create User button.
When the slider appears, enter the information for the ORDSTEST user (like you see in the image here).
Once you’ve entered everything in, hit that “Show code” switch.
You’ll need to enable “Web Access” for this user. When you do this (click the switch), two things will happen:
The CONNECT and RESOURCE roles will be automatically selected for you
The Authorization required toggle will be enabled – shut this off for now
Once you’ve done that, you can select the “Show code” switch at the bottom of the slider. This will reveal the actual code that is being executed should you click the “Create User” button (which you will most certainly NOT!).
Copy and paste this into a text editor.
I copied this code and placed it into a text editor. I made one small change to the QUOTA line (at the bottom of the script).
Stole this from the ORDS Quick Start Guide.I am making a slight change to that last line.
Then I headed back to my Terminal and opened a new tab. I’ll execute this code in the database (remember, it’s in that Podman container running idle in the background this entire time) using SQLcl.
Forgot your connection string?
IF YOU FORGET the connection string format for logging in, have no fear! That Jeff Smith recently showed me the history command. I also have a couple of shorts on how I used the command:
Using the history + the number of your choice.Here is a connection string that is close enough!Entering the slightly modified string into SQLcl.
Using SQLcl to REST-enable a user
Now that I have the proper format for the connection string, I’ll adjust it so the password is correct. Then I’ll execute the code in SQLcl to create a new database user and REST-enable that user’s schema.
I changed that final line; this is what it looks like in SQLcl.
Cheat code with PL/SQL in SQLcl
I’ve just learned you can REST-enable yourself by logging into SQLcl (i.e., connecting to the database in the Podman container) and issuing the following command:
EXECUTE ORDS.ENABLE_SCHEMA;
This command assumes that you have already been granted the CONNECT and RESOURCE roles but have yet to REST-enable your schema (what allows ORDS to act as the intermediary between the database and the rest of the web).
The command will employ the procedure’s default parameters, which are:
ORDS.ENABLE_SCHEMA( p_enabled IN boolean DEFAULT TRUE, p_schema IN ords_schemas.parsing_schema%type DEFAULT NULL, p_url_mapping_type IN ords_url_mappings.type%type DEFAULT 'BASE_PATH', p_url_mapping_pattern IN ords_url_mappings.pattern%type DEFAULT NULL, p_auto_rest_auth IN boolean DEFAULT NULL);
Here is what a sample output would look like, if I were signed in as the HR user:
An example output is if I were signed in as the HR user.
🛑 FYI: This above image is just a sample, and not related to the rest of the images in this article. Be sure to pay attention to the connection string (sql hr/oracle@localhost:1521/freepdb1). This is if the HR user is logging into SQLcl and REST-enabling their own schema. That's why you see references to HR throughout. I don't want anybody to get confused!
Logging into the SQL Worksheet, for real this time
With all this code executed, I can NOW go back to the SQL Worksheet (remember, we’re on localhost:8080/ords/sql-developer) and log in as the newly created ORDSTEST user.
I am logging in as the ORDSTEST user.A true “will-they, won’t they” moment.Congrats, you have arrived!
And once you’re in, you’ll notice the SQL Worksheet is no different than what you might expect in the Oracle Autonomous database. So if you made it this far, go forth and CREATE, DELETE, INSERT, SELECT, and Query away, friends!
Shutting it all down
Once you are done playing and tinkering, you can log out of the SQL Worksheet, stop the ORDS process with CTL + C (on Mac, at least), stop the Podman container, and shut down the Podman virtual machine.
Use Control + C to stop the ORDS process.I am stopping the entdb213 container.Exiting from the Podman virtual machine.
And since we set this all up with a volume (so….so long ago, I know; we called it entdb213vol), you can start the container later on, and all your work will still be there (i.e., It shall persist!).
The end
Congrats, we made it! What do you think? Did I miss anything? If so, comment, and I’ll respond and update this post as needed. And if you think this could be useful for others, do share!
Podman is a daemonless container engine for developing, managing, and running OCI Containers on your Linux System. Containers can either be run as root or in rootless mode. Simply put: alias docker=podman.
I’ve spent the past couple of weeks setting up Podman to work on my MacBook.
My current setup
I really wanted to take advantage of our Oracle Container Registry. While, we have several containers, the one I’m most interested in is the [Ace] freely available Oracle Enterprise Database version 21.3.0.0.
FYI: It can be found in the Database containers category.
I wanted to learn more about containers while also connecting locally (i.e., from my MacBook) via SQLcl to said container. In that scenario, as far as my computer thinks, the container is a production database running elsewhere in the world. Oh, and I’m using Podman instead of Docker to do all this.
be able to connect to it with various Oracle database tools
I began this exercise with SQLcl since it was used in one of my recent posts. But as a follow-on to this article, I’d like to install ORDS on my local computer and then connect again but with ORDS joining the party. But that’s for another time.
But before connecting to this container, you’ll need a lot of prerequisites. As far as “ingredients” go, you’ll need the following:
Homebrew installed and updated. (If you need to do this, review my recent article for instructions.)
Podman installed and updated
Apple X-Code Command Line Tools updated (this is tricky, so check my notes below)
SQLcl (you can review the installation steps here)
A sample CSV file (The subject doesn’t matter; I grabbed one from Kaggle – “IMDb’s Top 100 Movies“)
The setup before the setup
Since this was such a huge PITA, I’m going to walk through all the steps I took to make Podman work on my MacBook. I’ve done this about ten times so far to make sure I’m clearly explaining every step I took.
I first opened up a new Terminal session using Spotlight (Left Command + Spacebar). Once in Spotlight, I searched for “terminal” and then hit enter. A new Terminal window will appear.
Opening SpotlightSearching for the Terminal applicationA new Terminal window
From there, I reviewed Homebrew using the brew list command. If you’re following along, you’ll see a list similar to mine, depending on what you have installed.
The next part is easy. I installed Podman with the following command: brew install podman. Homebrew will run through an auto-update, and eventually, Podman will begin installing.
Upon the first installation, and depending on the macOS you are on, you may see a couple of errors appear. I can tell you they will cause issues within Podman later on down the line. So (and without having to take you back down the rabbit hole with me), you’ll need to uninstall Podman with the brew uninstall podman command.
The errors can be seen in this image:
Errors with Podman installing
There are a few ways one can remedy this. First, you should uninstall Podman, close your Terminal window, and open up a new Terminal window. I found (via this GitHub issue) that this is a known bug. Some have suggested running the brew doctor command to review a list of possible problems (this will reveal any potential problems Homebrew has discovered). This seems like a good practice, regardless, and I wasn’t aware of this feature until now!
And while writing this article, I did just that and found two errors I’ll need to fix. I’m still trying to figure out what either means, but the one about the executable is troubling.
But back to the Podman issue. To resolve the xcrun errors, I stumbled upon a solution buried deep in the recesses of the internet. Long story short, I needed to manually install Apple’s X-Code Command Line tools. But if you try and the installation fails, you have to take an extra step.
BTW, it did NOT take 78 hours to download and install
The x-tra step
If the xcode-select --install command fails, you have to remove the Command Tools from your machine altogether. I did this with the following command:
sudo rm -rf /Library/Developer/CommandTools
If you want to bore yourself with this issue, here are some resources I found:
I’ve seen sudo in the past; I wonder if I ever bothered to look up its meaning. Taken directly from the docs:
Sudo (su “do”) allows a system administrator to give certain users (or groups of users) the ability to run some (or all) commands as the superuser or another user, while logging all commands and arguments. Sudo operates on a per-command basis, it is not a replacement for the shell.
Back to our regularly scheduled program…you’ll probably need to enter your system’s password (the thing you use to log on to your computer when it first starts up and/or wakes). And after that, restart your Terminal (I don’t believe I did this, but it’s probably a good idea to restart the Terminal).
Once that new Terminal window fired up, I used the following command to install the latest X-Code Command Line tools:
sudo xcode-select --install
Reminder, it will not take 78 hours to install this. I just followed the prompts (license terms, the usual stuff, etc.).
NOTE: I suspect we have to do this because for some reason, X-Code Command Line tools are not updated upon every macOS version update. So, who knows when the last time these tools have been updated. This is just a hunch, but in reality, I've no idea what I'm talking about.
Are we ready yet? Well, almost. Again, if you’re following along, navigate to our Oracle Container Registry site to retrieve the database container for this “recipe.” The path I took was Landing page > Database > Enterprise.
The main landing pageYou want “enterprise”Pay attention to the sign-in
YOU NEED TO SIGN IN for this to work!!! Oh, suuuuure… it’ll seem like it’s working when you’re in Podman, and you’ve tried ten times…but then it just keeps failing, and failing, and failing! So be sure to sign in (or create an account if you haven’t already).
Once signed in and chosen your preferred language, you’ll see this:
I’m ready to head back to the Terminal
PAUSE: reviewing the limitations of this exercise
Alright, so there are a few limitations I should address, and in no particular order:
Checkpointing containers in Podman
Volumes in Podman
Creating the database versus signing on to the database
Checkpointing currently doesn’t work in Podman (at least for Macs on macOS Ventura). This is documented as well. Here’s a GitHub issue I found. I don’t seem to be the only one with the issue. I spent about a day on this trying to get it to work. I couldn’t figure it out; maybe if you’re reading this, you know the secret. Please share if you know!
Secondly, I couldn’t figure out how to mount a volume to a container. I know this is fundamental to containers, but I encountered error after error for days. And for the purposes of this exercise, it isn’t a big deal. Now, if I were on an actual development team, that would be a different story. But I’m too dumb for development, that is why I’m a product manager 🤣!
Finally, working with containers requires a paradigm shift. Shortly you’ll see that I’m setting up a container and “starting” the database therein. Later, I’ll separately log on to that database, using SQLcl,after the database is up and running. They are two different steps.
Looking at this screen you would think, “I’m just going to jump right in and execute the first command I see on this page.” Wrong!
Initial docker run command
Actually, you do NOT want to do that. You must scroll down to the “Connecting from outside of the container” section. Because I’m going to be connecting to this container from the outside.
Referring to the Custom Configurations section
I know this documentation mentions SQL*Plus, but this all applies to SQLcl also. And if you refer to my previous SQLcl post, you can review the logon syntax for logging on. The critical point is that I need to start the container with the -p (or Port) option included. Are you still with me? Let’s take a trip to the “Custom Configurations” section.
docker run -d --name <container_name> \
-p <host_port>:1521 -p <host_port>:5500 \
-e ORACLE_SID=<your_SID> \
-e ORACLE_PDB=<your_PDBname> \
-e ORACLE_PWD=<your_database_password> \
-e INIT_SGA_SIZE=<your_database_SGA_memory_MB> \
-e INIT_PGA_SIZE=<your_database_PGA_memory_MB> \
-e ORACLE_EDITION=<your_database_edition> \
-e ORACLE_CHARACTERSET=<your_character_set> \
-e ENABLE_ARCHIVELOG=true \
-v [<host_mount_point>:]/opt/oracle/oradata \
container-registry.oracle.com/database/enterprise:21.3.0.0
Parameters:
--name
The name of the container (default: auto generated
-p
The port mapping of the host port to the container port.
Two ports are exposed: 1521 (Oracle Listener), 5500 (OEM Express)
-e ORACLE_SID
The Oracle Database SID that should be used (default:ORCLCDB)
-e ORACLE_PDB
The Oracle Database PDB name that should be used (default: ORCLPDB1)
-e ORACLE_PWD
The Oracle Database SYS, SYSTEM and
PDBADMIN password (default: auto generated)
-e INIT_SGA_SIZE
The total memory in MB that should be used for all
SGA components (optional)
-e INIT_PGA_SIZE
The target aggregate PGA memory in MB that should be used
for all server processes attached to the instance (optional)
-e ORACLE_EDITION
The Oracle Database Edition (enterprise/standard, default: enterprise)
-e ORACLE_CHARACTERSET
The character set to use when creating the database (default: AL32UTF8)
-e ENABLE_ARCHIVELOG
To enable archive log mode when creating the database (default: false).
Supported 19.3 onwards.
-v /opt/oracle/oradata
The data volume to use for the database. Has to be writable by the
Unix "oracle" (uid: 54321) user inside the container If omitted the
database will not be persisted over container recreation.
-v /opt/oracle/scripts/startup | /docker-entrypoint-initdb.d/startup
Optional: A volume with custom scripts to be run after database startup.
For further details see the "Running scripts after setup and on
startup" section below.
-v /opt/oracle/scripts/setup | /docker-entrypoint-initdb.d/setup
Optional: A volume with custom scripts to be run after database setup.
For further details see the "Running scripts after setup and on startup"
section below
I believe the colons you see throughout the original code block (with certain exceptions) are there for the definitions (you wouldn’t actually include these in your commands). If you are coming from database development, I suspect some may think, “ahh, bind parameter.” I do not think that is the case here.
You might be asking, in this code block, what the hell am I supposed to be looking at? Well, the container has a “listener”, listening on port 1521. So if I want to connect to the container, I’ll need to “map” to it. I’m not sure if that is what it is called exactly (not a networking guy, don’t claim to be). But the next question is, what is my <host port> (How it is referred to in the code block above)?
Everything matters, and nothing matters
Executing a ping command in my Terminal, to see what my computer’s address is great, but it tells me nothing about the port.
Use ping localhost to see your IP address
So I took to the internet to try to figure out the appropriate port…Honestly, I’ve tried searching but I can’t find anything definitive. In the Podman documentation, I see a lot of reference to port 8080; as in localhost:8080.
Care to review it? Here are some search results using “8080” as the search parameter.
Buried in the docs, there is a brief mention of the port and it can be found in the –publish or -p parameter. The way I understand ports on your local machine is that if you omit the local host information, you shouldn’t have any problems. It will just default to…something. So..it doesn’t matter, nothing matters. It’s all an illusion.
I also reviewed the cURL documentation. I found something in the --connect-to option:
Can you leave it empty?
Aaaand, more port nonsense (if you are having trouble sleeping at night, try reading this):
Okay, with all this out of the way, I can finally start to make some progress (almost there, promise).
Remember, you have to start the Podman Linux virtual machine before you do anything (this is in the instruction steps, so review that first (steps for macOS). This is where the container “lives.” Once the virtual machine is up and running.
Podman virtual machine is ready
I then grabbed the Oracle container. But, since I’m using Podman I needed to modify the run command, like this:
podman run -d -p :1521 --name myoracledb container-registry.oracle.com/database/enterprise:21.3.0.0
REMINDER: Make sure you are logged into the Oracle Container Registry site before you attempt all this!
Assuming you’re still following along, you’ll see something like this in your Terminal:
New container ID
I used the podman ps command to check the status of the container. You should see something like this:
“Starting” and “Healthy” statuses
For several minutes, you’ll continue to see the container status as “starting”. You can do like me and just periodically enter the podman ps command, or you can go do something meaningful with your time. Check back in 10 mins or so. The choice is yours. Either way, you’ll eventually see the status change from “starting” to “healthy”.
The container is healthy and ready
“Healthy” means I now have an Oracle Enterprise database (version 21.3.0.0) running in my Linux virtual machine as a container. I still need to log in with SQLcl, though.
Hold up, I can’t just log into SQLcl. I still have some more setup to do. I need to reset the randomly generated password to one of my choosing. Our instructions tell you to first issue the docker logs + [your database name] command to view your database logs. And from there you should be able to locate it. I couldn’t maybe you can. Let me know if you were able to.
Using the logs command
Since I’m doing this in Podman, that command is slightly modified:
podman logs myoracledb
The printout of that command will appear like this (yours will be very similar). Although I wasn’t able to locate the password, there are still some important pieces of information that you’ll want to review and note.
podman logs [your database name] printout
In this print out you’ll see things like the local host and port information, and the “Global Database Name” and “System Identifier (SID)” can be seen as well. You’ll see where the log files are located (your temporary password can be retrieved from here) and the database version you are running. Finally, you’ll see the message “DATABASE IS READY TO USE!”
Use the included shell script for changing your password
We are this close to logging onto the database. Even though I couldn’t find the temporary password, it doesn’t matter. You have to change your password anyways. If you refer back to the instructions on the Oracle Container Registry page, there is a section entitled “Changing the Default Password for SYS User” and it reads as such (emphasis added):
On the first startup of the container, a random password will be generated for the database if not provided. The user [must] change the password after the database is created and the corresponding container is healthy.
Using the docker exec command, change the password for those accounts by invoking the setPassword.sh script that is found in the container. Note that the container must be running. For example:
Easy enough, and since my container is “healthy” at this point, I can execute this script. But since I’m using Podman, the command will look like this:
And the output of that command will look like this:
Automated password change with the provided Shell script
I guess it worked. As you can see, my new password is password1234 (pleeeeease, do NOT share that with anybody). And at this point, I’m ridiculously close to logging onto this completely containerized Oracle enterprise database. All I need to do now is log on using the same steps as before (in my previous post).
Referring back to the Oracle Container Registry docs, I see the following:
The different login options
NOTE: Remember I'm logging into this container from the outside.
The connect options are cut-off in that image, so let me copy/paste them here. Also, assume where it states “sqlplus” I’ll be connecting with SQLcl. The options are as follows:
Turns out you can just use the port command to discover the container’s port (I’m guessing this is the route the container uses to communicate with my MacBook – it’s all quite muddled at this point).
Here is the command I executed:
podman port myoracledb
And here is what was returned:
Exposing the ports for this network
If you are starting your journey from the MacBook, its address would be 0.0.0.0 with a port of 43073. Data/info flows in and out of that port. And 1521 is a reference to the [bleep blurp 🤖] TCP port at which the Transparent Network Substrate (TNS) Listener is located.
Actually, if you look at the previous output (from the podman logs myoracledb command) you’ll see how all the addresses and ports connect (including the TNS Listener).
TNS Listener information
It's in the logs, how could you not know this!?
Honestly, this is all ludicrous. You shouldn’t know all this, nobody should! It’s too much to retain, but that’s okay, I’m glad you’re still here…toughing it out with me. Once you get past this first big hurdle, I imagine working with containers is very fun.
Here is where I actually logged on (or is it logged into?) to this database with SQLcl. Here’s the command I used:
sql sys/password1234@//localhost:43073/ORCLCDB as sysdba
Which, if you recall is modeled on the original command found in the Oracle Container Registry docs; it looks like this (it’s also a few paragraphs back):
$ sqlplus sys/<your_password>@//localhost:<exposed_port>/<your_SID> as sysdba
NOTE: Exposed port is that where the TNS Listener is located, and the SID is the “System Identifier” – I showed that in the database logs earlier.
And again, I don’t think it matters if you include the localhost port. Here is what the output looked like in my Terminal:
Alright, so finally, I’m in! Next, I tested a SQLcl function to see if it worked as expected. I chose the LOAD function. And just as a refresher on the LOAD function I referred to the SQLcl help for in-context assistance. That help looks like this:
SQLcl Help and LOAD information
Specifically, I am going to test out the “CREATE TABLE” and “LOAD TABLE” function. So I scrolled down to the examples for reference.
Example showing how to create and load a table at the same time
At this point, the commands are pretty straightforward. I decided to use the LOAD NEW command, as seen above.
The beginning of the LOAD...NEW commandAlmost forgot the “NEW“Ready to execute
PRO TIP: You can simply drag the file and drop it into Terminal to get the complete file path.
DON’T forget to include the “NEW” at the end of the command. I forgot it the first time and my load failed. If doesn’t break anything, just a silly mistake.
I hit enter, and if you look at that image with the “Ready to execute” caption, everything worked as expected, here it is a zoomed-in (please excuse the gray shading):
Alright, so I have a brand new table. And if you recall, this was a data set that included the IMDb top 100 highest-rated movies of all time.
IMDb dataset by way of Kaggle
Well in the next few images, I wanted to test some SQL on the new table. I first searched by genre, with the following SQL:
SELECT DISTINCT genre FROM t100movies;
Selecting by the movie genre
Which returns all the distinct matches. Easy enough right? Then (because I like Adventure and Fantasy) I selected only those films that match those criteria, with this SQL statement:
SELECT * FROM t100movies WHERE genre = 'Adventure, Fantasy';
Single quotes and don’t forget your semi-colon 🫡
And once I correctly entered the SQL query, I found a single movie in the top 100 that meets those criteria. I’m actually surprised this one made the top 100 list.
Okay, but there’s just one more thing. The data persists inside the container even after I’ve stopped it. This isn’t necessarily the focus of this article, but I just wanted to demonstrate that even after shutting everything down, the table still exists.
This is true even after completely stopping my Podman container and shutting down the Podman Linux virtual machine.
The process was as follows:
Exited out of the SQLcl application
Stopped the myoracledb container process
Checked to make sure the process was actually stopped
Stopped and then restarted the Podman Linux virtual machine
Restarted the myoracledb container
Executed the same SQL query as before
Exited from the SQLcl application a final time
And if you take a look at all these images (they are numbered in order) you can see all the steps I took to during this little test. Pretty cool, eh!?
1. Exiting out of SQLcl2. Stopping the Podman container3. Stopping the Podman virtual machine4. Restarting the Podman virtual machine5. Restarting the myoracledb container6. Using the same SQL from before7. A final exit from SQLcl
A couple of notes here:
When I restarted the container, it only took about a minute for it to move from a “starting” to a “healthy” status. So I think the first time you start this container it takes a while. Subsequent start-ups, a minute or less.
When you start back up the container, you don’t have to map anything, I believe all those settings are still intact. Either that or I just spent a whole bunch of time networking when I didn’t need to.
And that does bring us to a close. If you’ve made it this far, you understand how you can at least get this container started and log on using SQLcl. Remember you can use Brew to install SQLcl and Podman. And of course, you’ll need to get the container I used (from our Container Registry; you can find it here in the Database category).
Remember, I didn’t do anything with checkpoints (or checkpointing containers) or with volumes. At the time of this article, I wasn’t 100% confident in my approach, so I wanted to exclude it. However, as I understand it, volumes (and their use) are the preferred approach to persisting data for later use (in containers). Just keep that in mind.
Finally, I’m not going to sugarcoat it. This was a grind – it was very tedious and frustrating, so hopefully, you can learn from my mistakes. I’m not claiming to be a developer or an expert in CI/CD. But I can totally see the appeal of having portable containers like this. The barrier to understanding stuff like this is incredibly high, so good luck to you. But hey, if you screw up, don’t worry about it. You can always uninstall and reinstall and try again.
Be sure to leave a comment if you see something amiss or if you have a better approach to something I’ve shown here. And as always…
