The part 1 of this blog post can be found in this link
I’ll go through the rest of the steps quickly here with explanations wherever required. Rest of the explanation can be read from this link.
I’ll go through the rest of the steps quickly here with explanations wherever required. Rest of the explanation can be read from this link.
5. Param Files Setup
5.1 Source files:
Place all these files in the dirprm
directory under source GoldenGate home.
mgr.prm
PORT 11000
DYNAMICPORTLIST 15010-15020
msd1e.prm
EXTRACT MSD1E
SOURCEDB GG
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.56.131, MGRPORT
12000
RMTTRAIL
/u01/app/oracle/product/12.1/oggcore_1/dirdat/za
TABLE GG_USER.EMP;
When you enable supplemental logging with the ADD TRANDATA command
for at least one table in a SQL Server database, a secondary truncation point
is created in the transaction log that has to be moved for log space to be
released as needed, following subsequent log backups.
MANAGESECONDARYTRUNCATIONPOINT
- Extract will not be running concurrently (for the same source database) with
SQL Server transactional replication and/or CDC that is configured for
applications other than Oracle GoldenGate. To read more about this parameter
see this link.
msd1i.prm
SOURCEISTABLE
SOURCEDB GG
RMTHOST 192.168.56.131, MGRPORT
12000
RMTFILE
/u01/app/oracle/product/12.1/oggcore_1/dirdat/zi
TABLE gg_user.emp;
SOURCEISTABLE should be
used on initial load extract alone which dictates the process to extract data
directly from the table and use created DSN name for SOURCEDB to connect to the database (GG in our case).
Note: In this demo I have
not used the local trail and pump processes unlike I have used in the Oracle to
Oracle replication as explained in this link. It is always best
practice to use local trails for real time capture and send to remote trail
using data pump process.
5.2 Target Files:
I would like to replicate the data on to EMPLOYEE table under OPENSUSE
user. I already have a table with the below definition. ID column has a unique
constraint enabled.
SQL> desc opensuse.employee Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER FIRST_NAME VARCHAR2(50) LAST_NAME VARCHAR2(50)
Place all these files in the dirprm
directory under target GoldenGate home.
mgr.prm
PORT 12000
DYNAMICPORTLIST 12010-12020
USERID GGS_DBA, PASSWORD ggs_dba
msd1r.prm
REPLICAT MSD1R
SOURCEDEFS /u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def
USERID ggs_dba, PASSWORD ggs_dba
HANDLECOLLISIONS
MAP GG_USER.emp, TARGET
opensuse.employee;
msd1i.prm
SPECIALRUN
END RUNTIME
USERID ggs_dba, PASSWORD ggs_dba
EXTFILE /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi
SOURCEDEFS
/u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def
MAP gg_user.emp, TARGET opensuse.employee;
6. Final Destination – The replication
Now once all the files are placed in their respective directories, do
the following operations in the Source and Target as shown below.
Target:
Start the manager process to allow incoming connections for
GoldenGate.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (opensuse.selvapc.com) 1> start manager Manager started. GGSCI (opensuse.selvapc.com) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (opensuse.selvapc.com) 3> exit oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1>
Source:
Start the manager, real time extraction process and initial extract
process.
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>ggsci Oracle GoldenGate Command Interpreter for SQL Server Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22 Operating system character set identified as windows-1252. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (Prinky) 1> start manager Manager started. GGSCI (Prinky) 2> ADD EXTRACT msd1e, TRANLOG, BEGIN NOW EXTRACT added. GGSCI (Prinky) 3> ADD RMTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za, EXTRACT msd1e RMTTRAIL added. GGSCI (Prinky) 4> start ext msd1e Sending START request to MANAGER ... EXTRACT MSD1E starting GGSCI (Prinky) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING MSD1E 00:00:47 00:00:05 GGSCI (Prinky) 6> stats ext msd1e Sending STATS request to EXTRACT MSD1E ... No active extraction maps.
-- At this point, we don't have any active changes happening at the
database in the real time even though we have data in the table.
-- When there is a change in the database (either of an insert,
update, delete), then the process will show the status as below.
-- Inserted 4 rows to the gg_user.emp
table now
-- Data in the source table now is as belowuse ggate select * from gg_user.emp -- Result: id first_name last_name 1 Selva Kumar 2 Rajini Kanth 3 David Billa 4 Manic Badsha 5 Manne Pacquo 6 Rolls Royse 7 Big Ben 8 Masala PooriContinuing the operations in ggsci prompt
GGSCI (Prinky) 7> stats ext msd1e
Sending STATS request to EXTRACT MSD1E ...
Start of Statistics at 2016-05-08 02:19:16.
Output to /u01/app/oracle/product/12.1/oggcore_1/dirdat/za:
Extracting from gg_user.emp to gg_user.emp:
*** Total statistics since 2016-05-08 02:17:42 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2016-05-08 02:17:42 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2016-05-08 02:17:42 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2016-05-08 02:17:42 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
GGSCI (Prinky) 8> exit
Start
the initial extract using "extract"
executable so that the process ends when the initial data is completely
extracted.E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>extract paramfile dirprm\msd1i.prm reportfile dirrpt\msd1i.rpt
2016-05-08 02:23:23 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
Oracle GoldenGate Capture for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 16:22:53
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-05-08 02:23:23
***********************************************************************
Operating System Version:
Microsoft Windows 8 , on x64
Version 6.2 (Build 9200)
Process id: 3328
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-05-08 02:23:23 INFO OGG-03059 Operating system character set identified as windows-1252.
2016-05-08 02:23:23 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2016-05-08 02:23:23 WARNING OGG-05236 ODBC Warning: The specified DSN 'GG' uses a client driver that may be incompatible with the database server. Microsoft SQL Server 2012 requires SQLNCLI11.DLL or a more recent version.
2016-05-08 02:23:23 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.
2016-05-08 02:23:23 INFO OGG-03037 Session character set identified as windows-1252.
2016-05-08 02:23:23 INFO OGG-06509 Using the following key columns for source table gg_user.emp: id.
2016-05-08 02:23:23 INFO OGG-01851 filecaching started: thread ID: 72465440.
2016-05-08 02:23:23 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: MapViewOfFile anon free: UnmapViewOfFile
file alloc: MapViewOfFile file free: UnmapViewOfFile
target directories:
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 4G
CACHEPAGEOUTSIZE (default): 4M
PROCESS VM AVAIL FROM OS (min): 7.97G
CACHESIZEMAX (strict force to disk): 6.88G
2016-05-08 02:23:23 WARNING OGG-01842 CACHESIZE PER DYNAMIC DETERMINATION (4G) LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 7.97G
Check swap space. Recommended swap/extract: 128G (64bit system).
Database Version:
Microsoft SQL Server
Version 11.00.5058
ODBC Version 03.80.0000
Driver Information:
SQLSRV32.DLL
Version 10.00.10240
ODBC Version 03.52
2016-05-08 02:23:29 INFO OGG-01226 Socket buffer size set to 65536 (flush size 27985).
2016-05-08 02:23:29 INFO OGG-01478 Output file /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi is using format RELEASE 12.2.
2016-05-08 02:23:29 INFO OGG-02911 Processing table gg_user.emp.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2016-05-08 02:23:29 (activity since 2016-05-08 02:23:23)
Output to /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi:
From Table gg_user.emp:
# inserts: 8
# updates: 0
# deletes: 0
# discards: 0
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
Now you can notice that all the 8 records in the table has been
extracted by the initial load extract.
Target:
Start the initial replicat,
real time replicat with HANDLECOLLISIONS and without HANDLECOLLISIONS and check
for data sync.
Initial replicat can be started as a background process so that it
can completed and exited when loading is done.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> sqlplus opensuse/opensuse
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 8 02:59:24 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Apr 30 2016 08:46:44 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from employee;
no rows selected
SQL> !
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> echo $OGG_HOME
/u01/app/oracle/product/12.1/oggcore_1
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt &
[1] 27150
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> nohup: ignoring input and appending output to ‘nohup.out’
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> jobs
[1]+ Running nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt &
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> jobs
[1]+ Running nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt &
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> jobs
[1]+ Done nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> exit
exit
SQL> set lines 300
SQL> select * from employee;
ID FIRST_NAME LAST_NAME
---------- -------------------------------------------------- --------------------------------------------------
1 Selva Kumar
2 Rajini Kanth
3 David Billa
4 Manic Badsha
5 Manne Pacquo
6 Rolls Royse
7 Big Ben
8 Masala Poori
8 rows selected.
SQL> !
Now at this point I have added one record to the table at source by
using the below command.
use
ggate
insert into gg_user.emp values (9, 'Mercedez', 'Benz')
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (opensuse.selvapc.com) 1> dblogin userid ggs_dba, password ggs_dba
Successfully logged into database.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 2> ADD REPLICAT msd1r, EXTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za, CHECKPOINTTABLE ggs_dba.checkpoint
REPLICAT added.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 3> alter replicat msd1r, begin 2016-05-08 02:23:23 -- This is the time we started our initial extract.
2016-05-08 19:43:04 INFO OGG-06594 Replicat MSD1R has been altered through GGSCI. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start MSD1R with NOFILTERDUPTRANSACTIONS option.
REPLICAT altered.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 4> start rep msd1r
Sending START request to MANAGER ...
REPLICAT MSD1R starting
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING MSD1R 00:00:00 00:00:04
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 6> stats rep MSD1R
Sending STATS request to REPLICAT MSD1R ...
Start of Statistics at 2016-05-08 19:43:22.
Replicating from gg_user.emp to OPENSUSE.EMPLOYEE:
*** Total statistics since 2016-05-08 19:43:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2016-05-08 19:43:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2016-05-08 19:43:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2016-05-08 19:43:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 7> exit
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> exit
exit
SQL> select * from employee;
ID FIRST_NAME LAST_NAME
---------- -------------------------------------------------- --------------------------------------------------
1 Selva Kumar
2 Rajini Kanth
3 David Billa
4 Manic Badsha
5 Manne Pacquo
6 Rolls Royse
7 Big Ben
8 Masala Poori
9 Mercedez Benz
9 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1>
If you take a look at the above command line 3, you might notice I
have altered the replicat to start at the exact time the initial extract is
started (can be obtained from initial extract report file). This is because,
the initial extract will take all the records from the table directly and
trying to applying the real time changes before this point is unnecessary and
would be an overhead for GoldenGate process as it has to handle any collisions
of data. That’s the reason, the real time replicat has inserted only one record
that it captured after the mentioned time.
Now let’s see what would have happened if we didn’t alter the begin
time of the replicat.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (opensuse.selvapc.com) 1> dblogin userid ggs_dba, password ggs_dba
Successfully logged into database.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 2> ADD REPLICAT msd1r, EXTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za, CHECKPOINTTABLE ggs_dba.checkpoint
REPLICAT added.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 3> start replicat msdr1
ERROR: REPLICAT MSDR1 does not exist.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 4> start replicat msd1r
Sending START request to MANAGER ...
REPLICAT MSD1R starting
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING MSD1R 00:00:00 00:00:03
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 6> stats rep msd1r
Sending STATS request to REPLICAT MSD1R ...
Start of Statistics at 2016-05-08 13:31:26.
Replicating from gg_user.emp to OPENSUSE.EMPLOYEE:
*** Total statistics since 2016-05-08 13:31:17 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
Total insert collisions 4.00
*** Daily statistics since 2016-05-08 13:31:17 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
Total insert collisions 4.00
*** Hourly statistics since 2016-05-08 13:31:17 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
Total insert collisions 4.00
*** Latest statistics since 2016-05-08 13:31:17 ***
Total inserts 4.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 4.00
Total insert collisions 4.00
End of Statistics.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING MSD1R 00:00:00 00:00:08
-- The insert on the source database happened at this point in time.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 8> stats rep MSD1R
Sending STATS request to REPLICAT MSD1R ...
Start of Statistics at 2016-05-08 19:40:01.
Replicating from gg_user.emp to OPENSUSE.EMPLOYEE:
*** Total statistics since 2016-05-08 13:31:17 ***
Total inserts 5.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
Total insert collisions 4.00
*** Daily statistics since 2016-05-08 13:31:17 ***
Total inserts 5.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
Total insert collisions 4.00
*** Hourly statistics since 2016-05-08 19:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2016-05-08 13:31:17 ***
Total inserts 5.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
Total insert collisions 4.00
End of Statistics.
GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 9> exit
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1>
In the above scenario, you can see that we have 4 collisions that the
GoldenGate process has handled and inserted only the record which is missing.
Once after all the records has been applied, you can stop the replicat process,
edit the parameter to remove the HANDLECOLLISIONS and then start the replicat
again to reduce the work overhead.
The parameter file would look like below.
msd1r.prm
REPLICAT MSD1R
SOURCEDEFS
/u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def
USERID ggs_dba, PASSWORD ggs_dba
MAP GG_USER.emp, TARGET
opensuse.employee;
This completes the replication setup between the source MS SQL Server
database and the target Oracle database. Hope you have enjoyed the article.
7. References
ü
OTN article - Replicating Transactions Between Microsoft
SQL Server and Oracle Database Using Oracle GoldenGate by Nikolay
Manchev
ü
Oracle documentation - Oracle® GoldenGate Installing and
Configuring Oracle GoldenGate for SQL Server
Happy Replicating!!!
