PDB Lifecycle Management Operations via the ORDS DB API

In previous posts I’ve demonstrated how to enable the Database Management REST API (DBAPI) with ORDS and do things like Data Pump Export and Import via simple HTTPS POST calls.

I’ve also put together some slides for the DB API feature, which you can find here.

But.

What if you’d like to have a system in place where you could quickly clone, create, or drop your Oracle Multitenant Pluggable Databases (PDBs)?

The DBAPI provides access to your PDB Lifecycle operations too!

Before getting started, we have a very hard requirement.

Requirement: Install ORDS to the CDB

You have two installation options for ORDS in a PDB world.

1. ORDS is installed at only the PDB level
2. ORDS puts the ORDS_PUBLIC_USER in the CDB, and ORDS_METADATA goes into the $SEED and each PDB.

If you want to use the PDB endpoints, we have to run those from the Container, so you MUST go with option #2. See CDB Installation Instructions here (Docs).

This has a few noticeable impacts in how you use and maintain ORDS. For upgrades, you’ll be running the same version of ORDS across all of your PDBs.

Addressing REST Services in your PDBs will have the following URI structure:

1. /ords/_/db-api/ for the PDB lifecycle operations – no database or schema name
2. /ords/pdb_name/schema/service|object for your regular REST Services

When I did my install I had 2 PDBs up and running, so the ORDS_METADATA schema was installed into both, and both were available for publishing REST Services, assuming they had REST Enabled Schemas of course.

Installation and Config

When running the installer, I pointed to my Container (CDB) Service, and had all of my PDBs open. I said ‘Yes’ to enabling SQL Developer Web, but this is not enough.

We need to also perform the following steps:

1. enable the DB API itself (the next version of ORDS will support doing this via the Installer)
2. Create a Common User in the CDB used to handle the PDB Lifecycle operations
3. Provide the user/password details to my ORDS config
4. Create a user with the ‘SQL Administrator’ role
5. Restart ORDS and go!

Below is a truncated bash shell log of me walking these steps. Please, please use the official Oracle Docs yourself – don’t rely on just this post for instructions.

Jeffreys-Mini:19.4 thatjeffsmith$ java -jar ords.war install
...
Enter the database service name:orclcdb
...
Retrieving information....
Your database connection is to a CDB.  ORDS common user ORDS_PUBLIC_USER will be created in the CDB.  ORDS schema will be installed in the PDBs.
Root CDB$ROOT - create ORDS common user 
PDB PDB$SEED - install ORDS 19.4.0.r3521226 (mode is READ ONLY, open for READ/WRITE)
PDB ORCL - install ORDS 19.4.0.r3521226 
PDB PDB2 - install ORDS 19.4.0.r3521226 
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:1
2020-04-06T14:16:26.437Z INFO   reloaded pools: []
 
 
Installing Oracle REST Data Services version 19.4.0.r3521226 in CDB$ROOT
... Log file written to /Users/thatjeffsmith/ords_cdb_install_core_CDB_ROOT_2020-04-06_101626_00651.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
Completed installation for Oracle REST Data Services version 19.4.0.r3521226. Elapsed time: 00:00:01.47 
...
Completed CDB installation for Oracle REST Data Services version 19.4.0.r3521226.
Total elapsed time: 00:00:31.36 
 
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
Jeffreys-Mini:19.4 thatjeffsmith$ java -jar ords.war set-property database.api.enabled true
2020-04-06T14:20:12.367Z INFO   Modified: /ords/config/ords/defaults.xml, setting: database.api.enabled = true
Jeffreys-Mini:19.4 thatjeffsmith$ java -jar ords.war set-properties --conf apex_pu cdbAdmin.properties
2020-04-06T14:21:27.010Z INFO   Modified: /ords/config/ords/conf/apex_pu.xml, updated properties: db.cdb.adminUser, db.cdb.adminUser.password
Jeffreys-Mini:19.4 thatjeffsmith$ java -jar ords.war user jeff "SQL Administrator, SQL Developer"
Enter a password for user jeff: 
Confirm password for user jeff: 
2020-04-06T14:23:01.425Z INFO   Created user: jeff in file: /ords/config/ords/credentials
Jeffreys-Mini:19.4 thatjeffsmith$

I’m not showing where I’ve created the credentials file for the C## user to pipe into the ORDS config , or deleting said file when I’m done. Finally, I end by creating a standalone Jetty user for basic auth to make the DB API PDB calls to.

With ORDS running, I can now ask it for a list of my pluggable databases with the following URL:

http://localhost:8080/ords/_/db-api/stable/database/pdbs/

You MUST include your credentials on this call, the browser won’t prompt for them. Also note that you may have hundreds of pluggable databases – so this Service is paged, and you can include query parameters on the URL to filter the responses.

Note that in Database 19c, you can have as many as 3 pluggable databases before you need to officially license the Multitenant Option – so this feature is available to everyone.

What operations are now available?

The Swagger Style doc is available in both the ORDS and Database Docs books. Here’s the link for the Database 20c version (assumes ORDS 19.2 or higher).

Our common user created to run these operations in the CDB has the SYSDBA role, so it can pretty much do anything. I need to say this again, be careful with where you set this up and who you trust with the keys to the castle.

Cloning a PDB

I’m going to follow the doc and ‘Clone or Unplug operations for PDBs.’ It needs an existing PDB for a POST.

From my /pdbs/ end point, I can find the URI for the database I want to clone.

http://localhost:8080/ords/_/db-api/stable/database/pdbs/PDB2/

And my POST body is remarkably simple:

{
  "method": "CLONE",
  "clonePDBName": "PDB2_CLONE",
  "fileNameConversions": "('/u01/app/oracle/oradata/ORCLCDB/pdb2/','/u01/app/oracle/oradata/ORCLCDB/pdb2_clone/')",
  "unlimitedStorage": true,
  "reuseTempFile": true,
  "totalSize": "UNLIMITED",
  "tempSize": "UNLIMITED"
}

The PDB filenames are using a FILE_NAME_CONVERT clause, so I simply provide the source and target directory names. My new PDB will be called PDB2_CLONE, and I don’t care how big it gets.

Let’s run it (this on my 2018 Mac Mini, Oracle 19c running on a VirtualBox VM with 6GB of RAM available, and ORDS 19.4 running standalone on the Mac host. The file I/O takes the longest for this operation, about 18 seconds – your mileage my vary depending on the size of your database and your disk speed.

Cool, so we should have a new database, yes? Let’s poke around in SQL Developer and let’s also try to connect and query in SQLcl.

My new PDB is there, and the schemas from the original PDB came over as expected.

I could also hit the GET on /pdbs/ again and see if my clone is there…hint, it is!

Now let’s nuke it from orbit, just to be safe.

I’m done playing, I’m ready to get rid of this PDB. So let’s drop it. Or to be nice, I’m just going to unplug it, in case I change my mind later.

The link for my new PDB is included in that GET from above, I just need to issue a POST to

http://localhost:8080/ords/_/db-api/stable/database/pdbs/PDB2_CLONE/

And if I go look at the XML manifest created from the unplug…

Let’s review

ORDS gives you the ability to publish a REST API for your database data. It also gives you a management API for the database itself.

PDB Lifecycle operations requires you install ORDS in the Container (CDB).

These things are very easy and very powerful, secure and test appropriately!