Using Oracle GoldenGate with Global Data Services

Use this procedure to gracefully transition Oracle GoldenGate replication roles and GDS services, ensuring minimal downtime, data consistency, and availability.

The following Oracle GoldenGate role transition example topology consists of two databases: GG replica1 and GG replica2. Oracle GoldenGate is set up with uni-directional replication, with Extract running initially on GG replica1 and Replicat running initially on GG replica2. The generic steps still apply for bi-directional GoldenGate replicas or downstream mining GoldenGate replicas.

Prerequisites

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

Oracle GoldenGate configuration that uses Oracle Database (19c or higher recommended)
GoldenGate processes should not connect to the source or target database using the GDS service name, but a dedicated TNS alias. Using the GDS service will cause the database connections to terminate prematurely, causing possible data loss.
A heartbeat table has been implemented in the GoldenGate source and target databases to track replication latency and ensure the Replicat applied SCN synchronization. The GoldenGate automatic heartbeat table feature should be enabled. Refer to the Oracle GoldenGate Administration Guide for details on the automatic heartbeat table: https://docs.oracle.com/en/middleware/goldengate/core/19.1/gclir/add-heartbeattable.html.
Global Data Services (GDS) configuration using global service manager (19c or higher recommended)

GDS service has been created so that it can be run on all databases in the GoldenGate configuration.

For example:

GDSCTL> add service -service sales_sb -preferred_all -gdspool sales
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

> Note: > > If you are using the lag tolerance option, specify the lag limit for the global service in seconds. Options for \add service\ or \modify service\ are \-lag {lag\_value | ANY}\.

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

At this point, the service should only be available on the source database.

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 (not using the FORCE option) on the source database where connections are to be removed.
- 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.
```
GDSCTL> stop service -service sales_sb -database mts -gdspool sales -force
```
Allow an agreed upon time for the sessions to disconnect and relocate, then continue with the next steps. The time to allow for sessions to drain depends on the workload and user transactions for your application.
Disconnect long-running sessions after the current transaction is completed.
Preferably, long-running batch jobs are scheduled to be stopped or queued before the maintenance window. This step handles long-running sessions that are still running and must be stopped abruptly (e.g., killed). Check with the application developers if these long-running batch jobs are idempotent and recoverable before disconnecting long-running sessions.
Log on to the instance that you intend to shut down, and 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 sessions have logged off from the service.

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

When all sessions associated with the GDS service have been disconnected, verify that all data from the GoldenGate source databases have been replicated to the target database.
- Record the current database SCN from the GoldenGate SOURCE database.
```
SQL> SELECT current_scn FROM v$database;
```
- On the GoldenGate TARGET database, continue to monitor the Replicat applied SCN using the following query.
```
SQL> SELECT lwm_position FROM v$gg_apply_coordinator;
```
- When the target LWM_POSITION SCN is greater than the CURRENT_SCN recorded in the first step, it is safe to assume that all transactions have been replicated from the source to the target database. The users can now be switched over to the GoldenGate target database.

The above steps allow for a graceful switchover. However, if this is a failover event where the source database is unavailable, you can estimate the data loss using the steps below.

When using the automatic heartbeat table, use the following query to determine the replication latency.

SQL> col Lag(secs) format 999.9
SQL> col "Seconds since heartbeat" format 999.9
SQL> col "GG Path" format a32
SQL> col TARGET format a12
SQL> col SOURCE format a12
SQL> set lines 140
SQL> select remote_database "SOURCE", local_database "TARGET", incoming_path "GG Path",
 incoming_lag "Lag(secs)", incoming_heartbeat_age "Seconds since heartbeat" from gg_lag;

SOURCE        TARGET              GG Path                          Lag(secs) Seconds since heartbeat
------------  ------------        -------------------------------- --------- -----------------------
         MTS          GDST          EXT_1A ==> DPUMP_1A ==> REP_1A       7.3                     9.0

The above example shows a possible 7.3 seconds of data loss between the source and target databases.

Start the GDS service on the target database and allow sessions to connect.

Note that if the application workload can accept a certain level of data lag, it is possible to perform this step much earlier than step two listed above.
```
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