Based on user feedback the ORDS (Oracle REST Data Sources) team overhauled a large chunk of the Pluggable Database Lifecycle Management REST API. The process of creating Pluggable Databases (PDBs) from the seed PDB, as well as hot-cloning PDBs (including those encrypted using Transparent Data Encryption, TDE) in particular saw a redesign.
The previously used endpoint is still available, however you now have a new one at your disposal. Only the latter can be used to perform hot clones of PDBs with TDE enabled.
Please show me how it works!
Let’s walk through an example. I am assuming that your setup adheres to the prerequisites listed in the API overview page:
- ORDS 24.1.1 or later installed and configured at Container Database (CDB) level
- Since I’m going to clone a PDB with TDE enabled ORDS and RDBMS must be co-located on the same host
db.cdb.adminUsercredentials are set in the pool configuration- Any client requires the SQL Administrator role to invoke this service
I installed ORDS against an Oracle Database Free 23ai database in the same virtual machine to comply with the 2nd pre-requisite.
Quick test
I typically perform a quick test first after installing ORDS and configuring PDB Lifecycle Management. The easiest one I can think of is listing all PDBs from the CDB:
$ curl --silent --user devops:"${API_PASSWORD}" \
https://my-ords-server:8443/ords/_/db-api/stable/database/pdbs/ | \
jq -r .items[].name
PDB$SEED
FREEPDB1
In the above example the devops user has been granted the SQL Administrator role. The password is provided as an environment variable in bash. I typically use jq on Linux to parse JSON results, but that’s just my preference, there are other tools that do the same job.
If you see at least those 2 PDBs in the output, you’re good to go!
Scheduler-based PDB cloning
The big change in ORDS 24.1 relates to the way the PDB cloning operation is performed. As soon as you invoke the REST endpoint a scheduler job is created and the prompt returns. You can check the job status (also via REST of course) to learn more about the progress and status.
Let’s clone FREEPDB1. Before doing that let’s check the TDE status of the CDB:
SQLcl: Release 23.4 Production on Mon May 13 12:18:02 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> select
2 wrl_parameter,
3 status,
4 wallet_type,
5 keystore_mode,
6 con_id
7 from
8* v$encryption_wallet;
WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE_MODE CON_ID
__________________________________________ _________ ______________ ________________ _________
/opt/oracle/admin/FREE/wallet_root/tde/ OPEN AUTOLOGIN NONE 1
OPEN AUTOLOGIN UNITED 2
OPEN AUTOLOGIN UNITED 3
As you can see, TDE is configured for all PDBs with an auto-login wallet using united mode. Time to clone the PDB! The payload is documented in the API documentation. I set db_create_file_dest in my CDB so I don’t need to worry about any file name conversions
curl --silent \
--user "devops:${API_PASSWORD}" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"new_pdb_name": "clone1",
"source_pdb_name": "freepdb1",
"keystore_password": "'"${KEYSTORE_PWD}"'",
"temp_file_reuse": true
}' \
http://my-ords-server:8443/ords/_/db-api/stable/database/pdbs/ | jq .
Note: be careful with shell interpolation, it’s easy to get it wrong.
As soon as the REST endpoint is invoked, ORDS provides a summary in JSON format. Thanks to jq it is pretty-printed and formatted for readability. The key field is the job name:
{
"owner": "SYS",
"job_name": "DBAPI_V8J2C54SO9WUZRDZ20240513102945",
"job_subname": null,
"instance_id": null,
"destination_owner": null,
...
Using this job name you can query the job status. Again, there is a lot of detail available, however all I care about is the status:
curl --silent --user "devops:${API_PASSWORD}" \
https://my-ords-server:8443/ords/_/db-api/stable/database/scheduler/jobs/SYS,DBAPI_V8J2C54SO9WUZRDZ20240513102945 | \
jq -r .status
SUCCEEDED
The ords server process emits a line with the jobLink each time the API invoked. I grabbed the URL from there and simply pasted it into the console.
The alert.log contains the details of the command issued, and the output, as you would expect:
2024-05-13 10:42:12.601000 +00:00
CREATE PLUGGABLE DATABASE clone1
FROM freepdb1
TEMPFILE REUSE
KEYSTORE IDENTIFIED BY *
AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
KZTDE: TDE keystore backup was taken : /opt/oracle/admin/FREE/wallet_root/tde/ewallet_2024051310421282.p12
2024-05-13 10:42:14.036000 +00:00
****************************************************************
Pluggable Database CLONE1 with pdb id - 5 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000130
****************************************************************
...
****************************************************************
Post plug operations are now complete.
Pluggable database CLONE1 with pdb id - 5 is now marked as NEW.
****************************************************************
2024-05-13 10:42:16.795000 +00:00
Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database CLONE1 opened read write
Completed: ALTER PLUGGABLE DATABASE clone1 OPEN READ WRITE
Summary
ORDS 24.1.1 introduces a hot-clone process for PDBs. Why is this a big deal for me? I am a great fan of modern software development principles. A DevOps culture is essential to releasing changes more frequently, with higher confidence.
Continuous Integration (CI) pipelines allow developers to run a multitude of automated tests, and they typically need a test environment for their backend code. Cloning a CI database using REST APIs is a great way to integrate database testing into the pipeline. As a result, the probability of issues with the release should be further reduced.
Martins Blog 