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.
Previously, only Classic Capture was possible with SQL Server databases. With the Oracle GoldenGate 12c (220.127.116.11) release, the CDC Capture replication is introduced.
What Do You Need?
Before starting this tutorial:
The SQL Server 2008/2008R2/2012/2014/2016 Enterprise Edition on Windows (Developer Edition can also be used for testing purposes), or SQL Server 2016 SP1 Standard Edition.
The Standard Edition of SQL Server 2008-2014 does not contain the CDC features, so TRANDATA cannot be enabled.
There are Microsoft bugs that impact the ability of the CDC job to capture correctly, and have been identified with the following two bugs that you need to apply based on your SQL Server version:
1. For SQL Server 2012, 2014, and 2016, Microsoft has
identified and fixed an issue where some
2. For SQL Server 2016, prior to enabling supplemental logging, you must ensure that you have patched the SQL Server instance based on the following Microsoft bug fix:
Only user databases are supported (can be AlwaysOn Primary or readable Synchronous Secondary).
The SQL Server Agent must be running. The SQL Server Native Client Drivers are supported; the Microsoft ODBC Driver 11 and 13 are not supported.
|Where to Install||
You can install Oracle GoldenGate on any local Windows database server or a remote Windows server.
If you are upgrading a database that was previously
enabled with Classic Extract supplemental logging, you
You should review Release Notes and the Oracle GoldenGate for SQL Server documents for complete information regarding SQL Server instance and database requirements including supported data types.
You download the product from OTN:
Create a source and target database and tables and a
SQL Server Authenticated user that has
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
- 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
sysadminrights for this connection. You can alter the permissions to
dbownerat a later time, if you want to use the same account for the Extract and are not allowed to have Extract running with
- Unzip the
ggs_Windows_x64_MSSQL_64bit_CDC.zipfile to a new Oracle GoldenGate installation directory.
- Create a
GLOBALSfile in the base Oracle GoldenGate installation directory, and set the
GGSCHEMAparameter 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. Do not to use the
GGSCI> EDIT PARAMS ./GLOBALS
Save the GLOBALS file.
GLOBALSfile is a new requirement for Oracle GoldenGate for SQL Server CDC Capture. It is required so that
ADD TRANDATAcan 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.
ggsci.exeand create the necessary sub directories.
GGSCI> CREATE SUBDIRS
- Create the Manager parameter file; list a valid
PORTfor the Manager to use, then save the file. For example,
GGSCI> EDIT PARAMS MGR
- 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
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.*
- 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';
- Use the
ogg_cdc_cleanup_setup.batutility (in the Oracle GoldenGate installation directory) to create the Oracle GoldenGate CDC cleanup job and associated objects. The
ggschemaname used must be the same that you used with the
GGSCHEMAparameter of the
GLOBALSfile. You must use a SQL Server authenticated user that has
d:\>OGG\ogg_cdc_cleanup_setup.bat createJob username password databasename servername\instancename ggschema
- Create and save a new Extract parameter file using this
sample of the minimum required parameters for a
Do Not wildcard the
GGSCI> EDIT PARAMS cdcext
SOURCEDB sourcedsn [USERID user PASSWORD password]
EXTTRAIL ./dirdat/ceTABLE dbo.*;
TABLEstatement if you are using the same schema with
GGSCHEMA TABLE dbo.*;.
- Add the Extract to the Oracle GoldenGate installation.
GGSCI> ADD EXTRACT cdcext, TRANLOG, BEGIN NOW GGSCI> ADD EXTTRAIL ./dirdat/ce, EXTRACT cdcext
Setting Up the Uni-Directional Pump and Replicat
- Create and save a new pump parameter file
using the following sample of the minimum required parameters
for a uni-directional implementation.
EXTRACT cdcpmp RMTHOST servername MGRPORT 7809 RMTTRAIL ./dirdat/cp TABLE dbo.*;
- Create a system DSN to the target database, as you did for the source database in Step 1.
- Create and save a new Replicat parameter file using the
following command and sample of the minimum required
parameters for a uni-directional implementation. The
GGSCI> EDIT PARAMS cdcrep
REPLICAT cdcrep TARGETDB targetdsn [USERID user PASSWORD password] MAP dbo.*, TARGET dbo.*;
- Add the pump to the Oracle GoldenGate installation.
GGSCI> ADD EXTRACT cdcpmp, EXTTRAILSOURCE ./dirat/ce GGSCI> ADD RMTTRAIL ./dirdat/cp, EXTRACT cdcpmp
- Add the Replicat to the Oracle GoldenGate installation.
GGSCI> DBLOGIN SOURCEDB targetdsn [USERID user PASSWORD password] GGSCI> ADD CHECKPOINTTABLE ggs.ggcheck GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/cp,CHECKPOINTTABLE ggs.ggcheck
- Start and verify that the processes are running.
GGSCI>START MGR GGSCI>START * GGSCI>INFO ALL
Setting Up the Bi-Directional CDC Extract
- Follow all of the steps for configuring Uni-Directional replication in Step 1 and Step 2.
- Add a checkpoint table to all of your source databases that
the Replicat will use when adding the Replicat to deliver to
the source database.
GGSCI> ADD CHECKPOINTTABLE ggs.ggcheck
- Enable supplemental logging for the checkpoint table.
GGSCI> ADD TRANDATA ggs.ggcheck
- Edit your Extract parameter files to add the following
entries, and then save the file.
IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ggs.ggcheck
- When you create the Replicat that delivers to this source
database, you must use the same checkpoint table for the
Replicat that is specified by the Extract's
GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/cp, CHECKPOINTTABLE ggs.ggcheck
You may encounter these issues and can use this information to correct them.
TRANDATAcommands are failing with the following message:
ERROR OGG-05263 No GGSCHEMA clause was specified in the GLOBALS file...
This message appears when either there is no
GLOBALSfile, or there is a
GLOBALSfile that does not have a
GGSCHEMAentry supplied with a schema name
- Ensure that the
GLOBALSfile exists, that there is a valid
GGSCHEMAentry, and that the schema listed is a valid schema in the database, restart GGSCI and re-issue the
- Ensure that the schema has been created in the source
Database. For example,
CREATE SCHEMA ggs.
- Ensure that the
- Extract is running, but
STATSreports that no data is captured.
- Verify that the Extract has checkpointed with a valid
LSN. When Extract first starts, if there has not been any
DML for tables enabled with
TRANDATAsince Extract was created, it will not checkpoint until new DML operations are picked up by the CDC Capture job and loaded into the staging tables.
INFO EXTRACT cdcext SHOWCH
Extract has not checkpointed:
Current Checkpoint (position of last record read in the data source): Timestamp: 2017-08-15 14:12:26.454000
Extract has checkpointed:
Current Checkpoint (position of last record read in the data source): Timestamp: 2017-09-27 09:17:37.640000 LSN: 0000f525:00000930:0007-0000f525:00000930:0007, Tran: 0000:0b475e72
- Verify that the
TABLEstatement for Extract includes tables that are actually enabled with
- Ensure that if the
TABLEstatement includes wildcards, that it is not for the schema that is used with the
GGSCHEMAparameter of the
- Verify that the Extract has checkpointed with a valid LSN. When Extract first starts, if there has not been any DML for tables enabled with