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