Oracle GoldenGate Technical Deep Dive - NYOUG

1 1 PatternsOracle GoldenGate Technical deep DiveNew York Oracle User Group ( NYOUG )7thDec 2016Y V Ravi KumarOracle ACEO racle Certified Master (OCM) Oracle ACE Spotlight June 2016 2 PatternsABOUT ME OracleCertified Master (OCM) May 2009 OracleACE May 2015 OracleACE Spotlight for the month -Jun 2016 Community Expert in DELL s Toad World Expert in Oracle Technology Network (OTN) communityOracle Speaker @ Oracle Technology Network (OTN) New York Oracle User Group ( NYOUG ) Independent Oracle User Group (IOUG) Sangam(Largest Oracle Event in India) All India Oracle User Group (AIOUG) Author of 70+ articles Oracle Technology Network (OTN) Toad World -Connected-Driven Innovation OTechMagazine All things Oracle from Redgate UKOUG LibraryCO-FOUNDER OF Y V RAVI KUMARORACLE CERTIFICATIONSO racle database 10g: Certified Master (10g OCM) Oracle database 10g & 11g: Administering RAC Certified ExpertOracle database 11g: Performance Tuning Certified ExpertOracle Exadata 11g EssentialsOracle Golden Gate 10 EssentialsOracle database 11g.

2 SQL Tuning Certified ExpertOracle 9i & 10g: Managing Oracle on Linux Certified ExpertOracle Certified Professional (OCP) Oracle 12c, 11g, 10g, 9i and 8iSUN Certified Solaris System Administrator in SUN Solaris 93 PatternsORACLEGOLDENGATE 11g/12cIntroduction4 PatternsQuick History In ReplicationChange Data Capture (CDC) Oracle Advance ReplicationOracle StreamsOracle GoldenGate Multi-Master Replication MV Replication Synchronous Asynchronous Capture Messages Staging Messages Consumption Heterogeneous Multiple Architectures Multiple Use Cases5 PatternsDatabase Replication OptionsData Guard Active Data GuardInfoSphereChange Data Capture for Oracle Replication6 PatternsINTRODUCTIONO racle Golden Gate enables the exchange and manipulation of data at the transaction level among multiple, heterogeneous platformsacross the enterprise.

3 Oracle Golden Gate provides low-impact capture, routing, transformation, and delivery of database transactions across heterogeneous environments in near-real time. Oracle Golden Gate moves committed transactions from redo logs and maintainstransaction integrity with sub-second latency7 PatternsOracle GoldenGate Supported PlatformsChoice can be made from 2 types of Oracle GoldenGate (OGG):Supported DatabasesSupported Operating SystemsAIX, z/OS, iSeries, z/LinuxAlways check the latest Certification Matrix8 PatternsORACLE GOLDENGATET opologies9 PatternsORACLE GoldenGate TOPOLOGIESUNIDIRECTIONAL QUERY OFFLOADINGBIDIRECTIONAL STANDBY DB OR ACTIVE-ACTIVE FOR HIGH-AVAILABILITYPEER-TO-PEER LOAD BALANCING.

4 MULTI MASTER10 PatternsORACLE GOLDEN GATE TOPOLOGIESBROADCAST DATA DISTRIBUTIONINTEGRATION / CONSOLIDATION DATA WAREHOUSECASCADING DATAMARTS11 PatternsORACLE GOLDEN GATE TOPOLOGIESUNIDIRECTIONAL QUERY OFFLOADINGBIDIRECTIONAL STANDBY DB OR ACTIVE-ACTIVE FOR HIGH-AVAILABILITYPEER-TO-PEER LOAD BALANCING, MULTIMASTERBROADCAST DATA DISTRIBUTIONINTEGRATION / CONSOLIDATION DATA WAREHOUSECASCADING MARTS12 PatternsTRANSACTIONALDATA INTEGRATIONOPERATIONALBUSINESSINTELLIGEN CEHIGH AVAILABILITYSTANDBY(OPEN & ACTIVE) Oracle GoldenGate 12cOLTP SYSTEMSORACLEGOLDENGATEZERO DOWN-TIME UPGRADES & MIGRATIONSNEW DB / OS /HWLIVE REPORTINGREPORTINGDATABASEODS.

5 EDW .EDW .ETLETLETLETLETLMESSAGE BUS13 PatternsBenefits Of Oracle GoldenGate High Availability (Standby database ) Load balancing Application Specific HA Zero Down-Time Upgrades and Migrations Consolidations database Migrations / Upgrades Platform Migrations ( HP-UX to Linux) database Upgrades Homogeneous & Heterogeneous Application Upgrades ( Siebel 7 -> Siebel 8) Application Releases (special use cases) Live Reporting (Reporting database ) Off load reports to a separate system Operational Business Intelligence Transactional Data Integration14 PatternsBenefits Of Oracle GoldenGate Oracle GG is a middleware product designed to work in a heterogeneous environment with different databases.

6 Oracle GG moves only committed data across platforms where as Oracle database , which writes committed and uncommittedchanges to the redo logs. Moves across a TCP/IP network and does not require Oracle Net. Oracle GG will not provide automatic failover like Oracle DG. Oracle GG uses its own Commit Sequence Number (CSN) to identify a transaction which based on the Oracle database SCN (System Change Number). Complete data recoverability via trail Golden Gate Solutions for Oracle DatabaseUpgrade Oracle database versions 8i, 9i, 10g or 11g to 12c Migrate from non- Oracle databases to Oracle 12cUpgrade or migrate the database server or operating systemPerform database maintenance16 PatternsOracle GoldenGate Solutions for Oracle database Eliminate Down-Time During Oracle database Upgrades Eliminate Unplanned Down-Time With Active Data Guard Improve Production System Performance Real-Time Reporting from a Lower-Cost System Increase ROI On Existing Servers and Synchronize Global Data Capture can be offloaded from the source DB to an intermediate host by copying the

7 Redo logs17 PatternsORACLE GoldenGate Components and Architecture18 PatternsORACLE GOLDEN GATE LOGICAL ARCHITECTUREEXTRACTEXTRACTREPLICATREPLIC ATCOLLECTORDATA PUMPMANAGERMANAGERNETWORKDATA SOURCE FOR INITIAL LOAD: SOURCE TABLESINITIAL LOADTRAIL OR FILECHANGE SYNCHRONIZATIONDATA SOURCECHANGESYNCHRONIZA-TION:TRANSACTION LOG(OPTIONAL)19 PatternsSERVER: Golden Gate 1 (Source) Oracle GoldenGate 12c Source and Target SCHEMASAPARSYSHRWHLGWRP rocessDATABASE: ORACLEW rites databaseChanges to redo log filesOnline RedoLog FilesGoldenGateextract process(HREXT)---only reads redo for HR schemaReadsredologfilesGoldenGatedata pumpprocessGoldenGateTrail FilesFile grows until records are processedReadsTrail FilesExtractWritesHR schema changes toSERVER: Golden Gate 2 (Target)SCHEMASSYSHRDATABASE.

8 ORACLEG oldenGateReplicat (RPHR01)processChanges written toHR SchemaGoldenGateCollectorprocessTrail files grow until data processedReads Trail FilesWrites toTrail FilesGoldenGateData Pump writesWrites to collectors20 PatternsSERVER: GoldenGate 1 (Source) Oracle GoldenGate 12c Source and Target SCHEMASAPARSYSHRWHDATABASE: ORACLEW rites databaseChanges to redo log filesOnline RedoLog FilesReadsredologfilesReadsTrail FilesExtractSERVER: GoldenGate 2 (Target)SCHEMASSYSDATABASE: ORACLEC hanges written toHR SchemaReads Trail FilesWrites toTrail FilesGoldenGateData Pump writesWrites to collectorsLGWRP rocessGoldenGateextract process(HREXT)---only reads redo for HR schemaGoldenGateTrail FilesFile grows until records are processedGoldenGateCollectorprocessTrail files grow until data processedGoldenGatedata pumpprocessGoldenGateReplicat (RPHR01)processHRWritesHR schema changes to21 PatternsSERVER: Golden Gate 1 (Source) Oracle GoldenGate 12c Source and Target SCHEMASAPARSYSHRWHLGWRP rocessDATABASE.

9 ORACLEW rites databaseChanges to redo log filesOnline RedoLog FilesGoldenGateextract process(HREXT)---only reads redo for HR schemaReadsredologfilesGoldenGatedata pumpprocessGoldenGateTrail FilesFile grows until records are processedReadsTrail FilesExtractWritesHR schema changes toSERVER: Golden Gate 2 (Target)SCHEMASSYSHRDATABASE: ORACLEG oldenGateReplicat (RPHR01)processChanges written toHR SchemaGoldenGateCollectorprocessTrail files grow until data processedReads Trail FilesWrites toTrail FilesGoldenGateData Pump writesWrites to collectors22 PatternsCheckpoints23 PatternsCHECKPOINTS -CAPTURESOURCE DATABASECAPTURECOMMIT ORDERED SOURCE TRAILCAPTURE CHECKPOINTBEGIN : TX1 INSERT : TX1 BEGIN : TX2 UPDATE : TX1 INSERT : TX2 COMMIT : TX2 BEGIN : TX3 INSERT : TX3 BEGIN : TX4 COMMIT : TX3 DELETE.

10 TX4 Start of Oldest Open (Uncommited) Transaction_____CURRENT READ POSITIONCURRENT WRITE POSITIONBEGIN : TX2 INSERT : TX2 COMMIT : TX2 BEGIN : TX3 INSERT : TX3 COMMIT : TX3 CAPTURECHECK POINT24 PatternsPUMPCOMMIT ORDERED TARGET TRAIL_____CURRENT READ POSITIONCURRENT WRITE POSITIONCHECKPOINTS -CAPTURE PUMPCHECK POINTPUMP CHECKPOINTCHECKPOINTS -CAPTURE -PUMPBEGIN : TX2 INSERT : TX2 COMMIT : TX2 BEGIN : TX3 INSERT : TX3 COMMIT : TX325 PatternsDELIVERY_____CURRENT READ POSITIONCHECKPOINTS -CAPTURE -PUMPPUMPCHECK POINTDELIVERY CHECKPOINTCHECKPOINTS -CAPTURE -PUMP -DELIVERYTARGET DATA