Skip to main content
FlashGrid.io

Example of configuring GoldenGate with Extract on target

  • Updated
  • Products: FlashGrid Cluster on Azure
  • Products: FlashGrid Cluster on GCP
  • Products: FlashGrid Cluster on AWS

Create a shared folder for GoldenGate on ACFS

Do the following steps on only the target cluster.

Create a volume:

$ sudo su - grid
$ asmcmd volcreate -G GGDG -s 10G ACFSGG

Determine the device name of the volume:

ASMCMD> volinfo --all
Diskgroup Name: GGDG
     Volume Name: ACFSGG
     Volume Device: /dev/asm/acfsgg-458
     State: ENABLED
     Size (MB): 10240
     Resize Unit (MB): 512
     Redundancy: MIRROR
     Stripe Columns: 8
     Stripe Width (K): 1024
     Usage: 
     Mountpath:

Create the filesystem on the volume as grid user:

$ mkfs -t acfs /dev/asm/acfsgg-458

As the root user, create an empty directory and mount ACFS volume:

# mkdir /GG_HOME
# chmod 775 /GG_HOME
# chown oracle:oinstall /GG_HOME

Setup the file system to be auto-mounted by clusterware:

# srvctl add filesystem -device /dev/asm/acfsgg-458 -path /GG_HOME -volume acfsgg -diskgroup GGDG -user oracle -fstype ACFS -description "ACFS for GoldenGate"

Start filesystem service:

# srvctl start filesystem -device /dev/asm/acfsgg-458

Installing the GoldenGate software

Do the following steps on only target cluster.

Download and install GoldenGate software on shared ACFS mount point. Software binaries (123012_fbo_ggs_Linux_x64_shiphome.zip) can be downloaded from http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Place downloaded software in /tmp/Install directory. Grant Oracle database owner the necessary permissions and install the software as oracle user:

# chown oracle:oinstall /tmp/Install/123012_fbo_ggs_Linux_x64_shiphome.zip
# su - oracle
$ cd /tmp/Install/
$ unzip 123012_fbo_ggs_Linux_x64_shiphome.zip
$ cd fbo_ggs_Linux_x64_shiphome/Disk1

$ ./runInstaller -silent -nowait -showProgress INSTALL_OPTION=ORA12c SOFTWARE_LOCATION=/GG_HOME/home_1 START_MANAGER=false MANAGER_PORT= DATABASE_LOCATION= INVENTORY_LOCATION=/u01/app/oraInventory UNIX_GROUP_NAME=oinstall

Install Oracle Grid Infrastructure Standalone Agents

Do the following steps on only the target cluster.

Download the software from https://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/xag-agents-downloads-3636484.html

Place downloaded software in /tmp/Install and grant necessary permissions to grid user. From the first node of the cluster, run the following via grid user: 

# chown grid:dba /tmp/Install/xagpack81b.zip
# su - grid
$ cd /tmp/Install
$ unzip xagpack81b.zip
$ ./xag/xagsetup.sh --install --directory /u01/app/grid/xag --all_nodes
Installing Oracle Grid Infrastructure Agents on: rac1-hq
Installing Oracle Grid Infrastructure Agents on: rac2-hq
Done.

XAG Registration

Do the following steps on only the target cluster.

From the first node, create GoldenGate Agent as oracle user.

$ /u01/app/grid/xag/bin/agctl add goldengate gg_replicate --gg_home /GG_HOME/home_1 --instance_type target --nodes rac1-dr,rac2-dr  --filesystems ora.ggdg.acfsgg.acfs --databases ora.orcl.db --oracle_home /u01/app/oracle/product/12.2.0/dbhome_1

Add necessary TNS entries

Do the following steps on only the target cluster.

Add the following TNS entries in $ORACLE_HOME/network/admin/tnsnames.ora file:

ogg_prim =
  (DESCRIPTION =
    (TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=1)
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=tcp)(HOST=primrac-scan.example.com)(PORT=1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
   )
  )

ogg_stby =
  (DESCRIPTION =
    (TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=6)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = stbyrac-scan.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Where primrac-scan.example.com on the target server is resolved to the VPC/VNet Private IP addresses of the source nodes.

Example for a 2-node cluster:

primrac-scan.example.com 10.100.0.1
primrac-scan.example.com 10.100.0.2

Setup a basic configuration of OGG

Do the following steps on only the target cluster.

To be able to run GGSCI command, create the following symbolic links in /GG_HOME/home_1 from oracle user:

$ cd /GG_HOME/home_1
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so libnnz12.so
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 libclntsh.so.12.1
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so libons.so
$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 libclntshcore.so.12.1

Create subdirs from GGSCI command line intererface:

$ . oraenv
ORACLE_SID = [orcl1] ? 
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1

$ /GG_HOME/home_1/ggsci
GGSCI> create subdirs

The following script connects to the source database from the target database through GGSCI and enables supplemental logging on HR.* tables:

$ /GG_HOME/home_1/ggsci
GGSCI> DBLOGIN USERID ggcw@ogg_prim PASSWORD ggcw
GGSCI> add trandata hr.*

Edit global parameters and create checkpoint table:

GGSCI> edit params ./GLOBALS
GGSCHEMA ggcw
CHECKPOINTTABLE ggcw.CKPTAB

GGSCI> DBLOGIN USERID ggcw PASSWORD ggcw
GGSCI> add checkpointtable

Create manager parameter file:

GGSCI> edit params mgr 
PORT 7809
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 60 
AUTOSTART ER *

ArchiveLog mode setup

Do the following steps on only the source cluster.

$ srvctl stop database -db orcl

From the first node only:

SQL> startup mount;
SQL> alter system set db_recovery_file_dest_size=10G;
SQL> alter system set db_recovery_file_dest='+FRA';
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

SQL> alter database archivelog;
SQL> alter database add supplemental log data;
SQL> shutdown immediate;

$ srvctl start database -db orcl

Schema setup

Do the following steps on the source and target clusters.

Set enable_goldengate_replication parameter to true:

SQL> alter system set enable_goldengate_replication=true;

For integrated capture, set streams_pool_size parameter (database restart is required):

SQL> alter system set streams_pool_size=4G scope=spfile;

Create tablespace, schema and grant necessary permissions:

$ cd /GG_HOME/home_1/
$ sqlplus / as sysdba

SQL> create tablespace ggcw datafile '+DATA' size 100m autoextend on next 5m maxsize unlimited;
SQL> create user ggcw identified by ggcw default tablespace ggcw temporary tablespace temp quota unlimited on ggcw;
SQL> create user ggcw identified by ggcw;
SQL> grant connect, resource to ggcw;
SQL> grant select any dictionary, select any table to ggcw;
SQL> grant create table to ggcw;
SQL> grant flashback any table to ggcw;
SQL> grant execute on dbms_flashback to ggcw;
SQL> grant execute on utl_file to ggcw;
SQL> grant create any table to ggcw;
SQL> grant insert any table to ggcw;
SQL> grant update any table to ggcw;
SQL> grant delete any table to ggcw;
SQL> grant drop any table to ggcw;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ggcw');

Create Integrated or Classic extracts

Do the following steps on only target system.

  1. Integrated capture

Make sure you have set the correct value for ORACLE_SID and establish a connection to the source database through GGSCI from the target server:

GGSCI> DBLOGIN USERID ggcw@ogg_prim PASSWORD ggcw

From the same session, add extract:

GGSCI> register extract ext1 database
GGSCI> add extract ext1, integrated tranlog, begin now
GGSCI> add exttrail ./dirdat/rt, extract ext1, megabytes 100
GGSCI> edit params ext1

EXTRACT ext1
USERID ggcw@ogg_prim, PASSWORD ggcw
EXTTRAIL ./dirdat/rt
TABLE hr.emp
  1. Classic capture:

Please apply the patch for bug 27379190 before using classic capture, see Doc ID 2360874.1

If archive logs are located on ASM, then you need to add the following entries in $ORACLE_HOME/network/admin/tnsnames.ora:

Cluster Node 1:

ASM =
    (ADDRESS=(PROTOCOL=BEQ)
         (PROGRAM=/u01/app/12.2.0/grid/bin/oracle)
         (ARGV0=oracle+ASM1)
         (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
         (ENVS='ORACLE_HOME=/u01/app/12.2.0/grid,ORACLE_SID=+ASM1'))

Cluster Node 2:

ASM =
    (ADDRESS=(PROTOCOL=BEQ)
         (PROGRAM=/u01/app/12.2.0/grid/bin/oracle)
         (ARGV0=oracle+ASM2)
         (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
         (ENVS='ORACLE_HOME=/u01/app/12.2.0/grid,ORACLE_SID=+ASM2'))

Set the correct value for ORACLE_SID and login to the database using GGSCI command line interface:

GGSCI> DBLOGIN USERID ggcw@ogg_prim PASSWORD ggcw

Add extract with the number of threads:

GGSCI> add extract ext2, tranlog, threads 2, begin now
GGSCI> add exttrail ./dirdat/et, extract ext2, megabytes 100

GGSCI> edit params ext2
EXTRACT ext2
USERID ggcw@ogg_prim, PASSWORD ggcw 
TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD MyPassword2017
EXTTRAIL ./dirdat/et
TABLE hr.sal;

Create replicat

Do the following steps on only the target system.

GGSCI> DBLOGIN USERID ggcw, PASSWORD ggcw

GGSCI> add replicat rep1, exttrail ./dirdat/rt
GGSCI> add replicat rep2, exttrail ./dirdat/et

At this point, if the ADD REPLICAT command fails with the following error, ERROR: No checkpoint table specified for ADD REPLICAT simply exit that GGSCI session and reconnect before issuing ADD REPLICAT. The ADD REPLICAT command fails if issued from the same session where the GLOBALS file was created.

GGSCI> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggcw@ogg_stby, PASSWORD ggcw
MAP hr.emp, TARGET hr.emp;

GGSCI> edit params rep2
REPLICAT rep2
ASSUMETARGETDEFS
USERID ggcw@ogg_stby, PASSWORD ggcw
MAP hr.sal, TARGET hr.sal;

Start GoldenGate service

Start GoldenGate on the target via oracle user:

$ /u01/app/grid/xag/bin/agctl start goldengate gg_replicate