ORACLE GOLDEN GATE CONFIGURATION
=========================
Here are the high-level steps to configure Oracle GoldenGate for data replication:
========================
Pre-Configuration
1)Verify System Requirements:
Ensure the source and target environments meet GoldenGate compatibility requirements.
2)Install GoldenGate Software:
Install Oracle GoldenGate on both the source and target servers.
Extract the GoldenGate ZIP or TAR file, and set the environment variables (e.g., GG_HOME).
3)Create Database Users:
Create dedicated users for GoldenGate on both source and target databases with appropriate privileges:
Source:
CREATE USER ogg IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, ALTER SESSION, SELECT ANY TABLE, FLASHBACK ANY TABLE TO ogg;
Target:
CREATE USER ogg IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, ALTER SESSION, INSERT ANY TABLE, UPDATE ANY TABLE TO ogg;
3)Enable Supplemental Logging: (No data loss)
On the source database:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Configuration on Source Database
4)Prepare the Source Database:
Enable force logging (optional but recommended):
ALTER DATABASE FORCE LOGGING;
5)Add required tables for replication to the GoldenGate extract:
DBMS_CAPTURE_ADM.ADD_SUPPLEMENTAL_LOGGING(
schema_name => 'SCHEMA_NAME',
table_name => 'TABLE_NAME',
column_list => 'ALL');
Configure Extract Process:
6)Add an extract process for capturing changes:
ADD EXTRACT ext_name, TRANLOG, BEGIN NOW
Define the tables to be captured:
ADD EXTTRAIL /ggs/dirdat/aa, EXTRACT ext_name
TABLE schema_name.table_name;
Configuration on Target Database
7)Configure Replicat Process:
Add a replicat process on the target server:
ADD REPLICAT rep_name, EXTTRAIL /ggs/dirdat/aa
Define the mapping for target tables:
MAP schema_name.table_name, TARGET schema_name.table_name;
8)Data Synchronization
Initial Data Load (Optional):
If the source and target are not already in sync, use GoldenGate to perform an initial data load:
9)ADD EXTRACT init_load, SOURCEISTABLE
Define the target loading process:
10)ADD REPLICAT init_load, SPECIALRUN
MAP schema_name.table_name, TARGET schema_name.table_name;
Start Processes
Start Extract and Replicat Processes:
On the source server:
START EXTRACT ext_name
On the target server:
START REPLICAT rep_name
11)Monitor the Processes:
Use the GoldenGate command-line interface (ggsci) to monitor:
INFO ALL
Post-Configuration
12)Verify Data Replication:
Check for consistent data on both the source and target databases.
Use GoldenGate statistics commands to ensure data is being replicated:
STATS EXTRACT ext_name
STATS REPLICAT rep_name
13)Enable Real-Time Monitoring:
Configure GoldenGate Manager for process monitoring and alerts.
Ongoing Maintenance:
Regularly monitor logs and processes for errors or latency.
==========================
GOLDEN GATE CONFIGURATIONS :
===========================
Prerequisites:
* Verify that LOG_MODE is set to ARCHIVELOG.
select LOG_MODE from v$database;
* Verify that supplemental logging and forced logging are set properly.
select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
* This parameter must be changed on source and target databases:
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
show parameter ENABLE_GOLDENGATE_REPLICATION
Configure the Streams Pool
Show parameter streams_pool_size
* Create the administrator and user/schema owners on both source and target database.
create user Goldengate identified by gguser default tablespace goldengate quota unlimited on goldengate;
grant create session,connect,resource,alter system to op_tsg5 ;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>’GOLDENGATE ’, privilege_type=>’CAPTURE’, grant_optional_privileges=>’*’);
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( -
grantee => 'GOLDENGATE', -
privilege_type => 'capture', grant_select_privileges=> true, do_grants => TRUE);
Golden gate version 19.1.0.0.4 installation on AIX Version 7.2
Downloaded and unzipped the software V983656-01.zip
Edit the response file add required details and save.
./runInstaller -invPtrLoc /Goldengate/oraInst.loc -silent -showProgress -waitforcompletion -responseFile /GOLDENGATE/installation/fbo_ggs_AIX_ppc_shiphome/Disk1/response/oggcore.rsp
set the bashprofile for Goldengate
Once the installation completed successfully proceed with GG configuration
Create Golden Gate sub directories: Connect to ggsci utility and create golden gate sub directories
Go to Golden Gate Installed location (in our scenario/GOLDENGATE/GG_HOME_19C) and then run the following Golden Gate in build scripts for creating all necessary objects to support DDL replication.
@marker_setup
@ddl_setup
@role_setup.sql
@ddl_enable.sql
GRANT GGS_GGSUSER_ROLE TO GOLDENGATE;
By default manager parameter has created while installing the golden gate software we just add the user information to manager parameter file.
PORT 7809
Check the manager parameter and status.
view param mgr
Start GGSCI and login into database using dblogin command.
dblogin USERID GOLDENGATE, PASSWORD GOLDENGATE_123
Create a credential store for the Golden Gate user
>>> add credentialstore
>>> alter credentialstore add user GOLDENGATE, password GOLDENGATE_123 alias_ggadmin
>>> dblogin useridalias alias_ggadmin
Source side add trandata for particular table which we wants to replicate the data to target database.
add tranadata IPSLIIVE.*
Create the primary Extract parameter file.
Create the Extract group and the local Extract trail file and start the extract process.
ADD EXTRACT EIPICI1, INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL /GOLDENGATE/GG_HOME_19C/dirdat/ea, EXTRACT EIPICI1
Create the primary Extract parameter file.
EXTRACT EIPICI1
SETENV(ORACLE_HOME='/DBHOME/orabase/oracle')
SETENV(ORACLE_SID='IPSUATRC2')
USERID GOLDENGATE, PASSWORD GOLDENGATE_123
REPORTCOUNT EVERY 60 MINUTES, RATE
FETCHOPTIONS USESNAPSHOT, USELATESTVERSION
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
TRANLOGOPTIONS INTEGRATEDPARAMS (_CKPT_RETENTION_CHECK_FREQ 2700)
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 5120)
--TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 128MB)
TRANLOGOPTIONS CHECKPOINTRETENTIONTIME 0.125
TRANLOGOPTIONS USENATIVEOBJSUPPORT
WARNLONGTRANS 1h, CHECKINTERVAL 30m, SKIPEMPTYTRANS
EXTTRAIL /GOLDENGATE/GG_HOME_19C/dirdat/ea
--EXCEL SHEET 1 TABLES WITH PK AND FK PUMP & REPLICAT PROCESS SHOULD BE SAME FOR BELOW SET OF TABLE (IN PUMP1,REPLICAT1)
TABLE IPSLIVE.CCHG_ADVICE_DTL;
TABLE IPSLIVE.CCHG_ADVICE_WAIVER_DTL;
REGISTER EXTRACT EIPICI1 DATABASE
start extract EIPICI1
ADD EXTRACT EIPICI2, INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL /GOLDENGATE/GG_HOME_19C/dirdat/eb, EXTRACT EIPICI2
REGISTER EXTRACT EIPICI2 DATABASE
Create the secondary Extract (data pump) parameter file.
ADD EXTRACT PIPICI1, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/ea
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/ra,EXTRACT PIPICI1
ADD EXTRACT PIPICI2, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/eb
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/rb,EXTRACT PIPICI2
ADD EXTRACT PIPICI3, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/ea
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/rc,EXTRACT PIPICI3
ADD EXTRACT PIPICI4, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/ea
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/rd,EXTRACT PIPICI4
ADD EXTRACT PIPICI6, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/ea
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/rf,EXTRACT PIPICI6
ADD EXTRACT PIPICI7, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/ea
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/rg,EXTRACT PIPICI7
ADD EXTRACT PIPICI8, EXTTRAILSOURCE /GOLDENGATE/GG_HOME_19C/dirdat/ea
ADD RMTTRAIL /Goldengate/GG_HOME/dirdat/rh,EXTRACT PIPICI8
Create param file for all Extract (data pump)
EXTRACT PIPICI1
SETENV(ORACLE_HOME='/DBHOME/orabase/oracle')
SETENV(ORACLE_SID='IPSUATRC2')
USERID GOLDENGATE, PASSWORD GOLDENGATE_123
REPORTCOUNT EVERY 60 MINUTES, RATE
RMTHOST 10.11.118.38, MGRPORT 7809, COMPRESS
--RMTHOST 10.52.50.127, MGRPORT 7809, COMPRESS
RMTTRAIL /goldengate/GG_HOME_19C/dirdat/ra
PASSTHRU
-- EXCEL SHEET 1 TABLES WITH PK AND FK
TABLE IPSLIVE.CCHG_ADVICE_DTL;
TABLE IPSLIVE.CCHG_ADVICE_WAIVER_DTL;
start extract PIPICI1
To check the status of data pump process.
info PIPICI1
TARGET (ICMSORAUAT-10.11.118.38):
Start GGSCI and login into database using dblogin command.
./ggsci
dblogin userid gguser password gguser
To check the manager status on target server.
view param mgr
info mgr
To create a checkpoint Table in the target database.
ADD CHECKPOINT TABLE goldengate.chkpt
info checkpointtable goldengate.chkpt
Create and start the replicat process.
ADD REPLICAT REPIPS1 , EXTTRAIL /Goldengate/GG_HOME/dirdat/ra, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS11 , EXTTRAIL /Goldengate/GG_HOME/dirdat/ra, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS12 , EXTTRAIL /Goldengate/GG_HOME/dirdat/ra, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS2 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rb, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS3 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rc, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS31 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rc, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS4 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rd, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS41 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rd, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS42 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rd, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS43 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rd, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS44 , EXTTRAIL /Goldengate/GG_HOME/dirdat/rd, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS6, EXTTRAIL /Goldengate/GG_HOME/dirdat/rf, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS7, EXTTRAIL /Goldengate/GG_HOME/dirdat/rg, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS8, EXTTRAIL /Goldengate/GG_HOME/dirdat/rh, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS81, EXTTRAIL /Goldengate/GG_HOME/dirdat/rh, CHECKPOINTTABLE goldengate.chkpt
ADD REPLICAT REPIPS9, EXTTRAIL /Goldengate/GG_HOME/dirdat/ra, CHECKPOINTTABLE goldengate.chkpt
Create the Replicat parameter file for all replicat.
REPLICAT REPIPS1
SETENV(ORACLE_HOME='/ora12203/orabase/oracle')
SETENV(ORACLE_SID='ICMSUAT')
--MACRO #exception_handler
--BEGIN
-- Use the same Golden Gate Exceptions Table for all exception records
--, TARGET GOLDENGATE.EXCEPTIONS
--, COLMAP ( rep_name = @GETENV('GGENVIRONMENT', 'GROUPNAME')
--, TABLE_NAME = @GETENV ('GGHEADER', 'TABLENAME')
--, ERRNO = @GETENV ('LASTERR', 'DBERRNUM')
--, DBERRMSG = @GETENV ('LASTERR', 'DBERRMSG')
--, OPTYPE = @GETENV ('LASTERR', 'OPTYPE')
--, ERRTYPE = @GETENV ('LASTERR', 'ERRTYPE')
--, LOGRBA = @GETENV ('GGHEADER', 'LOGRBA')
--, LOGPOSITION = @GETENV ('GGHEADER', 'LOGPOSITION')
--, COMMITTIMESTAMP = @GETENV ('GGHEADER', 'COMMITTIMESTAMP')
--, GGS_FILENAME = @GETENV('GGFILEHEADER', 'FILENAME')
--, CDRFAIL = @GETENV('DELTASTATS','CDR_RESOLUTIONS_FAILED')
--, CDRSUC = @GETENV('DELTASTATS','CDR_RESOLUTIONS_SUCCEEDED')
--, CDRDETECT = @GETENV('DELTASTATS','CDR_CONFLICTS'))
--, INSERTALLRECORDS
--, EXCEPTIONSONLY;
--END;
USERID GOLDENGATE, PASSWORD goldengate_123
REPORTCOUNT EVERY 60 MINUTES, RATE
--BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 100000, OPSPERBATCH 100000, OPSPERQUEUE 100000, BYTESPERQUEUE 1000000000
--BATCHSQL BATCHESPERQUEUE 100, OPSPERBATCH 2000
--DBOPTIONS NOSUPPRESSTRIGGERS, DEFERREFCONST
--INSERTMISSINGUPDATES
--REPERROR (1403,IGNORE)
IGNOREDELETES
HANDLECOLLISIONS
--UPDATEINSERTS
-- EXCEL SHEET 1 TABLES WITH PK AND FK
MAP IPSLIVE.CCHG_ADVICE_DTL, TARGET IPSLIVE.CCHG_ADVICE_DTL;
MAP IPSLIVE.CCHG_ADVICE_WAIVER_DTL, TARGET IPSLIVE.CCHG_ADVICE_WAIVER_DTL;
start replicat REPIPS1
To check the status of replicat process.
info REPIPS1
info all
========================
Prerequisites of GG-
shutdown
startup mount
alter database archivelog; (No archives are missing from source dp )
alter database open;
select log_mode from v$database;
alter database add supplemental log data(all) columns;
(provide extra suppliment columns for db no data missing)
select SUPPLEMENTAL_LOG_DATA_ALL from v$database;
alter database force logging;
alter system switch logfile;
alter database add supplemental log data;
SCHEMA REPLICATION IN GG
on source:
create user e6p identified by e6p ;
grant connect, resource to e6p;
alter user e6p quota unlimited on users;
--
create user sbz identified by sbz ;
grant connect, resource to sbz;
alter user sbz quota unlimited on users;
add schematrandata e6p
add schematrandata sbz
Extract
add extract e6pex1, integrated tranlog , begin now
add extract sbzex, integrated tranlog , begin now
register extract e6pex1 database
register extract sbzex database
add exttrail /home/oracle/ogghome/dirdat/sa, extract sbzex
edit param e6pex1
extract e6pex1
userid ogg,password ogg
exttrail /home/oracle/ogghome/dirdat/e6
ddl include all
table e6p.*;
--
extract sbzex
userid ogg,password ogg
exttrail /home/oracle/ogghome/dirdat/sa
ddl include all
table sbz.*;
Pump create:
add extract e6pdp1, exttrailsource /home/oracle/ogghome/dirdat/e6
add extract sbzdp, exttrailsource /home/oracle/ogghome/dirdat/sa
add rmttrail /home/oracle/dep/var/lib/data/sb, extract sbzdp
edit param e6pdp1
extract e6pdp1
userid ogg,password ogg
rmthost devdb,mgrport 7809
rmttrail /home/oracle/ogghome/dirdat/r6
table e6p.*;
--
extract sbzdp
userid ogg,password ogg
rmthost 192.168.93.134, port 7003
rmttrail /home/oracle/dep/var/lib/data/st
table sbz.*;
~
REPLICATION
add replicat e6prep1, integrated exttrail /home/oracle/ogghome/dirdat/r6
edit param e6prep1
replicat e6prep1
userid ogg,password ogg
assumetargetdefs
map e6p.* target e6p.*;
add replicat sbzrep, integrated exttrail /home/oracle/ogghome/dirdat/sh
add replicat sbzrep, integrated exttrail /home/oracle/dep/var/lib/data/sb
replicat sbzrep
userid ogg,password ogg
assumetargetdefs
map sbz.* target sbz.*;
ALTER REPLICAT REP1 , EXTTRAIL /home/oracle/dep/var/lib/data/sb
RMTTRAIL /dirdat/bb
PORT 7809
DynamicPortList 7810-7820
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7
--AUTOSTART ER *
AUTORESTART extract *, retries 10, WAITMINUTES 5,RESTMINUTES 60
create table profiles
(
pname varchar2(32) primary key,
pemail varchar2(64),
paddr varchar2(128)
);
insert into profiles values('scott','scott@gmail.com','USA');
insert into profiles values('james','james@gmail.com','UK');
commit
--------------------------------------------------
Table replication
create user fox identified by fox;
grant connect ,resource to fox;
alter user fox quota unlimited on users;
conn fox/fox
create table tab1
(
c1 number primary key,
c2 varchar2(30),
c3 number ,
c4 number
);
add trandata fox.tab1
Creating Extract :
add extract ex2,integrated tranlog ,begin now
register extract ex2 database
add exttrail /home/oracle/ogghome/dirdat/ex, extract ex2
edit param ex2
extract ex2
userid ogg,password ogg
exttrail /home/oracle/ogghome/dirdat/ex
table fox.tab1;
Pump create:
add extract dp2, exttrailsource /home/oracle/ogghome/dirdat/ex
add rmttrail /home/oracle/ogghome/dirdat/r2, extract dp2
edit param dp2
extract dp2
userid ogg, password ogg
rmthost 190.168.93.132 ,mgrport 7809
rmttrail /home/oracle/ogghome/dirdat/r2
table fox.tab1;
REPLICAT create:
add replicat rp2 ,integrated exttrail /home/oracle/ogghome/dirdat/r2
edit param rp2
replicat rp2
userid ogg,password ogg
assumetargetdefs
map fox.tab1 target fox.tab1;
connect fox/fox
insert into tab1 values (1,'alpha',10,100);
insert into tab1 values (2,'beta',20,200);
------------------------------------------------
Table Initial load:
on prod -
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
hire_date DATE,
salary NUMBER
);
INSERT INTO fox.emp(employee_id, first_name, last_name, email, hire_date, salary)
VALUES (4, 'SBZ', 'XYZ', 'sbz@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 550000);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', TO_DATE('2022-03-22', 'YYYY-MM-DD'), 55000);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (3, 'Michael', 'Brown', 'michael.brown@example.com', TO_DATE('2021-07-30', 'YYYY-MM-DD'), 60000);
create table fox.emp as select * from employees;
alter table fox.emp add primary key ("employee_id");
set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','EMP','FOX') from dual;
add exttrail /home/oracle/ogghome/dirdat/es, extract PFOXE1
EXTRACT PFOXE1
USERID ogg, PASSWORD ogg
EXTTRAIL /home/oracle/ogghome/dirdat/es
TABLE FOX.EMP;
Add extract PFOXD1, EXTTRAILSOURCE /home/oracle/ogghome/dirdat/es
Add rmttrail /home/oracle/ogghome/dirdat/rf, extract PFOXD1
EXTRACT PFOXD1
USERID ogg, PASSWORD ogg
RMTHOST 192.168.93.130, MGRPORT 7809
RMTTRAIL /home/oracle/ogghome/dirdat/rf
TABLE FOX.EMP;
add replicat DFOXR1, integrated exttrail /home/oracle/ogghome/dirdat/rf
REPLICAT DFOXR1
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP FOX.EMP TARGET TOM.EMP;
--------------------------------
Initial Load-
ADD EXTRACT INITLE, SOURCEISTABLE
EXTRACT INITLE
userid ogg, password ogg
RMTHOST 192.168.93.130, mgrport 7809
RMTTASK REPLICAT, GROUP INITLR (replicat name)
TABLE FOX.EMP;
ADD REPLICAT INITLR, SPECIALRUN
REPLICAT INITLR
userid ogg, password ogg
ASSUMETARGETDEFS
MAP FOX.EMP, TARGET TOM.EMP;
ACCESSRULE, PROG *, IPADDR *, ALLOW
ALTER TABLE fox.emp ADD emp_join VARCHAR2(100);
ALTER TABLE fox.emp DROP COLUMN emp_location;
alter table fox.emp drop column emp_join;
-----------------------------------------
Bidirectional table replication:
on prod and devdb
conn fox/fox
create table bidir_d
(
pname varchar2(32) primary key,
pemail varchar2(64),
paddr varchar2(128)
);
on prod -
add trandata fox.bidir
on target-
add trandata tom.bidir_d
prod -
add extract foxex ,integrated tranlog ,begin now
register extract foxex database
add exttrail /home/oracle/ogghome/dirdat/lp ,extract foxex
edit param foxex
extract foxex
userid ogg,password ogg
exttrail /home/oracle/ogghome/dirdat/lp
tranlogoptions excludeuser ogg
table fox.bidir;
datapump on prod
add extract foxdp , exttrailsource /home/oracle/ogghome/dirdat/lp
add rmttrail /home/oracle/ogghome/dirdat/rd,extract foxdp
edit param foxdp
extract foxdp
userid ogg,password ogg
rmthost 192.168.93.132, mgrport 7809
rmttrail /home/oracle/ogghome/dirdat/rd
table fox.bidir;
on devdb-
add replicat tomrep, integrated exttrail /home/oracle/ogghome/dirdat/rd
edit param tomrep
replicat tomrep
userid ogg,password ogg
assumetargetdefs
map fox.bidir target tom.bidir_d;
on dev db extract-
add extract tomex,integrated tranlog ,begin now
register extract tomex database
add exttrail /home/oracle/ogghome/dirdat/fx ,extract tomex
add exttrail /home/oracle/dep_dir/var/lib/data/lt,extract tomex
edit param tomex
extract tomex
userid ogg ,password ogg
exttrail /home/oracle/ogghome/dirdat/fx
tranlogoptions excludeuser ogg
table tom.bidir_d;
extract tomex
userid ogg ,password ogg
exttrail /home/oracle/dep_dir/var/lib/data/lt
tranlogoptions excludeuser ogg
table tom.bidir_d;
datapump on dev db
add extract tomdp, exttrailsource /home/oracle/ogghome/dirdat/fx
add rmttrail /home/oracle/ogghome/dirdat/tx ,extract tomdp
edit param tomdp
extract tomdp
userid ogg,password ogg
rmthost 192.168.93.130 ,mgrport 7809
rmttrail /home/oracle/ogghome/dirdat/tx
table tom.bidir_d;
replicat on prod -
add replicat foxrep,integrated exttrail /home/oracle/ogghome/dirdat/tx
add replicat tomrep1 ,integrated exttrail /home/oracle/ogghome/dirdat/cb
add replicat msrep, exttrail C:\GGHOME\dirdat/ss
/home/oracle/DP_DIR/var/lib/data/
edit param foxrep
replicat foxrep
userid ogg ,password ogg
assumetargetdefs
map tom.bidir_d target fox.bidir;
EXEC DBMS_LOGMNR.START_LOGMNR(
STARTSCN => 7482821,
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.CONTINUOUS_MINE);
replicat tomrep1
userid ogg ,password ogg
assumetargetdefs
map tom.bidir_d target fox.bidir;
map fox.bidir target tom.bidir_d;
conn fox/fox
insert into bidir values ('Jay','Jay@gmail.com','KA');
insert into bidir_d values ('aman','aman@gmail.com','MH');
DELETE FROM bidir_d WHERE pname='Ramesh';
insert into bidir values ('rakesh','rakesh@gmail.com','IND');
ALTER TABLE bidir DROP COLUMN P_NO;
insert into bidir values ('ABC','ABC@gmail.com','Sangli'55);
ALTER REPLICAT <foxrep> EXTSEQNO <ld000000004>, EXTRBA <2020>
ADD CHECKPOINTTABLE sbz.bidir
ALTER REPLICAT foxrep EXTSEQNO 2 EXTRBA 2854
add exttrail /home/oracle/ogghome/dirdat/lp, extract foxex
Add extract foxdp, EXTTRAILSOURCE /home/oracle/ogghome/dirdat/lp
Add rmttrail /home/oracle/ogghome/dirdat/rd, extract foxdp
add replicat tomrep, integrated exttrail /home/oracle/ogghome/dirdat/rd
EXTRACT foxex
USERID ogg, PASSWORD ogg
EXTTRAIL /home/oracle/ogghome/dirdat/lp
TRANLOGOPTIONS EXCLUDEUSER OGG
TABLE FOX.BIDIR;
EXTRACT foxdp
USERID ogg, PASSWORD ogg
RMTHOST 192.168.93.130, MGRPORT 7809
RMTTRAIL /home/oracle/ogghome/dirdat/rd
TABLE FOX.BIDIR;
REPLICAT tomrep
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP FOX.BIDIR TARGET TOM.BIDIR_D;
add exttrail /home/oracle/ogghome/dirdat/ld, extract tomex
EXTRACT tomex
USERID ogg, PASSWORD ogg
EXTTRAIL /home/oracle/ogghome/dirdat/ld
TRANLOGOPTIONS EXCLUDEUSER OGG
TABLE TOM.BIDIR_D;
Add extract tomdp, EXTTRAILSOURCE /home/oracle/ogghome/dirdat/ld
Add rmttrail /home/oracle/ogghome/dirdat/rp, extract tomdp
EXTRACT tomdp
USERID ogg, PASSWORD ogg
RMTHOST ggprod, MGRPORT 7809
RMTTRAIL /home/oracle/ogghome/dirdat/rp
TABLE TOM.BIDIR_D;
add replicat foxrep, integrated exttrail /home/oracle/ogghome/dirdat/rp
REPLICAT foxrep
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP TOM.BIDIR_D TARGET FOX.BIDIR;
----------------------
size checking gghome - du -sh $GG_HOME - sh df -Ph .
Replication for different table,schema and different structure
on prod-
conn fox/fox
create table tab3
(
one number primary key ,
two varchar2(30),
three number ,
four number
);
on devdb
conn tom/tom
create table tab3_diffcol
(
col_1 number primary key,
col_2 number ,
col_3 varchar2(20),
col_4 number
);
on prod
add trandata fox.tab3
edit params defgen1
defsfile /home/oracle/ogghome/dirdef/foxtab3def.def
userid ogg,password ogg
table fox.tab3;
./defgen paramfile /home/oracle/ogghome/dirprm/defgen1.prm
Definitions generated for 1 table in /home/oracle/dep/etc/conf/ogg/sbz.temp.def.
scp foxtab3def.def 192.168.93.130:$GG_HOME/dirdef/
scp cwallet.sso 192.168.93.130:/home/oracle/DP_DIR/var/lib/wallet
scp .bash_profile 192.168.93.130:$ORACLE_HOME
on prod -
add extract pfoxt3e ,integrated tranlog,begin now
register extract pfoxt3e database
add exttrail /home/oracle/ogghome/dirdat/a1, extract pfoxt3e
edit param pfoxt3e
extract pfoxt3e
userid ogg,password ogg
exttrail /home/oracle/ogghome/dirdat/a1
table fox.tab3;
Datapump -
add extract pfoxt3d, exttrailsource /home/oracle/ogghome/dirdat/a1
add rmttrail /home/oracle/ogghome/dirdat/b1 ,extract pfoxt3d
add rmttrail /home/oracle/DP_DIR/var/lib/data/rd ,extract foxex
edit param pfoxt3d
extract pfoxt3d
userid ogg,password ogg
rmthost 192.168.93.130, mgrport 7809
rmttrail /home/oracle/ogghome/dirdat/b1
table fox.tab3;
replicat
add replicat dtomltr, integrated exttrail /home/oracle/ogghome/dirdat/b1
edit param dtomltr
replicat dtomltr
userid ogg ,password ogg
sourcedefs /home/oracle/ogghome/dirdef/foxtab3def.def
map fox.tab3 target tom.tab3_diffcol,colmap (col_1=one,col_3=two,col_2=three,col_4=four);
ADD replicat msrep,INTEGRATED TRANLOG, BEGIN NOW
ADD REPLICAT msrep, EXTTRAIL C:\GGHOME\dirdat\ss, checkpointtable gg.chk1
replicat msrep
dblogin sourcedb gg
sourcedefs C:\GGHOME\dirdef/sbz.rep_tab1.def
map sbz.rep_tab1 target hr.rep_tab;
- GoldenGate Initial Data Load Delivery
--
REPLICAT RINI<unique id>
TARGETDB <dsn>, USERID <login>, PASSWORD <password>
DISCARDFILE ./dirrpt/RINI<unique id>.txt, PURGE
SOURCEDEFS ./dirdef/source.def
MAP <owner/schema>.TCUSTMER, TARGET <owner/schema>.TCUSTMER
replicat initrep
targetdb gg
SOURCEDEFS ./dirdef/emp.def
map sbz.emp target sb.emp;
for semi different table
only replicat param need to change -
replicat dtomltr
userid ogg ,password ogg
sourcedefs /home/oracle/ogghome/dirdef/foxtab3def.def
map fox.tab3 target tom.tab3_diffcol,colmap (useddefaults,col_3=three,col_4=four);
insert into tab3 values(1,'alpha',10,100);
insert into tab3 values (2,'beta',20,200);
insert into tab3 values (3,'gamma',30,300);
alter table bidir add col_name P_NO;
export OGG_ETC_HOME=/home/oracle/dep_dir/etc
export OGG_VAR_HOME=/home/oracle/dep_dir/var
export OGG_HOME=/home/oracle/OGGHOME
export PATH=$OGG_HOME:$PATH
&ORACLE_HOME
CONNECT http://192.168.93.134:7000 USER ogg PASSWORD sys
add extract msex, exttrailsource /home/oracle/dep/var/lib/data/ns
add rmttrail /home/oracle/DP_DIR/var/lib/data/rd ,extract foxex
extract msex
useridalias ogg domain OracleGoldenGate
exttrail /home/oracle/dep/var/lib/data/ms
table sbz.rep_tab1;
/home/oracle/dep/etc/conf/ogg
ORACLE_HOME=/u01/app/oracle/product/19/db_1;
export ORACLE_HOME
register extract msex database
ORACLE_SID=devdb; export ORACLE_SID
export ORACLE_HOME=/u01/app/oracle/product/19/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
exec dbms_goldengate_auth.grant_admin_privilege ('ggadmin', container=>'all');
create user ggadmin identified by ogg default tablespace GG_TBS;
grant dba to ggadmin container=all;
LOG_GROUP_NAME
add credentialstore userid ogg@devdb, password ogg
add credentialstore userid ogg@devdb, password ogg
delete credentialstore userid ogg@devdb
export TNS_ADMIN=/home/oracle/dep_dir/var/lib/wallet
export TNS_ADMIN=$ORACLE_HOME/network/admin
dblogin useridalias ogg
/home/oracle/dep_dir/var/lib/data
/home/oracle/DP_DIR/var/lib/data/rd
ADD CHECKPOINTTABLE ogg.chk1.ggcheckpoint
ADD CHECKPOINTTABLE ogg.chk01
tools which we can use to get source tables metadata in hybrid configuration database
./runInstaller -silent -showProgress -waitforcompletion -responseFile /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
ADD CHECKPOINTTABLE sbz.GGCHECKPOINT
ALTER REPLICAT sbzrep, SCN 6422728
ALTER REPLICAT sbzrep, EXTSEQNO 9
ALTER REPLICAT sbzrep, EXTRBA 0
ALTER DISTPATH Path BEGIN SEQNO 7 RBA 3056
add replicat sbzrep, integrated exttrail /home/oracle/ogghome/dirdat/sh
Chcking running extract on database;
select capture_name,start_scn,source_database,status from dba_capture;
AUTOSTART EXTRACT
PURGEOLDEXTRACTS ./dirdat/* MINKEEPDAYS 1 USECHECKPOINTS
PURGEOLDEXTRACTS /home/oracle/DP_DIR/var/lib/data/* MINKEEPDAYS 1 USECHECKPOINTS
send sbzex get param info
----------------------------------------------
LOGDUMP COMMANDS
Logdump 1 >open /home/oracle/ogghome/dirdat/sh000000002
Current LogTrail is /home/oracle/ogghome/dirdat/sh000000002
Logdump 2 >ghdr on
Logdump 3 >detail data
Logdump 4 >detail on
Logdump 5 >count
POS EOF
POS -r
n
decrypt on (for seeing the trail files) in logdump utility
new trail reading - next
Create the Master Key for trail files
Stop both ext and pump -
GGSCI> create wallet
GGSCI> open wallet
GGSCI> add masterkey
GGSCI> sh ls dirwlt
GGSCI> info masterkey
Add this parameters in extract
ENCRYPTTRAIL AES192
ENCRYPTTRAIL (By default it will take AES128)
scp cwallet.sso 192.168.93.130:/home/oracle/DP_DIR/var/lib/wallet
2024-06-27 03:18:29 INFO OGG-25209 Retrieved masterkey OGG_DEFAULT_MASTERKEY version 1 with state Active.
.nlXn....G4]..W...v.
...5t^.L4..6d...>..x
F..,..;o>..{.u...~..
BrG.Z(...U...l.M...=
"..uQ/... ...}.....
alter sbzdp etrollover
ALTER EXTRACT sbzex, SCN 6725454
ALTER replicat sbzrep, EXTSEQNO 6, EXTRBA 1483
add replicat rep1 coordinated exttrail ./dirdat/rt maxthreads 5
MAP sbz.temp, TARGET sbz.temp, THREADRANGE (1-3,temp_id);
replicat sbzrep
useridalias ogg domain OracleGoldenGate
-- PURGEOLDEXTRACTS /home/oracle/DP_DIR/var/lib/data/* MINKEEPDAYS 1 USECHECKPOINTS
HANDLECOLLISIONS
DDL INCLUDE ALL
assumetargetdefs
map sbz.* target sbz.*;
PARAM FILE FOR CO REP-
replicat crep
useridalias ogg domain OracleGoldenGate
HANDLECOLLISIONS
DDLERROR 955 IGNORE
DDL INCLUDE ALL
assumetargetdefs
map sbz.* target sbz.*,THREADRANGE (1-3,id);
IMP PARAMETER FOR INT REPLICAT FOR INCREASING THE SPEED OF APPLY PROCESS
DBOPTIONS INTEGRATEDPARAMS(PARALLELISM 4, EAGER_SIZE 22000)
---------------------------------------------------
Checking the open running transction(Uncommited) -
send sbzex showtrans
-- Checking old trans
info sbzex showch detail
Checking the conflict detection report
stats replicat SBZREP ,report CDR
--------------------------------------------------
*** Handling addtiional column on the source and target DB
If source table has extract column so we want need map it on the target DB -
map sbz.temp,target sbz.temp, colmap(useddefaults ,ID=@COLSTAT(MISSING));
If we don't want to include we skip the col
sbz.temp ,COLEXECEPT (ID);
----------------------------------------------------
Macro$ SBM
BEGIN
GETINSERT
IGNOREUPDATE
GETDELETES
END;
=======================================
MSSQL TO ORACLE REPLICATION WITH GOLDENGATE BIDIRECTIONAL :
DATABASE MSSQL -2017 ENTERPRISE EDITION (classic ext and rep)
ORACLE 19C - (GG VERSION 19C BOTH) (classic ext and rep)
DB_NAME - SB (SQL)
DB_NAME - DEVDB(ORACLE)
prerequiste:
USE sb
GO
create schema gg;
note for windows os only
shell install addservice (as a adm)
Install addevents addservice
administers GoldenGate (gg) his role db_owner and as long as the installation process lasts his role sysadmin:
GO
EXEC sp_addrolemember 'db_owner', 'gg'
GO
EXEC sp_addsrvrolemember 'gg', 'sysadmin';
GO
execute sys.sp_cdc_drop_job 'cleanup';
shell ogg_cdc_cleanup_setup.bat createJob gg ogg sb DELL_SB gg
setup mgr on source
GGSCI (DELL_SB) 103> view param mgr
port 7809
PURGEOLDEXTRACTS ./dirdat/*, MINKEEPHOURS 1
ACCESSRULE, PROG *, IPADDR *, ALLOW
on GLOBALS file (file should be in gghome loaction not in prm with no extension)
GGSCHEMA gg
checkpointtable gg.chk1
start manager
=============
dblogin sourcedb gg userid gg password ogg
After connecting with the user, we set supplemental logging to be enabled for each table we want to replicate:
add trandata hr.rep_tab
=>Once we enable supplemental logging it will enable it Change Data Capture in the SQL Server table.
Along with the table it will also create one cleanup job of SQL Server Agent which deletes information that is no longer needed.
But this cleanup job does not check if the Extract process of GoldenGate managed to record the information, so we will have to delete
it and make a new one in its place.
To delete the original cleanup job, connect to the instance of SQL Server with the following query:
execute sys.sp_cdc_drop_job 'cleanup';
=>Then to create the compatible cleanup job, we return to the GoldenGate interface and run the following
command putting in order username, password, dbname, hostname and schema
ogg_cdc_cleanup_setup.bat createJob user1 pword1 dbname1 HOSTNAME\INSTANCE oggschema
shell ogg_cdc_cleanup_setup.bat createJob gg ogg sb DELL_SB gg
==================
MSSQL TO ORACLE REPLICATION
defgen file
defgen paramfile C:\GGHOME\dirprm\defgen.prm
Definitions generated for 1 table in C:\GGHOME\dirdef/hr.rep_tab.sql.
prm file:
defsfile C:\GGHOME\dirdef/hr.rep_tab.sql
sourcedb gg userid gg password ogg
table hr.rep_tab;
EXTRACT
============
add extract msext , tranlog , begin now
add exttrail C:\GGHOME\dirdat\i2 ,extract msext
GGSCI (DELL_SB) 104> view param msext
EXTRACT MSEXT
sourcedb gg userid gg password ogg
EXTTRAIL C:\GGHOME\dirdat\i2
TABLE hr.rep_tab;
PUMP
==============
add extract msdp, exttrailsource C:\GGHOME\dirdat\i2
add rmttrailRMTTRAIL /home/oracle/dep/var/lib/data/ms,, extract dp
GGSCI (DELL_SB) 105> view param msdp
EXTRACT MSDP
SOURCEDB gg userid gg password ogg
RMTHOST 192.168.93.134, PORT 7003
RMTTRAIL /home/oracle/dep/var/lib/data/ms,
TABLE hr.rep_tab;
REPLICAT
==============
add replicat srep, exttrail /home/oracle/ogghome/dirdat/ms
replicat srep
useridalias ogg domain OracleGoldenGate
SOURCEDEFS /home/oracle/sm/var/lib/def/hr.rep_tab.sql
HANDLECOLLISIONS
MAP hr.rep_tab, TARGET sbz.rep_tab1;
-----------------------------------
ORACLE TO MSSQL REPLICATION
defgen file
defgen paramfile /home/oracle/dep/etc/conf/ogg/defgen.prm
/home/oracle/dep/etc/conf/ogg/sbz.rep_tab1.sql
[oracle@test1 ogg]$ cat defgen.prm
defsfile /home/oracle/dep/etc/conf/ogg/sbz.rep_tab1.sql
userid ogg@devdb password ogg
table sbz.rep_tab1;
==============
EXTRACT:
extract msex
useridalias ogg domain OracleGoldenGate
exttrail ns
table sbz.rep_tab1;
==============
DISTPATH (ON GG MS19C)
Status:
running
Description:
Source:
trail://192.168.93.134:7002/services/v2/sources?trail=ns
Target:
ogg://192.168.1.33:7809/services/v2/targets?trail=C:\GGHOME\dirdat/ss
DB Name:
DEVDB
Extract:
MSEX
Trail Name:
ns
Source Trail File:
ns000000003
REPLICAT ON SQL SERVER
=======================
add checkpointtable gg.chk1
add replicat msrep, exttrail ./dirdat/ms, checkpointtable gg.chk1
GGSCI (DELL_SB) 106> view param msrep
replicat msrep
targetdb gg userid gg password ogg
sourcedefs C:\GGHOME\dirdef/sbz.rep_tab1.sql
HANDLECOLLISIONS
map sbz.rep_tab1 target hr.rep_tab;
OUTPUT:
C1 C2 C3 C4 CREATION_
---------- ------------------------------ ---------- ---------- ---------
120 THANE 100 200 08-NOV-25
119 PUNE 100 200 02-AUG-24
121 RABALE 100 200 08-NOV-25
123 SANGALI 100 200 02-AUG-24
124 AIROLI 100 200 02-AUG-24
----------------------------
ORACLE TO MSSQL Initial Load :
add extract Init,sourceistable
extract Init
useridalias ogg domain OracleGoldenGate
rmthost 192.168.1.33 , mgrport 7809
RMTTASK REPLICAT, GROUP Initrep
TABLE sbz.rep_tab1;
Add replicat initrep, specialrun
GGSCI (DELL_SB) 72> view param initrep
replicat initrep
targetdb gg userid gg password ogg
sourcedefs C:\GGHOME\dirdef/sbz.rep_tab1.sql
map sbz.rep_tab1 target hr.rep_tab2;
========================
ORACLE TO MSSQL - FILE TO REPLICAT Load:
add extract Fileload, sourceistable
extract Fileload
useridalias ogg domain OracleGoldenGate
RMTHOST 192.168.26.92, MGRPORT 7809
rmtfile C:\GGHOME\dirdat\F2.dat
TABLE sbz.rep_tab1;
rmtfile max size change
RMTFILE <path_to_rmt_file> <size_in_mb>
RMTFILE C:\GGHOME\dirdat\F2.dat 100
Normal REPLICAT on target:
add replicat nrep, extfile C:\GGHOME\dirdat/f2.dat ,checkpointtable gg.chk1
replicat nrep
targetdb gg userid gg password ogg
sourcedefs C:\GGHOME\dirdef/sbz.rep_tab1.sql
map sbz.rep_tab1 target hr.rep_tab2;
=========================
PERFORMANCE IMPROVE IN GG
ON EXTRACT:
TRANLOGOPTIONS DBLOGREADER
FETCHOPTIONS NOUSESNAPSHOT
CACHEMGR CACHESIZE 512MB
TRANLOGOPTIONS MAXREADSIZE 128
Replicat Parameters:
BATCHSQL
BATCHSQL MAXBATCHES 500
GROUPTRANSOPS 5000
INSERTAPPEND
======================
TABLE SIZE Checking IN ORACLE :
SELECT SEGMENT_NAME AS "REP_TAB1",
ROUND(SUM(BYTES) / 1024 / 1024 , 2) AS "Size (MB)"
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
AND OWNER = 'SBZ'
AND SEGMENT_NAME = 'REP_TAB1'
GROUP BY SEGMENT_NAME;
REP_TAB1 Size (MB)
-------------------- ----------
REP_TAB1 64
==========================
ORACLE TO MSSQL CSV FILE DATA IMPORT :
create a csv file in oracle database :
set colsep ','
set pagesize 0
set linesize 32767
set feedback off
spool /u01/test.csv
select * from sbz.rep_tab1 as of scn 8514677;
spool off
transfer it mssql server location
Using BULK INSERT:
BULK INSERT hr.rep_tab2
FROM 'C:\Users\barak\Downloads/test.csv'
WITH
(
FIELDTERMINATOR = ',', -- Or another delimiter
ROWTERMINATOR = '\n', -- Use '\r\n' for Windows files
FIRSTROW = 0 -- Skip the header row if there is one
);
----------------
Using OPENROWSET
INSERT INTO hr.rep_tab2 (Column1, Column2, Column3)
SELECT *
FROM OPENROWSET(
BULK 'C:\Users\barak\Downloads/test.csv',
FORMAT = 'CSV',
FIRSTROW = 0
) AS Data;
==================================
Comments
Post a Comment