Using the Multitenant Architecture with Oracle REST Data Services

This section outlines installing, configuring, upgrading and uninstalling Oracle REST Data Services in a multitenant container database.

Once ORDS metadata is saved in the database, it is necessary to provide a runtime configuration setting for ORDS to efficiently map requests to the corresponding pluggable databases.

Setting Up ORDS in a CDB Environment

This section describes how to setup Oracle REST Data Services (ORDS) into a multitenant container database (CDB) environment.

Oracle Database 12c Release 1 ( 12.1) introduced the multitenant architecture. This database architecture has a multitenant container database (CDB) that includes a root container, CDB$ROOT, a seed database, PDB$SEED, and multiple pluggable databases (PDBs). A PDB appears to users and applications as if it were a non-CDB. Each PDB is equivalent to a separate database instance in Oracle Database Release 11g.

The root container, CDB$ROOT, holds common objects that are accessible to every PDB utilizing metadata links or object links. The seed database, PDB$SEED, is used when you create a new PDB to seed the new pluggable database. The key benefit of the Oracle Database 12c multitenant architecture is that the database resources, such as CPU and memory, can be shared across all of the PDBs. This architecture also enables many databases to be treated as one for tasks such as upgrades or patches, and backups.

The installation process when you have multiple releases is described in the following section:

Preinstallation Tasks for Oracle REST Data Services CDB Installation

Installation Enabling Multiple Releases

This section describes the installation process when you have multiple releases of Oracle REST Data Services and patch sets in the PDBs in a multitenant environment.

When Oracle REST Data Services is installed into a CDB, the proxy user, Oracle REST Data Services public user (ORDS_PUBLIC_USER) is installed in the root container and is a common user. The ORDS_METADATA schema is a local user that contains the metadata for Oracle REST Data Services. Both the ORDS_METADATA schema and the ORDS_PUBLIC_USER are installed in the seed container (PDB$SEED) and all of the pluggable databases.

Since the ORDS_METADATA is installed as a local user, this provides you the flexibility of installing multiple Oracle REST Data Services releases in the pluggable databases.

Command Line Installation

You must provide the SYS AS SYSDBA credentials in the Root (CDB$ROOT) container to perform the installation.

Interactive Installation

This section describes the installation prompts for installing Oracle REST Data Services into a CDB to enable multiple Oracle REST Data Services releases.

To install Oracle REST Data Services into a CDB to enable multiple Oracle REST Data Services releases, perform the following steps:

Enter the following command:

ords --config <CONFIG_FOLDER> install

You are prompted for necessary information to create or update the ORDS configuration files, install ORDS schema, ORDS database user, and related database objects in the database. When you are prompted for the connection information, ensure that you specify the connection information to your CDB.

Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]:
  Enter the database host name [localhost]: myhost
  Enter the database listen port [1521]:    1523
  Enter the database service name [orcl]:   mycdb

Note: To use the pluggable mapping feature, see Making All PDBs Addressable by Oracle REST Data Services (Pluggable Mapping) for more information.

See Also: Creating an Additional Database Pool

Silent Installation

To perform a silent installation, provide the options on the command-line. Ensure that you specify the connection information to your CDB.

Enter the following command:

ords --config <CONFIG_FOLDER> install <OPTIONS>

See Also: Non-Interactive Command-Line Interface Installation (Silent)

Upgrading Oracle REST Data Services in a CDB Environment

When you use the latest release of Oracle REST Data Services (ORDS), and ORDS already exists in your database from an earlier ORDS release, then you can upgrade the ORDS schema in the CDB and its pluggable databases (PDBs) using the install command.

For example:

ords install --interactive --log-folder <LOG_FOLDER>

Note: If you are using the ORDS interactive install command to upgrade ORDS in the CDB, and if ORDS is already installed or upgraded to the current release, then you are not prompted for the information.

Migrating Oracle REST Data Services in the CDB to Enable Multiple Releases

This section describes how to migrate Oracle REST Data Services in the CDB to enable multiple releases.

Starting with release 18.2.0 and later, if you have an Oracle REST Data Services schema and ORDS_METADATA that is installed in the CDB$ROOT container, then during upgrade it will migrate the common ORDS_METADATA schema to your PDBs as a local schema. Oracle database 12.1.0.2 and later releases support this change.

Uninstalling Oracle REST Data Services in a CDB Environment

To uninstall Oracle REST Data Services from a CDB, use the uninstall command.

For example:

ords uninstall --interactive --log-folder <LOG_FOLDER>

Oracle REST Data Services will be removed from the CDB and its pluggable databases (PDBs).

Setting Up ORDS in an Application Container

This section describes how to setup Oracle REST Data Services in an application container.

Starting with ORDS release 20.2.1, Oracle REST Data Services can be installed or upgraded into an application container using the ORDS SQL scripts provided in the ords.version.number.zip file.

An application container consists of an application root where the application is defined and one or more PDBs that share the metadata about the application from the application root. You can have multiple application containers within a CDB and each container can have different versions of Oracle REST Data Services. Installing or upgrading Oracle REST Data Services in an application container is done against the application root container. When an application PDB wants to use the upgraded version, it must synchronize with the application root. Oracle REST Data Services continues to run in the application PDB with the existing version until the application PDB synchronizes with the application root.

Topics:

Prerequisites for Creating ORDS in an Application Container

This section describes the prerequisites for installing ORDS in an application container.

Following prerequisites must be met before you install ORDS in an application container:

Creating an Application Root Container

This section describes how to create an application root container.

To create an application root container: 1. Ensure that the current container is in CDB$ROOT.

  1. Use the AS APPLICATION CONTAINER clause of the CREATE PLUGGABLE DATABASE statement to create an application container.

  2. Open the application container.

Example:

CREATE PLUGGABLE DATABASE ords_app_root1 AS APPLICATION CONTAINER ADMIN USER admin IDENTIFIED BY <admin_password>

FILE_NAME_CONVERT=('pdbseed', 'ords_app_root1');

ALTER PLUGGABLE DATABASE ords_app_root1 OPEN;

  <div class="infoboxnote" markdown="1">
  **Note:**

  and the

  user in the preceding example can be any valid Oracle identifier.

  If Oracle managed files is enabled in the CDB or the `PDB_FILE_NAME_CONVERT` initialization parameter is set, then omit the `FILE_NAME_CONVERT` clause.

  The ORDS users, namely `ORDS_PUBLIC_USER` and `ORDS_METADATA`, must not exist in the seed (for example, `pdbseed`) or cloned pdb.

  </div>  **See Also:**  [Creating an Application Container](/pls/topic/lookup?ctx=en/database/oracle/oracle-rest-data-services/26.3/ordig&id=MULTI-GUID-20B0B12F-A6A5-4403-95FF-64B3A5CC00A9)

Installing ORDS in the Application Root Container

This section describes how to install ORDS in the application root container.

To install ORDS in the application root container, perform the following steps:

  1. Connect to the application root container.

  2. Run /path/to/scripts/install/core/ords_app_con_install.sql command using the following parameters:

    • Log folder (must include the forward slash at the end)

    • Default tablespace for ORDS schema

    • Temporary tablespace for ORDS schema

    • Default tablespace for ORDS proxy user

    • Temporary tablespace for ORDS proxy user

    • ORDS proxy user password

    • Scripts path (requires the fully qualified path to the ORDS scripts) Note: The tablespaces must already exist in the database.

ALTER SESSION SET CONTAINER = ords_app_root1;

@/path/to/scripts/install/core/ords_app_con_install.sql /path/to/logs/ SYSAUX TEMP SYSAUX TEMP <PASSWORD> /path/to/scripts

Where:

The ords_app_con_install.sql creates an application named ORDS and assigns the application version to the ORDS product version. The product version format is Year.Quarter.Patch.rJulianDay24HRMM (for example, 20.2.1.r2121800).

The preceding script installs ORDS and creates the following:

See Also: Verifying ORDS in the Application Container

Creating an Application Seed

This section describes how to create an application seed.

An application seed is used to provision application PDBs with the application root’s applications pre-installed.

To create an application seed: 1. Ensure that the current container is in the CDB$ROOT.

  1. Alter session and set container to the application root.

  2. Use the AS SEED clause of the CREATE PLUGGABLE DATABASE statement to create an application seed.

  3. Sync the ORDS application with the application seed.

  4. Compile invalid objects.

  5. Open the application seed in a read only mode. Note: ords_app_root1 and the admin user in the following example can be any valid Oracle identifier. If Oracle managed files is enabled in the CDB or the PDB_FILE_NAME_CONVERT initialization parameter is set, then omit the FILE_NAME_CONVERT clause.

ALTER SESSION SET CONTAINER = ords_app_root1;
CREATE PLUGGABLE DATABASE AS SEED ADMIN USER admin IDENTIFIED BY <admin_password>
FILE_NAME_CONVERT=('pdbseed', 'ords_app_root1_seed');
ALTER PLUGGABLE DATABASE ords_app_root1$seed open;
ALTER SESSION SET CONTAINER = ords_app_root1$seed;
ALTER PLUGGABLE DATABASE application ORDS sync;
begin
  sys.dbms_utility.compile_schema('ORDS_METADATA', FALSE);
end;
/
ALTER PLUGGABLE DATABASE ords_app_root1$seed close immediate;
ALTER PLUGGABLE DATABASE ords_app_root1$seed open read only;

See Also: Creating an Application Container

Creating an Application PDB from the Application Seed

This section describes how to create an application PDB that is seeded from the application seed

An application PDB is created by issuing the CREATE PLUGGABLE DATABASE statement from the application root.

To create an application PDB from the application seed: 1. Ensure that the current container is in CDB$ROOT.

  1. Alter session and set the container to the application root.

  2. Use the CREATE PLUGGABLE DATABASE command to create a PDB from the application seed. Note: ords_app_pdb1 and the admin user in the following example can be any valid Oracle identifier. If Oracle managed files is enabled in the CDB or the PDB_FILE_NAME_CONVERT initialization parameter is set, then omit the FILE_NAME_CONVERT clause.

ALTER SESSION SET CONTAINER=ords_app_root1;
CREATE PLUGGABLE DATABASE ords_app_pdb1 ADMIN USER admin IDENTIFIED BY <admin password>
FILE_NAME_CONVERT=('ords_app_root1_seed','ords_app_pdb1');
ALTER PLUGGABLE DATABASE ords_app_pdb1 OPEN;
ALTER SESSION SET CONTAINER = ords_app_pdb1;
select app_name, app_version, app_status from dba_applications where app_name = 'ORDS';
APP_NAME     APP_VERSION         APP_STATUS
--------     ---------------     ------------
ORDS
20.2.1.r2121800     NORMAL

See Also: Creating an Application Container

Configuring the PLSQL Gateway for APEX

This section describes how to configure the PL/SQL Gateway to work with APEX, if required. The APEX_PUBLIC_USER must be configured to proxy to the ORDS_PUBLIC_USER to support ORDS RESTful Services from within APEX.

To configure the PL/SQL Gateway:

Using ORDS CLI command, add the additional setting:

ords --config <configuration folder> config set plsql.gateway.mode proxied

ORDS Configuration Files Setup

This section describes how to setup the ORDS configuration files:

Topics:

Specifying the ORDS Configuration Folder

This section describes how to specify the ORDS configuration folder.

You must provide your configuration folder prior to creating your configuration. Refer to Setting Up the Configuration Folder Structure section for setting-up the folder structure.

Creating the ORDS Configuration Files for the Application Root Container

This section describes how to create the ORDS configuration files for the application root container.

To create the ORDS configuration files for the application root container, execute the following install command to create the configuration files:

ords --config <configuration folder> install --interactive --config-only --log-folder <Log Folder>

Where the --config-only option must be specified to create the configuration files. There are a list of options displaying the current values, you can change the values. When this list of options are prompted for the service name, specify the application root servicename.

Example 8-1 Creating ORDS Configuration

You are prompted for the necessary information to create a database pool in your ORDS configuration files. When the multiple options prompts are displayed, you must specify the same password that you used for the proxy user, ORDS_PUBLIC_USER.

  <div class="infoboxnote" markdown="1">

  **See Also:**
  - [Installing ORDS in the Application Root Container](using-multitenant-architecture-oracle-rest-data-services.html#GUID-594F4ACD-9063-4FE9-95F6-65A328D4BEEA "This section describes how to install ORDS in the application root container.")
  - [Multiple Options for Database Pool and Standalone Configuration](installing-and-configuring-oracle-rest-data-services.html#GUID-5202CE16-4740-4C43-AA63-55DA464C548B "This section explains how to configure Oracle REST Data Services (ORDS) and run in a standalone mode.")
  </div>

      **Note:**

If Oracle APEX is installed in your database, then specify the APEX images location.

Example:

APEX static resources location: /path/to/apex/images.

Oracle recommends to use HTTPS protocol instead of HTTP.

Example 8-2 Using a Basic Connection

Oracle REST Data Services - Interactive Install
  Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]:
  Enter the database host name [localhost]:
  Enter the database listen port [1521]:
  Enter the database service name [orcl]: ords_app_root1

Specify the Application Root servicename.

  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=localhost PORT=1521 SERVICE_NAME=ords_app_root1
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
    [4] Additional Feature: Database Actions
    [5] Configure for Standalone Mode: Yes
    [6]    Protocol: HTTP
    [7]       HTTP Port: 8080
    [8]   APEX static resources location:
    [A] Accept and Continue - Create database pool
    [Q] Quit - Do not proceed. No changes
  Choose [A]: 3

Select option 3 to update the ORDS_PUBLIC_USER password.

  Enter your choice for the runtime user password (ORDS_PUBLIC_USER)
    [S] Specify your own password
    [G] Generate password
    [C] Cancel - No Changes
  Choose [G]: S

Select option S to specify the password.

  Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Enter the same password you specified for the ORDS_PUBLIC_USER in section Installing ORDS in the Application Root Container.

Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=localhost PORT=1521 SERVICE_NAME=ords_app_root1
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <specified password>
    [4] Additional Feature: Database Actions
    [5] Configure for Standalone Mode: Yes
    [6]    Protocol: HTTP
    [7]       HTTP Port: 8080
    [8]   APEX static resources location:
    [A] Accept and Continue - Create database pool
    [Q] Quit - Do not proceed. No changes
  Choose [A]:

Make any additional changes.

  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: Basic
    [2] Basic Connection: HOST=localhost PORT=1521 SERVICE_NAME=ords_app_root1
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <specified password>
    [4] Additional Feature: Database Actions
    [5] Configure for Standalone Mode: Yes
    [6]    Protocol: HTTPS
    [7]       HTTPS Port: 8443
    [8]    Certificate Type: Use my SSL Certificate
    [9]      Path for the SSL Certificate: /path/to/mycert.crt
    [10]      Path for the SSL Certificate private key: /path/to/mycert.der
    [11]   APEX static resources location: /path/to/apex/images
    [A] Accept and Continue - Create database pool
    [Q] Quit - Do not proceed. No changes
  Choose [A]:

Select option A to create your database pool.

Running ORDS

This section lists the different methods available to run ORDS after installing ORDS in the application container.

Once you install ORDS in the application container and create the ORDS configuration files, run ORDS using one of the following methods:

See Also: Deploying Oracle REST Data Services

Upgrading ORDS in the Application Container

This section describes how to upgrade ORDS in the application container.

You can upgrade an application in an application container. These operations are performed in the application root. The application container propagates the application changes to the application PDBs when the application PDBs synchronize with the application in the application root.

Prerequisites:

To upgrade ORDS in the application root:

  1. In SQLcl or SQL*Plus, connect to the application root.

  2. Run /path/to/scripts/upgrade/ords_app_con_upgrade.sql with the following parameters:

    • Log folder (must include the forward slash at the end)

    • Scripts path (requires the fully qualified path to the ORDS scripts) Note: The ords_app_con_upgrade.sql script upgrades ORDS in the application root container to the release that you are using. For example, if the ORDS application version is 20.2.1.r2121800, and the ORDS upgrade script is 20.3.0.r2601900, then the script upgrades ORDS to release 20.3.0.r2601900 in the application root container.

To synchronize the ORDS application in an application PDB with the upgrade changes in the application root:

  1. In SQLcl or SQL*Plus, ensure that the current container is the application PDB.

  2. Run the ALTER PLUGGABLE DATABASE APPLICATION statement specifying the ORDS application with the SYNC clause.

ALTER SESSION SET CONTAINER = ords_app_pdb1;

ALTER PLUGGABLE DATABASE APPLICATION ORDS SYNC;

See Also: Verifying ORDS in the Application Container

Uninstalling ORDS from the Application Container

This section describes how to uninstall ORDS from the application container.

You can uninstall an application from an application container. These operations are performed in the application root. The application container propagates the application changes to the application PDBs when the application PDBs synchronize with the application in the application root.

To uninstall ORDS from the application root: 1. In SQLcl or SQL*Plus, connect to the application root.

  1. Run /path/to/scripts/uninstall/core/ords_app_con_uninstall.sql with the following parameters:

    • Log folder (must include the forward slash at the end)

    • Scripts path (requires the fully qualified path to the ORDS scripts)

ALTER SESSION SET CONTAINER = ords_app_root1;

@/path/to/scripts/uninstall/core/ords_app_con_uninstall.sql /path/to/logs/ /path/to/scripts

To synchronize the application PDB to uninstall the ORDS application: 1. In SQLcl or SQL*Plus, ensure that the current container is the application PDB.

  1. Run the ALTER PLUGGABLE DATABASE APPLICATION statement specifying the ORDS application with the SYNC clause.

For example:

ALTER SESSION SET CONTAINER = ords_app_pdb1;

ALTER PLUGGABLE DATABASE APPLICATION ORDS SYNC;

See Also: Verifying ORDS in the Application Container

Verifying ORDS in the Application Container

This section describes how to verify ORDS in the application container.

To verify the ORDS for install, upgrade, validate, and uninstall in the application container:

Making All PDBs Addressable by Oracle REST Data Services (Pluggable Mapping)

Pluggable mapping refers to the ability to make all PDBs in a CDB root or in an application root container addressable by Oracle REST Data Services. To use this feature, follow the instructions described in this topic.

If the Oracle REST Data Services configuration file includes the db.serviceNameSuffix parameter, this indicates that the Oracle REST Data Services pool points to a CDB root or an application root, and that the PDBs connected to that CDB root or an application root should be made addressable by Oracle REST Data Services. For Pluggable mapping to work, you must specify this parameter setting. You can specify the db.serviceNameSuffix parameter at a pool-specific level or at a global level. If you set the parameter to a global level, then you can apply it to all the database pool configurations that define a CDB connection. Also, the Database Actions landing page (<server-name>:<port-number>/ords/sql-developer) displays a prompt for the PDB name, if you set the parameter to a global level.

The value of the db.serviceNameSuffix parameter must correspond to the value of the DB_DOMAIN database initialization parameter, based on the following rules:

To set the value of the db.serviceNameSuffix parameter:

  1. In SQLcl or SQL*Plus, connect to the root as a user with SYSDBA privileges.

  2. Check the value of the DB_DOMAIN database initialization parameter.

SQL> show parameter DB_DOMAIN
  1. Exit SQLcl or SQL*Plus.
SQL> exit
  1. If the db.serviceNameSuffix parameter value is not defined, enter a command in the following format to add an entry to the configuration file:
ords --config /path/to/config config --db-pool pool-name set "db.serviceNameSuffix" ".value-of-DB_DOMAIN"
Where `pool-name` is one of the following:

- `default` for the default and migrated pool

- \<*custom_pool>* as specified during ORDS install

Example 1: You want to make PDBs in a CDB root or an application root addressable globally. Specify global settings by entering the following command:

ords --config /path/to/config config set --global "db.serviceNameSuffix" ".value-of-DB_DOMAIN"

Note: The approach shown in Example 1, that is, setting the property for all the pools through the global/settings.xml file, works the best for most use cases.

Example 2: You want to make PDBs in a CDB root or an application root addressable for your PL/SQL Gateway, and your pool name is `my_pool`. Enter the following command:

ords --config /path/to/conf config --db-pool my_pool set "db.serviceNameSuffix" ".example.com"
For example, if the database pointed to by apex has a `DB_DOMAIN` value of `example.com` and contains the two PDBs `pdb1.example.com` and `pdb2.example.com`, the first PDB will be mapped to URLs whose path starts with `/ords/pdb1/`, and the second PDB will be mapped to URLs whose path starts with `/ords/pdb2/`.

Example 3: You want to make PDBs in a CDB root or an application root addressable globally and the DB_DOMAIN is an empty value. Enter the following command to specify `db.serviceNameSuffix` parameter globally:

`ords --config /path/to/config config set --global "db.serviceNameSuffix" ""`