# Oracle GoldenGate with Oracle Active Data Guard

Nowadays, Oracle Active Data Guard is often implemented has High Availability and Disaster Recovery solution to protect the Oracle databases.

Additionally, Oracle GoldenGate can be configured on the Primary database as logical replication solution to extracts the data for the Data warehouse systems.

But not only. with Oracle GoldenGate we have also the possibility to extracts the data from the standby database, and it exist 2 mode of extraction :

* **Archive log Only(ALO mode):** with this mode the replication is delayed, because the transactions can only be extracted from the archivelog files
* **Real-Time mode:** To use the Real-Time mode, Oracle Active Data Guard must be licensed, but this is not a problem. Because when you buy Oracle GoldenGate you will also get the license for Oracle Active Data Guard.

**The below Oracle GoldenGate solution description for Oracle Active Data Guard, need at minimum the Oracle GoldenGate version 12.1.2**

## Purpose

The dbi services GoldenGate scripts allows to extracts the transaction from the Primary and Standby in parallel, and in case of a Switchover or Failover will occur in your environment, no manual actions are required to restart the GoldenGate extracts on the new primary and standby database.

<figure><img src="https://3382315087-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FPtQvF2ryopbZznR9blJe%2Fuploads%2Fgit-blob-aa0b9d829892a91001397d368f308f8c64e21a8c%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

To achieve the automatic restart of the Oracle GoldenGate processes after a switchover or failover, more additional GoldenGate configuration files are needed.

## Implementation task for the first database (current primary)

We will use as example a simple table replication of the schema scott

```
oracle@vmreforatun01:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott.prm

extract scott
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
exttrail /u00/app/goldengate/trail/sa
ddl include mapped objname scott.*
table SCOTT.EMP;
```

### Additional GoldenGate configuration file to create

First we will make a backup of the current configuration file to **scott\_primary.prm**, thus we will able to set back the configuration after a switchover or failover as a PRIMARY database.

```
oracle@vmreforatun01:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott_primary.prm

extract scott
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
exttrail /u00/app/goldengate/trail/sa
ddl include mapped objname scott.*
table SCOTT.EMP;
```

To be able to extracts data’s from this database, when the role of the database is a STANDBY database, we have to create a new GoldenGate parameter file with an additional parameter

```
oracle@vmreforatun01:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott_standby.prm

extract scott
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
TRANLOGOPTIONS MINEFROMACTIVEDG
exttrail /u00/app/goldengate/trail/sa
ddl include mapped objname scott.*
table SCOTT.EMP;
```

But these two configuration file are not sufficient, because in case of a failover an additional step is required, thus we have to create a third temporary configuration file.

```
oracle@vmreforatun01:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott_primary_tmp.prm

extract scott
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
TRANLOGOPTIONS USEPREVRESETLOGSID
exttrail /u00/app/goldengate/trail/sa
ddl include mapped objname scott.*
table SCOTT.EMP;
```

## Implementation task for the second database (current standby)

The 3 additional configuration files must also exist on the standby site, to be able to extracts data from a primary or a standby database after a failover or switchover.

For the example we will use the same simple table replication of the schema scott

```
oracle@vmora12c:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott2.prm
extract SCOTT2
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
TRANLOGOPTIONS MINEFROMACTIVEDG
exttrail /u00/app/goldengate/trail/tb
ddl include mapped objname scott.*
table SCOTT.EMP;
```

### Additional configuration file to create

First we will make a backup of the current configuration file to **scott2.prm**, thus we will able to set back the configuration after a switchover or failover as a STANDBY database.

```
oracle@vmora12c:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott2_standby.prm
extract SCOTT2
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
TRANLOGOPTIONS MINEFROMACTIVEDG
exttrail /u00/app/goldengate/trail/tb
ddl include mapped objname scott.*
table SCOTT.EMP;
```

To be able to extracts datas from this database, when the role of the database is a PRIMARY database, we have to create a new GoldenGate parameter file with removing the MINEFROMACTICEDG parameter.

```
oracle@vmora12c:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott2_primary.prm
extract SCOTT2
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
exttrail /u00/app/goldengate/trail/tb
ddl include mapped objname scott.*
table SCOTT.EMP;
```

But these two configuration files are not sufficient, because in case of a failover an additional step is required, thus we have to create a third temporary configuration file.

```
oracle@vmora12c:/u00/app/goldengate/product/12.1.2.1/dirprm/ [DB1] cat
scott2_primary_tmp.prm
extract scott2
useridalias goldengate12
DBOPTIONS ALLOWUNUSEDCOLUMN
TRANLOGOPTIONS USEPREVRESETLOGSID
exttrail /u00/app/goldengate/trail/tb
ddl include mapped objname scott.*
table SCOTT.EMP;
```

## Configuration

Now we can start the configuration of the automatic configuration change in case a switchover or a failover will occur.

### Create the Job to execute in case of a role change

First we have to create the below Jobs on the primary database.

```
oracle@vmreforatun01:/u01/app/oracle/local/dmk_ha/sql/ [DB1] cat
create_job_for_ogg_failover.sql
spool ${DMK_ORA_ADMIN_SID}/log/CREATE_job_for_ogg_failover.log
set echo on
set feed on
BEGIN
 dbms_scheduler.drop_job
 (
 job_name => 'OGG_FAILOVER_TO_PRIMARY'
 );
 dbms_scheduler.drop_job
 (
 job_name => 'OGG_FAILOVER_TO_STANDBY'
 );
EXCEPTION
 WHEN OTHERS THEN
 NULL;
END;
/
BEGIN
 dbms_scheduler.CREATE_job
 (
 job_name => 'OGG_FAILOVER_TO_PRIMARY',
 job_type => 'EXECUTABLE',
 job_action =>
'/u01/app/oracle/local/dmk_ha/bin/ogg_failover_to_primary_actions.ksh',
 enabled => true,
 comments => 'Failover GoldenGate configuration to Extract from
the Primary',
 auto_drop => false
 );
END;
/
BEGIN
 dbms_scheduler.CREATE_job
 (
 job_name => 'OGG_FAILOVER_TO_STANDBY',
 job_type => 'EXECUTABLE',
 job_action =>
'/u01/app/oracle/local/dmk_ha/bin/ogg_failover_to_standby_actions.ksh',
 enabled => true,
 comments => 'Failover GoldenGate configuration to Extract from
the Active Standby Database',
 auto_drop => false
 );
END;
/
spool off
exit
```

### Create the Role Change trigger to execute in case of a Role Change

To start the above job automaticylly, we will use a Role Change Trigger. The below Trigger will only be executed in case of a ROLE CHANGE, this trigger is not the same as a STARTUP TRIGGER, which will always be started during a started of the database

```
oracle@vmreforatun01:/u00/app/oracle/local/dmk_ha/sql/ [DB1] cat
create_role_change_trigger_ogg.sql
spool ${DMK_ORA_ADMIN_SID}/log/ogg_failover_trigger.log
set echo on
set feed on

CREATE OR REPLACE TRIGGER OGG_FAILOVER_TRIGGER
AFTER DB_ROLE_CHANGE ON DATABASE
DECLARE
 DATABASE_ROLE VARCHAR(30);
BEGIN
 select DATABASE_ROLE into DATABASE_ROLE from V$DATABASE;
 IF DATABASE_ROLE = 'PRIMARY'
 THEN
 dbms_system.ksdddt; /* write timestamp INTO alert.log */
 dbms_system.ksdwrt(2,'calling
/u01/app/oracle/local/dmk_ha/bin/ogg_failover_to_primary_actions.ksh ');
 dbms_system.ksdfls; /* flush the alert.log */
 DBMS_SCHEDULER.RUN_JOB('OGG_FAILOVER_TO_PRIMARY');
 ELSIF DATABASE_ROLE = 'PHYSICAL STANDBY'
 THEN
 dbms_system.ksdddt; /* write timestamp INTO alert.log */
 dbms_system.ksdwrt(2,'calling
/u01/app/oracle/local/dmk_ha/bin/ogg_failover_to_standby_actions.ksh');
 dbms_system.ksdfls; /* flush the alert.log */
 DBMS_SCHEDULER.RUN_JOB('OGG_FAILOVER_TO_STANDBY');
 END IF;
END;
/
exit
```

### Adapt the failover\_to\_\[standby|primary]\_actions.sh scripts on both server

After the Jobs are created, the both scripts failover\_to\_\[standby|primary]\_actions.sh must be adapted

On both server.

* On the first server update the below parameter with your environment settings.

```
oracle@vmreforatun01:/u00/app/oracle/local/dmk_ha/bin/ [DB1] grep scott
failover_to_primary_actions.sh
 export ORACLE_SID=DB1
 export GG_EXTRACT_PARAM="scott"
 export OGG_HOME=/u00/app/goldengate/product/12.1.2.1
oracle@vmreforatun01:/u00/app/oracle/local/dmk_ha/bin/ [DB1] grep scott
failover_to_standby_actions.sh
 export ORACLE_SID=DB1
 export GG_EXTRACT_PARAM="scott"
 export OGG_HOME=/u00/app/goldengate/product/12.1.2.1
```

* On the second server update the below parameter with your environment settings.

```
oracle@vmora12c:/u00/app/oracle/local/dmk_ha/bin/ [DB1] grep scott2
failover_to_primary_actions.sh
 export ORACLE_SID=DB1
 export GG_EXTRACT_PARAM="scott2"
 export OGG_HOME=/u00/app/goldengate/product/12.1.2.1
oracle@vmora12c:/u00/app/oracle/local/dmk_ha/bin/ [DB1] grep scott2
failover_to_standby_actions.sh
 export ORACLE_SID=DB1
 export GG_EXTRACT_PARAM="scott2"
 export OGG_HOME=/u00/app/goldengate/product/12.1.2.1
```

## Usage

Now to test if Oracle GoldenGate will restart automatically on both databases after a Switchover, a simple Oracle Data Guard switchover is needed.

After that you can test the failover, for that an Oracle Data Guard failover is required. Take Care, after the failover the OLD primary database must be reinstated, which will work. But afterwards you must manually resynchronize the GoldenGate configuration. It will not work automatically!
