合 【OGG】Using the Oracle GoldenGate for SQL Server CDC Capture Replication

参考：https://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/12c/sql_cdcrep/sql_cdcrep.html

Before You Begin

This tutorial shows you how to use the CDC Capture replication, which utilizes the SQL Server Change Data Capture feature to read DML from the transaction log and load it into individual staging tables for each user table enabled with supplemental logging. The CDC Extract then reads the DML from the staging tables and reconstructs transactions then writes the data into trail files.

This tutorial takes approximately 15 minutes to complete.

Background

Previously, only Classic Capture was possible with SQL Server databases. With the Oracle GoldenGate 12c (12.3.0.1) release, the CDC Capture replication is introduced.

What Do You Need?

Before starting this tutorial:

Using the following tasks, you create both a Uni-Directional and a Bi-Directional SQL Server to SQL Server Replication.

一、Setting Up the Uni-Directional CDC Extract

1. Create a system DSN to the source database and set the change the default database to option to the source database. Use a Windows or SQL Server login that has sysadmin rights for this connection. You can alter the permissions to dbowner at a later time, if you want to use the same account for the Extract and are not allowed to have Extract running with sysadmin.

2. Unzip the ggs_Windows_x64_MSSQL_64bit_CDC.zip file to a new Oracle GoldenGate installation directory.

3. Create a GLOBALS file in the base Oracle GoldenGate installation directory, and set the GGSCHEMA parameter to that of an existing or new schema in the source database. Oracle recommends that you create a specific schema for Oracle GoldenGate objects.

   For example -

   CREATE SCHEMA ggs

   1	CREATE SCHEMA ggs

   . Do not to use the dbo schema.

   GGSCI> EDIT PARAMS ./GLOBALS

   1	GGSCI> EDIT PARAMS ./GLOBALS

   Save the GLOBALS file.

   Using GGSCHEMA in the GLOBALS file is a new requirement for Oracle GoldenGate for SQL Server CDC Capture. It is required so that ADD TRANDATA can identify which schema to create necessary objects under then Extract knows which schema to call those objects from during runtime. Classic Extract does not have this requirement.

4. Start ggsci.exe and create the necessary sub directories.

   GGSCI> CREATE SUBDIRS

   1	GGSCI> CREATE SUBDIRS

5. Create the Manager parameter file; list a valid PORT for the Manager to use, then save the file. For example,PORT 7809

   GGSCI> EDIT PARAMS MGR

   1	GGSCI> EDIT PARAMS MGR

6. Connect to the source database from GGSCI and enable supplemental logging for the user tables to be captured from using one of the following:

   For a SQL Server Authenticated DSN, the USERID and PASSWORD are optional:

   GGSCI> DBLOGIN SOURCEDB sourcedsn [USERID user PASSWORD password] GGSCI> ADD TRANDATA dbo.*

   1 2	GGSCI> DBLOGIN SOURCEDB sourcedsn [USERID user PASSWORD password] GGSCI> ADD TRANDATA dbo.*

   Or for a Windows Authenticated DSN:

   GGSCI> DBLOGIN SOURCEDB sourcedsn GGSCI> ADD TRANDATA dbo.*

   1 2	GGSCI> DBLOGIN SOURCEDB sourcedsn GGSCI> ADD TRANDATA dbo.*

7. In the Management Studio, within a query window for the source database. You must manually drop the SQL Server CDC cleanup job for the database because it may cause data loss for the Extract.

   EXECUTE sys.sp_cdc_drop_job 'cleanup';

   1	EXECUTE sys.sp_cdc_drop_job 'cleanup';

8. Use the ogg_cdc_cleanup_setup.bat utility (in the Oracle GoldenGate installation directory) to create the Oracle GoldenGate CDC cleanup job and associated objects. The ggschema name used must be the same that you used with the

   GGSCHEMA parameter of the GLOBALS file. You must use a SQL Server authenticated user that has sysadmin rights.

   d:\>OGG\ogg_cdc_cleanup_setup.bat createJob username password databasename servername\instancename ggschema

   1	d:\>OGG\ogg_cdc_cleanup_setup.bat createJob username password databasename servername\instancename ggschema

9. Create and save a new Extract parameter file using this sample of the minimum required parameters for a uni-directional implementation.

   GGSCI> EDIT PARAMS cdcext EXTRACT cdcext SOURCEDB sourcedsn [USERID user PASSWORD password] EXTTRAIL ./dirdat/ce TABLE dbo.*;

   1 2 3 4 5 6

   GGSCI> EDIT PARAMS cdcext                             EXTRACT cdcext SOURCEDB sourcedsn [USERID user PASSWORD password] EXTTRAIL ./dirdat/ce   TABLE dbo.*;

   本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
   后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

   验证码：

   获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

   相关文章

   • 使用OGG for MSSQL微服务快速双向同步RDS数据库（双主）
   • OGG-00446 Could not find archived log for sequence
   • OGG从入门到高可用系列
   • OGG之logdump系列
   • Connection test fails with: weblogic.transaction.internal.TimedOutException: Transaction timed out after 60 seconds (文档 ID 1669166.1)
   • 视图DBA_GOLDENGATE_SUPPORT_MODE引起的集成模式OGG不同步问题排查过程
   • OGG抽取进程与数据字典的关系
   • OGG不抽取或不应用数据No active extraction maps 或 No active replication maps
   • 在Oracle中，常用的OGG运维命令有哪些？
   • OGG之BR（Bounded Recovery ）说明
   • 使用OGG微服务将PG同步到kafka(全量+增量)
   • 使用OGG传统模式将PG同步到kafka(全量+增量)
   • 使用OGG微服务将Oracle同步到kafka(全量+增量)
   • OGG故障OGG-12029、OGG-08241、OGG-08221、OGG-01758、OGG-02870、RMAN-08137
   • 使用OGG传统模式将Oracle同步到kafka(全量+增量)
   • 报错“OGG-01201：Error reported by MGR : Access denied”
   • 使用OGG 21c迁移Oracle 12c到MySQL 8.0并配置实时同步
   • OGG-05798 Write LOB Column Segment Operation (token 0x23) Detected At Trail File (Doc ID 2843936.1)
   • How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
   • 使用OGG 21.3远程实时互相同步Oracle 11.2.0.4（双主）
   打赏赞(1)分享