If you have an Active Data Guard configuration there are situations where you want to have a connection pool to a READ ONLY Standby database.
One situation is, direct all READ requests to this side and have the modifying tasks on the primary side. Another situation is, if you want to use some ORDS Data Guard endpoints, some of the requests need to be sent to a standby side.
In this blog post I’ll describe two ways on how to configure a connection pool for a READ ONLY database with ORDS 24.2.3.
Using a Read Write Service
I only would suggest using this way if you don’t need an endpoint pointing a read write service. If the goal is to create a service for the Read Write database service and one for the Read Only database service I strongly suggest using the second way.
[oracle@ordshost~]$ ords --config /etc/ords/config/ install
ORDS: Release 24.2 Production on Mon Sep 09 11:49:18 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
Oracle REST Data Services - Interactive Install
Enter a number to select the database pool to upgrade ORDS.
Additional options to Generate script, or Create an additional database pool.
[1] default jdbc:oracle:thin:@//localhost:1521/cdb2.example.com
[2] free jdbc:oracle:thin:@//192.168.1.141:1521/freepdb1
[G] Generate script to upgrade ORDS in all the database pools
[C] Create an additional database pool
Choose [1]: C
Enter the database pool name: ropool
Enter a number to select the TNS net service name to use from /u01/app/oracle/product/rdbms21c/2111/network/admin/tnsnames.ora or specify the database connection
[1] CDB1_HA SERVICE_NAME=cdb1_ha.example.com
[2] CDB1_SITE1 SERVICE_NAME=cdb1_site1.example.com
[3] CDB1_SITE2 SERVICE_NAME=cdb1_site2.example.com
[4] CDB1_STBY SERVICE_NAME=cdb1_ha.example.com
[S] Specify the database connection
Choose [1]: 4
Provide database user name with administrator privileges.
Enter the administrator username: sys
Enter the database password for SYS AS SYSDBA:
Retrieving information........
Completed verifying Oracle REST Data Services schema version 24.2.3.r2011847.
Enter a number to update the value or select option A to Accept and Continue
[1] Connection Type: TNS
[2] TNS Connection: TNS_NAME=CDB1_STBY TNS_FOLDER=/u01/app/oracle/product/rdbms21c/2111/network/admin
Administrator User: SYS AS SYSDBA
[3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
[4] Additional Feature: Database Actions
[5] Configure and start ORDS in Standalone Mode: Yes
[6] Protocol: HTTP
[7] HTTP Port: 8081
[A] Accept and Continue - Update configuration
[Q] Quit - Do not proceed. No changes
Choose [A]: A
I’ve already created some database pools with tnsnames config, therefore I got the connections included in the tnsnames.ora as suggestion. As you can see on line 24 my CDB1_STBY definition (should be the connection to the standby database) is using the ha (read write service), else it would not be possible to install ORDS.
After I’ve configured everything, I change the service in the tnsnames.ora to standby read only service.
cdb1_stby=
(DESCRIPTION_LIST=
(FAILOVER=true)
(LOAD_BALANCE=no)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST= dghost1)(PORT=1521))
(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=cdb1_stby.example.com))
#(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=cdb1_ha.example.com))
)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=dghost2)(PORT=1521))
(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=cdb1_stby.example.com))
#(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=cdb1_ha.example.com))
)
)
Finally I can restart my ORDS configuration in the way I’ll start ORDS and you’ll see your ropool in the logfiles or on the screen.
Mapped local pools from /etc/ords/config/databases:
/ords/ => default => VALID
/ords/free/ => free => VALID
/ords/ropool/ => ropool => VALID
Configure a Read Only pool with –config-only
The preferred way of configuring a Read Only pool is to have already a pool connected to the read write service and configure the pool via command line and the “–config-only” parameter.
The only tricky thing is during the installation/configuration of an ORDS pool the password for the ORDS_PUBLIC_USER is set.
Mapped local pools from /etc/ords/config/databases:
/ords/ => default => VALID
/ords/rwpool/ => rwpool => VALID
In my configuration I do have the default pool configured and additionally ORDS installed in the rwpool (read write pool).
Now I can configure the ropool:
[oracle@ordshost~]$ ords --config /etc/ords/config/ install \
> --config-only \
> --db-pool ropool \
> --db-tns-alias ropool \
> --db-tns-dir /u01/app/oracle/product/rdbms21c/2111/network/admin \
> --feature-db-api true
ORDS: Release 24.2 Production on Tue Sep 10 07:23:20 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
Oracle REST Data Services - Non-Interactive Install
The setting named: db.connectionType was set to: tns in configuration: ropool
The setting named: db.tnsAliasName was set to: ropool in configuration: ropool
The setting named: db.tnsDirectory was set to: /u01/app/oracle/product/rdbms21c/2111/network/admin in configuration: ropool
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: ropool
The setting named: db.password was set to: ****** in configuration: ropool
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: ropool
2024-09-10T07:23:20.872Z INFO To run in standalone mode, use the ords serve command:
2024-09-10T07:23:20.873Z INFO ords --config /etc/ords/config serve
2024-09-10T07:23:20.873Z INFO Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
Due to the situation that we do not know the password set during the configuration/installation of the rwpool we have to reset the password for the ORDS_PUBLIC_USER in the database.
SQL> alter user ORDS_PUBLIC_USER identified by ****** ;
User altered.
SQL>
After we’ve set the password in the database we can set the password for both pools (rwpool/ropool), if we do have other pools configured for this database we have to reset the password for each pool
ords --config /etc/ords/config/ config --db-pool rwpool secret db.password
ORDS: Release 24.2 Production on Tue Sep 10 07:24:53 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
Enter the database password:
Confirm password:
Reset the password for the ropool:
ords --config /etc/ords/config/ config --db-pool ropool secret db.password
ORDS: Release 24.2 Production on Tue Sep 10 07:25:30 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
Enter the database password:
Confirm password:
The setting named: db.password was set to: ****** in configuration: ropool
After that we can start ORDS and have the ropool and the rwpool configured:
Mapped local pools from /etc/ords/config/databases:
/ords/ => default => VALID
/ords/ropool/ => ropool => VALID
/ords/rwpool/ => rwpool => VALID
