8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

Assumptions

This article assumes the following.

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.

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.

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.

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:

Hope this helps. Regards Tim...

Back to the Top.