X

Best Practices from Oracle Development's A‑Team

Oracle GoldenGate: Capture and Apply of Microsoft SQL Server DDL Operations

Introduction

Oracle GoldenGate (OGG) supports DDL capture and apply for the Oracle and MySQL databases only. Because of this, maintenance tasks for other databases require a lot of coordination and typically an extended OGG outage. In this article we shall discuss one option for automating the capture, delivery, and execution of DDL operations using OGG for Microsoft SQL Server. The techniques discussed may also be used for other databases supported by OGG.

The concepts, scripts and information presented in this article are for educational purposes only. They are not supported by Oracle Development or Support, and come with no guarantee or warrant for functionality in any environment other than the test system used to prepare this article. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.

Main Article

Oracle GoldenGate (OGG) provides data capture from databases and delivery for homogeneous and heterogeneous databases and messaging systems. Built-in functionality allows OGG capture and apply processes to perform non-database actions based upon database transactional data.

Using this knowledge, we can use OGG as the intermediary mechanism to execute DDL statements on the source and target databases. The high level overview for accomplishing this is:

1. Create an OGG_EVENT table on the source and target to hold DDL statements.

2. Create a Stored Procedure on the source and target that reads the OGG_EVENT table and executes the DDL statements when an event is processed by the OGG Extract and Replicat.

3. Set the OGG Extract and Replicat to execute a Windows Batch script when the event trigger is processed.

The OGG_EVENT Table

The OGG_EVENT table for this use case is defined as:

CREATE TABLE [dbo].[OGG_EVENT]( [EVENT_ID] [nvarchar](10) NOT NULL, [EVENT_ITM] [bigint] NOT NULL, [EVENT_IMP] [nchar](1) NOT NULL, [EVENT_IMP_DATE] [datetime] NULL, [EVENT_TXT] [nvarchar](4000) NOT NULL, CONSTRAINT [pk_OggEvent] PRIMARY KEY CLUSTERED ( [EVENT_ITM] ASC, [EVENT_ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[OGG_EVENT] WITH CHECK ADD CONSTRAINT [CK_OGG_EVENT_EVENT_IMP] CHECK (([EVENT_IMP]=N'Y' OR [EVENT_IMP]=N'N')) GO ALTER TABLE [dbo].[OGG_EVENT] CHECK CONSTRAINT [CK_OGG_EVENT_EVENT_IMP] GO ALTER TABLE [dbo].[OGG_EVENT] ADD DEFAULT (N'N') FOR [EVENT_IMP] GO

Each row must have a unique item number (EVENT_ITM) and event id (EVENT_ID). EVENT_IMP and EVENT_IMP_DATE are used as an "audit trail". These columns are updated by the stored procedure upon successful execution of the DDL statement in EVENT_TXT. The EVENT_TXT column will contain the DML statement to be executed by the stored procedure. DDL statements that would exceed 4000 bytes must be broken up into multiple statements.

The execOGGDDL Stored Procedure

The execOGGDDL Procedure is the heart of this process.

CREATE PROCEDURE dbo.execOGGDDL @evtId nvarchar(10) AS BEGIN  BEGIN TRY   SET NOCOUNT ON   DECLARE @evtItm bigint   DECLARE @evtTxt nvarchar(4000)   -- Test for any Events to process   IF (SELECT COUNT (*) FROM dbo.OGG_EVENT                       WHERE EVENT_ID = @evtId AND UPPER(EVENT_IMP) = N'N') = 0    PRINT N'There are no Events to process for Event_Itm '+@evtId +N'.'   -- Start looping through the records   WHILE (SELECT COUNT (*) FROM dbo.OGG_EVENT                         WHERE EVENT_ID = @evtId AND UPPER(EVENT_IMP) = N'N') > 0    BEGIN    -- Grab the first record out    SET @evtItm = (SELECT MIN(EVENT_ITM) FROM dbo.OGG_EVENT                                          WHERE EVENT_ID = @evtId AND UPPER(EVENT_IMP) = N'N')    SELECT @evtTxt = EVENT_TXT FROM dbo.OGG_EVENT                                WHERE EVENT_ID = @evtId AND EVENT_ITM = @evtItm AND UPPER(EVENT_IMP) = N'N'    -- execute the statements    IF @evtTxt != N'End Event'     exec(@evtTxt)    -- Update the record to set the EVENT_IMP to 'Y'    UPDATE dbo.OGG_EVENT SET EVENT_IMP = N'Y', EVENT_IMP_DATE = GETDATE()                             WHERE EVENT_ID = @evtId and EVENT_ITM = @evtItm   END  END TRY  BEGIN CATCH   SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;  END CATCH END GO

When the OGG Extract or Replicat processes the event trigger record, this Procedure is called and supplied the EVENT_ID column data from the trigger record. The Procedure queries the EVENT_TXT records from the OGG_EVENT table that match the supplied EVENT_ID. Each row is then read, executed, and updated with an EVENT_IMP code of ‘Y’ and current execution time for the EVENT_IMP_DATE in order.

Because of the overhead associated with having the OGG Extract and Replicat execute the Procedure, they instead execute a Windows Batch file that calls the Procedure via the Windows SQLCMD utility

The ddlevt.bat Script

Two Windows Batch scripts are executed via an EVENTACTION defined in the OGG Extract and Replicat parameter files. These scripts automate the table maintenance and OGG param file maintenance process.

The script used on the source side, ddlevt_s.bat, is:

@echo off setlocal enabledelayedexpansion echo. echo *******  Source Side Maintenance Begin ****** echo. REM Set the OGG home directory set OGGHOME=C:\Users\lpenton\OGG\MSS123CDC REM Set the location of the GGSCI obey files set INLOC=C:\Users\lpenton\OGG\MSS123CDC\dirsql REM Get the event id for this run set EVTID=%1 REM Set an output location set OUTLOC=%~dp0 REM Strip spaces from the directory name for /f "tokens=* delims= " %%a in ('echo %OUTLOC%') do (set S=%%a & set S=!S: =! &echo !S!) set OUTLOC=%S% %EVTID%.out for /f "tokens=* delims= " %%a in ('echo %OUTLOC%') do (set S=%%a & set S=!S: =! &echo !S!) set OUTLOC=%S% echo. echo    Executing Stored Procedue @echo on sqlcmd -Q "exec dbo.execOGGDDL @evtId='%EVTID%'" -S .\MSSQL16 -U ggadmin -P Oracle1 -d LJP_TPCC -o %OUTLOC% @echo off echo. echo    Appending new tables to Extract param file @echo on type %INLOC%\dm001_tables.in >> %OGGHOME%\dirprm\cdctpc.prm @echo off echo. echo    Executing "add trandata" statements chdir /D %OGGHOME% @echo on ggsci.exe < %INLOC%\dm001_trandata.oby > %INLOC%\dm001_trandata.out @echo off echo *******  Source Side Maintenance Complete ****** echo ** Check for errors and close this window to continue **

The script used on the target side, ddlevt_t.bat, is:

@echo off setlocal enabledelayedexpansion REM Set the OGG Home directory set OGGHOME=C:\Users\lpenton\OGG\MSS123CDC REM Get the event id for this run set EVTID=%1 REM Set an output location for the stored procedure set OUTLOC=%~dp0 REM Strip spaces from the directory name for /f "tokens=* delims= " %%a in ('echo %OUTLOC%') do (set S=%%a & set S=!S: =! &echo !S!) set OUTLOC=%S% %EVTID%_t.out for /f "tokens=* delims= " %%a in ('echo %OUTLOC%') do (set S=%%a & set S=!S: =! &echo !S!) set OUTLOC=%S% REM Execute the Stored Procedure sqlcmd -Q "exec dbo.execOGGDDL @evtId='%EVTID%'" -S .\MSSQL16 -U ggadmin -P Oracle1 -d LJPNSK -o %OUTLOC% REM Append new MAP statements to the replicat param file type %OGGHOME%\dirsql\dm001_maps.in >> %OGGHOME%\dirprm\rtpc.prm

These script create output files that will contain error information should the stored procedure fail. The stored procedure is executed via the Windows SQLCMD utility, and provided the unique EVENT_ID from the captured trigger record.

The -S, -U, -P, and -d options for SQLCMD must be set correctly to provide the database server for the connection (-S), the database userid (-U) and password (-P) for a user with create and alter table authority, and the database where the DDL statements will be executed (-d).

Setup

To setup the database and Windows components:

1. Create the dbo.OGG_EVENT table in the source and target database.

2. ADD TRANDATA to the dbo.OGG_EVENT table on the source database.

3. Create the dbo.execOGGDDL Procedure in the source and target database.

4. Create the ddlevt_s.bat and ddlevt_t.bat script in the OGG "dirsql" directory on the source and target server.

(a) Be sure to set the correct options for SQLCMD to access each database.

OGG Extract Changes

A sample Change Data Capture Extract parameter file is shown below.

extract cdctpc -- -- Use credential store for database access credentials -- sourcedb mss16ljp, useridalias ggsrc exttrail ./dirdat/ab -- -- Report operational stats -- reportcount every 60 seconds, rate report at 00:01 reportrollover at 00:01 statoptions resetreportstats -- -- Needed to capture event marker data -- allowduptargetmap -- -- Do not capture updates or deletes for the event market table -- ignoreupdates ignoredeletes table dbo.OGG_EVENT, filter (@strcmp (EVENT_TXT, 'End Event') <> 0); -- -- When the EVENT_TXT columns contains the string 'End Event", execute the shell script. -- This server defaults to PowerShell so must use the PS "start" command. -- Needed because we are running GGSCI from the batch script. -- "/k" keeps the CMD Window open until closed by the user. -- table dbo.OGG_EVENT, filter (@strcmp (EVENT_TXT, 'End Event') = 0),   eventactions(SHELL ('start cmd.exe /k C:\Users\lpenton\OGG\MSS123CDC\dirsql\ddlevt_s.bat ddljob',                 VAR ddljob = EVENT_ID), STOP); -- -- Get updates and deletes for the other tables being captured. -- getupdates getdeletes table dbo.categories; table dbo.categories_description; table dbo.customers; table dbo.customers_info; table dbo.next_cust; table dbo.next_order; table dbo.orders; table dbo.orders_products; table dbo.orders_status_history; table dbo.orders_total; table dbo.products; table dbo.products_description; table dbo.products_to_categories;

Key parameters that must be set:

1. Set ALLOWDUPTARGETMAPS

(a) This will force Extract to resolve the OGG_EVENT table twice instead of ignoring the second TABLE statement.

2. Set IGNOREUPDATES and IGNOREDELETES before the first OGG_EVENT table statement.

(a) We only want to process inserts for this table.

3. Add two table statements for the OGG_EVENT table.

(a) The first will capture all records where the EVENT_TXT is not "End Event".

(b) The second will only capture the record where the EVENT_TXT is "End Event".

i. This is the trigger event record.

ii. When this record is processed, the Windows Batch script ddlevt_s.bat is executed and supplied the EVENT_ID of the trigger record.

iii. This in turn executes the execOGGDDL Procedure which executes the DDL changes and adds trandata and modifies the Extract parameter file for newly created tables.

(c) As part of the EVENTACTIONS option, the Extract will be gracefully stopped.

4. After the second OGG_EVENT table statement:

(a) Set GETUPDATES and GETDELETES to reactivate default capture of update and delete operations.

OGG Extract Data Pump Changes

If the Extract Data Pump is configured with PASSTHRU and wild card TABLE statements, no changes

are required. If not, the new tables must be added.

OGG Replicat Changes

A sample Coordinated Apply parameter file is presented below.

replicat rtpc -- -- Use credential store for database access credentials -- targetdb NSKMSS16LJP, useridalias ggtgt -- -- Report operational stats -- reportcount every 60 seconds, rate report at 00:01 reportrollover at 00:01 statoptions resetreportstats -- -- Use a dedicated thread for barrier transactions -- usededicatedcoordinationthread -- Organize similar SQL statements into arrays and apply them at an accelerated rate -- batchsql -- -- Needed to apply event marker data -- allowduptargetmap map dbo.OGG_EVENT, target dbo.OGG_EVENT, filter (@strcmp (EVENT_TXT, 'End Event') <> 0), thread (1); -- -- When the EVENT_TXT columns contains the string 'End Event", execute the shell script. -- map dbo.OGG_EVENT, target dbo.OGG_EVENT, filter (@strcmp (EVENT_TXT, 'End Event') = 0),   eventactions(SHELL ("C:\Users\lpenton\OGG\MSS123CDC\dirsql\ddlevt_t.bat ddljob", VAR ddljob = EVENT_ID),              checkpoint before, ignore, stop), thread (1); map dbo.categories_description, target dbo.categories_description, thread (1); map dbo.categories, target dbo.categories, thread (1); map dbo.customers_info, target dbo.customers_info, thread (2); map dbo.customers, target dbo.customers, thread (2); map dbo.next_cust, target dbo.next_cust, thread (2); map dbo.next_order, target dbo.next_order, thread (2); map dbo.orders, target dbo.orders, thread (3); map dbo.orders_products, target dbo.orders_products, thread (3); map dbo.orders_status_history, target dbo.orders_status_history, thread (3); map dbo.orders_total, target dbo.orders_total, thread (3); map dbo.products_to_categories, target dbo.products_to_categories, thread (1); map dbo.products_description, target dbo.products_description, thread (1); map dbo.products, target dbo.products, thread (1);

These changes must be made to one Replicat only. Key parameters to set are:

1. ALLOWDUPTARGETMAP to enable multiple map statements for the OGG_EVENT table.

2. If using wildcards in the Replicat, set NODYNAMICRESOLUTION to have Replicat resolve the object record for the OGG_EVENT table at startup.

3. Two map statements for the OGG_EVENT table.

(a) The first will apply all records where the EVENT_TXT column does not contain "End Event".

(b) The second will only apply the "End Event" record.

i. Like in Extract, this is the trigger record for Replicat.

4. When this record is read from the trail, Replicat does the following:

i. The ddlevt.bat script is executed and provided the EVENT_ID data of the trigger record.

This in turn executes the execOGGDDL Procedure which executes the DDL changes.

ii. The Replicat will checkpoint.

iii. The trigger record will be ignored; meaning it will not be applied to the target table.

iv. The Replicat will perform a graceful stop.

All other Replicats applying data to the same database must be modified as shown in the following example.

In these Replicats, we only check for the trigger record. When the trigger record is read each Replicat will checkpoint and gracefully stop.

It should be noted that in the Replicats we are being somewhat conservative. If wildcards are used there is no need to stop the Replicats because the new table objects will be resolved when the first record for the each new table is encountered by Replicat. This is not true if explicit mapping is used; in that case each Replicat must be stopped, new map statements added, and the Replicat restarted before data will be applied to the new tables.

Example Database Maintenance Run

To test this procedure, we will simulate a production database maintenance window. To prepare for the maintenance window, assigned maintenance event DM001, I create the following staging files on the source (Note: All .bat, .in, and .oby files are stored in the OGG_HOME/dirsql directory):

1. dm001_ddl.in

(a)This file contains the ddl statements that will be loaded into the OGG_EVENT table.

These statements are:

USE [LJP_TPCC] GO insert into [dbo].[OGG_EVENT] values (   'dm001',   1,   'N',   CURRENT_TIMESTAMP,   'CREATE TABLE [dbo].[ljpddl01] ([cola] [bigint] not null, [colb] [datetime] null,\  [colc] [nvarchar](50) null, CONSTRAINT [pk_ljpddl01] primary key ([cola] ASC) WITH\  (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,\  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]' ); GO insert into [dbo].[OGG_EVENT] values (   'dm001',   2,   'N',   CURRENT_TIMESTAMP,   'CREATE TABLE [dbo].[ljpddl02] ([cola] [bigint] not null, [colb] [datetime] null,\  [colc] [nvarchar](50) null, CONSTRAINT [pk_ljpddl02] primary key ([cola] ASC) WITH\  (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,\  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]' ); GO insert into [dbo].[OGG_EVENT] values ('dm001',3,'N',CURRENT_TIMESTAMP, 'End Event'); GO

(b)The last record in the file must be the trigger record.

2. dbm001_trandata.oby

(a) This file is called when the ddlevt_s.bat script starts GGSCI and is used to execute "ADD TRANDATA" commands when new tables are created via this maintenance procedure:

obey dirsql\dm001_trandata.in

3. dbm001_trandata.in

(a)This file contains all of the "ADD TRANDATA: commands required to ensure the OGG Extract captures all database operations for the new tables.

dblogin sourcedb mss16ljp, useridalias ggsrc add trandata dbo.ljpddl01 add trandata dbo.ljpddl02

4. dbm001_tables.in

(a)This file contains the "TABLE" statements that will be appended to the Extract parameter file. Note the first line only contains a carriage return, this ensure these statements are added as new lines to the existing parameter file.

table dbo.ljpddl01; table dbo.ljpddl02;

5. dbm001_dml.in

(a)This file contains all of the DML statements required to load data into the new tables. DML statements cannot be executed until after the GGSCI ADD TRANDATA commands are executed. Data inconsistencies may occur if data is loaded into the new tables before Trandata is enabled.

When the database maintenance window begins, application processing is suspended and allow all outstanding database operations to be captured and applied to the target. Make sure all OGG Extract and Replicat Groups report "At EOF".

SQL Server Management Studio shows the current source and target tables.

USE [LJP_TPCC] GO INSERT INTO [dbo].[ljpddl01] values (1, CURRENT_TIMESTAMP, 'This is an insert of row 1.'); INSERT INTO [dbo].[ljpddl01] values (2, CURRENT_TIMESTAMP, 'This is an insert of row 2.'); INSERT INTO [dbo].[ljpddl01] values (3, CURRENT_TIMESTAMP, 'This is an insert of row 3.'); INSERT INTO [dbo].[ljpddl01] values (4, CURRENT_TIMESTAMP, 'This is an insert of row 4.'); INSERT INTO [dbo].[ljpddl01] values (5, CURRENT_TIMESTAMP, 'This is an insert of row 5.'); INSERT INTO [dbo].[ljpddl01] values (6, CURRENT_TIMESTAMP, 'This is an insert of row 6.'); INSERT INTO [dbo].[ljpddl01] values (7, CURRENT_TIMESTAMP, 'This is an insert of row 7.'); INSERT INTO [dbo].[ljpddl01] values (8, CURRENT_TIMESTAMP, 'This is an insert of row 8.'); INSERT INTO [dbo].[ljpddl01] values (9, CURRENT_TIMESTAMP, 'This is an insert of row 9.'); INSERT INTO [dbo].[ljpddl01] values (10, CURRENT_TIMESTAMP, 'This is an insert of row 10.'); INSERT INTO [dbo].[ljpddl02] values (1, CURRENT_TIMESTAMP, 'This is an insert of row 1.'); INSERT INTO [dbo].[ljpddl02] values (2, CURRENT_TIMESTAMP, 'This is an insert of row 2.'); INSERT INTO [dbo].[ljpddl02] values (3, CURRENT_TIMESTAMP, 'This is an insert of row 3.'); INSERT INTO [dbo].[ljpddl02] values (4, CURRENT_TIMESTAMP, 'This is an insert of row 4.'); INSERT INTO [dbo].[ljpddl02] values (5, CURRENT_TIMESTAMP, 'This is an insert of row 5.'); INSERT INTO [dbo].[ljpddl02] values (6, CURRENT_TIMESTAMP, 'This is an insert of row 6.'); INSERT INTO [dbo].[ljpddl02] values (7, CURRENT_TIMESTAMP, 'This is an insert of row 7.'); INSERT INTO [dbo].[ljpddl02] values (8, CURRENT_TIMESTAMP, 'This is an insert of row 8.'); INSERT INTO [dbo].[ljpddl02] values (9, CURRENT_TIMESTAMP, 'This is an insert of row 9.'); INSERT INTO [dbo].[ljpddl02] values (10, CURRENT_TIMESTAMP, 'This is an insert of row 10.'); GO UPDATE [dbo].[ljpddl01] set colb = CURRENT_TIMESTAMP, colc = 'This is an update of row 5' WHERE cola = 5; UPDATE [dbo].[ljpddl01] set colb = CURRENT_TIMESTAMP, colc = 'This is an update of row 8' WHERE cola = 8; UPDATE [dbo].[ljpddl02] set colb = CURRENT_TIMESTAMP, colc = 'This is an update of row 5' WHERE cola = 5; UPDATE [dbo].[ljpddl02] set colb = CURRENT_TIMESTAMP, colc = 'This is an update of row 8' WHERE cola = 8; GO

On the target, create a single file dm001_maps.in. This file contains the MAP statements that will be appended to the Coordinated Apply parameter file when new tables are created via this maintenance process. Note the first line only contains a carriage return, this ensure these statements are added as new lines to the existing parameter file.

map dbo.ljpddl01, target dbo.ljpddl01, thread (4); map dbo.ljpddl02, target dbo.ljpddl02, thread (4);

In SQL Server Management Studio, execute the dm0001_ddl.in commands. When the trigger record with EVENT_TXT of "End Event" is captured the Extract EVENTACTION will open a Windows CMD screen and execute the ddlevt_t.bat script on the source; which in turn executes the stored procedure and OGG environmental changes.

winddl1

If there were no errors, close the CMD Window. The Extract will gracefully stop, trandata will be added for new tables, and the extract parameter file will be modified:

GGSCI (den01eln as ggadmin@MSS16LJP) 183> info trandata dbo.* Logging of supplemental log data is enabled for table dbo.OGG_EVENT Logging of supplemental log data is enabled for table dbo.ljpddl01 Logging of supplemental log data is enabled for table dbo.orders_total Logging of supplemental log data is enabled for table dbo.ljpddl02

Microsoft SQL Server Management Studio now shows new tables in the source database.

winddl2

Restart the Extract. You may now load the new tables and begin application DML against the source database.

On the target, the trigger record will cause the EVENTACTION to execute in the Coordinated Apply thread. The DDL statements will be retrieved and executed by the stored procedure, the new tables will be visible from within Microsoft SQL Server Management Studio, and the Apply parameter file will be updated with MAP statements for the new tables.

winddl3

Restart the Coordinated Apply. Data captured on the source is being queued in the OGG Trails. Once the Replicats are running, all captured data will be applied to the target tables.

Summary

Oracle GoldenGate advanced functionality along with operating system scripts and a database stored procedure may be used to minimize the impact of database maintenance tasks. This article presented one example of this functionality for Microsoft SQL Server.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha