PDB lifecycle management with ORDS

This post illustrates the PDB lifecycle management with Oracle Rest Data Services (ORDS). ORDS PDB lifecycle management is part of the “Database Management API”, that was released with ORDS 19.1.0.092.1545 (April 2019), and offers nowadays more than 500 REST endpoints to manage PDB lifecycle, performance, monitoring, data loading and data dictionary in your Oracle databases.

In an ORDS installation, “Database Management API” is disabled by default, therefore it needs to be installed and configured before using it. In this post, you will be walked through the installation and configuration steps.

You’ll also see some usage examples of the PDB lifecycle management module, like creating, dropping, cloning, unplugging and plugging a PDB. You’ll find out that these REST API can be easily integrated in a CI/CD pipeline, like for example ANSIBLE. 

The following steps were successfully executed with ORDS 21.4.3.117.0405 and Oracle RDBMS 21.3.

They apply to any version of Oracle RDBMS >= 12.1

Note: If you prefer reading in german language, you can also use the blog posting “Pluggable Database Lifecycle Management mit Oracle REST Data Service” (published here in April 2021) written by my Marcus Schroeder. 

Install ORDS

Unzip ORDS distribution in the ORACLE_HOME. The ZIP file (ords-21.4.3.117.0405.zip) has been previously copied to the server, in /tmp directory.

cd /opt/oracle/product/21c/dbhome_1

cp /tmp/ords-21.4.3.117.0405.zip .

mkdir ords-21.4.3.117.0405

unzip ords-21.4.3.117.0405.zip -d ./ords-21.4.3.117.0405

Now ensure java executable is in the PATH environment variable.

Include $ORACLE_HOME/jdk/bin in the PATH variable, editing /home/oracle/.bashrc.

Check that java is in the PATH:

which java

You should get the fully qualified path to the java executable:

 

Validate that ORDS has not been installed in your database:

cd /opt/oracle/product/21c/dbhome_1/ords-21.4.3.117.0405

java -jar ords.war validate

 

In the case ORDS is already installed, you might remove it with the uninstall command:

java -jar ords.war uninstall

For the PDB lifecycle management functionality, ORDS needs to be installed in the CDB$ROOT and all its PDBs, including PDB$SEED.

Now we will install ORDS using a parameter file and a silent mode installation. Create a /tmp/params.txt file with the following content:

db.connectionType=basic
db.hostname=localhost
db.port=1521
db.servicename=ORCLCDB
sys.user=SYS
sys.password=Oracle_4U
database.api.admin.enabled=true
database.api.enabled=true
database.api.management.services.disabled=false
db.cdb.adminUser=C##DBAPI_CDB_ADMIN as SYSDBA
db.cdb.adminUser.password=Oracle_4U
dbc.auth.enabled=true
debug.debugger=true
debug.printDebugToScreen=true
feature.sdw=true
jdbc.auth.enabled=true
restEnabledSql.active=true
db.adminUser=test_ords
db.adminUser.password=Oracle_4U
db.password=Oracle_4U
db.serviceNameSuffix=
db.username=ORDS_PUBLIC_USER
user.public.password=Oracle_4U
resource.templates.enabled=true
standalone.mode=true
standalone.http.port=8080
standalone.use.https=false
plsql.gateway.add=false
rest.services.ords.add=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP

Then run the install in silent mode:

java -jar ./ords.war install --parameterFile /tmp/params.txt simple

 

If you hit this error, it’s probably because the install process tries to put PDB$SEED back in read-only mode, after having successfully installed ORDS inside. If you look at PDB$SEED, you’ll discover it has been left in read-write mode, and you’ll need to put it back in read-only mode. Operations against the  PDB$SEED must be executed undercover of a hidden parameter, that is more generally used when installing oracle maintained schemas in CDB$ROOT.

Let’s work around the error doing the following: connect to the CDB$ROOT as SYS, and manually open the PDB$SEED in READ ONLY MODE:

sqlplus / as sysdba

SQL> alter session set "_oracle_script" = true;

Session altered.

SQL> ALTER pluggable database PDB$SEED open read only force;

Pluggable database altered.

SQL> exit

Now you can re-launch the install process. It will detect that ORDS has been installed in both CDB$ROOT and PDB$SEED, and will proceed to install it in all the other PDBs inside CDB$ROOT.

 java -jar ./ords.war install --parameterFile /tmp/params.txt simple

 

As a last step, let’s validate the installation:

java -jar ./ords.war validate

 

Configure PDB lifecycle management

Now we can proceed with the configuration steps.

Connect to CDB$ROOT as sysdba, and proceed to create a common user and a test user in the PDB$SEED. The test user in PDB$SEED is meant to be used when you create a new PDB by cloning PDB$SEED:

sqlplus / as sysdba

CREATE USER C##DBAPI_CDB_ADMIN IDENTIFIED BY "Oracle_4U";
GRANT SYSDBA TO C##DBAPI_CDB_ADMIN CONTAINER = ALL;
GRANT DBA TO C##DBAPI_CDB_ADMIN CONTAINER = ALL;
GRANT SYSDBA TO C##DBAPI_CDB_ADMIN CONTAINER = ALL;
GRANT PDB_DBA  TO C##DBAPI_CDB_ADMIN CONTAINER = ALL;

alter session set "_oracle_script" = true;
ALTER pluggable database PDB$SEED open read write force;
alter session set "_oracle_script" = false;

alter session set container=PDB$SEED;
create user test_ords identified by "Oracle_4U";
grant dba to test_ords;
GRANT PDB_DBA TO test_ords;
GRANT INHERIT PRIVILEGES ON USER SYS TO PUBLIC;

BEGIN
    ords.enable_schema(p_enabled => TRUE,p_schema => 'TEST_ORDS',p_url_mapping_type => 'BASE_PATH',p_url_mapping_pattern => 'test_ords', p_auto_rest_auth => FALSE);

    commit;
END;
/

exit

sqlplus / as sysdba

alter session set "_oracle_script" = true;
ALTER pluggable database PDB$SEED open read only force;
alter session set "_oracle_script" = false;
exit

Now let’s configure some users in ORDS.

Generate a cdbAdmin.properties file, and use it to configure the CDB admin user in ORDS:

echo db.cdb.adminUser=C##DBAPI_CDB_ADMIN as SYSDBA > cdbAdmin.properties
echo db.cdb.adminUser.password=Oracle_4U >> cdbAdmin.properties
java -jar ords.war set-properties cdbAdmin.properties 
java -jar ords.war set-properties --conf default cdbAdmin.properties 
java -jar ords.war set-properties --conf apex_rt cdbAdmin.properties 
java -jar ords.war set-properties --conf apex_pu cdbAdmin.properties 
rm  cdbAdmin.properties

Generate a pdbAdmin.properties file, and use it to configure the PDB admin user in ORDS:

echo db.adminUser=test_ords > pdbAdmin.properties
echo db.adminUser.password=Oracle_4U >> pdbAdmin.properties
java -jar ords.war set-properties --conf default pdbAdmin.properties
java -jar ords.war set-properties --conf apex_rt pdbAdmin.properties
java -jar ords.war set-properties --conf apex_pu pdbAdmin.properties
rm pdbAdmin.properties

Configure the network domain, if any. The correct value is given by “show parameter db_domain” executed in CDB$ROOT as sysdba.

If db_domain is null, you can just skip this step.

echo db.serviceNameSuffix=.db_domain
   >snsuffix.properties
java -jar ords.war set-properties --conf apex_rt snsuffix.properties
java -jar ords.war set-properties --conf default snsuffix.properties
java -jar ords.war set-properties --conf apex_pu snsuffix.properties
 

Pay attention to the dot (.) symbol just before the db_domain !!!

Create an admin user in ORDS. This user will be used to authenticate the URL in the REST calls. In my example, admin user will be authenticated by “welcome1”.

java -jar ords.war user admin "SQL Administrator" "System Administrator" "SQL Administrator"

 

Now you are ready to test PDB lifecycle management.

Test PDB lifecycle management

For your commodity, create some short scripts that will start, stop, and get ORDS status.

Move to ORDS home directory:

cd /opt/oracle/product/21c/dbhome_1/ords-21.4.3.117.0405

Create a “start_ords.sh” script with the following content:

#!/bin/bash

export JAVA_HOME=/opt/oracle/product/21c/dbhome_1/jdk
export PATH=/usr/sbin:/usr/local/bin:/usr/bin:/usr/local/sbin:$PATH
LOGFILE=/opt/oracle/product/21c/dbhome_1/ords-21.4.3.117.0405/logs/ords-`date +"%Y""%m""%d"`.log
cd /opt/oracle/product/21c/dbhome_1/ords-21.4.3.117.0405
export JAVA_OPTIONS="-Dorg.eclipse.jetty.server.Request.maxFormContentSize=3000000"
nohup $JAVA_HOME/bin/java ${JAVA_OPTIONS} -jar ords.war standalone >> $LOGFILE 2>&1 &
echo "View log file with : tail -f $LOGFILE"

Create a “stop_ords.sh” script with the following content:

#!/bin/bash

kill `ps -ef | grep ords.war | awk '{print $2}'`

Create a “status_ords.sh” script with the following content:

#!/bin/bash

ps -ef | grep ords.war

Now start ORDS:

chmod +x *_ords.sh

./start_ords.sh

Now let’s test the PDB lifecycle management functionality: in the following command, you might replace “localhost” by the name or the IP of the server where ORDS is running:

First, get the list of the PDBs in your CDB:

curl -ks -X GET --user admin:welcome1 -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/ | python3 -mjson.tool

We can check the status of a particular PDB:

curl -ks -X GET --user admin:welcome1 -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/orclpdb1/ | python3 -mjson.tool

We can also create a new PDB, as a clone of PDB$SEED:

curl -ks -X POST --user admin:welcome1 --data '{
"method": "CREATE",
"adminName": "Admin",
"adminPwd": "welcome1",
"pdb_name": "PDB2",
"asClone": true,
"fileNameConversions": "NONE",
"unlimitedStorage": true,
"reuseTempFile": true,
"totalSize": "10G",
"tempSize": "100M",
"getScript": false
}' -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/  | python3 -mjson.tool

Check that PDB2 has been correctly provisioned in your CDB:

sqlplus / as sysdba
show pdbs

Now, let’s drop the recently created PDB2:

curl -ks -X DELETE --user admin:welcome1 --data '{"action":"INCLUDING"}' -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/PDB2/ | python3 -mjson.tool

And let’s create PDB2 as a clone of an existing PDB (orclpdb1):

curl -ks -X POST --user admin:welcome1 --data '{
  "method": "CLONE",
  "clonePDBName": "pdb2",
  "unlimitedStorage": true,
  "reuseTempFile": true,
  "totalSize": "UNLIMITED",
  "tempSize": "UNLIMITED"
}' -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/orclpdb1/ | python3 -mjson.tool

You can also unplug PDB2, in order to plug it back. To unplug PDB2, you must close it first:

curl -ks -X POST --user admin:welcome1 --data '{
  "state": "CLOSE",
  "modifyOption": "IMMEDIATE"
}' -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/pdb2/status | python3 -mjson.tool

curl -ks -X POST --user admin:welcome1 --data '{
  "method": "UNPLUG",
  "xmlFileName": "/tmp/pdb2.xml"
}' -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/pdb2/ | python3 -mjson.tool

Observe that PDB2 has been removed from your CDB. Nevertheless its datafiles are still there, ready to be plugged back as a new PDB.

sqlplus / as sysdba
show pdbs

Now let’s plug PDB2 back, and change its name to “plugged_pdb”:

curl -ks -X POST --user admin:welcome1 --data '{
  "method": "PLUG",
  "pdb_name": "plugged_pdb",
  "xmlFileName": "/tmp/pdb2.xml",
  "sourceFileNameConversions": "NONE",
  "copyAction": "NOCOPY",
  "fileNameConversions": "NONE",
  "unlimitedStorage": true,
  "reuseTempFile": true,
  "totalSize": "UNLIMITED",
  "tempSize": "UNLIMITED"
}' -H "Content-Type: application/json" -k http://localhost:8080/ords/_/db-api/stable/database/pdbs/ | python3 -mjson.tool

This concludes this tutorial.

For further information, please find below some useful links:

• ORDS documentation: https://www.oracle.com/database/technologies/appdev/rest.html
• ORDS 21.4.3.117.0405 download link: https://download.oracle.com/otn_software/java/ords/ords-21.4.3.117.0405.zip