Database Resident Connection Pool (DRCP) and Data Guard

There are few restrictions that must be considered when using DRCP in a data guard configuration. This post shows these restrictions in action in a physical data guard configuration.

Staring DRCP
The DRCP in standby cannot be started unless the DRCP in the primary is up. Trying to do so will result in an error similar to below.

SQL>  exec dbms_connection_pool.start_pool();
BEGIN dbms_connection_pool.start_pool(); END;

*
ERROR at line 1:
ORA-56501: DRCP: Pool startup failed
ORA-56501: DRCP: Pool startup failed
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 4
ORA-06512: at line 1

Starting the DRCP in primary doesn't automatically start the DRCP in the standby. However, if dba_cpool_info is viewed it will show that pool is active. This is misleading. What this shows is the primary's DRCP status due to DG configuration. DRCP pool must be manually started on standby and one way to find out is to make a connection and see if connection. Another is to view on lsnrctl services for N000 and CMON processes.

Instance "testcdb3", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER
      "N000" established:1 refused:0 current:0 max:40000 state:ready
         CMON <machine: ip-172-31-11-54, pid: 6935>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=33362))

Stopping DRCP
Similar to starting, DRCP in the standby could only be stopped if the primary DRCP is stopped first. Trying to stop standby DRCP while the primary DRCP is up will result in an error similar to below.

SQL>  exec dbms_connection_pool.stop_pool();
BEGIN dbms_connection_pool.stop_pool(); END;

*
ERROR at line 1:
ORA-56506: DRCP: Pool shutdown failed
ORA-56506: DRCP: Pool shutdown failed
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 16
ORA-06512: at line 1

Stopping the DRCP in primary doesn't stop the DRCP in standby. However, the dba_cpool_info will show pool inactive, simialr to described in starting DRCP section.

ONNECTION_POOL                STATUS
------------------------------ ---------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

This is misleading. The DRCP will remain open in the standby and new connections could be established. While primary DRCP is down , pool on standby could be stopped. However, once stopped it cannot be started again until primary is started as described earlier.

Auto Starting DRCP
If DRCP is active on the primary DB, then restaritng the primary DB will automatically will bring up the DRCP as well.
If DRCP is active on standby DB then restarting the standby DB will not automatically will bring up the pool even if pool on primary is also up. It has to be manually started after restart of the standby DB. This is due to bug 18116889.
One way to ensure DRCP always start is to use startup trigger similar to below.

create or replace trigger start_drcp after startup on database
begin
	sys.dbms_system.ksdwrt(2, 'Starting DRCP ...');
	sys.dbms_connection_pool.start_pool();
	sys.dbms_system.ksdwrt(2, 'DRCP started.');

end;
/

Startup trigger is also governed by earlier mentioned restrictions. For example starting standby while primary is down will not start the pool on the standny. If startup trigger is there it will give an error as below.

ORA-04088: error during execution of trigger 'SYS.START_DRCP'
ORA-56501: DRCP: Pool startup failed
ORA-56501: DRCP: Pool startup failed

Changing DRCP Configuration
DRCP related configuration values could only be changed in the primary. The new values take effect immediately on the primary but on the standby then DRCP would need a restart for changes to take effect. This means stopping and starting the primary pool as well since standby pool cannot be stopped while primary pool is up.
Below is an example where number of brokers is changed from 1 to 2.

ONNECTION_POOL                NUM_CBROK
------------------------------ ---------
SYS_DEFAULT_CONNECTION_POOL    1

EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','NUM_CBROK',2);

ONNECTION_POOL                NUM_CBROK
------------------------------ ---------
SYS_DEFAULT_CONNECTION_POOL    2

Changes are immediately visible on the primary and could see two broker processes as well.

Instance "testcdb", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
      "N000" established:0 refused:0 current:0 max:40000 state:ready
         CMON <machine: ip-172-31-15-132, pid: 11969>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15748))
      "N001" established:0 refused:0 current:0 max:40000 state:ready
         CMON <machine: ip-172-31-15-132, pid: 12532>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=31368))

However, on the standby instance there will be only a single broker.

Instance "testcdb3", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
      "N000" established:3 refused:0 current:1 max:40000 state:ready
         CMON <machine: ip-172-31-11-54, pid: 9874>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=33761))

Restarting the DRCP on the standby result in additional broker being created.

Instance "testcdb3", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:12 refused:0 state:ready
         LOCAL SERVER
      "N000" established:0 refused:0 current:0 max:40000 state:ready
         CMON <machine: ip-172-31-11-54, pid: 11119>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=26996))
      "N001" established:0 refused:0 current:0 max:40000 state:ready
         CMON <machine: ip-172-31-11-54, pid: 11121>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14744))

Switchover and DRCP
In a multi-standby configuration, switchover between a primary and standby doesn't effect the availabilty of the DRCP on other standby instances. For example if there are multiple terminal standbys working as part of a reader farm then primary switching roles will not have any effect on the DRCP in the reader farm.