Before You Begin
This document touches briefly on many important and complex concepts and does not provide a detailed explanation of any one topic since the intent is to present the material in the most expedient manner. The goal is simply to help the reader become familiar enough with the product to successfully design and implement an Oracle GoldenGate environment. To that end, it is important to note that the activities of design, unit testing and integration testing which are crucial to a successful implementation have been intentionally left out of the guide. All the sample scripts are provided as is. Oracle consulting service is highly recommended for any customized implementation.
This tutorial steps you through setting up Oracle GoldenGate Remote Capture and Delivery for SQL Server. The sample steps below guide you through the installation of Oracle GoldenGate on a mid-tier Windows or Linux application server.
This tutorial takes approximately 30 minutes to complete.
What Do You Need?
Review the following information before you start this tutorial.
Certification |
Remote and local capture and apply are supported for SQL Server 2012/2014/2016/2017 Enterprise Edition on Windows (Developer Edition can also be used for testing purposes) and SQL Server 2016 SP1/2017 Standard Edition. Remote apply is supported for Azure SQL Database. | |
---|---|---|
Requirements |
There are Microsoft bugs that impact the ability of the CDC job to capture correctly. These known issues require that you apply Microsoft patches for your SQL Server version: For SQL Server 2012, 2014, 2016, and 2017, Microsoft has identified and fixed several important issues that directly affect the SQL Server Change Data Capture feature. This impacts the ability for Oracle GoldenGate to correctly capture data. The current known issues that require Microsoft patches include KB3030352, KB3166120, and KB4073684. If you are using SQL Server 2012, 2014, 2016, or 2017 as a source database, Oracle highly recommends that you apply the latest Service Pack or Cumulative Update for your version of SQL Server. Only user databases are supported (can be Always On Primary or readable Synchronous Secondary). The SQL Server Agent must be running. Ensure that Auto Update Statistics is enabled for the database. Ensure that the server where Oracle GoldenGate is installed has the same system time and time zone as the database server. | |
Restrictions |
| |
Where to Install |
You can install Oracle GoldenGate on a supported Windows or Linux server. However, only SQL Server running on Windows is supported as a source or target database. | |
Upgrades |
If you are upgrading a database that was previously enabled with 12.x Classic Extract supplemental logging, you must Upgrading from an OGG 12.3 CDC Extract to OGG 19.1 CDC Extract requires running Careful consideration and extra steps are required for this type of upgrade, so carefully review the instructions available in the Oracle GoldenGate documentation. | |
Review |
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. https://docs.oracle.com/en/middleware/goldengate/core/19.1/index.html | |
Download |
You download Oracle GoldenGate from OTN: http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html | |
Create |
Create a source and target database, tables, and a SQL Server Authenticated user that has |
text
Configure Uni-Directional SQL Server to SQL Server Replication
Prerequisites: Create a source and target database, tables, and a SQL Server Authenticated user that has sysadmin
rights.
Set up the CDC Extract
Create a SQL Server login to be used for initial setup and by the Extract, and grant
sysadmin
rights to the login. You can alter the permissions of the login todbowner
after completing the initial setup tasks and prior to starting the Extract.Create a schema in the source database that will be used for Oracle GoldenGate objects. Oracle recommends that you create a specific schema for Oracle GoldenGate.
CREATE SCHEMA [ogg];
- Optional, but recommended step. Create a new filegroup in the database that will be used to store the CDC staging tables that will be created when enabling supplemental logging in an upcoming step. For example:
ALTER DATABASE [sourcedb] ADD FILEGROUP [cdcfg]; ALTER DATABASE [sourcedb] ADD FILE ( NAME = N'cdcdata', FILENAME = N'C:\cdcdata.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [cdcfg];
For Oracle GoldenGate installed on Windows, create a system DSN to the source database and set the change the default database to option to the source database.
-
For Oracle GoldenGate installed on Linux, follow Microsoft’s RedHat Enterprise Server installation instructions for installing the ODBC Drivers for Linux, and create an ODBC data source afterwards as described below:
Create a template file for your data source:
$ vi odbc_template_file.ini
Describe the data source in the template file. In the following example, myserver_sql2017 is used as the name with DBLOGIN and SOURCEDB to connect to the source database.
[myserver_sql2017_source] Driver = ODBC Driver 17 for SQL Server Server = myserver,1433 Database = dbname User = username Password = password
Save and close the file.
Install the data source using the following command:
$ odbcinst -i -s -f odbc_template_file.ini
- Unzip the
ggs_Windows_x64_MSSQL_64bit_CDC.zip
orggs_Linux_x64_MSSQL_64bit_CDC.zip
build to a new OGG installation directory. -
Create a
GLOBALS
file in the Oracle GoldenGate installation directory and set theGGSCHEMA
parameter to that of the Oracle GoldenGate schema created for the source database. Save and close theGLOBALS
file.Using
GGSCHEMA
inGLOBALS
is a new requirement for OGG for SQL Server CDC Capture, and is required so thatADD TRANDATA
knows which schema to create necessary objects under, and Extract knows which schema to call those objects from during runtime.GGSCHEMA ogg
Start
ggsci.exe
orggsci.sh
and run the command to create the necessary sub directories.GGSCI> CREATE SUBDIRS
-
Create the Manager parameter file; list an unused
PORT
for the Manager to use, then save the file.GGSCI> EDIT PARAMS MGR PORT 7809
-
Connect to the source database from GGSCI and enable supplemental logging for the user tables to be captured from.
On Windows:
GGSCI> DBLOGIN SOURCEDB sourcedsn USERID username PASSWORD password
On Linux:
GGSCI> DBLOGIN SOURCEDB myserver_sql2017_source USERID username PASSWORD password
GGSCI> ADD TRANDATA dbo.table1 FILEGROUP cdcfg GGSCI> ADD TRANDATA dbo.table2 FILEGROUP cdcfg
Note: Remove the
FILEGROUP
option and filegroup name from theADD TRANDATA
command if you did not perform step 3. -
Manually drop the SQL Server CDC cleanup job for the database, as this could cause data loss for the Extract. Do this task from the Management Studio, within a query window for the source database.
EXECUTE sys.sp_cdc_drop_job N'cleanup';
-
Use the
ogg_cdc_cleanup_setup.bat
orogg_cdc_cleanup_setup.sh
utility provided in the Oracle GoldenGate installation directory to create the Oracle GoldenGate CDC cleanup job and associated objects. Theggschema
name used must be the same that you used with theGGSCHEMA
parameter of theGLOBALS
file. You must use a SQL Server authenticated user that hassysadmin
rights to perform this step.For Windows connections, you will not use the system DSN that you created but the server name or server IP address, followed by the SQL Server instance name or its TCP/IP port number.
For Linux connections, you will not use the ODBC data source name that you created earlier but the server name or server IP address, followed by the SQL Server instance’s TCP/IP port number.
For Windows:
c:\>OGG\ogg_cdc_cleanup_setup.bat createJob username password databasename servername\instancename ggschema
For Linux:
./ogg_cdc_cleanup_setup.sh createJob username password databasename servername,port ggschema
-
Create and save a new Extract parameter file. Below is a sample of the minimum required parameters for a uni-directional implementation.
GGSCI> EDIT PARAMS cdcext EXTRACT cdcext SOURCEDB sourcedsn USERID username PASSWORD password EXTTRAIL ./dirdat/ce --Note: Optional TRANLOGOPTIONS Parameter to support data extraction from a secondary replica configured for read-only routing as part of an Always On Availability Group TRANLOGOPTIONS ALWAYSONREADONLYROUTING TABLE dbo.table1; TABLE dbo.table2;
Add the Extract to the Oracle GoldenGate installation.
GGSCI> ADD EXTRACT cdcext, TRANLOG, BEGIN NOW GGSCI> ADD EXTTRAIL ./dirdat/ce, EXTRACT cdcext
Start the Manager and Extract and verify that the processes are running.
GGSCI>START MGR GGSCI>START EXTRACT cdcext GGSCI>INFO ALL
Set up the Replicat
Create a Windows system DSN or Linux ODBC data source connection to the target database, as you did earlier for the source database.
Create and save a new Replicat parameter file. Below is a sample of the minimum required parameters for a uni-directional implementation.
GGSCI> EDIT PARAMS cdcrep REPLICAT cdcrep TARGETDB targetdsn USERID username PASSWORD password MAP dbo.*, TARGET dbo.*
Create a schema in the target database that will be used for Oracle GoldenGate objects. Oracle recommends that you create a specific schema for Oracle GoldenGate.
CREATE SCHEMA [ogg];
Create a checkpoint table and add the Replicat to the Oracle GoldenGate installation.
GGSCI> DBLOGIN SOURCEDB targetdsn USERID username PASSWORD password GGSCI> ADD CHECKPOINTTABLE ogg.ggcheck GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/ce, CHECKPOINTTABLE ogg.ggcheck
Start the Replicat and verify that all processes are running.
GGSCI>START REPLICAT cdcrep GGSCI>INFO ALL
Configure a Bi-Directional CDC Extract
To configure a Bi-Directional CDC Extract:
Follow the steps for configuring Uni-Directional replication, and perform the extra steps listed below.
-
Add a checkpoint table to all the source databases, which will be used by the Replicats.
GGSCI> ADD CHECKPOINTTABLE ogg.ggcheck
-
Enable supplemental logging for the checkpoint tables.
GGSCI> ADD TRANDATA ogg.ggcheck
Edit and save the Extracts’ parameter files, to include the following new entries:
IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ogg.ggcheck
When you create the Replicat that delivers to this source database, you must use the same checkpoint table for the Replicat that is listed with the Extract’s
FILTERTABLE
parameter.GGSCI>ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/ce, CHECKPOINTTABLE ogg.ggcheck
Troubleshoot Issues
Following are some troubleshooting and correction tips:
-
TRANDATA
commands 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
GLOBALS
file, or there is aGLOBALS
file but does not have aGGSCHEMA
entry supplied with a schema name.- Ensure that the
GLOBALS
file exists, that there is a validGGSCHEMA
entry, and that the schema listed is a valid schema in the database, restartGGSCI
and re-issue theTRANDATA
commands. - Ensure that the schema has been created in the source Database. Example:
CREATE SCHEMA ggs
- Ensure that the
- Extract is running, but
STATS
reports 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
TRANDATA
since 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
TABLE
statement for Extract includes the tables that are enabled withTRANDATA
.INFO TRANDATA dbo.tablename
- Ensure that if the
TABLE
statement includes wildcards, that it is not for the schema that is used with theGGSCHEMA
parameter of theGLOBALS
file.
- 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
-
Extract abends with the following message:
ERROR OGG-05329 Oracle GoldenGate Capture for SQL Server, CDCEXT.prm: The SQL Server CDC Capture job for database dbname is not running. Please ensure that the SQL Server CDC Capture job is running.
Verify that the SQL Server Agent and the SQL Server CDC capture job are running.
-
Extract abends with a message regarding object versioning:
ERROR OGG-05338 The versions of the Oracle GoldenGate CDC objects on database, dbname, are not consistent with the expected version, 2. The following version(s) were found: 2,Unknown. Rerun
ADD TRANDATA
for all tables previously enabled, including heartbeat, heartbeat seed, and filter tables.- This message occurs if you are upgrading from an earlier version of Oracle GoldenGate than 19.1 and supplemental logging was not reissued with the 19.1 GGSCI. Ensure that you have re-run
ADD TRANDATA
for all tables previously enabled with it, including any filter tables and theheartbeat
andheartbeat_seed
tables if used. - If Oracle GoldenGate objects or CDC tables were deleted without first running
DELETE TRANDATA
, then this could also leave other Oracle GoldenGate objects in the database that the Extract will evaluate against, in which case you will need to manually remove those objects.
- This message occurs if you are upgrading from an earlier version of Oracle GoldenGate than 19.1 and supplemental logging was not reissued with the 19.1 GGSCI. Ensure that you have re-run