8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : Database API - PDB Lifecyle Management
The Oracle REST Data Services (ORDS) database API allows us to manage the lifecycle of PDBs via REST web service calls.
- Assumptions
- Enable ORDS Database API (ORDS Version 22.1 Onward)
- Enable ORDS Database API (ORDS Versions 21.4 or lower)
- Database Credentials : PDB Lifecycle Management
- Application Server Credentials
- Basic Usage
Related articles.
- Oracle REST Data Services (ORDS) : Database API - Database Administration
- Oracle REST Data Services (ORDS) : All Articles
Assumptions
This article assumes the following.
- You already have a functioning installation of ORDS, using an application server or standalone mode.
- ORDS must be installed in the root container for PDB Lifecycle Management to work.
- You have an Oracle database available. In this article I will be using a 19c database, but it works just the same for other versions.
- You have a way to call the web services. In these examples we use "curl", but you could use Postman or Insomnia.
Enable ORDS Database API (ORDS Version 22.1 Onward)
If you have followed one of the ORDS installation articles from on this site, you may already have the ORDS Database API enabled.
During the installation the following command line arguments can be used to enable REST Enabled SQL and the ORDS Database API.
--feature-rest-enabled-sql true --feature-db-api true
For an existing installation, the following commands will set these attributes.
export ORDS_HOME=/u01/ords export ORDS_CONFIG=/u01/config/ords export PATH=${ORDS_HOME}/bin:${PATH} ords --config ${ORDS_CONFIG} config set restEnabledSql.active true ords --config ${ORDS_CONFIG} config set database.api.enabled true
Remember to restart ORDS after altering the settings. We are using Tomcat to run ORDS, so we would restart Tomcat to restart ORDS.
$CATALINA_HOME/bin/shutdown.sh $CATALINA_HOME/bin/startup.sh
If you are running ORDS in standalone mode, you will then need to restart ORDS, as described here.
There is an additional level of control using the database.api.management.services.disabled
parameter, which is set to false
by default. Setting this to true
during the installation, or using the above command syntax will disable the following services.
- DBCA Jobs
- DBCA Templates
- Oracle Home Environment
- PDB Lifecycle
- Open Service Broker
Enable ORDS Database API (ORDS Versions 21.4 or lower)
If you have followed one of the ORDS installation articles from on this site, you may already have the ORDS Database API enabled.
During the installation the following properties can be included in the properties file to enable REST Enabled SQL and the ORDS Database API.
restEnabledSql.active=true database.api.enabled=true
For an existing installation, the following commands will set these properties in the "defaults.xml" file.
cd /u01/ords $JAVA_HOME/bin/java -jar ords.war set-property restEnabledSql.active true $JAVA_HOME/bin/java -jar ords.war set-property database.api.enabled true
Remember to restart ORDS after altering the settings. We are using Tomcat to run ORDS, so we would restart Tomcat to restart ORDS.
$CATALINA_HOME/bin/shutdown.sh $CATALINA_HOME/bin/startup.sh
If you are running ORDS in standalone mode, you will then need to restart ORDS, as described here.
There is an additional level of control using the database.api.management.services.disabled
parameter, which is set to false
by default. Setting this to true
during the installation, or using the above command syntax will disable the following services.
- DBCA Jobs
- DBCA Templates
- Oracle Home Environment
- PDB Lifecycle
- Open Service Broker
Database Credentials : PDB Lifecycle Management
For the PDB Lifecycle Management functionality to work we need an ORDS installations in the root container of a Multitenant installation. It will not work if you install ORDS in the PDB.
For the PDB Lifecycle Management services we need a user in the root container with SYSDBA
privileges.
conn / as sysdba create user c##pdb_lifecycle_user identified by PdbLifecycleUserPassword1; grant create session to c##pdb_lifecycle_user container=all; grant sysdba to c##pdb_lifecycle_user container=all;
We need to add these credentials to the ORDS connection pool. For the default connection pool we would do something like the following. We add the credential parameters to a properties file, set the properties for the connection pool, then delete the properties file.
cat > /tmp/pdb_lifecycle_user.properties <<EOF db.cdb.adminUser=c##pdb_lifecycle_user as sysdba db.cdb.adminUser.password=PdbLifecycleUserPassword1 EOF cd /u01/ords $JAVA_HOME/bin/java -jar ords.war set-properties --conf apex_pu /tmp/pdb_lifecycle_user.properties rm /tmp/pdb_lifecycle_user.properties
Remember to restart ORDS after altering the settings.
Application Server Credentials
We need application server credentials mapped to the "SQL Administrator" role to allow us to authenticate to the PDB Lifecycle Management REST endpoints. How you achieve this will vary depending on the way you are running ORDS.
If you are running ORDS under Tomcat, you must add the relevant credentials to the "$CATALINA_BASE/conf/tomcat-users.xml" file. The example below create a new "SQL Administrator" role and maps a user to that role.
<role rolename="SQL Administrator"/> <user username="sql_admin" password="sql_admin_password1" roles="SQL Administrator"/>
You must then restart Tomcat.
$CATALINA_HOME/bin/shutdown.sh $CATALINA_HOME/bin/startup.sh
If you are running in standalone mode, you may wish to use the file-based repository. You can create a similar user using the following command, which will prompt for a password.
cd /u01/ords $JAVA_HOME/bin/java -jar ords.war user sql_admin "SQL Administrator"
You will then need to restart ORDS, as described here.
Basic Usage
The PDB Lifecycle Management REST endpoints are described here. The examples below use the curl
utility to give an idea of their basic usage.
The examples include a few common elements.
-k
: We are using a self-signed certificate, whichcurl
sees as weak. The "-k" options tellscurl
to ignore the weak certificate. This is not necessary if you are using a certificate from a certificate authority.-s
: Run in silent mode, to remove additionalcurl
output.-X
: Define the HTTP method used for the call. These will typically beGET
,POST
,PUT
,DELETE
, but there are others.--user username:password
: Enter the application server credentials defined earlier.| python3 -mjson.tool
: The JSON output is minified, which is hard to read. This Python utility reformats the JSON to make is more readable. You would not use this for real calls.--data-binary
: We use this option to pass a JSON document as a raw payload. This can be inline, or from a file using the "@" symbol.--header "Content-Type:application/json"
: The POST and DELETE HTTP methods require the Content-Type header to be included.
Get All PDBs
We list all pluggable databases in the CDB using a HTTP GET method call to the "_/db-api/stable/database/pdbs/" endpoint.
curl -ks -X GET \ --user sql_admin:sql_admin_password1 \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/ | python3 -mjson.tool { "items": [ { "inst_id": 1, "con_id": 2, "dbid": 1559324454, "con_uid": 1559324454, "guid": "rxFYwrB1TODgVQAAAAAAAQ==", "name": "PDB$SEED", "open_mode": "READ ONLY", "restricted": "NO", "open_time": "2020-09-27T08:56:25.613Z", "create_scn": 2140470, "total_size": 944766976, "block_size": 8192, "recovery_status": "ENABLED", "snapshot_parent_con_id": null, "application_root": "NO", "application_pdb": "NO", "application_seed": "NO", "application_root_con_id": null, "application_root_clone": "NO", "proxy_pdb": "NO", "local_undo": 1, "undo_scn": 280, "undo_timestamp": null, "creation_time": "2020-09-11T21:27:07Z", "diagnostics_size": 0, "pdb_count": 0, "audit_files_size": 0, "max_size": 0, "max_diagnostics_size": 0, "max_audit_size": 0, "last_changed_by": "COMMON USER", "template": "NO", "tenant_id": null, "upgrade_level": 1, "guid_base64": "rxFYwrB1TODgVQAAAAAAAQA=", "links": [ { "rel": "self", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/PDB%24SEED/" } ] }, { "inst_id": 1, "con_id": 3, "dbid": 2277034905, "con_uid": 2277034905, "guid": "rxGDVQlLUpzgVQAAAAAAAQ==", "name": "PDB1", "open_mode": "READ WRITE", "restricted": "NO", "open_time": "2020-09-27T10:40:18.344Z", "create_scn": 2395196, "total_size": 9426698240, "block_size": 8192, "recovery_status": "ENABLED", "snapshot_parent_con_id": null, "application_root": "NO", "application_pdb": "NO", "application_seed": "NO", "application_root_con_id": null, "application_root_clone": "NO", "proxy_pdb": "NO", "local_undo": 1, "undo_scn": 280, "undo_timestamp": null, "creation_time": "2020-09-11T21:39:01Z", "diagnostics_size": 0, "pdb_count": 0, "audit_files_size": 0, "max_size": 0, "max_diagnostics_size": 0, "max_audit_size": 0, "last_changed_by": "COMMON USER", "template": "NO", "tenant_id": null, "upgrade_level": 1, "guid_base64": "rxGDVQlLUpzgVQAAAAAAAQA=", "links": [ { "rel": "self", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/PDB1/" } ] }, { "inst_id": 1, "con_id": 4, "dbid": 3289938731, "con_uid": 3289938731, "guid": "r5Oz/RdPIPLgVQAAAAAAAQ==", "name": "PDB2", "open_mode": "READ WRITE", "restricted": "NO", "open_time": "2020-09-27T08:56:28.790Z", "create_scn": 3955989, "total_size": 986710016, "block_size": 8192, "recovery_status": "ENABLED", "snapshot_parent_con_id": null, "application_root": "NO", "application_pdb": "NO", "application_seed": "NO", "application_root_con_id": null, "application_root_clone": "NO", "proxy_pdb": "NO", "local_undo": 1, "undo_scn": 280, "undo_timestamp": null, "creation_time": "2020-09-18T08:58:23Z", "diagnostics_size": 0, "pdb_count": 0, "audit_files_size": 0, "max_size": 0, "max_diagnostics_size": 0, "max_audit_size": 0, "last_changed_by": "COMMON USER", "template": "NO", "tenant_id": null, "upgrade_level": 1, "guid_base64": "r5Oz/RdPIPLgVQAAAAAAAQA=", "links": [ { "rel": "self", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/PDB2/" } ] } ], "hasMore": false, "limit": 25, "offset": 0, "count": 3, "links": [ { "rel": "self", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/" }, { "rel": "describedby", "href": "https://localhost:8443/ords/_/db-api/stable/metadata-catalog/" }, { "rel": "first", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/" } ] }
Get a PDB
We list details about a specific pluggable database in the CDB using a HTTP GET method call to the "_/db-api/stable/database/pdbs/{pdb-name}/" endpoint. The details include the datafiles.
curl -ks -X GET \ --user sql_admin:sql_admin_password1 \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/ | python3 -mjson.tool { "inst_id": 1, "con_id": 3, "dbid": 2277034905, "con_uid": 2277034905, "guid": "rxGDVQlLUpzgVQAAAAAAAQ==", "name": "PDB1", "open_mode": "READ WRITE", "restricted": "NO", "open_time": "2020-09-27T10:40:18.344Z", "create_scn": 2395196, "total_size": 9426698240, "block_size": 8192, "recovery_status": "ENABLED", "snapshot_parent_con_id": null, "application_root": "NO", "application_pdb": "NO", "application_seed": "NO", "application_root_con_id": null, "application_root_clone": "NO", "proxy_pdb": "NO", "local_undo": 1, "undo_scn": 280, "undo_timestamp": null, "creation_time": "2020-09-11T21:39:01Z", "diagnostics_size": 0, "pdb_count": 0, "audit_files_size": 0, "max_size": 0, "max_diagnostics_size": 0, "max_audit_size": 0, "last_changed_by": "COMMON USER", "template": "NO", "tenant_id": null, "upgrade_level": 1, "guid_base64": "rxGDVQlLUpzgVQAAAAAAAQA=", "data_files": [ { "inst_id": 1, "file#": 9, "creation_change#": 2395196, "creation_time": "2020-09-11T21:39:06Z", "ts#": 0, "rfile#": 1, "status": "SYSTEM", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 440401920, "blocks": 53760, "create_bytes": 314572800, "block_size": 8192, "name": "/u02/oradata/CDB1/pdb1/system01.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 10, "creation_change#": 2395204, "creation_time": "2020-09-11T21:39:06Z", "ts#": 1, "rfile#": 4, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 471859200, "blocks": 57600, "create_bytes": 419430400, "block_size": 8192, "name": "/u02/oradata/CDB1/pdb1/sysaux01.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 11, "creation_change#": 2395222, "creation_time": "2020-09-11T21:39:06Z", "ts#": 2, "rfile#": 9, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 613416960, "blocks": 74880, "create_bytes": 173015040, "block_size": 8192, "name": "/u02/oradata/CDB1/pdb1/undotbs01.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 12, "creation_change#": 2397411, "creation_time": "2020-09-11T21:39:13Z", "ts#": 5, "rfile#": 12, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 5242880, "blocks": 640, "create_bytes": 5242880, "block_size": 8192, "name": "/u02/oradata/CDB1/pdb1/users01.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 13, "creation_change#": 2397490, "creation_time": "2020-09-11T21:39:18Z", "ts#": 6, "rfile#": 13, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 247463936, "blocks": 30208, "create_bytes": 1048576, "block_size": 8192, "name": "/u02/oradata/CDB1/AF118355094B529CE055000000000001/datafile/o1_mf_apex_hoqvn6v1_.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 14, "creation_change#": 2817107, "creation_time": "2020-09-13T15:31:20Z", "ts#": 7, "rfile#": 1024, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 2704277504, "blocks": 330112, "create_bytes": 2469396480, "block_size": 8192, "name": "/u02/oradata/CDB1/AF118355094B529CE055000000000001/datafile/o1_mf_soe_howgswlc_.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 15, "creation_change#": 3894810, "creation_time": "2020-09-13T16:03:03Z", "ts#": 8, "rfile#": 1024, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 1677721600, "blocks": 204800, "create_bytes": 1073741824, "block_size": 8192, "name": "/u02/oradata/CDB1/AF118355094B529CE055000000000001/datafile/o1_mf_tpcdslik_howjom19_.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 }, { "inst_id": 1, "file#": 16, "creation_change#": 3894875, "creation_time": "2020-09-13T16:03:06Z", "ts#": 9, "rfile#": 1024, "status": "ONLINE", "enabled": "READ WRITE", "checkpoint_change#": 4579704, "checkpoint_time": "2020-09-27T13:00:34Z", "unrecoverable_change#": 0, "unrecoverable_time": null, "last_change#": null, "last_time": null, "offline_change#": 4537177, "online_change#": 4537221, "online_time": "2020-09-27T10:40:18Z", "bytes": 1479540736, "blocks": 180608, "create_bytes": 472907776, "block_size": 8192, "name": "/u02/oradata/CDB1/AF118355094B529CE055000000000001/datafile/o1_mf_tpcdslik_howjorxn_.dbf", "plugged_in": 0, "block1_offset": 8192, "aux_name": "NONE", "first_nonlogged_scn": 0, "first_nonlogged_time": null, "foreign_dbid": 0, "foreign_creation_change#": 0, "foreign_creation_time": null, "plugged_readonly": "NO", "plugin_change#": 0, "plugin_resetlogs_change#": 0, "plugin_resetlogs_time": null, "con_id": 3 } ], "links": [ { "rel": "self", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/" }, { "rel": "describedby", "href": "https://localhost:8443/ords/_/db-api/stable/metadata-catalog/" }, { "rel": "collection", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/" }, { "rel": "status", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/status" } ] }
Get the Status of a PDB
We get the status of a specific pluggable database in the CDB using a HTTP GET method call to the "_/db-api/stable/database/pdbs/{pdb-name}/status" endpoint.
curl -ks -X GET \ --user sql_admin:sql_admin_password1 \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/status | python3 -mjson.tool { "inst_id": 1, "con_id": 3, "name": "PDB1", "open_mode": "READ WRITE", "restricted": "NO", "links": [ { "rel": "collection", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/" } ] }
Change the Status of a PDB
We change the status of a specific pluggable database in the CDB using a HTTP POST method call to the "_/db-api/stable/database/pdbs/{pdb-name}/" endpoint.
We need a JSON payload describing the status change.
cat > /tmp/payload.json <<EOF { "state": "CLOSE", "modifyOption": "IMMEDIATE" } EOF
We then make the POST call for the PDB1 pluggable database passing the payload.
curl -ks -X POST \ --user sql_admin:sql_admin_password1 \ --data-binary @/tmp/payload.json \ --header "Content-Type:application/json" \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/status | python3 -mjson.tool { "env": { "defaultTimeZone": "UTC" }, "items": [ { "statementId": 1, "response": [ "\nPluggable database \"pdb1\" altered.\n\n" ], "result": 0 } ] }
We check the status of the PDB1 pluggable database.
curl -ks -X GET \ --user sql_admin:sql_admin_password1 \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/status | python3 -mjson.tool { "inst_id": 1, "con_id": 3, "name": "PDB1", "open_mode": "MOUNTED", "restricted": null, "links": [ { "rel": "collection", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/" } ] }
We create a new JSON payload to change the status of the PDB1 pluggable database back.
cat > /tmp/payload.json <<EOF { "state": "OPEN", "modifyOption": "NORMAL" } EOF
We make the call again, passing the new payload.
curl -ks -X POST \ --user sql_admin:sql_admin_password1 \ --data-binary @/tmp/payload.json \ --header "Content-Type:application/json" \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/status | python3 -mjson.tool { "env": { "defaultTimeZone": "UTC" }, "items": [ { "statementId": 1, "response": [ "\nPluggable database \"pdb1\" altered.\n\n" ], "result": 0 } ] }
We check the status of the PBD1 pluggable database.
curl -ks -X GET \ --user sql_admin:sql_admin_password1 \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/status | python3 -mjson.tool { "inst_id": 1, "con_id": 3, "name": "PDB1", "open_mode": "READ WRITE", "restricted": "NO", "links": [ { "rel": "collection", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb1/" } ] }
Create a PDB
We create a new PDB from the seed database using a HTTP POST method call to the "_/db-api/stable/database/pdbs/" endpoint. The payload describes the action we want to perform. In this case we are creating a new pluggable database called PDB3. We are using Oracle Managed Files (OMF), so we don't need to include file name conversions.
cat > /tmp/payload.json <<EOF { "method": "CREATE", "pdb_name": "pdb3", "adminName": "pdbadmin", "adminPwd": "MyPassword1", "fileNameConversions": "NONE", "unlimitedStorage": true, "reuseTempFile": true, "totalSize": "UNLIMITED", "tempSize": "UNLIMITED" } EOF
We make the post call, passing the JSON payload.
curl -ks -X POST \ --user sql_admin:sql_admin_password1 \ --data-binary @/tmp/payload.json \ --header "Content-Type:application/json" \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/ | python3 -mjson.tool { "env": { "defaultTimeZone": "UTC" }, "items": [ { "statementId": 1, "response": [ "\nPL/SQL procedure successfully completed.\n\n" ], "result": 0 }, { "statementId": 2, "response": [ "\nPluggable database \"pdb3\" altered.\n\n" ], "result": 0 }, { "statementId": 3, "response": [], "result": 0 } ] }
We check the status of the new PDB3 pluggable database.
curl -ks -X GET \ --user sql_admin:sql_admin_password1 \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb3/status | python3 -mjson.tool { "inst_id": 1, "con_id": 5, "name": "PDB3", "open_mode": "READ WRITE", "restricted": "NO", "links": [ { "rel": "collection", "href": "https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb3/" } ] }
Drop a PDB
We drop a specific pluggable database in the CDB using a HTTP DELETE method call to the "_/db-api/stable/database/pdbs/{pdb-name}/" endpoint.
We create a payload describing the action. By default the drop will attempt to keep the datafiles. We use an action of INCLUDING
to make sure the datafiles are dropped.
cat > /tmp/payload.json <<EOF { "action": "INCLUDING" } EOF
We now make the DELETE
call passing the payload.
curl -ks -X DELETE \ --user sql_admin:sql_admin_password1 \ --data-binary @/tmp/payload.json \ --header "Content-Type:application/json" \ https://localhost:8443/ords/_/db-api/stable/database/pdbs/pdb3/ | python3 -mjson.tool { "env": { "defaultTimeZone": "UTC" }, "items": [ { "statementId": 1, "response": [ "\nPL/SQL procedure successfully completed.\n\n" ], "result": 0 } ] }
For more information see:
- Enabling ORDS Database API
- About the REST APIs
- Pluggable Database Lifecycle Management REST Endpoints
- Oracle REST Data Services (ORDS) : Database API - Database Administration
- Oracle REST Data Services (ORDS) : All Articles
Hope this helps. Regards Tim...