slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529) PowerPoint Presentation
Download Presentation
Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529)

Loading in 2 Seconds...

play fullscreen
1 / 66

Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529) - PowerPoint PPT Presentation


  • 249 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle GoldenGate’s Coordinated Replicat : Scalable Apply Performance (CON8529)' - reid


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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

slide3

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.

agenda
Agenda
  • GoldenGate Overview
  • Motivations
  • Concepts
  • Configuration & Usage
  • Resources and Q & A
oracle data integration solutions
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
fun facts about oracle goldengate
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

oracle data integration dis
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

delivering real time integration for big data and cloud

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

slide10

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

oracle goldengate
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

oracle enterprise data quality
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

oracle data services integrator
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

oracle goldengate1
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

how oracle goldengate works
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)

how oracle goldengate works1
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)

how oracle goldengate works2
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)

how oracle goldengate works3
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)

how oracle goldengate works4
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)

how oracle goldengate works5
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

goldengate flexible deployment architectures
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

current approach to high volume replication
Current Approach to High Volume Replication

Customer defines partitioning

SQL

SQL

Replicat

Replicat

Replicat

Replicat

SQL

Trail

Files

SQL

TargetDatabase

coordination requirements
Coordination Requirements
  • Coordination requires for the following barrier events
    • DDL
    • Primary key update when using RANGE partition
    • Event Actions
    • SQLEXEC
multiple replicat range partitioning execution
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

ddl coordination requirement
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

pk update coordination requirement
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

usability requirements
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
coordinated delivery approach
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

coordinated delivery architecture
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

single parameter file thread
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);

single parameter file threadrange
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));

replicat coordination
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.
replicat coordination cont
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.
stop semantics
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.
force stop semantics
FORCE STOP Semantics

FORCE STOP

  • Force Stop
    • Threads potentially abort current transaction and stop immediately.
    • Different Threads may end at different trail positions
re partitioning
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.).

re partitioning parameter file changed
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));

synchronize replicat
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
synchronize replicat cont
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
unplanned repartitioning
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.
crash recovery
Crash Recovery
  • Replicat group (coordinator and all threads) will ABEND on encountering any unhandled error.
crash recovery options
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.
crash recovery options cont
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.
reporting
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
creating coordinated replicat groups
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.
creating coordinated replicat group cont
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.
parameter changes
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
parameter changes cont
Parameter Changes (cont.)
  • HANDLECOLLISIONS (already outlined)
  • SPECIALRUN, SHOWSYNTAX (disabled for Coordinated Replicats)
  • TRACE/TRACE2
    • Can specify THREADS
    • Applies to all threads if there is no thread specification
    • For multiple threads, will automatically append thread suffix (001, 002, etc) to the given trace file name
  • SQLEXEC
    • Global SQLEXEC supplemented with THREAD/THREADS parameter
    • SQLEXEC associated with barrier thread will execute with Coordination
ggsci commands affected
GGSCI Commands Affected
  • The output of the INFO command for Coordinated Replicats contains

COORDINATED Coordinator MAXTHREADS 10

COORDINATED Apply Thread Thread 1

  • INFO DETAIL includes information about all running threads

ID Group Name PID Status Lag at Chkpt Time Since Chkpt

1 REP2001 15138 RUNNING 00:00:00 00:00:00

2 REP2002 15139 RUNNING 00:00:00 00:00:00

ggsci commands affected cont
GGSCI Commands Affected (cont.)
  • A LAG request to the Coordinator returns the lag of each thread along with the minimum, maximum, and average lag of all threads

REP2001- Last record lag:6 seconds

REP2002- Last record lag:8 seconds

REP2003- Last record lag:7 seconds

Minimum Lag: Thread 1 - REP2001: 6 seconds

Maximum Lag: Thread 2 - REP2002: 8 seconds

Average Lag: 7 seconds.

ggsci commands affected cont1
GGSCI Commands Affected (cont.)
  • STATS includes information about barrier events

Coordinated Replicat Statistics:

Thread Lag Gap: 00:00:00 (updated 00:00:03 ago)

Coordinated Total DDLs: 0

Coordinated Total PK-update transactions: 0

Coordinated Total EMI transactions: 0

Total transactions with user-requested coordination: 0

Average Coordination Time: 00:00:00

ggsci commands affected cont2
GGSCI Commands Affected (cont.)

START and STOP will only be permitted on the Coordinator

ALTER, when used on a coordinator, will alter all threadsGGSCI> alter replicat rep1Alters rep1, rep1001, rep1002, etc.

CLEANUP, when used on a coordinator, will perform cleanup on each thread

KILL will make sure that both the coordinator and all the threads are killed

Some options for SEND (HANDLECOLLISIONS, TRACE) can take thread specifications

ggsci commands affected cont3
GGSCI Commands Affected (cont.)
  • If a GGSCI command is invalid for individual threads, it will be ignored. E.g., coordinator rep, and threads rep001-rep999
    • GGSCI> start rep0*No ER groups found, but coordinated servers may be excluded.
    • GGSCI >start rep*Sending START request to MANAGER ...REPLICAT REP starting(Only Coordinator starts and outputs to console)
  • Name collision errors are now possible
    • GGSCI>add replicatrep, …, coordinated
    • GGSCI> add replicat rep001,…. (non coordinated)
performance tuning
Performance Tuning

Performance tuning practices for non-Coordinated Replicat apply, e.g., BATCHSQL.

Each thread can connect to different RAC instances in a RAC configuration.

Leverage coordination events, lag spread and each thread’s report file for scalability tuning.

Avoid coordination heavy workload, e.g., frequent PK update workloads with THREADRANGE partitioning

Avoid too many threads if the workload can be handled by fewer threads.

troubleshooting aids
Troubleshooting Aids

Each thread has its own report file, discard file, ggserr.log.

Except START/STOP, other Replicat specific GGSCI commands apply to each thread.

join the data integration community
Join the Data Integration Community

LinkedIn“Oracle Data Integration”

Oracle’s Data Integration blogblogs.oracle.com/dataintegration

Twittertwitter.com/ORCLGoldenGate

Facebookfacebook.com/OracleDataIntegration

Oracle GoldenGate YouTube channelyoutube.com/oraclegoldengate

Oracle Technology Network

Oracle’s Public Help Forumhttp://forums.oracle.com/forums/forum.jspa?forumID=860&start=0

Oracle.com/goto/dataintegration