1. Installation and Configuration Guide
  2. Installing and Configuring Oracle REST Data Services

2 Installing and Configuring Oracle REST Data Services

This chapter describes how to install, configure, and upgrade Oracle REST Data Services (ORDS).

Topics:

Related Topics

2.1 Installing Oracle REST Data Services

You must complete the following steps before installing ORDS.

Note:

The procedures in this topic apply to installing Oracle REST Data Services in a single instance Database or a PDB. For setting up ORDS in a CDB environment, refer to Using the Multitenant Architecture with Oracle REST Data Services section.

You can then install ORDS using one of the following options:

See Also:

2.1.1 Downloading ORDS

This section describes the how to download ORDS.

Downloading ORDS Zip file

Download the ords_<latest>.zip file from the Oracle REST Data Services (ORDS) download page, and then unzip ords_<latest>.zip file into a folder of your choice. The folder you choose to unzip the file is referred to as the ORDS product folder. The ORDS product folder contains a bin folder and other folders and files required to run ORDS.

Adding ORDS bin folder to your Operating System PATH

Oracle recommends to add the ORDS bin folder to your operating system PATH environment variable.

Example for LINUX/UNIX Operating System
echo -e 'export PATH="$PATH:/<ords product folder>/bin"' >> ~/.bash_profile

Start a new shell to pick up this change.

Example for WINDOWS Operating System
Use a command prompt with administrator privileges.
SetX PATH "%PATH%;<ords product folder>\bin"

Start a new command prompt to pick up this change.

Example for MAC Operating System
echo -e 'export PATH="$PATH:/<ords product folder>/bin"' >> ~/.zprofile

Start a new terminal to pick up this change.

Downloading ORDS Using yum

ORDS is published in OL7 and OL8 repositories. The system administrator can then add this repository to the yum configuration in order to install the ORDS and handle its dependencies.

OL7 repo: https://yum.oracle.com/repo/OracleLinux/OL7/oracle/software/x86_64/

OL8 repo: https://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64/

If the oracle software repository has been added to your yum configuration, then you can download and install ORDS using the following command:
sudo yum install ords

The preceding command handles all the ORDS dependencies and place the ORDS program in /usr/local/bin/ords folder to set up your ORDS configuration and install or upgrade ORDS in the database.

Oracle recommends you to use /etc/ords/conf configuration directory. The following comand is used to configure ORDS:
ords -–config /etc/ords/config install
If you want to use a different directory to place your ORDS configurations and you want to use the Linux system service management as system d or system v, then configure the ORDS config directory in /etc/ords.conf folder.

Installing ORDS Using RPM

If your system where you want to install is not using yum or is not connected to the internet, then you can download the ORDS RPM from the links mentioned in the preceding section (Download ORDS using yum) and install the RPM manually. This does not handle the ORDS dependencies, you must install the java JDK manually. After downloading the RPM file you can install it using the following command:
sudo rpm -i ords-22.1.X-X.elX.noarch.rpm
This places the ORDS program in /usr/local/bin/ords folder to set up your ORDS configuration and install or upgrade ORDS in the database.
To configure your ORDS, we recommend using the configuration directory in /etc/ords/conf folder with the following command:
ords -–config /etc/ords/config install
If you want to use a different directory to place your ORDS configurations and you want to use the LINUX system service management as systemd or system v, then configure the ORDS configuration directory in /etc/ords.conf folder.

2.1.2 Setting Up the Configuration Folder Structure

This section describes how to set up the configuration folder structure.

2.1.2.1 Specifying the New ORDS Configuration Folder

New installation has the ORDS configuration files placed in the configuration folder. Configuration files from ORDS release 21.4.x or earlier are migrated to the configuration folder.

You can obtain the configuration folder location using one of the following options:
  1. Command option:

    Use the --config option to specify your configuration folder.

    Example:

    ords --config /path/to/conf install

  2. Environment variable:

    Note:

    This option is preferred if you forget to include the --config option when you are using the ORDS command-line Interface.
    • ORDS_CONFIG: Create the ORDS_CONFIG environment variable.

      Example for LINUX or UNIX operating system: 

      Shell script containing ORDS_CONFIG
cat example_env
export ORDS_CONFIG=/path/to/conf

echo $ORDS_CONFIG
/path/to/conf
    • _JAVA_OPTIONS:

      Example:

      export _JAVA_OPTIONS=-Dconfig.url=/Users/<username>/work/dbtools-dev/config
./ords-22.1.0.087.1756/bin/ords config list

_JAVA_OPTIONS: -Dconfig.url=/Users/<username>/work/dbtools-dev/config
  3. Current Working Directory:

    If the --config <configuration_folder> option is not specified, and the ORDS_CONFIG environment variable is not defined, then your current working directory is used as the configuration folder.

    Example:

    If your current working directory is /path/to/conf, then it uses that location for your configuration folder.

Note:

  • Oracle highly recommends you to ensure that ORDS does not create the configuration directory in the ORDS product folder. For example, you can have a configuration folder in the following location: 
    /Users/<user_name>/work/dbtools-dev/config/

    The best practice is to have your configuration files separate from the application files, this makes maintenance and upgrades easier and more reliable.

  • The following command is no longer valid:

    Example: java -jar ords.war install

    If you specify the legacy commands using java -jar ords.war, you get the following warning message:

    Warning: Support for executing: java -jar ords.war has been deprecated. Please add ords to your PATH and use the ords command instead. Run the following command to add ORDS to your PATH:

    <Displays an example of adding the bin folder to your PATH>

    Start a new terminal to pick up this change. Oracle recommends to add the ORDS product bin folder to your path.

2.1.3 Interactive Command-Line Interface Installation

This section explains the interactive command-line interface (CLI) installation prompts. They prompt you for necessary information to create or update the ORDS configuration files, install or upgrade ORDS schema, ORDS database user, and related database objects in the database. You have the option to run in standalone mode.

To be prompted, you must execute one of the following commands:
  • Specify the install command. For example: $ ords install

    Note:

    Assumes that the configuration directory is specified through the environment variable or you are defaulting to the current working directory.
  • Specify the option --config <CONFIG FOLDER> followed by the install command.
    For example:
    • $ ords --config /path/to/config install
    • ords --config /path/to/conf install --interactive --log-folder /path/to/logs

Table 2-1 Interactive Installation Prompts

Prompt Number Prompt Description
1. 
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]:
 Refer to Entering a Number to Select the Type of Installation.
2. 
Enter a number to select the database pool to update, or create an additional database pool.
  The selected (or created) database pool will be used to install or upgrade ORDS in the database.
    [1] default  jdbc:oracle:thin:@//localhost:1521/orcl
    [2] Create an additional database pool
  Choose [1]:
Refer to Entering a Number to Select the Database Pool
3. 
Enter the database pool name:
 Refer to Entering the Database Pool Name
4. 
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]:
 Refer to Entering a Number to Select the Database Connection
5. 
Enter the database host name [localhost]:
 Refer to Entering the Database Host Name
6. 
Enter the database listen port [1521]:
 Refer to Entering the Database Listener Port
7. 
Enter the database service name [orcl]:
 Refer to Entering the Database Service Name
8. 
Enter the TNS location: /path/to/tns
 Refer to Entering the TNS Location
9. 
Enter a number to select the TNS Network alias to use
    [1] DB1  ...CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1))
    [2] DB2  ...CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb2)))     
    [3] DB3  ...CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb3)))     
  Choose [1]:
Refer to Entering a Number to Select the TNS Network Alias
10. 
Enter the Custom database URL:
 Refer to Entering the Custom Database URL
11. 
Provide database username with administrator privileges.
Enter the administrator username: SYS
 Refer to Entering the Administrator Username
12. 
Enter the database password for SYS AS SYSDBA:
 Refer to Entering the Database Password
13. 
Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: 
Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]:
Refer to Entering the Default Tablespace
14. 
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]:
Refer to Entering a Number to Select Additional Feature(s)
15. 
Enter a number to configure and start ORDS in standalone mode
    [1] Configure and start ORDS in standalone mode
    [2] Skip
  Choose [1]:
Refer to Entering a Number to Configure and Start ORDS in Standalone Mode
16. 
Enter a number to use HTTP or HTTPS protocol    
[1] HTTP
[2] HTTPS
  Choose [1]:
Refer to Entering a Number to Use HTTP or HTTPS Protocol
17. 
Enter the HTTP port [8080]:
Refer to Entering the HTTP Port
18. 
Enter the HTTPS port [8443]:
 Refer to Entering the HTTPS Port
19. 
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]:
Refer to Entering a Number to Select the Certificate Type
20. 
Enter the SSL hostname:
 Refer to Entering the SSL Hostname
21. 
Enter the path for the SSL Certificate:
 Refer to Entering the Path for the SSL Certificate
22. 
Enter the path for the SSL Certificate’s private key:
 Refer to Entering the Path for the SSL Certificate’s Private Key
23. 
Enter the APEX static resources location: /path/to/apex/images
 Refer to Entering the APEX Static Resources Location
2.1.3.1 Entering a Number to Select the Type of Installation

Select the type of installation.

  1. Option 1
    • Install or upgrade ORDS in the database only
      • Installs the ORDS schema, ORDS database user, and related database objects if ORDS does not exist in the database.
      • Upgrades ORDS if the schema version is less than the product version.
  2. Option 2
    • Create or update a database pool and install/upgrade ORDS in the database
      • Create a database pool, or select an existing database pool to update.
      • For an existing database pool, when prompted, it displays the setting value as the default values.
    • Install/Upgrade ORDS in the database
      • Installs the ORDS schema, ORDS database user, and related database objects if ORDS does not exists.
      • Upgrades ORDS if the schema version is less than the product version.
  3. Option 3
    • Create or update a database pool only
      • Create a database pool, or select an existing database pool to update.
      • For an existing database pool, when prompted, it displays the setting value as the default values.
2.1.3.2 Entering a Number to Select the Database Pool

If the ORDS database pool(s) already exist in your configuration folder, it will display the database pool(s) along with its connection.

You can select the database pool, or

Create an additional database pool if you selected
  • option 2 for prompt 1
  • option 3 for prompt 1

Note:

If the ORDS configuration pool does not exist, then it prompts you for the database connection type. See prompt 3.
2.1.3.3 Entering the Database Pool Name

If you select the option 2 for prompt number 2, then the database pool name prompt is displayed. Enter the database pool name.

Note:

  • The database pool name must only contain lowercase alphabets a-z, digits 0-9, and the "-" character and must start with a lowercase alphanumeric character and end with a lowercase alphanumeric character.

    For example, if the pool name you enter is database1, then all REST requests to that pool will start with /ords/database1/.

  • When the database pool is created, the database pool folder is also created along with the pool specific settings, and a wallet to store the database user credentials.

  • This prompt also sets the mapping pattern. See Configuring Additional Databases for more information.
2.1.3.4 Entering a Number to Select the Database Connection

Select the database connection type.

Note:

If ORDS configuration pool does not exist in your configuration folder, then after selecting from prompt number 1, prompt number 4 is displayed.

If option 2 or option 3 is selected for prompt 1, then the database pool folder named default is created along with the settings specific to the pool, and a wallet to store the database user credentials.

2.1.3.5 Entering the Database Host Name

Specify the database host name for basic connection.

2.1.3.6 Entering the Database Listener Port

Specify the database port for a basic connection.

2.1.3.7 Entering the Database Service Name
Specify the service name. The default value for the service name is obtained from one of the following in the given sequence:
  1. The service name setting in the ORDS configuration file. If not present, then
  2. It checks for ORACLE_PDB environment variable. If undefined, then
  3. It checks for ORACLE_SID environment variable. If undefined, then
  4. It uses orcl as the default value.
2.1.3.8 Entering the TNS Location

Specify the location for tnsnames.ora file. The location can be to the folder where tnsnames.ora file is located, or a zip file that contains the tnsnames.ora file for TNS connection.

2.1.3.9 Entering a Number to Select the TNS Network Alias

Select the TNS alias name from the list of TNS network alias names displayed.

2.1.3.10 Entering the Custom Database URL

Specify the custom database URL for custom database URL connection.

2.1.3.11 Entering the Administrator Username

Specify a user who has installer privileges to install or upgrade ORDS in the database.

See Also:

Privileges Granted to the Oracle REST Data Services Users
2.1.3.12 Entering the Database Password

Specify the administrator user password.

2.1.3.13 Entering the Default Tablespace
Specify the tablespaces for both the ORDS schema and ORDS database user.

Note:

If ORDS is not installed in the database, then you are prompted for the tablespaces.
2.1.3.14 Entering a Number to Select Additional Feature(s)

Select the additional features that you want to enable.

2.1.3.15 Entering a Number to Configure and Start ORDS in Standalone Mode

You can configure ORDS to run in standalone mode. In addition, you can start ORDS in standalone mode after the install or upgrade is complete.

If you do not want to configure ORDS and start in standalone mode, choose Option 2 to exit.

2.1.3.16 Entering a Number to Use HTTP or HTTPS Protocol

Select the protocol (HTTP or HTTPS) that you want to use.

Note:

It is highly recommended to run ORDS in Standalone mode with HTTPS versus HTTP.
2.1.3.17 Entering the HTTP Port

Specify the HTTP port, if you selected option 1: HTTP for prompt number 16.

2.1.3.18 Entering the HTTPS Port

Specify the HTTPS port, if you selected option 2: HTTPS for prompt number 16.

2.1.3.19 Entering a Number to Select the Certificate Type

Select an option for the certificate type, if you selected option 2: HTTPS Protocol for prompt number 16.

If option 1 is selected, then ORDS automatically generates the self-signed certificate and prompts you for the SSL hostname.

If option 2 is selected, then you get additional prompts for the certificate path and certificate private key path. Refer to SSL Certificate section for more details.

2.1.3.20 Entering the SSL Hostname

Specify the SSL hostname, if you selected option 1: Use self-signed certificate for prompt 19.

2.1.3.21 Entering the Path for the SSL Certificate

Specify the SSL certificate path, if you selected option 2: Use my SSL certificate for prompt number 19.

2.1.3.22 Entering the Path for the SSL Certificate’s Private Key

Specify the path for the SSL Certificates’ private key, if you have selected option 2: Use my SSL certificate for prompt 19.

2.1.3.23 Entering the APEX Static Resources Location

ORDS detects if APEX is installed in the database and if the database user APEX_PUBLIC_USER exists, if found to be true, then it prompts for the location of the APEX images.

2.1.4 Non-Interactive Command-Line Interface Installation (Silent)

The non-interactive command-line interface (CLI) installation or silent installation, performs the following tasks:

  • Creates or updates the ORDS configuration files
  • Installs or upgrades the ORDS schema
  • Creates the ORDS proxy user and the related objects in the Database

The non-interactive installation command does not run in standalone mode. If you want to run it in standalone mode, then it must be followed by the serve command.

If you provide the command-line options for the non-interactive installation and if you are missing a required option (for example: Basic connection requires the db-port option), then an error message is displayed.

The --db-pool <pool_name> option specifies the database pool to create or update. If the --db-pool option is omitted, then it defaults to the default database pool.

2.1.4.1 Understanding Command Options for Command-Line Interface Installation

Table 2-2 Command Options for Command-Line Interface Installation

Options Description
--admin-user <USERNAME> Specifies the user with privileges to install or upgrade ORDS in the database, or obtains the ORDS status information.
--bequeath-connect Specifies that bequeath connect that enables clients to connect directly to an Oracle database bypassing the network listener. This is when the client is on the same server as the database. Refer to Oracle REST Data Services documentation to setup your environment to use the --bequeath-connect option.
--config <CONFIG_PATH> Specifies the path to the folder containing the configuration files.
--config-only Specifies to create or update the configuration only.
--db-only Specifies to install or upgrade ORDS in the database and not to create or update the configuration files.
--db-pool <POOL_NAME> Specifies the name of the database pool to create, update or use. If the db-pool option is omitted, then the default database pool is used.
--db-custom-url <URL> Specifies the custom database url for the database connection.
--db-hostname <HOST> Specifies the database host name.
--db-port <PORT> Specifies the database port.
--db-servicename <SERVICENAME> Specifies the database service name.
--db-sid <SID> Specifies the database SID.
--db-tns-alias <ALIAS_NAME> Specifies the TNS alias name that exists in the tnsnames.ora file.
--db-tns-dir <TNS_DIR> Specifies the folder where the tnsnames.ora file is located.
--feature-db-api <BOOLEAN>

Specifies if you want to enable DB API feature.

Possible values are: true or false.
  • If the value is set to true, then DB API feature is enabled.
  • If the value is set to false, then DB API feature is disabled.

Returns an error if the specified options are --feature-sdw true and --feature db-api false.

--feature-rest-enabled-sql <BOOLEAN>

Specifies if you want to enable REST-Enabled SQL feature.

Possible values are: true or false.
  • If the value is set to true, then the REST-Enabled SQL feature is enabled.
  • If the value is set to false, then the REST-Enabled SQL feature is disabled.

Returns an error if the specified options are --feature-sdw true and --feature-rest-enabled-sql false.

--feature-sdw <BOOLEAN>

Specifies if you want to enable Database Actions feature.

Possible values are: true or false.

  • If the value is set to true, then the Database Actions feature is enabled.
  • If the value is set to false, then the Database Actions feature is disabled.

If the option is set to true, then the following settings are set to true in the configuration file:

  • database.api.enabled
  • restEnabledSql.active

Returns an error if --feature-sdw true and any of following options are specified, and are set to false:

  • --feature-db-api
  • --feature-rest-enabled-sql
--gateway-mode <MODE>

Specifies the PL/SQL gateway mode.

Possible values are: proxied, direct, or disabled.

Default value is disabled.

Where:
  • disabled- PL/SQL gateway is not allowed.
  • direct - The PL/SQL gateway call is performed directly as the user identified by db.username (located in the pool.xml setting).
  • proxied - The PL/SQL gateway call is performed by proxying from the run-time user (db.username is located in the pool.xml) to the relevant schema configured by ords_admin.config_plsql_gateway.
--gateway-user <USER> Specifies the user that exists in the Oracle database and has privileges to access the stored procedures.
--interactive, -i Prompts user for all the required information.
--help, -h Shows how to use the command.
--legacy-config <FOLDER> Specify the legacy configuration folder to migrate to the revised configuration structure.
--legacy-context <STRING> Specifies the context (example: ords, apex, and so on.) that was used in the legacy configuration.
--log-folder Writes the logs from the install, upgrade, repair or uninstall to the log folder. If this option is omitted, then the output is written to standard output.
--password-stdin To run the ORDS install, upgrade, repair, or uninstall command non-interactively, use this option to read the password value from standard input when redirecting input to a file or here document.
--pdb-exclude <(PDB...)>

Specifies the option applicable for CDB.

List of PDBs that are excluded from the ORDS install/upgrade, or repair.
--pdb-open-readwrite-all

Specifies the option applicable for CDB.

Specifies whether to open all PDBs in read write mode if their status is either closed or read only.
--pdb-open-readwrite <(PDB...)>

Specifies the option applicable for CDB.

List of PDBs that are read-only or closed to be open for read/write to install, upgrade, repair, or uninstall ORDS.
--pdb-skip-readonly

Specifies the option applicable for CDB.

Skips PDBs that are read-only.

If this option is omitted, an error message is returned informing the user that the PDB(s) are read-only for install or upgrade ORDS. Excludes PDB$SEED since it is open for read/write.

--pdb-skip-closed

Specifies the option applicable for CDB.

Skips PDBs that are closed (mounted).

If this option is omitted, an error message is returned informing the user that the PDB(s) are closed for installation/upgradation of ORDS.
--proxy-user Specifies the password of the ORDS database user (ORDS_PUBLIC_USER) that is in a file or in a here document when redirecting the standard input.
--proxy-user-tablespace <TABLESPACE>

Specifies the option applicable for ORDS install.

The default tablespace for proxy user (For example: ORDS_PUBLIC_USER).

If this option is omitted, then the defaulted database default tablespace is used.
--proxy-user-temp-tablespace <TABLESPACE>

Specifies the option applicable for an ORDS install.

The temporary tablespace for proxy user (For example: ORDS_PUBLIC_USER).

If this option is omitted, then the defaulted database temporary tablespace is used.
--schema-tablespace <TABLESPACE>

Specifies the default tablespace for ORDS_METADATA schema.

If this option is omitted, then the defaulted database default tablespace is used.
--schema-temp-tablespace <TABLESPACE>

Specifies the option applicable for an ORDS install.

The temporary tablespace for ORDS_METADATA schema.

If this option is omitted, then the defaulted database temporary tablespace is used.
2.1.4.2 Using Input Redirection

This section describes how to redirect the standard input using the here command or to a file.

Redirect STDIN to a file

Redirect STDIN to a file that contains the password. In the following example, the file must contain two passwords. Each password must be on a separate line.

Example:
$ cat password.txt
firstpassword
secondpassword

The first password belongs to the first user on the command line (for example: SYS). The second password belongs to the second user on the command line (for example: proxy-user). 

ords --config <configuration_folder> install --db-pool <pool_name> --admin-user <username> --proxy-user 
--db-hostname <host> --db-port <port_number> --db-servicename <service_name> 
--log-folder <log_folder> --feature-sdw <boolean> --password-stdin < <filename>

Example:


ords --config /path/to/conf install --db-pool db1 --admin-user SYS --proxy-user --db-hostname localhost 
--db-port 1521 --db-servicename orcl --log-folder /path/to/logs --feature-sdw true 
--password-stdin < password.txt

Redirect Standard Input Using Here Document

Redirect STDIN using the Here document (also known as heredoc) for the password(s). The heredoc consists of the '<<' redirection operator followed by a delimiter token.

Each password must be on a separate line and it is ended by the delimiter token.

Example:

The first password belongs to the first user on the command line (for example: SYS). The second password belongs to the second user on the command line (for example: proxy-user). 

ords --config <configuration_folder> install --db-pool <pool_name> --admin-user <username> --proxy-user --db-hostname
 <host> --db-port <port_number> --db-servicename <service_name> --log-folder <log_folder> --feature-sdw <boolean> 
--password-stdin << EOF
<password1>
<password2>
EOF
ords --config /path/to/conf install --db-pool db1 --admin-user SYS --proxy-user --db-hostname 
localhost --db-port 1521 --db-servicename orcl --log-folder /path/to/logs --feature-sdw true 
--password-stdin << EOF
<password1>
<password2>
EOF

2.2 Repairing the Oracle REST Data Services Installation

If you want to check that the Oracle REST Data Services installation is valid, specify the repair command.

Note:

To repair ORDS in the database, the ORDS product version must be the same version as the ORDS installed schema version in the database.
Example of repair command to prompt for information:
ords [--config <folder>] install repair --interactive [--log-folder <folder>]

If you have an existing configuration, you may choose to select from a list of database pools. You also have the option to specify the database connection for ORDS to be installed. You are prompted to provide the administrator username and password.

Example

Oracle REST Data Services - Interactive Repair

  Enter a number to select the database pool to use or specify the database connection
    [1] default      jdbc:oracle:thin:@//localhost:1521/orcl
    [2] sales          jdbc:oracle:thin:@//localhost:1521/pdb1
    [3] Specify the database connection
  Choose [1]: 
  Provide database user name with administrator privileges.
    Enter the administrator username: SYS
    Enter the database password for SYS AS SYSDBA:

Note:

When you install Oracle REST Data Services, it attempts to find the Oracle Application Express (APEX) schema and creates a view. This view joins the relevant tables in the APEX schema to the tables in the Oracle REST Data Services schema. If you install Oracle REST Data Services before APEX, then Oracle REST Data Services cannot find the APEX schema and it creates a stub view in place of the missing APEX tables.

Oracle highly recommends that you install Oracle REST Data Services after APEX to ensure that the APEX objects, which Oracle REST Data Services needs to query, are present. If you install Oracle REST Data Services before APEX, then use the repair command to force Oracle REST Data Services to reconstruct the queries against the APEX schema.

2.2.1 Command Options for Repair CLI

This section describes the interactive and non-interactive install repair CLI commands used to repair the ORDS schema in the database.

Table 2-3 Command Options for Repair CLI

Command Description
--admin-user <string> Specifies the user with privileges to install, upgrade, or uninstall ORDS in the database.
--bequeath-connect Specifies that the bequeath connect enables clients to connect directly to an Oracle database bypassing the network listener. This occurs if client is on the same server as the database.
--db-custom-url <url> Specifies the custom database url for the database connection.
--db-hostname <string> Specifies the database host name.
--db-pool <string> Specifies the name of the database connection pool.
--db-port <int> Specifies the database port.
--db-servicename <string> Specifies the database service name.
--db-sid <string> Specifies the system identifier which is a unique name that identifies the database.
--db-tns-alias <string> Specifies the TNS alias name that exist in the tnsnames.ora file.
--db-tns-dir <folder> Specifies the folder where the tnsnames.ora file is located.
-h, --help Shows how to use and provides information of the command.
-i, --interactive Prompts for the required information.
--log-folder <folder> Writes the logs from the install, upgrade or uninstall to the log folder. If this option is omitted, then the output is written to standard output.
--password-stdin Specifies to use this option to read the password value from standard input when redirecting input to a file or here document when you run the ords install or uninstall command non-interactively, If the --password-stdin option and the redirection symbol ("<" or "<<") are omitted, then you are prompted for the password(s) for the command option(s) that require the password.
--pdb-exclude <string...> Specifies the list of PDBs that are excluded from the ORDS install, upgrade, or repair. This option only applies to the CDB.
--pdb-open-readwrite <string...> Specifies the list of PDBs that are read only or closed to be open for read write for ORDS install, upgrade, repair or uninstall. This option only applies to the CDB.
--pdb-open-readwrite-all Specifies to open all PDBs in read write mode if their status is either closed or read only. This option only applies to the CDB.
--pdb-skip-closed Skips PDBs that are closed (mounted). This option only applies to the CDB.
--pdb-skip-readonly Skips PDBs that are read-only. This option only applies to the CDB.

2.3 Upgrading Oracle REST Data Services

This section describes how to upgrade to Oracle REST Data Services latest release.

Upgrading from ORDS 22.1.x or Later

If you are upgrading from ORDS 22.1.x or later releases, then refer to the following sections:

Upgrading from ORDS 21.4.x or earlier releases

If you are upgrading from ORDS 21.4.x or earlier releases, then refer to Migrate Configuration and Upgrade from ORDS 21.4.x or Earlier Releases

2.3.1 Migrate Configuration and Upgrade from ORDS 21.4.x or Earlier Releases

This section describes how to upgrade from ORDS 21.4.x or earlier releases.

To upgrade to ORDS 22.x from ORDS 21.4.x or earlier releases:

Provide the location of your configuration files that were used from your previous ORDS release (21.4.x or earlier). If you do not know the location, then specify the command using your previous ORDS release (21.4.x or earlier).

java -jar /path/to/earlierRelease/ords.war configdir

Example:
java -jar /path/to/earlierRelease/ords.war configdir
 
INFO   The config.dir value is /path/to/legacy/conf

In the preceding example /path/to/legacy/conf is configuration location

You can upgrade to ORDS 22.x release from ORDS 21.4.x or earlier releases in the following two ways:
2.3.1.1 Interactive Upgrade

Interactive Upgrade

Specify the following install command to migrate your configuration, and to upgrade ORDS in your specified database:
ords --config <folder> install -i --legacy-config <folder> --log-folder
    <folder>

Table 2-4 Interactive Upgrade Commands

Command Options Description
--config <folder>

Specifie the configuration folder to store your migrated configuration files.

Your configuration folder must not be in the same location as your legacy configuration folder. Otherwise, an error occurs.
-i or --interactive Specifies to run the command in interactive mode. You are then prompted for the information.
--legacy-config <folder> Specifies to provide the location of your configuration files that you used for ORDS 21.4.x or earlier releases.
--log-folder <folder> Specifies to provide the location to store your log file. 
ords --config /path/to/new/conf install -i --legacy-config /path/to/legacy/conf --log-folder /path/to/logs
 
ORDS: Release 22.x Production on Wed <month> 1 12:00:01 <year>
 
Copyright (c) 2010, 2022, Oracle.
 
Configuration:
  /path/to/new/conf
 
Oracle REST Data Services 22.x.x.rNNNNNN - Migrate Configuration
Migrating ORDS Configuration files located at /path/to/legacy/conf/ords
. . .
Legacy configuration files located /path/to/legacy/conf/ords are no longer being used to configure ORDS or its connection pools.  Your migrated configuration files are now located at /path/to/new/conf
 
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 [1]:

Select option 1 to upgrade ORDS in your database. A list of the migrated database pools are displayed. The legacy configuration database pool named apex is migrated, and the name is changed to default. 

Enter a number to select the database pool to use or specify the database connection
  [1] default      jdbc:oracle:thin:@//localhost:1521/orcl
  [2] hr           jdbc:oracle:thin:@//localhost:1521/pdb1
  [3] Specify the database connection
Choose [1]:

Select the database pool that you want to upgrade. Then you are prompted for the username and password of the administrator.

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.
Connecting to database user: ORDS_PUBLIC_USER url:jdbc:oracle:thin:@//localhost:1521/orcl
Created folder /path/to/logs/
INFO   Upgrading Oracle REST Data Services schema 21.4.2.rNNNNNN to version 22.x.x.rNNNNNN in ORCL
INFO   Completed upgrade for Oracle REST Data Services version 22.x.x.rNNNNNN. Elapsed time: 00:00:02.585
 
INFO   Log file written to /path/to/logs/ords_upgrade_<date-time>.log

This completes the upgradation process.

If you have multiple database pools to upgrade, then perform the install command again excluding the --legacy-config option since you have already migrated the configuration. 

ords --config /path/to/new/conf install -i --log-folder /path/to/logs
 
. . .
  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 [1]:

Select option 1

2.3.1.2 Silent Upgrade
To do a silent upgrade you must provide the following:
  • Legacy configuration location
  • The database password for the administrator user
The database password can be in a text file or using a Here document. If you are using a text file, then ensure to delete the text file when the upgradation is complete. The legacy configuration database pool named apex is migrated, and the name is changed to default.

If you omit the --database-pool <name> option, then it uses the default database pool to upgrade.

Using a Text File

You can redirect standard input using a text file that contains the password. Delete the text file once you complete the upgrade operation.
ords --config <folder> install --admin-user <user> --legacy-config <folder> --log-folder <folder>
      --password-stdin <  <text-file>

Example of using a <text-file> with 1 password

The text file contains 1 password for --admin-user <user>

ords --config /path/to/new/conf install --admin-user SYS --legacy-config /path/to/legacy/conf 
--log-folder /path/to/logs --password-stdin < adminpwd.txt

Using a Here Document

You can redirect Standard Input using the Here document (also known as heredoc) for the password(s). The heredoc consists of the << redirection operator followed by a delimiter token once the upgrade operation completes. If you are using a script that contains the password(s), then delete the script. 

ords --config <folder> install --admin-user <user> --legacy-config <folder> --log-folder <folder> --password-stdin <<  <delimiter-token>
> <password>
> <delimiter-token>
ords --config /path/to/new/conf install --admin-user SYS --legacy-config /path/to/legacy/conf --log-folder
        /path/to/logs --password-stdin << EOF
> <password for admin-user>
> EOF

2.4 Uninstalling Oracle REST Data Services

This section describes the interactive and non-interactive uninstallation CLI commands used to uninstall the ORDS schema.

2.4.1 Interactive Uninstall CLI

The interactive uninstallation CLI prompts you for the necessary information to uninstall the ORDS schema, ORDS proxy user and related database objects from the database.

To be prompted, you must execute one of the following commands:
  • Specify the uninstall command only. For example: $ ords uninstall

    Note:

    Assumes that the configuration folder is specified through the environment variable or you are defaulting to the current working directory.
  • Specify the option --config <configuration folder> followed by the uninstall command. For example: $ ords --config /path/to/config uninstall
  • Specify the --interactive option.

    For example: $ ords --config /path/to/config uninstall --interactive

Table 2-5 Interactive Uninstall Prompts

Prompt Number Prompt Description
1. 
Enter a number to select the database pool to use or create the database pool
[1] default jdbc:oracle:thin:@//localhost:1521/orcl
[2] Specify the database connection 
Choose [1]:
 Refer to Entering a Number to Select the Database Pool
2. 
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]:
 Refer to Entering a Number to Select the Database Connection Type
3. 
Enter the database host name [localhost]:
 Refer to Entering the Database Host Name
4. 
Enter the database listen port [1521]:
 Refer to Entering the Database Listener Port
5. 
Enter the database service name [orcl]:
 Refer to Entering the Database Service Name
6. 
Enter the TNS location: /path/to/tns
 Refer to Entering the TNS Location
7. 
Enter a number to select the TNS Network alias to use
    [1] DB1  ...CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1)))
    [2] DB2  ...CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb2)))     
    [3] DB3  ...CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb3)))     
  Choose [1]:
Refer to Entering a Number to Select the TNS Network Alias
8. 
Enter the Custom database URL:
 Refer to Entering the Custom Database URL
9. 
Provide database username with administrator privileges.
    Enter the administrator username: SYS
Refer to Entering the Administrator Username
10. Enter the database password for SYS AS SYSDBA: Refer to Entering the Database Password for SYS AS SYSDBA
11. 
ORDS contains 1 enabled/disabled schema: HR

  Uninstall ORDS in the database
    [1] Yes
    [2] No
  Choose [2]:
 Refer to Entering an Option for Uninstalling ORDS
2.4.1.1 Entering a Number to Select the Database Pool
You get this prompt only if the configuration pool(s) exists in the configuration folder.
  • If you select a database pool, then it bypasses the database connection type and prompts you for the administrator username and password.
  • If you select option 2, then you get the prompt number 2.

Note:

If the configuration pool(s) does not exist in your configuration folder, then it prompts you for the database connection type.
2.4.1.2 Entering a Number to Select the Database Connection Type

Select the database connection type.

2.4.1.3 Entering the Database Host Name

Specify the database host name for basic connection.

2.4.1.4 Entering the Database Listener Port

Specify the database port for a basic connection.

2.4.1.5 Entering the Database Service Name
Specify the service name. The default value for the service name is obtained from one of the following in the given sequence:
  1. The service name setting in the ORDS configuration file. If not present, then
  2. It checks for ORACLE_PDB environment variable. If undefined, then
  3. It checks for ORACLE_SID environment variable. If undefined, then
  4. It uses orcl as the default value.
2.4.1.6 Entering the TNS Location

Specify the location for tnsnames.ora file. The location can be to the folder where tnsnames.ora file is located, or a zip file that contains the tnsnames.ora file for TNS connection.

2.4.1.7 Entering a Number to Select the TNS Network Alias

Select the TNS alias name from the list of TNS network alias names displayed.

2.4.1.8 Entering the Custom Database URL

Specify the custom database URL for custom database URL connection.

2.4.1.9 Entering the Administrator Username

Specify a user who has installer privileges to install or upgrade ORDS in the database.

See Also:

Privileges Granted to the Oracle REST Data Services Users
2.4.1.10 Entering the Database Password for SYS AS SYSDBA

Specify the administrator user password.

2.4.1.11 Entering an Option for Uninstalling ORDS

If the ORDS enabled or disabled schemas exist, and the --force command option is not specified, then it displays the enabled or disabled schemas and prompts for a confirmation to uninstall ORDS in the database.

If the --force command option is specified, then the uninstall confirmation is not shown,

2.4.2 Non-Interactive Uninstall CLI

The non-interactive uninstall CLI uninstalls the ORDS schema, ORDS proxy user, and related database objects from the database.

Provide the command options for the non-interactive uninstall CLI. If you are missing a required option (for example, db-port for a connection), then you are not prompted. Instead, an error is returned.

2.4.2.1 Using Input Redirection

This section describes how to redirect standard input using Here document or to a file.

Redirect Standard Input to a File

Redirect STDIN to a file that contains the password. In the following example, the file contains one password.

Example:
$ cat adminpwd.txt
password

The password belongs to the administrator user on the command line (for example: SYS). This example assumes that the config folder and the db1 pool already exists.

ords --config /path/to/conf uninstall --db-pool db1 --admin-user SYS --log-folder /path/to/logs --password-stdin < adminpwd.txt

Redirect Standard Input using Here Document

Redirect STDIN using the Here document (also known as heredoc) for the password. The heredoc consists of the << redirection operator followed by a delimiter token.

The password must be on a separate line and it is ended by the delimiter token.

Example:

ords --config <configuration_folder> uninstall --db-pool <pool_name> --admin-user <username> 
--db-hostname <host> --db-port <port_number> --db-servicename <service_name> --log-folder <log_folder> 
--password-stdin << EOF
<password>
EOF

The password belongs to the the administrator user on the command line. (for example: SYS). 

ords --config /path/to/config uninstall --db-pool db1 --admin-user SYS --db-hostname
      localhost --db-port 1521 --db-servicename orcl --log-folder /path/to/logs << EOF
<password>
EOF
2.4.2.2 Command options for Uninstall CLI

Table 2-6 Command Options for Uninstall CLI

Options Description
--admin-user <USERNAME> Specifies the user with privileges to install, upgrade, repair, or uninstall ORDS in the database.
--bequeath-connect Specifies that the bequeath connect enables clients to connect directly to an Oracle database bypassing the network listener. This occurs if client is on the same server as the database.
--config <CONFIG_PATH> Specifies the path to the folder containing the configuration files.
--db-pool <POOL_NAME> Specifies the name of the database pool.
--db-custom-url <URL> Specifies the user specified custom url.
-db-hostname <HOST>- Specifies the database host name.
--db-port <PORT> Specifies the database port.
--db-servicename <SERVICENAME> Specifies the database service name.
--db-sid <SID> Specifies the database SID.
--db-tns-alias <ALIAS_NAME> Specifies the tns alias name that exists in the tnsnames.ora file.
-db-tns-dir <TNS_DIR>- Specifies the folder where the tnsnames.ora file is located.
--force Specifies that you can uninstall ORDS from the database regardless of the number of ORDS enabled/disabled schemas.
--help, -h Shows how to use the command.
--interactive, -i Prompts the user for all the required information.
--log-folder

Writes the logs from the install, upgrade, repair, or uninstall to the log folder. If this option is omitted, then the output is written to the standard output.
-password-stdin Specifies when you want to run the ORDS install or uninstall command non-interactively, you can use this option to read the password value from standard input when redirecting input to a file or here document. If the --password-stdin option and the redirection symbol ("<" or "<<") are omitted, you are prompted for the password(s) for the command option(s) that requires the password.
--pdb-open-readwrite-all

Specifies the option applicable for CDB.

Specifies whether to open all PDBs in read write mode if their status is either closed or read only.
--pdb-open-readwrite <(PDB...)>

Specifies the option applicable for CDB.

Specifies the list of PDBs that are readonly or closed to be open for readwrite to uninstall ORDS.

2.5 Updating the Configuration Settings

The ORDS config command is used to update and edit the contents of the global and database pool specific settings in the configuration files.

The config command comprises a list of sub-commands. The config command has the --db-pool <POOL_NAME> option that identifies the database pool to which you want to apply the setting. If this option is omitted, then the default database pool settings get updated.

ords config [OPTIONS] [SUB-COMMAND]

Where:
  • OPTIONS:
    • --db-pool <string>: The name of the database connection pool to configure
    • -h, --help: Show usage information for the command
  • SUB-COMMAND: delete, get, info, list, secret, set, and user

info

Following is the list of sub-commands along with the descriptions:

Shows the description of the specified key, explaining its purpose. Indicates if it is a sensitive value and what is the default value.

Example: ords config info <KEY>

set

Sets the config key to a value. If you wish the setting to be set at a global level, then use the --global option.

Example: ords config set [--global] <KEY> <VALUE>

secret

Sets the sensitive values (for example: connection pools password). Use ords config secret to set the sensitive values. Some values are sensitive (example: db.password). Per Oracle Secure Coding Standards such values must not be passed as command line arguments as this leads to leakage of the value (appears in ps output).

Example: ords config secret --password-stdin <KEY> < <file>

Note:

If an attempt is made to do ords config set on a sensitive value, an error is returned.

Non-Interactive example:

If the --password-stdin option is specified, then the value is read from stdin.

Example:

ords config secret --password-stdin db.password < secret.txt

Interactive Example

If --password-stdin is not specified, then the user is prompted to enter and confirm the secret value.

Example:

ords config secret db.password

get

Echo the config value to stdout. If the value is a secret then it's value will be replaced by the text ******. If you wish to see the plain text of the secret, then use the --secret argument.

Example: ords config get [--global] [--secret] <KEY>

list

Show all explicitly configured values for the pool settings and global settings. Secret values are replaced with the text ******. Use -–include-defaults to list all the settings including those with the default values.

Example: ords config list [--include-defaults]

The information lists the name of the setting, value, and the source location (global configuration, pool specific configuration, or global/pool wallet. The table includes the columns such as
  • Setting: Name of the setting
  • Value: The value of the setting
  • Source: Location of the global/pool specific setting

For example the output is similar to the following:

Configuration: /path/to/conf/
 
Database pool: default
 
Setting                        Value                      Source
-------------------         ---------------            ------
db.connectionType           basic                      Pool       
db.hostname                 localhost                  Pool       
db.password                 ******                     Pool Wallet
db.port                     1521                       Pool       
db.servicename              orcl                       Pool       
db.username                 ORDS_PUBLIC_USER           Pool       
feature.sdw                 true                       Pool       
restEnabledSql.active       true                       Pool
The column Source indicates the origin of the setting value which can be any one of the following:
  • Default: Uses the default value for the setting
  • Global: The value is specified in the global/settings.xml configuration file
  • Pool:The value is explicitly configured in the pool
  • Pool Wallet:The (sensitive) value is explicitly configured in the pool wallet

user delete

Deletes the key from the configuration.

Example: delete [--global]

user add

Add a user and its password and role(s) in the credentials file. If the user already exists, then it is updated.

Non-interactive

Include option --password-stdin to read from STDIN. Requires the password file of the user.

Example: 
ords config user add --password-stdin <USERNAME> roles <ROLES> < userpwd.txt

Interactive

Prompts for the password if --password-stdin option is omitted.

Example: 
ords config user add <USERNAME> roles <ROLES>

Enter the password:
Confirm password:

user delete

ords config user delete <USERNAME>

Delete the specified user.

user get

ords config user get <USERNAME>

Retrieve the specified user.

user list

ords config user list

Lists the users in the credential file.

See Also: