Golden Gate Replication Process Setup
- 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>
- 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>
- 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>
- We need to set few parameters in GLOBALS which is applicable to all the above Extract and Pump/Replicat processes.
- 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
- 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.
- 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.*;
- 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
- 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'
- 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.
- Repeat the above step at Goldengate Target as well
- 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*;
- 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
- Add Checkpoint table at Goldengate TARGET side(ODS_STAGING)
- Login to GGSCHEMA schema from Goldengate console
Command
GGSCI (den02czh) 3> DBLOGIN USERID << GGSCHEMA >> PASSWORD <<GGSCHEMA>>
GGSCI (den02czh) 3> ADD CHECKPOINTTABLE ggowner.chkpnttbl
- 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
- 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*;
- 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
- The DEF file will be referenced in the REPORA file as below, in this case its /scratch/database/gg_files/defs.def
- 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
- Copy "cwallet.sso" which was created from the above STEP in the dirwlt directory (In Goldengate home directory) to dirwlt directory at Goldengate target
- 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
- 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
- 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
- If no issues are there in the created param files, all the process should be RUNNING successfully.
- 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