This section explains how to configure Oracle REST Data Services for connecting to multiple databases for routing requests, and it refers to other documentation sources for other configuration information.
Note:
Oracle REST Data Services must be restarted after making configuration changes. See your application server documentation for information on how to restart applications.
Topics:
Oracle REST Data Services supports the ability to connect to more than one database. This section describes different strategies for routing requests to the appropriate database.
Topics:
Oracle REST Data Services supports a number of different strategies for routing requests to the appropriate database. All of these strategies rely on examining the request URL and choosing the database based on some kind of match against the URL. It is useful to recap the pertinent portions of a request URL. Consider the following URL:
https://www.example.com/ords/sales/f?p=1:1
This URL consists of the following sections:
Protocol: https
Host Name: www.example.com
Context Root: /ords
The context root is the location at which Oracle REST Data Services is deployed on the application server.
Request Path: /sales/f?p=1.1
This is the portion of the request URL relative to the context root.
For different applications, it may be important to route requests based on certain prefixes in the request path or certain prefixes in the full request URL.
There are two steps to configuring multiple databases:
Configuring the database connection information
Configuring which requests are routed to which database
When you first configure Oracle REST Data Services, you configure a default database connection named: apex
. You can create additional database connections using the setup
command.
Tip:
To see full help for the setup
command type:
java -jar ords.war help setup
To create a database connection type the following:
java -jar ords.war setup --database <database name>
Where:
<database name>
is the name you want to give the database connection.
You are prompted to enter the information required to configure the database. After you have configured the additional databases, define the rules for how requests are routed to the appropriate database.
You create request routing rules using the map-url
command.
Tip:
To see full help for the map-url
command type:
java -jar ords.war help map-url
If you want to route requests based just on matching a prefix in the request path portion of the URL, use the map-url
command as follows:
java -jar ords.war map-url --type base-path --workspace-id <workspace name> <path prefix> <database name>
Where:
<workspace name>
is the name of the Oracle Application Express workspace where RESTful services for this connection are defined. This may be omitted if RESTful Services are not being used.
<path prefix>
is the prefix that must occur at the start of the request path.
<database name>
is the name of the database connection configured in the previous step.
Related Topics
Assuming Oracle REST Data Services is deployed on a system named example.com
at the context path /ords
, then create the following rule:
java -jar ords.war map-url --type base-path --workspace-id sales_rest /sales sales_db
This rule means that any requests matching https://example.com/ords/sales/...
are routed to the sales_db
database connection. The sales_rest
workspace defined within the sales_db
database is searched for RESTful Services definitions.
The previous rule matches all of the following requests:
https://example.com/ords/sales/f?p=1:1
https://example.com/ords/sales/leads/
https://www.example.com/ords/sales/forecasting.report?month=jan (If www.example.com resolves to the same system as example.com.)
The previous rule does not match of any of the following requests:
http://example.com/ords/sales/f?p=1:1 (The protocol is wrong.) https://example.com:8080/ords/sales/f?p=1:1 (The port is wrong: 443 is default for https, but don't specify if using default.) https://example.com/ords/f?p=1:1 (Missing the /sales prefix.) https://example.com/pls/sales/leads/ (The context path is wrong.)
If you want to route requests based on a match of the request URL prefix, use the map-url
command as follows:
java -jar ords.war map-url --type base-url --workspace-id <workspace name> <url prefix> <database name>
Where:
<workspace name>
is the name of the Oracle Application Express workspace where RESTful services for this connection are defined. This may be omitted if RESTful Services are not being used.
<url prefix>
is the prefix with which the request URL must start.
<database name>
is the name of the database connection.
Assuming Oracle REST Data Services is deployed on a system named example.com
at the context path /ords
, then create the following rule:
java -jar ords.war map-url --type base-url --workspace-id sales_rest https://example.com/ords/sales sales_db
This rule means that any requests matching https://example.com/ords/sales/...
are routed to the sales_db
database connection. The sales_rest
workspace defined within the sales_db
database is searched for RESTful Services definitions.
The previous rule matches all of the following requests:
https://example.com/ords/sales/f?p=1:1 https://example.com/ords/sales/leads/ https://example.com/ords/sales/forecasting.report?month=jan
The previous rule does not match of any of the following requests:
http://example.com/ords/sales/f?p=1:1 (The protocol is wrong.) https://example.com:8080/ords/sales/f?p=1:1 (The port is wrong: 443 is default for https, but don't specify if using default.) https://example.com/ords/f?p=1:1 (Missing the /sales segment of the base URL.) https://example.com/pls/sales/leads/ (The context path is wrong.) https://www.example.com/ords/sales/forecasting.report?month=jan (The host name is wrong.)
This section outlines installing, configuring, upgrading and uninstalling Oracle REST Data Services in a multitenant container database.
This section describes the installation choices available for installing Oracle REST Data Services into a multitenant container database (CDB).
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.
Note:
If you want to install directly into a PDB (not connected to Root during installation), see Advanced Installation Using Command-Line Prompts for more information.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.
Note:
ORDS_METADATA
and ORDS_PUBLIC_USER
are installed in the seed container, and the default and temporary tables 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 GuideThis 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.
You must provide the SYS AS SYSDBA
credentials in the Root (CDB$ROOT
) container to perform the installation.
Note:
Verify that the property,cdb.common.schema=true
does not exist in your Oracle REST Data Services parameter file. If this property exists, then either set the property to false
or remove it from the Oracle REST Data Services parameter file.This section describes the advanced installation prompts for installing Oracle REST Data Services into a CDB to enable multiple Oracle REST Data Services releases.
Navigate to the folder where you unzipped the Oracle REST Data Services installation kit.
Enter the following command:
java -jar ords.war install advanced
When prompted, enter the database connection information for your CDB.
Enter the name of the database server[localhost]: Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name:(for example, cdb.example.com)
Verify the Oracle REST Data Services installation.
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Accept or enter 1 (the default) to install Oracle REST Data Services into the CDB and all of its PDBs.
Enter and confirm the ORDS_PUBLIC_USER password: Enter the database password for ORDS_PUBLIC_USER: Confirm password: Requires SYS AS SYSDBA to verify Oracle REST Data Services schema. Enter the database password for SYS AS SYSDBA: Confirm password:
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 18.1.1.<JulianDay.Time> (mode is READ ONLY, open for READ/WRITE) PDB PDBName1 - install ORDS 18.1.1.<JulianDay.Time> PDB PDBName2 - install ORDS 18.1.1.<JulianDay.Time>
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. See Advanced Installation Using Command-Line Prompts for more information.
Note:
To use the pluggable mapping feature, see Making All PDBs Addressable by Oracle REST Data Services (Pluggable Mapping) for more information.Silent installation reads the properties from the Oracle REST Data Services parameter file. If the property, cdb.common.schema=true
exists in the Oracle REST Data Services parameter file, then set the property to false, cdb.common.schema=false.
java –jar ords.war install simple java –jar ords.war
This section describes how to install when you have same releases of Oracle REST Data Services.
Note:
This option will NOT allow you to have multiple releases of Oracle REST Data Services in the PDBs. If you want to have multiple Oracle REST Data Services releases in the PDBs, see Installation Enabling Multiple Releases (Recommended)When Oracle REST Data Services is installed into a CDB, it is installed in the root container, the seed container, and any existing PDBs. The root container (CDB$ROOT
) includes the ORDS_METADATA
schema to store the common database objects for Oracle REST Data Services packages, functions, procedures, and views. It also includes the proxy user Oracle REST Data Services public user (ORDS_PUBLIC_USER
).
The seed container (PDB$SEED
) includes the ORDS_METADATA
schema and the Oracle REST Data Services public user. You can create a new PDB by copying PDB$SEED
and creating metadata links back to the common database objects in the ORDS_METADATA
schema within the CDB$ROOT
. As a result, there are multiple copies of the Oracle REST Data Services tables and only single copies of the Oracle REST Data Services packages, functions, procedures, and views. Thus, each PDB has the ORDS_METADATA
schema and its own copy of the Oracle REST Data Services tables, so that it can hold the metadata for the Oracle REST Data Services application within that PDB. Each PDB also has its own Oracle REST Data Services public user.
Related Topics
You must provide the SYS AS SYSDBA
credentials in the Root (CDB$ROOT
) container to perform the installation.
If you want to have the same Oracle REST Data Services release for all the PDBs when you install or upgrade, then you must specify the commonSchema
option from the command-line or specify cdb.common.schema=true
in the parameter file.
Note:
If the above option is missing, then it defaults to installation that enables multiple releases of Oracle REST Data Services in the PDBs.Related Topics
This section describes the advanced installation prompts for installing Oracle REST Data Services into a CDB requiring the same release of Oracle REST Data Services release.
To install Oracle REST Data Services into a CDB requiring the same release of Oracle REST Data Services release, perform the following steps:
Navigate to the folder where you unzipped the Oracle REST Data Services installation kit.
Enter the following command:
java -jar ords.war install --commonSchema advanced
When prompted, enter the database connection information for your CDB:
Enter the name of the database server [localhost]: Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name: (for example, cdb.example.com)
Verify the Oracle REST Data Services installation:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: Accept or enter 1 (the default) to install Oracle REST Data Services into the CDB and all of its PDBs Enter and confirm the ORDS_PUBLIC_USER password: Enter the database password for ORDS_PUBLIC_USER: Confirm password: Requires SYS AS SYSDBA to verify Oracle REST Data Services schema. Enter the database password for SYS AS SYSDBA: Confirm password:
Retrieving information.... Your database connection is to a CDB. ORDS will be installed in the CDB and PDBs. Root CDB$ROOT - install ORDS 18.1.1.<JulianDay.Time> PDB PDB$SEED - install ORDS 18.1.1.<JulianDay.Time> (mode is READ ONLY, open for READ/WRITE) PDB PDBName1 - install ORDS 18.1.1.<JulianDay.Time> PDB PDBName2 - install ORDS 18.1.1.<JulianDay.Time>
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. See Advanced Installation Using Command-Line Prompts for more information.
The silent installation reads the properties from the Oracle REST Data Services parameter file. You must specify the option commonSchema
from the command-line, or specify the property commonSchema=true
in the Oracle REST Data Services parameter file.
java –jar ords.war install --commonSchema simple java –jar ords.war –commonSchema orSpecify
cdb.common.schema=true
in the Oracle REST Data Services parameter file to indicate that it requires the same release of Oracle REST Data Service for all the PDBsEdit the ORDS Parameter file and include cdb.common.schema=true
Run the following command:
java -jar ords.war
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:
java -jar ords.war
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.
Note:
If you are upgrading the Oracle REST Data Services CDB installation from Oracle REST Data Services 17.4 release or an earlier release to Oracle REST Data Services 18.1.1 release or later, then you will get a prompt to check if you want to migrate your Oracle REST Data Services CDB installation to enable multiple releases.Note:
When Oracle REST Data Services CDB installation is installed or migrated to enable multiple releases, you cannot change it to Oracle REST Data Services CDB installation using the same release.This section describes how to migrate Oracle REST Data Services CDB installation to enable multiple releases.
In prior releases, Oracle REST Data Services installation into the CDB required the same releases of Oracle REST Data Services in the PDBs. You can now enable multiple Oracle REST Data Services releases in the PDB.
Oracle REST Data Services is installed in the Root container (CDB$ROOT
)
The --commonSchema
option is not specified on the command-line
The cdb.common.schema
property does not exist in the Oracle REST Data Services parameter file
Example: Verify ORDS schema in Database Configuration apex with connection host: localhost port: 1521 service name: mycdb Retrieving information. Enter 1 if you want to migrate ORDS schema from the CDB Root to allow for different ORDS releases in the PDBs or 2 to skip this step [1]: Requires SYS AS SYSDBA to verify Oracle REST Data Services schema. Enter the database password for SYS AS SYSDBA: Confirm password: Retrieving information.... Your database connection is to a CDB. Root CDB$ROOT - migrate common ORDS schema to local ORDS schema 18.1.1.JulianDay.Time PDB PDB$SEED - upgrade and migrate common ORDS schema to local ORDS schema from version 3.0.12.263.15.32 to 18.1.1.JulianDay.Time (mode is READ ONLY, open for READ/WRITE) PDB ORCL - upgrade and migrate common ORDS schema to local ORDS schema from version 3.0.12.263.15.32 to 18.1.1. JulianDay.Time PDB ORDS - upgrade and migrate common ORDS schema to local ORDS schema from version 3.0.12.263.15.32 to 18.1.1.JulianDay.Time. . .
Pluggable mapping refers to the ability to make all PDBs in a CDB addressable by Oracle REST Data Services. To use this feature, follow the instructions 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, and that the PDBs connected to that CDB should be made addressable by Oracle REST Data Services.
The value of the db.serviceNameSuffix
parameter must match the value of the DB_DOMAIN
database initialization parameter, and it must start with a period (.). To set the value of the db.serviceNameSuffix
parameter:
In 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 SQL*Plus.
SQL> exit
If the DB_DOMAIN
value was not empty, then on the command line enter the command to create the key and value for the db.serviceNameSuffix
parameter and its DB_DOMAIN
. This will be used to add this entry to the Oracle REST Data Services configuration file.
echo db.serviceNameSuffix=.value-of-DB_DOMAIN > snsuffix.properties
For example, if DB_DOMAIN
is set to example.com
, enter the following:
echo db.serviceNameSuffix=.example.com > snsuffix.properties
If the db.serviceNameSuffix
parameter value is not defined, enter a command in the following format to add an entry to the configuration file:
java -jar ords.war set-properties --conf pool-name snsuffix.properties
Where pool-name is one of the following:
poolName
for a PL/SQL Gateway configuration
poolName_pu
for an Oracle REST Data Services RESTful Services configuration
poolName_rt
for an Application Express RESTful Services configuration
Example 1: You want to make PDBs in a CDB addressable globally. Specify defaults
by entering the following command:
java -jar ords.war set-properties --conf defaults snsuffix.properties
Note:
The approach shown in Example 1 (setting the property for all pools through the defaults.xml
file) is best for most use cases.
Example 2: You want to make PDBs in a CDB addressable for your PL/SQL Gateway, and your pool name is apex
. Enter the following command:
java -jar ords.war set-properties --conf apex snsuffix.properties
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 addressable for your Oracle REST Data Services RESTful Services, and your pool name is apex_pu
. Enter the following command:
java -jar ords.war set-properties --conf apex_pu snsuffix.properties
Example 4: You want to make PDBs in a CDB addressable for your Application Express RESTful Services and your pool name is apex_rt
. Enter the following command:
java -jar ords.war set-properties --conf apex_rt snsuffix.properties
Related Topics
Oracle REST Data Services support the Fast Connection Failover (FCF) feature of Oracle Real Application Clusters (Oracle RAC).
Oracle REST Data Services runs with the Universal Connection Pool (UCP) in all the Application Server environments that it supports, such as WebLogic, Tomcat, GlassFish. UCP in turn supports Fast Connection Failover . To enable FCF, Oracle Notification Service (ONS) must to be enabled. To enable ONS, add entries to the list of properties in the Oracle REST Data Services defaults.xml
configuration file as shown in the following code snippet:
<entry key="jdbc.enableONS">true</entry> <entry key= "jdbc.ONSConfig">nodes=racnode1:4200,racnode2:4200\nwalletfile=/oracle11/onswalletfile</entry>
<entry key="db.connectionType">customurl</entry> <entry key="db.customURL">jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST= (LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=prod_scan.example.com)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=ISPRD)))|</entry>
After updating the defaults.xml
configuration file, Oracle REST Data Services need to be restarted for the changes to take effect.
Unplanned outages: When RAC detects an instance failure, it generates a FAN Down event which FCF picks up. FCF then terminates all connections to the failed instance and directs all future requests to the surviving RAC instances.
Planned outages: For instance, when a Database Administrator (DBA) wants to gracefully shut down a RAC instance for performing some maintenance activity. The instance shutdown generates a FAN Planned Down event which FCF picks up. FCF then directs all new requests to other RAC instances and drains or allows currently active transactions to complete.
Note:
Long running transactions may need to be terminated forcefully.To configure security, caching, pre- and post- processing, environment, and Excel settings, see Using SQL Developer Oracle REST Data Services Administration (Optional).
This section explains how to configure the REST- Enabled SQL service.
Note:
Enabling the REST- Enabled SQL service enables authentication against the Oracle REST Data Service enabled database schemas. This makes the database schemas accessible over HTTPS, using the database password. Oracle highly recommends that you provide strong secure database passwordsLocate the folder where the Oracle REST Data Services configuration file is stored.
Open the defaults.xml
file and add: <entry key="restEnabledSql.active">true</entry>
.
Save the file.
Restart Oracle REST Data Services.
Locate the folder where the Oracle REST Data Services configuration file is stored.
Open the defaults.xml
file and update the value of the misc.pagination.maxRows
parameter:<entry key=”misc.pagination.maxRows”>1500</entry>
Note:
The default value formisc.pagination.maxRows
is 500.Save the file.
Restart Oracle REST Data Services.
This section explains how to configure a custom error page instead of the error page generated by Oracle REST Data Services.
To configure a custom error page, perform the following steps:
Locate the folder where the Oracle REST Data Services configuration file is stored.
Open the defaults.xml
file and update the value of the error.externalPath
parameter:
<entry key=”error.externalPath">/path/to/error/pages/folder/</entry>
Where:
/path/to/error/pages/folder
is the path to a folder containing files that define the error pages. The files are stored in {status}.html
format.
Where, {status}
is the HTTP status code for which you want to create a custom error page.
Save the file.
Restart Oracle REST Data Services.
Example 2-1 Configuring custom error page for “HTTP 404” status code
To configure a custom error page for the “HTTP 404 – Not Found” status, perform the following steps:
Create a file named 404.html
.
Save it under /usr/local/share/ords/error-pages/
folder.
Configure the error.externalPath
parameter to point to /usr/local/share/ords/errro-pages/
folder.
Save the file.
Restart Oracle REST Data Services.
For more information on how to develop RESTful Services for use with Oracle REST Data Services, see Developing Oracle REST Data Services Applications.