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.
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
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.
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
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.
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.
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.
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.
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!
