Knowledge is Power – share IT



Using Oracle GoldenGate 12c for CDC between Oracle 12c Multitenant Database and MS SQL Server 2012This note describes the process of configuring a initial data load job as well as Change Data Capture from an Oracle 12c pluggable database source and a MS SQL Server 2012 target database.It assumes the GoldenGate user has been created in the Oracle 12c source database and other setup tasks like enabling supplemental logging in the database have been completed as well.Downloaded from OTN and unzipped on the SQL Server 2012 box121201_ggs_Windows_x64_MSSQL_64bit.zipFrom the unzipped location ran ggsciCreate the GLOBALS file Add the following lineMGRSERVNAME MANAGERSave the file. The file is saved automatically with the name GLOBALS, without a file extensionCreate the Manager serviceD:\software\goldengate12c>install ADDSERVICE USER CORP.DOMAIN\gavin PASSWORD xxxService 'MANAGER' created.Check the status of the manager serviceD:\software\goldengate12c>ggsciOracle GoldenGate Command Interpreter for SQL ServerVersion 12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_131206.0309Windows x64 (optimized), Microsoft SQL Server on Dec 6 2013 12:06:08Operating system character set identified as windows-1252.Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.GGSCI (kensmssql001uat) 1> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER STOPPEDGGSCI (kensmssql001uat) 3> EDIT PARAMS mgrGGSCI (kensmssql001uat) 4>PORT 7809GGSCI (kensmssql001uat) 8> START MANAGERStarting Manager as service ('MANAGER')...Service started.GGSCI (kensmssql001uat) 9> INFO ALLProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGDownloaded and installed Oracle Data Access ComponentsODAC1120320_x64Create the data source via ODBC Data Source Administrator (launched from the ODAC Oracle Client Home)For login authentication, select With Integrated Windows Authentication for Oracle GoldenGate to use Windows authentication, or select With SQL Server authentication using a login ID and password entered by the user for Oracle GoldenGate to use database credentialsTest the data source by logging in via Oracle GoldenGateGGSCI (kensmssql001uat) 10> DBLOGIN SOURCEDB sqlserver20122014-01-30 08:28:05 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.2014-01-30 08:28:05 INFO OGG-03037 Session character set identified as windows-1252.Successfully logged into database.Create the definitions file using the defgen utility[oracle@orasql-001-dev goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBOLinux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54Operating system character set identified as UTF-8.Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.GGSCI (orasql-001-deV) 1> EDIT PARAM defgenDEFSFILE ./dirdat/source.def,USERIDALIAS gg_salesTABLE sales.sh.customers;[oracle@orasql-001-dev goldengate]$ ./defgen paramfile dirprm/defgen.prm*********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316 Linux, x64, 64bit (optimized), Oracle 12c on Sep 24 2013 16:23:06Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. Starting at 2014-01-30 11:36:28***********************************************************************Operating System Version:LinuxVersion #1 SMP Wed Aug 28 09:09:20 PDT 2013, Release 2.6.32-400.29.3.el5uekNode: orasql-001-deVMachine: x86_64 soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size : unlimited unlimitedFile Size : unlimited unlimitedCPU Time : unlimited unlimitedProcess id: 32174************************************************************************* Running with the following parameters *************************************************************************DEFSFILE ./dirdat/source.def,USERIDALIAS gg_salesTABLE sales.sh.myobjects;Retrieving definition for SALES.SH.MYOBJECTS.Definitions generated for 1 table in ./dirdat/source.def.[oracle@orasql-001-dev goldengate]$SCP or FTP the source.def file to the Windows Server hosting SQL Server 2012 instanceCopy it in the dirdef subdirectory in the GoldenGate software installation home directoryCreate the table in SQL server USE [AdventureWorks]GO/****** Object: Table [Person].[myobjects] Script Date: 4/02/2014 9:04:24 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [SCOTT].[myobjects]([OBJECT_ID] [int] NOT NULL,[OBJECT_NAME] [nvarchar](20) NULL,[OBJECT_TYPE] [nvarchar](40) NULL) ON [PRIMARY]GOCreate the Initial Load ExtractGGSCI (orasql-001-deV) 2> DBLOGIN USERIDALIAS gg_rootSuccessfully logged into database CDB$ROOT.GGSCI (orasql-001-deV) 1> ADD EXTRACT eini1 SOURCEISTABLEEXTRACT added.GGSCI (orasql-001-deV) 2> EDIT PARAMS eini1EXTRACT eini1USERIDALIAS gg_rootRMTHOST kensmssql001uat, MGRPORT 7809RMTTASK REPLICAT, GROUP rini1TABLE sales.sh.customers;Create the Initial Load Replicat job GGSCI (kensmssql001uat) 11> ADD REPLICAT rini1 SPECIALRUNREPLICAT added.GGSCI (kensmssql001uat) 12> EDIT PARAMS rini1GGSCI (kensmssql001uat) 13> VIEW PARAMS rini1REPLICAT rini1TARGETDB sqlserver2012SOURCEDEFS ./dirdef/source.defMAP sales.sh.customers, TARGET person.customers;Start the Initial Load Extract GGSCI (orasql-001-deV) 2> start extract eini1Sending START request to MANAGER ...EXTRACT EINI1 startingGGSCI (orasql-001-deV) 3> info extract eini1EXTRACT EINI1 Last Started 2014-01-30 11:40 Status RUNNINGCheckpoint Lag Not AvailableProcess ID 32456Log Read Checkpoint Table SALES.SH.MYOBJECTS 2014-01-30 11:40:42 Record 1Task SOURCEISTABLEGGSCI (orasql-001-deV) 11> info extract eini1EXTRACT EINI1 Last Started 2014-01-30 11:40 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Table SALES.SH.MYOBJECTS 2014-01-30 11:41:41 Record 77695Task SOURCEISTABLEOn the SQL Server 2012 database confirm the initial load to the MYOBJECTS table has completedConfigure Change Data Capture Add supplemental logging at the table level and create the integrated extract.Note – for 12c Multitenant Container databases we have to use Integrated Extracts in place of Classic ExtractsTo add trandata we need to be connected to the individual pluggable database and not the root container. In this case the PDB is SALES.GGSCI (orasql-001-deV) 4> dblogin useridalias gg_salesSuccessfully logged into database SALES.GGSCI (orasql-001-deV) 5> add trandata sales.sh.myobjectsLogging of supplemental redo data enabled for table SALES.SH.MYOBJECTS.TRANDATA for scheduling columns has been added on table 'SALES.SH.MYOBJECTS'.GGSCI (orasql-001-deV) 7> dblogin useridalias gg_rootSuccessfully logged into database CDB$ROOT.GGSCI (orasql-001-deV) 8> register extract eora1 database container (sales)Extract EORA1 successfully registered with database at SCN 4859756.GGSCI (orasql-001-deV) 9> ADD EXTRACT eora1 INTEGRATED TRANLOG BEGIN NOWEXTRACT added.GGSCI (orasql-001-deV) 11> edit params eora1EXTRACT eora1USERIDALIAS gg_rootRMTHOST kensmssql001uat, MGRPORT 7809RMTTRAIL ./dirdat/abTABLE sales.sh.myobjects;GGSCI (orasql-001-deV) 12> ADD RMTTRAIL ./dirdat/ab EXTRACT eora1RMTTRAIL added.GGSCI (orasql-001-deV) 13> START EXTRACT eora1Sending START request to MANAGER ...EXTRACT EORA1 startingGGSCI (orasql-001-deV) 14> INFO EXTRACT eora1EXTRACT EORA1 Initialized 2014-01-31 08:24 Status STARTINGCheckpoint Lag 00:00:00 (updated 00:14:43 ago)Process ID 5869Log Read Checkpoint Oracle Integrated Redo Logs 2014-01-31 08:24:07 SCN 0.0 (0)GGSCI (orasql-001-deV) 15> INFO EXTRACT eora1EXTRACT EORA1 Last Started 2014-01-31 08:38 Status RUNNINGCheckpoint Lag 00:00:06 (updated 00:00:05 ago)Process ID 5869Log Read Checkpoint Oracle Integrated Redo Logs 2014-01-31 08:39:30 SCN 0.4911465 (4911465)On the Windows 2008 server create the Replicat process GGSCI (kensmssql001uat) 18> ADD REPLICAT rora1, EXTTRAIL ./dirdat/abERROR: No checkpoint table specified for ADD REPLICAT.GGSCI (kensmssql001uat) 19> ADD CHECKPOINTTABLE PERSON.CHKTABSuccessfully created checkpoint table PERSON.CHKTAB.GGSCI (kensmssql001uat) 20> EDIT PARAMS ./GLOBALSMGRSERVNAME MANAGERCHECKPOINTTABLE PERSON.CHKTABNOTE:For the GLOBALS configuration to take effect, you must exit the session in which the changes were made.GGSCI (kensmssql001uat) 22> ADD REPLICAT rora1, EXTTRAIL ./dirdat/ab CHECKPOINTTABLE PERSON.CHKTABREPLICAT added.GGSCI (kensmssql001uat) 24> EDIT PARAMS rora1REPLICAT rora1TARGETDB sqlserver2012SOURCEDEFS ./dirdef/source.defMAP sales.sh.myobjects, TARGET person.myobjects;GGSCI (kensmssql001uat) 26> START REPLICAT rora1Sending START request to MANAGER ('MANAGER') ...REPLICAT RORA1 startingGGSCI (kensmssql001uat) 27> INFO REPLICAT rora1REPLICAT RORA1 Last Started 2014-01-31 08:50 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:08 ago)Process ID 5300Log Read Checkpoint File ./dirdat/ab000000 First Record RBA 1480Issue an UPDATE statement on the Oracle 12c Pluggable Database [oracle@orasql-001-dev goldengate]$ sqlplus sh/sh@localhost:1525/salesSQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 31 08:52:03 2014Copyright (c) 1982, 2013, Oracle. All rights reserved.Last Successful login time: Thu Jan 30 2014 10:04:23 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> update myobjects 2 set object_type='TABLE';77695 rows updated.SQL> commit;Commit complete.GGSCI (kensmssql001uat) 39> stats replicat rora1 latestSending STATS request to REPLICAT RORA1 ...Start of Statistics at 2014-01-31 08:58:34.Replicating from SALES.SH.MYOBJECTS to Person.myobjects:*** Total statistics since 2014-01-31 08:58:04 *** Total inserts 0.00 Total updates 37586.00 Total deletes 0.00 Total discards 0.00 Total operations 37586.00GGSCI (kensmssql001uat) 40> send replicat rora1 getlagSending GETLAG request to REPLICAT RORA1 ...Last record lag 387 seconds.GGSCI (kensmssql001uat) 41> send replicat rora1 getlagSending GETLAG request to REPLICAT RORA1 ...Last record lag 395 seconds.At EOF, no more records to process.GGSCI (kensmssql001uat) 42> stats replicat rora1 latestSending STATS request to REPLICAT RORA1 ...Start of Statistics at 2014-01-31 08:59:20.Replicating from SALES.SH.MYOBJECTS to Person.myobjects:*** Total statistics since 2014-01-31 08:58:04 *** Total inserts 0.00 Total updates 77695.00 Total deletes 0.00 Total discards 0.00 Total operations 77695.00On the SQL Server 2012 instance verify the update has been performed as well ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download