1 / 66

Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529)

Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529). Marie Couch, Principal Product Manager Lik Wong, Senior Director Oracle.

reid
Download Presentation

Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle GoldenGate’s Coordinated Replicat: Scalable Apply Performance(CON8529) Marie Couch, Principal Product Manager Lik Wong, Senior Director Oracle

  2. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

  3. Agenda • GoldenGate Overview • Motivations • Concepts • Configuration & Usage • Resources and Q & A

  4. GoldenGate Overview

  5. Oracle Data Integration Solutions • Thousands of customers including Fortune 500 companies, representing Financial Services, Communications, Healthcare, Public Sector, Retail, Utilities industries • 4 of top 5 largest commercial banks • 3 of top 3 busiest ATM networks • 5 of top 5 financial data services companies • 5 of top 5 telecommunications providers • 3 of top 5 largest food & drug stores

  6. Fun Facts About Oracle GoldenGate That’s the Magic of Macy’s Shopping at the worlds largest retails and your credit card transactions are moved in real-time to data centers around the world, before being approved, using Oracle GoldenGate (Retail Decision) Good TV. Better TV. DirecTV. If you call DIrecTV customer service, your call data will move to their data warehouse via Oracle GoldenGate, so the rep can provide you with personalized recommendations and promotions. Say it with Flowers Ordering from 1800-Flowers, Oracle GoldenGate will move your order to their reporting systems to help process and track your order seamlessly No Charge ATM Fees 3 of the 3 busiest ATM Networks use Oracle GoldenGate for Continuous Availability. Get Sick in Pittsburgh If you get a health check at UPMC, the clinical applications that doctors use are supported by Oracle GoldenGate to enable high-availability

  7. Oracle Data Integration (DIS) Breadth Enables Leverage and Agility Data Quality Data Integration Match/Merge Profiling Standardization Heterogeneous Virtualization Big Data ETL/E-LT Replication

  8. Real-time replication; optimized for Database 12c and Oracle Exadata End-to-end integrated with simplified deployment Unified tooling for both structured data sources and Hadoop / NoSQL Flexible deployment on-premise or in the Cloud for heterogeneous systems Expanded support for 3rd party systems and Oracle Applications in real-time data integration and continuous availability solutions Oracle Data Integration 12c Delivering Real-Time Integration for Big Data and Cloud Cloud Oracle Data Integrator Apps Oracle GoldenGate Oracle Enterprise Data Quality Database Oracle Data Service Integrator Big Data

  9. Oracle Data IntegratorBulk Data Processing and Data Transformation Oracle Data Integrator • Certified for leading technologies to deliver fast time to value • High-performance, low cost of ownership E-LT architecture • Lightweight deployment • Flexible, easy to enrich functionality Cloud High Performance E-LT Apps Declarative Design Database Extensible Knowledge Modules CEP Data Services Big Data Capable of Integrating Structured & Unstructured Data

  10. Oracle GoldenGate Real-time Data Integration Oracle GoldenGate • High-performance, low-impact real-time data integration and replication • Timely data for improved business insight • Continuous availability for 24/7 operation Cloud Log-based Change Data Capture and Delivery Apps Active-Active Replication Heterogeneous Source and Targets Database Reliability and Transaction Integrity Big Data Java Integration, Real-time Events

  11. Oracle Enterprise Data Quality Data Quality for Customer and Product Data Oracle Enterprise Data Quality • Improves data accuracy, usability and ‘fitness for purpose • Unified interface for ease-of-use, lower TCO • Depth of capability in multiple data domains reduces project risk Cloud Profile, Explore, Audit Apps Parse, Standardize, Cleanse Match, Merge, Enrich Database Governance Big Data Case Management

  12. Oracle Data Services Integrator Data Federation and Virtualization Oracle Data Services Integrator • Easily develop data services and ad-hoc queries without infrastructure changes • Open and standards based—SOA, Web Services, Java, Service Data Object Cloud Federated Data Services Apps Data Virtualization Integrates with Oracle SOA Database Bidirectional Reads & Writes Big Data Integrates with Oracle Business Analytics

  13. Oracle GoldenGate Low-Impact, Real-Time Data Integration & Transactional Replication Zero Downtime Upgrade & Migration New DB/HW/OS/APP Fully Active Distributed DB High Availability/ Disaster Recovery Legacy Systems Log-based Changed Data Query & Report Offloading Reporting Database Data Warehouse Real-time BI, Operational Reporting, MDM Oracle & Non-OracleDatabase(s) Data Integrator Global Data Centers ODS Data Synchronization within the Enterprise Message Bus Message Bus Event Driven Architecture, SOA

  14. How Oracle GoldenGate Works Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. As of V.11.2.1, GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture LAN / WAN / Internet Over TCP/IP Capture TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s)

  15. How Oracle GoldenGate Works Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. As of V.11.2.1, GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture LAN / WAN / Internet Over TCP/IP Trail: stages and queues data for routing. Capture Trail Files TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s)

  16. How Oracle GoldenGate Works Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. As of V.11.2.1, GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture LAN / WAN / Internet Over TCP/IP Trail: stages and queues data for routing. Pump: distributes data for routing to target(s). Capture Pump Trail Files TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s)

  17. How Oracle GoldenGate Works Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. As of V.11.2.1, GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture LAN / WAN / Internet Over TCP/IP Trail: stages and queues data for routing. Pump: distributes data for routing to target(s). Route: data is compressed, encrypted for routing to target(s). Capture Pump Trail Files Trail Files TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s)

  18. How Oracle GoldenGate Works Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. As of V.11.2.1, GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture LAN / WAN / Internet Over TCP/IP Trail: stages and queues data for routing. Pump: distributes data for routing to target(s). Route: data is compressed, encrypted for routing to target(s). Delivery: applies data with transaction integrity. Newwith GoldenGate 12c, Coordinated & Integrated Delivery. Capture Pump Delivery Trail Files Trail Files TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s)

  19. How Oracle GoldenGate Works Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs. As of V.11.2.1, GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture LAN / WAN / Internet Over TCP/IP Trail: stages and queues data for routing. Pump: distributes data for routing to target(s). Route: data is compressed, encrypted for routing to target(s). Delivery: applies data with transaction integrity. New with GoldenGate 12c, Coordinated & Integrated Delivery. Capture Pump Delivery Trail Files Trail Files Trail Files Trail Files Delivery Capture Pump TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s) Bi-directional

  20. GoldenGate: Flexible Deployment Architectures Unidirectional Query OffloadingZero-Downtime Migration Bi-Directional Hot Standby or Active-Active for HA Peer-to-Peer Load Balancing Multi-Master Broadcast Data Distribution Integration/Consolidation Data Warehouse Data Distribution via Messaging BPM BAM CEP

  21. Motivations

  22. Current Approach to High Volume Replication Customer defines partitioning SQL SQL Replicat Replicat Replicat Replicat SQL Trail Files SQL TargetDatabase

  23. Coordination Requirements • Coordination requires for the following barrier events • DDL • Primary key update when using RANGE partition • Event Actions • SQLEXEC

  24. Multiple Replicat Range Partitioning Execution Four Transactions Time 3 1 Replicat 1 2 4 Replicat 2 Rows 1 and 3 applied by Replicat 1 Rows 2 and 4 applied by Replicat 2

  25. DDL Coordination Requirement Time DDL 3 1 Replicat 1 2 4 Replicat 2 ERROR DDL committed by one Replicat before waiting for all preceding DMLs

  26. PK Update Coordination Requirement Three Transactions Time 12 Insert1 Replicat 1 Replicat 2 Delete 2 ERROR Delete occurs before the primary key is updated by another Replicat

  27. Usability Requirements • Ease of Use • Single parameter file for ease of configuration and maintenance • Simplify apply parallelism tuning • Simplify error recovery • Support for all database versions and platforms

  28. Concepts

  29. Coordinated Delivery Approach Single Delivery configuration for customer Replicat SQL Thread Customer Defines Partitioning SQL Thread SQL Thread Trail Files SQL GoldenGate coordinates threads for barrier operations Thread TargetDatabase

  30. Coordinated Delivery Architecture Checkpoint File Replicat Thread 1 Thread Creation/Shutdown Dependency Coordination Statistics Aggregation Two Way Communication Apply in Parallel SingleParameter File Trail File … Coordinator Checkpoint Table Database Checkpoint File Two Way Communication Replicat Thread N Checkpoint File

  31. Single Parameter File (THREAD) REPLICAT (Before) COORDINATED REPLICAT (After) • REP1.PRM • MAP sales.acct1, TARGET sales.acct1; • REP2.PRM • MAP sales.acct2, TARGET sales.acct2; • REP3.PRM • MAP sales.acct3, TARGET sales.acct3; REP.PRM MAP sales.acct1, TARGET sales.acct1, THREAD(1); MAP sales.acct2, TARGET sales.acct2, THREAD(2); MAP sales.acct,3 TARGET sales.acct3, THREAD(3);

  32. Single Parameter File (THREADRANGE) REPLICAT (Before) COORDINATED REPLICAT (After) • REP1.PRM • MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 3, ID)); • REP2.PRM • MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 3, ID)); • REP3.PRM • MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (3, 3, ID)); REP.PRM MAP sales.acct, TARGET sales.acct, THREADRANGE(1-3, ID));

  33. Replicat Coordination • Barrier thread: thread with lowest specified ID • Without THREAD/THREADRANGE specification, transactions are applied by the barrier thread • That means that if a THREAD is not specified in a MAP it will default to barrier thread (assumes 1 if no THREADs are specified), this will allow customers to upgrade without modifying their parameter files.

  34. Replicat Coordination (cont.) • If the user specified COORDINATED in a map, it is applied with full barrier coordination. • MAP u1.t1, TARGET u2.t1, COORDINATED; • Thread with lowest ID executes transactions involving Table t1 with full barrier synchronization • On the other hand: • MAP u1.t2, TARGET u2.t2, THREAD(2); • Thread 2 executes changes involving Table t2 assuming no need for coordination. • If a transaction modifies both t1 and t2, it will get executed by the Barrier thread with Coordination.

  35. STOP Semantics STOP • Stop • All threads stop at the same position (coordinated/clean stop). • Threads send current position and Coordinator chooses the maximum position as the consensus. • Threads use this position and stop at the transaction boundary following consensus position.

  36. FORCE STOP Semantics FORCE STOP • Force Stop • Threads potentially abort current transaction and stop immediately. • Different Threads may end at different trail positions

  37. Re-Partitioning TransactionX1 • Thread 1 has not applied Row 1, 2 & 5. • Thread 2 has applied Rows 3 & 4. • Replicatabends or is force stopped REP.PRM MAP sales.acct, TARGET sales.acct, THREADRANGE(1-2, ID)); User should not change the parameter file when Replicat has not stopped cleanly (ABEND, Force Stop etc.).

  38. Re-Partitioning (Parameter File Changed) New Partition Old Partition • Changing parameter file results in new thread (Thread 3) with ownership of Rows 2 & 3. • If Thread 3 starts at Row1, Row 3 will be re-applied. • If Thread 3 starts after Row 5, Row 2 will not be applied. • Thread 2 ignores unapplied Row 5 ? REP.PRM MAP sales.acct, TARGET sales.acct, THREADRANGE(1-3, ID));

  39. Synchronize Replicat TransactionX1 TransactionX1 • Command to facilitate clean repartitioning • Synchronize Replicat • All Threads will start up, execute until the maximum check-pointed position of all threads, and then stop. • GGSCI> SYNCHRONIZE REPLICAT REP1

  40. Synchronize Replicat (cont.) • Correct way to repartition • Stop cleanly or employ synchronize replicat command • Change thread list in parameter file (partition mapping) • Unclean re-partitioning notifications • After an unclean stop, on startup, a parameter file change will result in a warning. • After an unclean stop, on startup, Replicat will issue an error if the partition mapping has changed during repartitioning. • Changing order will not currently result in an error

  41. Unplanned Repartitioning • Coordinated Replicat stores the previous good parameter file for reference • In ./dirprm folder (.backup file), e.g., rep1.prm.backup • Suggested recovery procedure • Reposition to low-watermark. • Start Replicat employing handle collisions until high-watermark. • Stop Replicat. • Restart without handle collisions.

  42. Crash Recovery • Replicat group (coordinator and all threads) will ABEND on encountering any unhandled error.

  43. Crash Recovery Options • Manual fix • Coordinator report file will point to thread that caused the crash. • Manually fix the error (e.g., modify the database) and restart • Reposition the replicat (alter replicat) • Reposition the offending thread to position following the error and start replicat again.

  44. Crash Recovery Options (cont.) • Employ HANDLECOLLISIONS • HANDLECOLLISIONS accepts THREAD/THREADS/THREADRANGE parameter to prevent it from masking errors inadvertently. • HANDLECOLLISIONS • NOHANDLECOLLISIONS THREAD(3) • MAP scott.employees, TARGET scott.employess, THREADRANGE(1-4, OID); • Here, thread 3 will not employ handle collisions while threads 1, 2 and 4 will.

  45. Reporting • Each thread will continue to have its own report file. • GGSCI and monitoring infrastructure will get aggregated statistics from the Coordinator. • The Coordinator maintains some unique statistics: • Coordinated-Statistics (total DDLs, PKs, etc.) • Average-Coordination-Time • Thread-Lag-Gap

  46. Configuration & Usage

  47. Creating Coordinated Replicat Groups • GGSCI> add replicat rep1, coordinated, exttrail …, maxthreads 10 • Creates 12 “Replicats” in total • rep1 (Coordinator), rep1000 (Thread 0 that is created by default) • rep1001, rep1002, rep1003…. rep1010 (Threads 1, 2 … etc.) • Each Replicat has its own checkpoint file, report file and is a fully functional replicat. • Processes will only be created for threads mentioned in the Parameter file • MAXTHREADS defaults to 25 threads.

  48. Creating Coordinated Replicat Group (cont.) • Replicat group name (excluding generated characters) must be 5 characters or less. • Start Replicat with the Coordinator’s Replicat name • GGSCI> START rep1 • Stop and Start are not permitted on individual threads.

  49. Parameter Changes • THREAD: used in MAP, non-MAP parameters, GGSCI commands • THREADS: comma separated list or ranges (low-high), used in • non-MAP parameters, e.g., HANDLECOLLISIONS threads(2-4) • GGSCI commands, e.g., START REPLICAT rep1 AFTERCSN 1000 THREADS(2-5) • THREADRANGE (<thread-id-low>-<thread-id-high>, column list) • used in MAP for RANGE partitioning

More Related