After upgrading to Oracle 12c, 19c, database connections take a long time (10 sec or more), before the connections are established. The connection time was less than 0.5 sec in 11g.
This is caused by a setting in the used Oracle Clusterware/Grid Infrastructure service names for the database.
The AQ HA notifications is configured as true in the service like:
$ srvctl config service -s PROD_JAVA-APP -d PROD_DB Service name: PROD_JAVA-APP Server pool: Cardinality: 2 Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: true <<<<<<< Global: false Commit Outcome: false .../...
This feature was in use or required in 11g. But is deprecated, although still working, in Oracle 12c and higher.
According the Oracle 12 Database Development Guide :
“An important component of high availability is a notification mechanism called Fast Application Notification (FAN). FAN notifies other processes about configuration and service level information that includes service status changes, such as UP or DOWN events.
FAN provides the ability to immediately terminate an active transaction when an instance or server fails. FAN integrated Oracle clients receive the events and respond.
When you use Oracle Database 10g or Oracle Database 11g OCI or ODP.NET clients, you must enable Oracle Advanced Queuing (AQ) HA notifications for your Cluster services.
Beginning with Oracle Database 12c Release 1 (12.1), ONS is the primary notification mechanism for a new client (Oracle Database 12c Release 1 (12.1)) and a new server (Oracle Database 12c Release 1 (12.1)), while the AQ HA notification feature is deprecated and maintained only for backward compatibility when there is an old client (pre-Oracle Database 12c Release 1 (12.1)) or old server (pre-Oracle Database 12c Release 1 (12.1)).”
The new default setting in Oracle 12c, 19c, is false. But upgraded environments still have the original setting. After an upgrade the AQ HA setting can be disabled to false. Now connection time should be as fast as on the previous release:
$ srvctl modify service -s PROD_JAVA-APP -d PROD_DB -q false
One thought on “Delay in establishing RAC Database connections after upgrading from Oracle 11g.”
Do you understand why this setting has slowed down so much between releases? or is Oracle trying to force us away from the AQ based notification like this?