Using the Multitenant Architecture with Oracle REST Data Services

7 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.

7.1 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:

Installation Enabling Multiple Releases

Preinstallation Tasks for Oracle REST Data Services CDB Installation

Ensure that the PDBs are open (not mounted/closed) in read/write mode (except for PDB$SEED, which remains in read-only mode). For more information, see Oracle Multitenant Administrator’s Guide
Ensure that the default and temporary tablespaces to be used by the ORDS_METADATA schema and the ORDS_PUBLIC_USER user exist and that you know the tablespace names. The installation procedure creates those users, but it does not create the tablespaces.
Ensure that when a common user is used for the PL/SQL Gateway, the user has also been explicitly created in the PDB$SEED container otherwise, the installer will not be able to configure the PL/SQL Gateway correctly.

Note:

ORDS_METADATA and ORDS_PUBLIC_USER are installed in the seed container, and the default and temporary tablespaces exist in PDB$SEED. If these tablespaces do not already exist, then you must create the tablespaces in PDB$SEED. For more information, see Oracle Multitenant Administrator’s Guide

7.1.1 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.

7.1.1.1 Command Line Installation

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

7.1.1.2 Advanced Installation

This section describes the advanced 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:

Navigate to the folder where you unzipped the Oracle REST Data Services installation kit.
Enter the following command:
```
ords --config <CONFIG_FOLDER> install
```

Choose the installation type required

 Enter a number to select the type of installation
    [1] Install or upgrade ORDS in the database only
    [2] Create or update a database pool and install/upgrade ORDS in the database
    [3] Create or update a database pool only
  Choose [2]:

Accept or enter 2 (the default) to update the configuration and install ORDS REST Data Services into the CDB and all of its PDBs.

 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]:

Accept of enter 1 (the default) to enter the connection information to the CDB
Enter the database host name [localhost]: Enter the database listen port [1521]: Enter the database service name [orcl]: Provide database user name with administrator privileges. Enter the administrator username: SYS Enter the database password for SYS AS SYSDBA: Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/orcl Retrieving information... Your database connection is to a CDB. ORDS common user ORDS_PUBLIC_USER will be created in the CDB. ORDS schema will be installed in the PDBs. Root CDB$ROOT - create ORDS common user PDB PDB$SEED - install ORDS xx.x.x.xxx.xxxx (mode is READ ONLY, open for READ/WRITE) PDB PDBName1 - install ORDS xx.x.x.xxx.xxxx PDB PDBName1 - configure PL/SQL gateway user APEX_PUBLIC_USER in ORDS version xx.x.x.xxx.xxxx Install ORDS in the database [1] Yes [2] No Choose [1]:
```
Enter 1 if you want to install ORDS or 2 to skip this step [1]:
```
Press enter to continue with the installation.
When prompted, enter additional information as needed.

Note:

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

7.1.1.3 Silent Installation

Silent installation reads the properties from the Oracle REST Data Services parameter file.

To perform a silent installation, enter the following command:

ords --config /path/to/config install <OPTIONS>

7.1.2 Upgrading Oracle REST Data Services in a CDB Environment

When you use a new release of Oracle REST Data Services, upgrading its schema in the CDB and its pluggable databases (PDBs) will occur automatically when you perform a simple or advanced installation.

For example:

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

If Oracle REST Data Services is already installed or upgraded, a message displays the Oracle REST Data Services schema version, and you will not be prompted for information.

7.1.2.1 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.

7.1.3 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).

7.2 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:

7.2.1 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:

Download ORDS version 20.2.1 or later from Oracle REST Data Services Downloads.
Extract the ORDS SQL scripts.

To obtain the ORDS SQL scripts, execute the following commands:


unzip ords.version.number.zip ords.war 
unzip ords.war 'WEB-INF/lib/ords-installer-*.jar'    
unzip  'WEB-INF/lib/ords-installer-*.jar' 'db/*'    
mv db scripts

The ORDS SQL scripts are located in the scripts folder. The scripts folder contains the subdirectories for the install, upgrade, validate, and uninstall SQL scripts. You can run these SQL scripts using SQLcl, SQL*Plus, or SQL Developer.

7.2.1.1 Creating an Application Root Container

This section describes how to create an application root container.

To create an application root container:

Ensure that the current container is in CDB$ROOT.
Use the AS APPLICATION CONTAINER clause of the CREATE PLUGGABLE DATABASE statement to create an application container.
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;

Note:

ords_app_root1 and the admin 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.

See Also:

Creating an Application Container

7.2.2 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:

Connect to the application root container.
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 P033w0r6! /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:

The ORDS schema, ORDS_METADATA
The ORDS proxy user, ORDS_PUBLIC_USER and
The related database objects in the application container

7.2.3 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:

Ensure that the current container is in the CDB$ROOT.
Alter session and set container to the application root.
Use the AS SEED clause of the CREATE PLUGGABLE DATABASE statement to create an application seed.
Sync the ORDS application with the application seed.
Compile invalid objects.
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

7.2.4 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:

Ensure that the current container is in CDB$ROOT.
Alter session and set the container to the application root.
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

7.2.5 Configuring the PLSQL Gateway for APEX

This section describes how the 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:

Ensure that the current container is in CDB$ROOT.

Alter session and set the container to the application pdb, example: ords_app_pdb1.

ALTER SESSION SET CONTAINER = ords_app_pdb1;
begin
  ords_admin.config_plsql_gateway(
      p_runtime_user       => 'ORDS_PUBLIC_USER',
      p_plsql_gateway_user => 'APEX_PUBLIC_USER',
      p_comments           => 'APEX proxied to ORDS');
  commit;
end;
/

7.2.6 ORDS Configuration Files Setup

This section describes how to setup the ORDS configuration files:

Topics:

7.2.6.1 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.

7.2.6.2 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 setup command to create the configuration files:

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

Where, the --configOnly option must be specified to create the configuration files. When prompted for the service name, specify the application root servicename.

Example 7-1 Creating ORDS Configuration

This example assumes that ORDS configuration does not exist in your configuration folder. This example is for a Basic Connection.

Note:

If Application Express is installed in your database, then a prompt is displayed for the APEX images location.
Example: Enter the APEX static resources location: /path/to/apex/images

If you are not using Application Express, then press enter.
Oracle recommends to use HTTPS protocol instead of HTTP.

Oracle REST Data Services - Interactive Install

  Enter a number to select the type of installation
    [1] Install or upgrade ORDS in the database only
    [2] Create or update a database pool and install/upgrade ORDS in the database
    [3] Create or update a database pool only
  Choose [3]:  
  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
  Enter the database password for ORDS_PUBLIC_USER: 
Confirm password: 
  Enter a number to select additional feature(s) to enable:
    [1] Database Actions  (Enables all features)
    [2] REST Enabled SQL and Database API
    [3] REST Enabled SQL
    [4] Database API
    [5] None
  Choose [1]: 
  Enter a number to configure ORDS for standalone mode
    [1] Configure ORDS for standalone mode
    [2] Skip
  Choose [1]: 
  Enter a number to use HTTP or HTTPS protocol
    [1] HTTP
    [2] HTTPS
  Choose [1]: 2
  Enter the HTTPS port [8443]: 
  Enter a number to select the certificate type
    [1] Use self-signed certificate (generates automatically)
    [2] Use my SSL certificate (requires SSL certificate and SSL certificate private key)
  Choose [1]: 2
  Enter the path for the SSL Certificate: /path/to/<SSL certificate>
  Enter the path for the SSL Certificates private key: /path/to/<SSL Certificate private key>
  Enter the APEX static resources location: /path/to/apex/images

7.2.7 Running ORDS

This section lists the different methods you can use 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:

Standalone Mode
Deploy on Oracle WebLogic Server
Deploy Oracle REST Data Services on Apache Tomcat

See Also:

Deploying Oracle REST Data Services

7.2.8 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:

ORDS must already be installed in the application container.
Upgrading ORDS from an earlier release to a new release (for example, ORDS release 20.2.x.x to 20.3.x.x).

To upgrade ORDS in the application root:

In SQLcl or SQL*Plus, connect to the application root.
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:

In SQLcl or SQL*Plus, ensure that the current container is the application PDB.
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;

7.2.9 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:

In SQLcl or SQL*Plus, connect to the application root.
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:

In SQLcl or SQL*Plus, ensure that the current container is the application PDB.
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;

7.2.10 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:

Manually inspect the following log files for any errors:
- Install - ordsinstall_<timestamp>.log
- Upgrade - ordsupgrade_<timestamp>.log
- Uninstall - ordsuninstall_<timestamp>.log
Query dba_applications to verify if the ORDS application exists and its application version is the same as the ORDS product version.
```
SQL> 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
```
Query dba_app_errors to check for any errors:
SQL> select app_name, app_statement, errornum, errormsg from dba_app_errors where app_name = 'ORDS';

no rows selected

If you are uninstalling ORDS from the application container, the APP_STATUS contains the value UNINSTALLED.

7.3 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:

If the DB_DOMAIN value is blank, then the db.serviceNameSuffix parameter must also be an empty string.
If the DB_DOMAIN value is not blank, then the db.serviceNameSuffix parameter must have a value starting with a period (.).

To set the value of the db.serviceNameSuffix parameter:

In SQLcl or SQL*Plus, connect to the root as a user with SYSDBA privileges.
Check the value of the DB_DOMAIN database initialization parameter.
```
SQL> show parameter DB_DOMAIN
```
Exit SQLcl or SQL*Plus.
```
SQL> exit
```
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" ""