Category Archives: GOLDENGATE 实施

OGG FOR SQLSERVER 的 DDL 部署

1. 在目标端创建OGG_EVENT表
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

2. 在目标端创建OGGDDL Procedure
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

3. 停止源端的抽取
GGSCI (hisha02) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED EXHIS 00:00:00 00:00:10
EXTRACT STOPPED PUMP_T 00:00:00 00:00:02

4、在源端创建OGG_EVENT表
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

5. 在源端创建OGGDLL procedure
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

6. 在源端给dbo.OGG_EVENT添加TRANDATA
GGSCI (hisha02) 7> dblogin sourcedb cxzyy_his, userid ssa, password 20031118
GGSCI (hisha02 as ssa@CXZYY_HIS) 10> ADD TRANDATA dbo.OGG_EVENT

7. 在源端的OGG安装目录下的dirsql\ddlCaptureApply子目录下创建ddlevt.bat
@echo off
setlocal enabledelayedexpansion
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%
REM Execute the Stored Procedure
sqlcmd -Q “exec dbo.execOGGDDL @evtId=’%EVTID%'” -S VMMSS2008 -U gguser -P Oracle1
-d east -o %OUTLOC%

8. 在源端的OGG安装目录下的dirsql\ddlCaptureApply子目录下创建ddlevt.bat
@echo off
setlocal enabledelayedexpansion
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%
REM Execute the Stored Procedure
sqlcmd -Q “exec dbo.execOGGDDL @evtId=’%EVTID%'” -S VMMSS2008 -U gguser -P Oracle1
-d east -o %OUTLOC%

10 修改源端抽取进程的参数
EXTRACT exhis
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
SOURCEDB cxzyy_his, USERID ssa, PASSWORD 20031118
EXTTRAIL Z:\mh\soft\dirdat\lt
DISCARDFILE Z:\mh\soft\dirrpt\exhis.dsc,PURGE

nodynamicresolution
ignoreupdates
ignoredeletes
table dbo.OGG_EVENT,
filter (@strcmp(EVENT_TXT, “End Event”) <> 0)
;

table dbo.OGG_EVENT,
filter (@strcmp(EVENT_TXT, “End Event”) = 0),
eventactions(SHELL (“Z:\mh\soft\dirsql\ddlCaptureApply\ddlevt.bat ddljob”,
VAR ddljob = EVENT_ID),
STOP)
;

dynamicresolution
getupdates
getdeletes
tableexclude dbo.OGG_EVENT
table dbo.*;

TABLE cxzyy_his.*;
TABLE cxzyy_his1.*;
TableExclude dbo.dtproperties;
TableExclude dbo.MSreplication_objects;
TableExclude dbo.MSreplication_subscriptions;
TableExclude dbo.MSsavedforeignkeycolumns;
TableExclude dbo.MSsavedforeignkeyextendedproperties;
TableExclude dbo.MSsavedforeignkeys;
TableExclude dbo.MSsnapshotdeliveryprogress;
TableExclude dbo.MSsubscription_agents;
TableExclude dbo.systranschemas;
TableExclude dbo.ggsckpt;
11.修改源端的data pump进程的参数
没有修改

12修改目标端的replicat进程的参数
REPLICAT rep_t
ASSUMETARGETDEFS
HANDLECOLLISIONS
SOURCEDB cxykt_his, USERID ssa, PASSWORD 20031118
DISCARDFILE D:\mh\soft\dirrpt\rep_t.dsc, APPEND
batchsql

allowduptargetmap
nodynamicresolution

map dbo.OGG_EVENT, target dbo.OGG_EVENT,
filter (@strcmp(EVENT_TXT, “End Event”) <> 0)
;

MAP dbo.OGG_EVENT, TARGET dbo.OGG_EVENT,
FILTER ( @STRCMP(EVENT_TXT,”End Event”) <>
map dbo.OGG_EVENT, target dbo.OGG_EVENT,
filter (@strcmp(EVENT_TXT, “End Event”) = 0),
eventactions(SHELL (“D:\mh\soft\dirsql\ddlCaptureApply\Target\ddlevt.bat
ddljob”,
VAR ddljob = EVENT_ID),
checkpoint before, ignore, stop)

dynamicresolution
mapexclude dbo.OGG_EVENT
map dbo.*, target dbo.*;

MAP cxzyy_his.*, TARGET cxzyy_his.*;
MAP cxzyy_his1.*, TARGET cxzyy_his1.*;