Your Own Oracle Database Lab in 1-2-3 #JoelKallmanDay
You can’t release to production all the time. Sometimes you need to learn, try out stuff, and even have some fun. This post is about how you can get an Oracle database up and running in minutes (even in seconds) when you just need to do some experiments.
The fastest way to achieve that is using a prebuilt container from Oracle. This works nicely on Linux, Windows, and MacOS.
Install Podman
Podman is fully compatible with Docker, on Linux you may even do alias docker=podman if you have old scripts using the docker command.
Download Podman from podman.io . I use Linux when I can, but given that most people have a Windows PC I decided to show you the procedure on a mini PC I have with Windows 10. I decided to only install the CLI version:
Run the downloaded file to install Podman, and open a command window. The first time you need to init Podman:
podman machine init
It downloads a VM image:
Then you can start it with:
podman machine start
As demonstrated in screenshot above, it prints out a warning to change a setting in case the containers will need root permissions. I think that is a good idea:
podman machine set --rootful
podman machine stop
podman machine start
Run a container
With this in place we are now ready to download and start a container. Let us start with the Oracle 23c Free version. Go to Oracle Container Registry and click on Database:
and from there click on free:
The pages gives you a few option. If you want to download the image only without actually running it you can use the command podman pull container-registry.oracle.com/database/free:latest, or you can just run it and it will download and start it in one go:
podman run -p 1521:1521/tcp --name ora23cfree container-registry.oracle.com/database/free:latest
The -p option means port 1521 from the container is available on the host (your PC), so we can connect to the database through the listener later. You are free to name it to whatever you want.
The first time you run this it will take some time to download the layers and in the beginning it will look like this:
While you are waiting for the download to complete you can have a look on the other containers at https://container-registry.oracle.com/ . I’ll come back to those later. After a while it will start the container and print out messages like this:
If you include the option -d to the run command above it will run the container in the background. Since I did not specify it, I’ll just open another command window and run the following commands. We did not specify a password, but we can connect to the container and set one. But first verify that it is running:
podman ps
The following command will run bash in it with a terminal in interactive mode (that is what the -ti flag is for):
podman exec -ti ora23cfree bash
Since you don’t know the sys/system password you can set it with the next command (or you can connect to the database, see the following step):
ls
./setPassword.sh test42
Now you can connect to the database with SQL Developer or some other client from outside the container (that is, from your PC), but since we are here, we can connect directly to the CDB with sqlplus and test the new syntax:
sqlplus / as sysdba
select sysdate;
show pdbs
Connect with SQLcl
For fast connection to an Oracle database nothing beats Oracle SQL Developer Command Line, SQLcl. You can download it from https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/
or https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
will always give you the latest version, no interaction required. It does need Java, at least version 11. Unzip the file into some place where you’ll find it later. Top level is the directory sqlcl. The following commands is executed on a Windows PC:
cd sqlcl\bin
sql -L system/test42@/FREE
In the connection string above both the host and port is left out and it will connect to default port 1521 on the same machine. I always use option -L with SQLcl; it will not reprompt for username and password if the initial connect does not succeed.
Slightly more advanced
Ctrl-C in the window where the container was started will kill it. You can start it again with podman start ora23cfree, but I want to delete it and start over with a few options as shown on the webpage at container-registry.oracle.com mentioned earlier. I want to preserve the datafiles in a volume and set the password for SYS, SYSTEM and PDBADMIN:
mkdir c:\oradata
podman rm ora23cfree
podman run -p 1521:1521/tcp --name ora23cfree -e ORACLE_PWD=secret#42 -v /mnt/c/oradata:/opt/oracle/oradata container-registry.oracle.com/database/free:latest
Note the syntax for specifying the local directory: /mnt/c/oradata for the Windows directory c:\oradata. The container startup takes longer this time since it copies the database files and does some other stuff before the database becomes available:
The message DATABASE IS READY TO USE! means just that:
You can now connect as before with SQLcl and the new password. This time connect to the PDB, and create a user:
sql -L system/secret#42@/freepdb1
create user testuser identified by testuser42 default tablespace users quota unlimited on users;
grant connect, resource to testuser;
Disconnect with Ctrl-D and reconnect as the new user:
sql -L testuser/testuser42@/freepdb1
Create a table
create table foo as select level id, dbms_random.string('a',100) bar from dual connect by level < 1000;
Disconnect again with Ctrl-D. Now we can stop the container, and restart it:
podman stop ora23cfree
podman rm ora23cfree
podman run -d -p 1521:1521/tcp --name ora23cfree -e ORACLE_PWD=secret#42 -v /mnt/c/oradata:/opt/oracle/oradata container-registry.oracle.com/database/free:latest
# Wait a few seconds for the container to start
sql -L testuser/testuser42@&/freepdb1
select count(*) from foo;
When you execute the stop command above you will notice messages from the alert log in the other window where the podman was run the first time. Even though I deleted the container the database is preserved since I ran the container with the same volume path as before. Also the startup time now is just a few seconds (I am running this on a small Core i5 PC with SSD, YMMV.) Also note that I added the -d option this time in order to continue to work from the same window. You can check the log (output from the alert log in this case) with podman logs -f ora23cfree, the -f options means follow; it will poll the log for more data continuously.
From the testing above, we can see that if you are in hurry and don’t need to preserve the database after testing, then skip the volume option; it starts much faster. You can stop the container, start it again, and the data will be there until you delete the container with podman rm ora23cfree. When the volume options has been used, you can stop and remove the container, and next time you run it (create a new container with run command and volume option -v as above), it will start almost immediately and with the old data preserved. The main difference between these two options is the time it takes to run the container when the volume is empty (i.e. the database has never been created in the volume.)
This solution is perfect for extensive testing (integration tests, system tests, etc), or just try out stuff on your own.
More images
As you saw Oracle provides a lot of different images you can try. You can even try Autonomous database on your PC without using the cloud. This was recently released by Oracle, and I plan to explore it in another post. I am already using the Autonomous database in the Oracle Free Tier, so it is more out of curiosity.
Postscript
Joel Kallman did a fantastic job for the Oracle community. It was all about sharing and encouraging others. Some years ago it used to be much more complicated when you wanted to learn and try out Oracle, but now we have access to many options like these containers, VM images, Oracle Free Tier, etc. If you manage to stay curious there is and will be a wealth of things to try out.