1. Create shared folder for GoldenGate on ACFS
Do the following steps on only 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:
[grid@rac1-hq ~]$ 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
2. 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
3. Install Oracle Grid Infrastructure Standalone Agents
Do the following steps on only 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.
4. XAG Registration
Do the following steps on only 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
5. Add necessary TNS entries
Do the following steps on only 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
6. Setup a basic configuration of OGG
Do the following steps on only 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 *
7. ArchiveLog mode setup
Do the following steps on only source cluster.
$ srvctl stop database -db orcl
From to 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
8. 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');
9. 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 archivelogs are located on ASM, then 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 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;
10. Create replicat
Do the following steps on only 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;
11. Start GoldenGate service
Start golden gate on the target via oracle user:
$ /u01/app/grid/xag/bin/agctl start goldengate gg_replicate