Add pluggable databases to Oracles Database Docker images

I recently needed a database including a huge schema for demo in a very short notice of time. I know know how to run expd/impd but that brings usually hassle regarding users, schemas and so on.

As some of you might know I’m using Oracles Database Images for Docker for quite some time now (first described here). You can still build them to your own needs from the sources (here) or use the new Oracle Container Registry which works quite nice if you already have an OTN account.

Anyhow. Those are the images I use currently:

oracle/database                          12.1.0.2-ee                acb2002fe54b        53 minutes ago      11.4 GB
oracle/database                          12.2.0.1-se2               211d3ba03cb2        7 weeks ago         14.8 GB

The 12c database consists per default of a container database and one or more pluggable databases and the 12c images adhere to that. So does our main development database.

What I did on the development database was: Stopping and unplugging the database containing the schema and user of our application (luckily, I was clever enough to separate those from other applications the last time i set this up). Then, I copy over the files to my local machine. That took about 15 minutes, including giving my coworkers a short note and was way faster than exporting the whole 40Gb or something.

How to do this? Login as sys user or any user with administrative rights: sqlplus / as sysdba will do just nicely on the database server. Than

ALTER PLUGGABLE DATABASE pdb_awesome_app CLOSE;
ALTER PLUGGABLE DATABASE pdb_awesome_app UNPLUG INTO '/tmp/pdb_awesome_app.xml';
DROP PLUGGABLE DATABASE pdb_awesome_app KEEP DATAFILES;

This closes and unplugs the database and writes all metadata into pdb_awesome_app.xml. Then copy the database files. Those usually by in a folder like /opt/oracle/oradata/NAME_OF_THE_CONTAINER_DB/pdb_awesome_app. Copy the whole directory onto the machine that will run the container. Also copy /tmp/pdb_awesome_app.xml onto your machine.

First it’s time to make your coworkers work again. The last command dropped the pluggable database from your server which is fine, since we still have the database files and the metadata. Still on the server execute

CREATE pluggable DATABASE pdb_awesome_app USING '/tmp/pdb_awesome_app.xml' NOCOPY TEMPFILE REUSE;
ALTER PLUGGABLE DATABASE pdb_awesome_app OPEN;

That will plug the database again and open it for your coworkers to enjoy.

Back to your machine running the docker instance. Start the Oracle Database image that fits the version your server has. I was using 12.1.0.2 here. Also note that the container must have the same database options available as the source. You can still add them inside the container with dbca, it’s part of the docker image. dbca has a -silent option so it doesn’t molest you with the X11 UI.

Start the container using something like

docker run -d -p 1521:1521 -v /some/path/OracleFiles:/opt/oracle/oradata oracle/database:12.1.0.2-ee

Note: You might want to check the charset of your database! The container databases charset should match the pluggable database. Oracles Docker images use AL32UTF8 by default. Luckily you can pass the charset with ORACLE_CHARACTERSET. That charset is used for that instance only. For example, use e ORACLE_CHARACTERSET=WE8MS1252 to start a database with WE8MS1252.

It will map /some/path/OracleFiles into the running container. If this folder contains a database (for example when you already used the container), startup will be fast, otherwise the container takes some time to create the initial container database. It’s important that you map a local folder into this docker container here. Oracle databases are huge and slow to create, so it’s not really an option to keep them inside the docker container. Also, it makes the running docker container even bigger than it already is.

Also, for the purpose of this exercise here, it’s important to take note of /some/path/OracleFiles. While your container is starting, you can already move the pdb_awesome_app folder you copied from the database serve into /some/path/OracleFiles. Also copy pdb_awesome_app.xml into the same folder.

Here is an important step: You may have to fix paths inside the xml file if your server doesn’t have it’s database files under /opt/oracle/oradata. Take your favorite editor and fix each entry in the xml file if necessary.

Then, use Kitematic or docker command line tools to execute a bash inside the running database container. Login to the container database as follows

docker exec -i -t ID_OF_THE_CONTAINER /bin/bash 
export ORACLE_SID=ORCLCDB
oraenv
sqlplus / as sysdba
create pluggable database pdb_awesome_app using '/opt/oracle/oradata/pdb_awesome_app.xml' NOCOPY TEMPFILE REUSE;
ALTER PLUGGABLE DATABASE pdb_awesome_app OPEN;

If the database version and all options match: Congratulations, you just copied a 40Gb schema in 30 minutes instead of several hours. I personally had some time to write this blog post before rushing to a JUG meeting and than to a customer 700km away.