Using Oracle Active Data Guard with Global Data Services

Configure sessions to move in a rolling manner for Oracle Active Data Guard reader farm.

Prerequisites

You must have the following in place for this procedure to work correctly.

Oracle Active Data Guard configuration using Oracle Database 19c or Oracle AI Database.
Global Data Services (GDS) configuration using global service manager (release 19c or later recommended).

A GDS service created to run on all Active Data Guard databases in the configuration.

For example:

GDSCTL> add service -service sales_sb -preferred_all -gdspool sales
 -role physical_standby -notification TRUE
GDSCTL> modify service -gdspool sales -service sales_sb -database mts -add_instances
 -preferred mts1,mts2
GDSCTL> modify service -gdspool sales -service sales_sb -database stm -add_instances
 -preferred stm1,stm2
GDSCTL> start service -service sales_sb -gdspool sales

Check the current status of the services and related instances to ensure that services can be moved successfully.

Note that the service should only be available on the source standby database at this point.

GDSCTL> services
Service "sales_sb.sales.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "sales%1", name: "mts1", db: "mts", region: "slc", status: ready.
   Instance "sales%2", name: "mts2", db: "mts", region: "slc", status: ready.

Stop services typically (not using the FORCE option) on the source database where you are removing connections.
```
GDSCTL> stop service -service sales_sb -database mts -gdspool sales
```
- This step will quiesce the FAN-aware connection pools using FAN.
- New connections are directed to other instances offering that service, and idle sessions are disconnected from the pool using the services.
- Existing connections can continue until their work is complete and they are returned to the connection pool.
Allow an agreed time for the sessions to disconnect and relocate, and then continue with the next steps.

Note:

If you are performing a rolling upgrade of an Active Data Guard reader farm and the services are not running on other Active Data Guard reader nodes, then you can complete the service stop on this database before performing the GDSCTL stop service described in this step.
Disconnect long-running sessions after the current query is completed.
Preferably, long-running queries have been scheduled to stop or are queued before the window when connections are to be moved. This step handles long-running sessions that are still running and now need to be stopped (killed) abruptly.
Log on to the instance that you intend to shut down.

Check V$SESSION to see if any sessions are still connected to the service.

SQL> SELECT service_name, count(1) FROM v$session
 GROUP BY service_name ORDER BY 2;

Run the DBMS_SERVICE.DISCONNECT_SESSION package for the service you stopped earlier.

For example:
```
SQL> exec
 dbms_service.disconnect_session('oltp_work',DBMS_SERVICE.POST_TRANSACTION);
```

Check V$SESSION again to ensure that sessions have logged off from the service.

SQL> SELECT service_name, count(1) FROM v$session
 GROUP BY service_name ORDER BY 2;

Start the GDS service on the target database and allow sessions to connect.
```
GDSCTL>start service -service sales_sb -database stm -gdspool sales
```
Log on to the target database and check V$SESSION to see sessions connected to the service.
```
SQL> SELECT service_name, count(1) FROM v$session
 GROUP BY service_name ORDER BY 2;
```

Parent topic: Supported Replication Technologies and Implementation Architectures