Golden Gate Replication Process Setup

  1. Go to the Goldengate installation directory and launch the Goldengate command prompt by launching. /ggsci. (Both Goldengate SOURCE and DESTINATION side).

[database@den02czh goldengate]$ pwd

/scratch/database/app/database/product/goldengate

[database@den02czh goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (den02czh) 1>

  1. Create subdirectories for Goldengate process by issuing the below command. If the directories are not present, it will create them.(Both Goldengate SOURCE and DESTINATION side).

GGSCI (den02czh) 1> create subdirs

Creating subdirectories under current directory /scratch/database/app/database/product/goldengate

Parameter files /scratch/database/app/database/product/goldengate/dirprm: already exists

Report files /scratch/database/app/database/product/goldengate/dirrpt: already exists

Checkpoint files /scratch/database/app/database/product/goldengate/dirchk: already exists

Process status files /scratch/database/app/database/product/goldengate/dirpcs: already exists

SQL script files /scratch/database/app/database/product/goldengate/dirsql: already exists

Database definitions files /scratch/database/app/database/product/goldengate/dirdef: already exists

Extract data files /scratch/database/app/database/product/goldengate/dirdat: already exists

Temporary files /scratch/database/app/database/product/goldengate/dirtmp: already exists

Credential store files /scratch/database/app/database/product/goldengate/dircrd: already exists

Masterkey wallet files /scratch/database/app/database/product/goldengate/dirwlt: already exists

Dump files /scratch/database/app/database/product/goldengate/dirdmp: already exists

GGSCI (den02czh) 2>

  1. Once you got to this point execute the below command to see all the Goldengate running processes(Both Goldengate SOURCE and DESTINATION side)

GGSCI (den02czh) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (den02czh) 3>

  1. We need to set few parameters in GLOBALS which is applicable to all the above Extract and Pump/Replicat processes.
  2. To edit the GLOBALS parameters file, issue the below command from GG command line(Both Source and Target side)

GGSCI (den02czh) 3> edit param ./GLOBALS

  1. Set the content as below both at Source and Target side.

ALLOWOUTPUTDIR /scratch/database/gg_files/

Note: The output directory above will be the exttrail file location at source side and rmtrail file location at target side which are mentioned in the parameter files.

  1. EDIT MGR parameter file and add Goldengate port, default is 7809

Command

GGSCI (den02czh) 3> edit params mgr

Content

PORT 7809

Note: This is the default PORT. If other port is chosen, mention that in the MGR parameter file

6. Create the EXTRACT parameter file.

Command

GGSCI (den02czh) 3> edit params extora

Content

EXTRACT extora

SETENV (ORACLE_HOME = "<<Oracle Home Directory>>")

SETENV (ORACLE_SID="<<Oracle SID>>")

USERIDALIAS ods_ggadmin

ENCRYPTTRAIL AES256

EXTTRAIL <</scratch/database/gg_files/ex>>

CHECKPOINTSECS 5

TABLE OIPA_PAS.*;

  1. Add Extract, execute the below commands on the GoldenGate command prompt.

GGSCI (den02czh) 3> add extract extora , tranlog begin now

GGSCI (den02czh) 3> add exttrail /scratch/database/gg_files/ex, extract extora

  1. Add SchemaTranData to capture the updates from the source

Command

GGSCI (den02czh) 3> ADD SCHEMATRANDATA <<Source Schema Name>>

Note: In this case the 'Source Schema Name' is 'OIPA_PAS'

  1. Create the credentialstore at Goldengate Source and Target to add ggowner user and password so that the details will not be there in the parameter file as plain text

GGSCI (den02czh) 3> ADD CREDENTIALSTORE

GGSCI (den02czh) 3> ALTER CREDENTIALSTORE ADD USER <<user id>> PASSWORD <<password>> ALIAS <<alias name>>

Note: In this case user id=>ggowner, alias name => ods_ggadmin. Alias name is referenced both at Extract and Replicat param files.

  1. Repeat the above step at Goldengate Target as well
  2. Create PUMPORA extract parameter file.

Command

GGSCI (den02czh) 3> edit params pumpora

Content

EXTRACT pumpora

PASSTHRU

RMTHOST localhost, MGRPORT 7809

RMTTRAIL <</scratch/database/gg_files/rt>>

CHECKPOINTSECS 5

TABLE OIPA_PAS.AS*;

  1. Add pumpora Extract, execute the below commands on the GoldenGate command prompt.

Command

GGSCI (den02czh) 3> add extract pumpora, exttrailsource /scratch/database/gg_files/ex

GGSCI (den02czh) 3> add rmttrail /scratch/database/gg_files/rt, extract pumpora

  1. Add Checkpoint table at Goldengate TARGET side(ODS_STAGING)
  2. Login to GGSCHEMA schema from Goldengate console

Command

GGSCI (den02czh) 3> DBLOGIN USERID << GGSCHEMA >> PASSWORD <<GGSCHEMA>>

GGSCI (den02czh) 3> ADD CHECKPOINTTABLE ggowner.chkpnttbl

  1. Because the source and target tables are different (LAST_MODIFIED_TIME column was added to all the TARGET tables. We need to import the SOURCEDEFS as below
  2. Create a DEF param file e.g. defs.prm

Content

DEFSFILE <</scratch/database/gg_files/defs.def>>

USERID <<OIPA_PAS>>, PASSWORD <<OIPA_PAS>>

TABLE OIPA_PAS.AS*;

  1. Generate the Source DEF file with the below command. Execute this from the Goldengate Home directory(OS command line)

Command

./defgen paramfile ./dirprm/defs.prm

  1. The DEF file will be referenced in the REPORA file as below, in this case its /scratch/database/gg_files/defs.def
  2. Create Wallet and Add Masterkey at GG source to Encrypt the Trail files

GGSCI (SERVER) 6> CREATE WALLET

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (SERVER) 7> ADD MASTERKEY

Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'.

GGSCI (SERVER) 8> INFO MASTERKEY VERSION 1

Masterkey Name: OGG_DEFAULT_MASTERKEY

Creation Date: Tue May 30 05:41:58 2017

Version: 1

Renew Date: Tue May 30 05:41:58 2017

Status: Current

Key Hash (SHA1): 0xFE8ECC85686BE221738FA065D7F16BF61CE49FBA

  1. Copy "cwallet.sso" which was created from the above STEP in the dirwlt directory (In Goldengate home directory) to dirwlt directory at Goldengate target
  2. Verify the "Key Hash" after copied that wallet to Goldengate target and they should be same

GGSCI (SERVER) 8> INFO MASTERKEY VERSION 1

Masterkey Name: OGG_DEFAULT_MASTERKEY

Creation Date: Tue May 30 05:41:58 2017

Version: 1

Renew Date: Tue May 30 05:41:58 2017

Status: Current

Key Hash (SHA1): 0xFE8ECC85686BE221738FA065D7F16BF61CE49FBA

  1. Create REPPORA extract parameter file

Command

GGSCI (den02czh) 3> edit params repora

Content

REPLICAT repora

SETENV (ORACLE_HOME = "<<Oracle Home Directory>>")

SETENV (ORACLE_SID="<<Oracle SID>>")

ASSUMETARGETDEFS

USERIDALIAS ods_ggadmin

DISCARDFILE <</scratch/database/gg_files/discard.dsc>>, PURGE

DBOPTIONS NOSUPPRESSTRIGGERS

SOURCEDEFS <</scratch/database/gg_files/defs.def>>

REPERROR (DEFAULT, EXCEPTION)

MAP OIPA_PAS.AS*, TARGET ODS_STAGING.*,COLMAP(USEDEFAULTS, LAST_MODIFIED_TIME = @DATENOW ()),

MAPEXCEPTION (TARGET GGOWNER.ODSGGEXCEPTIONS, EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP

(

excp_date = @DATENOW(),

rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),

table_name = @GETENV ('GGHEADER', 'TABLENAME'),

errno = @GETENV ('LASTERR', 'DBERRNUM'),

errmsg = @GETENV ('LASTERR', 'DBERRMSG')

errtype = @GETENV ('LASTERR', 'ERRTYPE'),

optype = @GETENV ('LASTERR', 'OPTYPE'),

transind = @GETENV ('GGHEADER', 'TRANSACTIONINDICATOR'),

transimgind = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'),

committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),

reccsn = @GETENV ('TRANSACTION', 'CSN'),

recseqno = @GETENV ('RECORD', 'FILESEQNO'),

recrba = @GETENV ('RECORD', 'FILERBA'),

rectranspos = @GETENV ('RECORD', 'RSN'),

reclength = @GETENV ('GGHEADAER', 'RECORDLENGTH'),

logrba = @GETENV ('GGHEADER', 'LOGRBA'),

logposition = @GETENV ('GGHEADER', 'LOGPOSITION'),

grouptype = @GETENV ('GGENVIRONMENT', 'GROUPTYPE'),

filename = @GETENV ('GGFILEHEADER', 'FILENAME'),

fileno = @GETENV ('GGFILEHEADER', 'FILESEQNO'),

srcrowid = @GETENV ('TRANSACTION', 'CSN'),

srcdbcharset = @GETENV ('GGFILEHEADER', 'DBCHARSET'),

replag = @GETENV ('LAG', 'SEC'),

cnt_cdr_conflicts = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_CONFLICTS'),

cnt_cdr_resolutions = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_SUCCEEDED'),

cnt_cdr_failed = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_FAILED'))

)

);

IGNOREINSERTS

IGNOREUPDATES

INSERTDELETES

MAP OIPA_PAS.ASACTIVITYMULTIVALUEFIELD, TARGET ODS_STAGING.DLACTIVITYMULTIVALUEFIELD;

MAP OIPA_PAS.ASADDRESSMULTIVALUEFIELD, TARGET ODS_STAGING.DLADDRESSMULTIVALUEFIELD;

MAP OIPA_PAS.ASAGREEMENTMULTIVALUEFIELD, TARGET ODS_STAGING.DLAGREEMENTMULTIVALUEFIELD;

MAP OIPA_PAS.ASAGREEMENTROLEMVF, TARGET ODS_STAGING.DLAGREEMENTROLEMVF;

MAP OIPA_PAS.ASCLASSGROUPMULTIVALUEFIELD, TARGET ODS_STAGING.DLCLASSGROUPMULTIVALUEFIELD;

MAP OIPA_PAS.ASCLASSMULTIVALUEFIELD, TARGET ODS_STAGING.DLCLASSMULTIVALUEFIELD;

MAP OIPA_PAS.ASCLIENTMULTIVALUEFIELD, TARGET ODS_STAGING.DLCLIENTMULTIVALUEFIELD;

MAP OIPA_PAS.ASCLIENTRELATIONSHIPMVF, TARGET ODS_STAGING.DLCLIENTRELATIONSHIPMVF;

MAP OIPA_PAS.ASPLANSEGMENTNAMECLASSMVF, TARGET ODS_STAGING.DLPLANSEGMENTNAMECLASSMVF;

MAP OIPA_PAS.ASPOLICYMULTIVALUEFIELD, TARGET ODS_STAGING.DLPOLICYMULTIVALUEFIELD;

MAP OIPA_PAS.ASROLEMULTIVALUEFIELD, TARGET ODS_STAGING.DLROLEMULTIVALUEFIELD;

MAP OIPA_PAS.ASSEGMENT, TARGET ODS_STAGING.DLSEGMENT;

MAP OIPA_PAS.ASSEGMENTFIELD, TARGET ODS_STAGING.DLSEGMENTFIELD;

MAP OIPA_PAS.ASSEGMENTMULTIVALUEFIELD, TARGET ODS_STAGING.DLSEGMENTMULTIVALUEFIELD;

MAP OIPA_PAS.ASSEGMENTNAMESERVICEBNFTFIELD, TARGET ODS_STAGING.DLSEGMENTNAMESERVICEBNFTFIELD;

MAP OIPA_PAS.ASSUSPENSEMULTIVALUEFIELD, TARGET ODS_STAGING.DLSUSPENSEMULTIVALUEFIELD;

Add Replicat

Command

GGSCI (den02czh) 3> ADD REPLICAT repora, EXTTRAIL /scratch/database/gg_files/rt, BEGIN NOW, CHECKPOINTTABLE ggowner.chkpnttbl

  1. See all the GG replication processes, you should be able to see 3 processes. They are:

EXTRACT EXTORA

EXTRACT PUMPORA

REPLICAT REPORA

Start them with the below commands

start extract extora

start extract pumpora

start replicat repora

  1. If no issues are there in the created param files, all the process should be RUNNING successfully.
  2. After making sure all the above 3 process are running, the replication process can be tested now.
GROUP#         MEMBER
3              /scratch/oipa/app/oipa/oradata/orcl/redo03.log
2              /scratch/oipa/app/oipa/oradata/orcl/redo03.log

 

 

Oracle Insurance Logo Copyright © 2017, Oracle and/or its affiliates. All rights reserved. About Oracle Insurance | Contact Us