Pluggable Database (PDB) Lifecycle Management can be used to great effect in Continuous Integration/Continuous {Delivery,Deployment} pipelines. This post describes how to set up PDB Lifecycle Management with Oracle REST Data Services (ORDS) 23.3.0, the current version at the time of writing. The procedure in this article has been validated against Oracle Database 19c and Oracle Database 23ai Free running on Oracle Linux 8. The article assumes that a Container Database (CDB) is up and running.
UPDATE 240412 The actual implementation used behind the scenes to clone PDBs has changed with ORDS 24.1.0. More details, especially for cloning PDBs with Transparent Data Encryption (TDE) enabled, can be found in another post. The commands presented later in this article are still valid, the invocation of the clone command hasn’t changed.
UPDATE 241212 Instead of a local install, you can use ORDS in a container. The commands you’ll read about remain the same, however ORDS is provided as a container image. Here’s an example how to use the new container image.
By the way, these are by far not the only REST endpoints for Oracle Database, the full list is available in the documentation.
Configure Lifecycle Management
I chose to deploy ORDS separately from the database. Nothing prevents you from running ORDS on the database host, the advice on the best way to run ORDS with Oracle database however is out of the scope of this article.
If you would like to deploy PDB lifecycle management as well make sure to check with the admins and security team about the preferred setup. It may be different to this one! Also, make sure you are license compliant with all software referred to in this article before installing any of it!
Operating system setup
The first step is to make sure the environment is set up and ready for installation. To simplify things you can store environment variables pointing to the installation directories used later in a file. setenv.sh contains the following lines:
export ORDS_HOME=/opt/oracle/ords/23.3.0
export ORDS_CONFIG=/opt/oracle/ords/config
export ORDS_LOGS=/opt/oracle/ords/logs
export JAVA_HOME=/opt/oracle/jdk
export PATH=${JAVA_HOME}/bin:${ORDS_HOME}/bin:${PATH}
After source’ing the file into your current session the environment variables should be visible:
for dir in ${ORDS_HOME} ${ORDS_CONFIG} ${ORDS_LOGS} ${JAVA_HOME}
do
echo "- ${dir}"
done
Make sure the directories exist before you move to the next step:
for dir in ${ORDS_HOME} ${ORDS_CONFIG} ${ORDS_LOGS} ${JAVA_HOME}
do
mkdir -vp ${dir} || echo "ERR: could not create ${dir}"
done
Staging and deploying the software
With the directories created, it’s time to download the software. You will need an Oracle Java Runtime Environment, however, I prefer to install the JDK 17. You will obviously need ORDS 23.3.0 as well. Stage both files in a convenient location, then install the JDK using your favourite method. The JDK’s java binary must be in your path before continuing.
Unzip ORDS to ${ORDS_HOME} as follows
unzip \
-q /path/to/staged/software/ords-23.3.0.289.1830.zip \
-d ${ORDS_HOME}
Silent installation
Rather than performing an interactive installation, a silent installation is performed. To do so, you use input redirection for the database passwords. Save the necessary database password(s) in a text file in a secure, temporary location, then proceed with the silent installation. Adjust connection details to suit your needs. Don’t forget to remove the password file!
${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install \
--admin-user SYS \
--db-hostname dbhost \
--db-port 1521 \
--db-servicename ORCLPDB \
--feature-sdw true \
--log-folder ${ORDS_LOGS} \
--password-stdin < /path/to/file/containing/sys/pwd \
--proxy-user
rm /path/to/file/containing/sys/pwd
Review the documentation for more details about a silent installation.
Create the DBAPI_CDB_ADMIN account in the database
PDB lifecycle management requires a SYSDBA account in the CDB$ROOT to perform lifecycle tasks on ORDS’s behalf. Connect to CDB$ROOT as sysdba and create the user ORDS will use to perform PDB lifecycle management tasks. Make sure to use a secure password!
create user C##DBAPI_CDB_ADMIN identified by &password;
grant sysdba to C##DBAPI_CDB_ADMIN container=all;
Configure lifecycle management in ORDS
As soon as the admin account is created in the CDB$ROOT you can tell ORDS about it. The following 2 configuration directives are documented in table C-3 of the (ORDS) Installation and Configuration Guide. The code examples use output redirection again, store the password you assigned to C##DBAPI_CDB_ADMIN in a secure location, then run the configuration commands. Don’t forget to remove the file containing the password!
${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} \
config set db.cdb.adminUser "C##DBAPI_CDB_ADMIN as SYSDBA"
${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} \
config secret --password-stdin \
db.cdb.adminUser.password < /path/to/file/containing/dbapiadmin/pwd
rm /path/to/file/containing/dbapiadmin/pwd
Before you can access PDB lifecycle management APIs you need to secure the endpoint. The following example shows how to create a user called devops with its password stored temporarily in a secure location.
${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} \
config user add --password-stdin \
devops "SQL Administrator,SQL Developer" \
< /path/to/user/devops/user/pwd
rm /path/to/user/devops/user/pwd
The final step is to set the service name suffix (aka db_domain). This particular database doesn’t use one, the service name suffix can be set to the empty string:
${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} \
config set --global db.serviceNameSuffix ""
That should be it! Now start ORDS in your preferred way, in this scenario ORDS is started stand-alone using the HTTPS protocol.
Testing
The most basic test is to list all the PDBs in the CDB (${password} contains the password you chose for the account in the earlier step)
curl -su devops:${password} \
https://localhost:8443/ords/_/db-api/stable/database/pdbs/ | python3 -m json.tool
If this returns a lovely JSON document, you’re all done.
Cloning a PDB
The following example shows how to clone a PDB. Oracle Managed Files (OMF) has been enabled in the CDB, removing the need to provide file name conversion parameters in the JSON file to be passed to ORDS. In this example, the total size of the PDB is limited to 5 GB, with 1 GB allocated for the temp file
{
"method": "CLONE",
"clonePDBName": "TESTPDB1",
"unlimitedStorage": false,
"reuseTempFile": true,
"totalSize": "5G",
"tempSize": "1G"
}
Save the payload as /tmp/clone.json for example, then call the CLONE REST API to clone the existing “SRCPDB” as follows:
curl -iv -X POST -u devops:${password} \
-d @/tmp/clone.json \
-H "Content-Type:application/json" https://localhost:8443/ords/_/db-api/stable/database/pdbs/srcpdb/
The database’s alert.log will show you that the PDB has been created:
Completed: CREATE PLUGGABLE DATABASE "TESTPDB1" FROM "ORCLPDB1"
STORAGE ( MAXSIZE 5G MAX_SHARED_TEMP_SIZE 1G )
TEMPFILE REUSE
FILE_NAME_CONVERT=NONE
Summary
PDB lifecycle management is a great asset for developers of CI/CD pipelines. During the integration part of your pipeline’s execution, you can clone your CI database quickly and effectively, run your unit tests, and dispose of the PDB at the end of your pipeline run. Please pay attention to the maximum number of PDBs you create if your system is not licensed for the Multitenant option.
Martins Blog 