Replicate Oracle to Oracle, *Oracle to MySQL, *and Oracle to Analytics

50 %
50 %
Information about Replicate Oracle to Oracle, *Oracle to MySQL, *and Oracle to Analytics
Technology

Published on February 19, 2014

Author: 777oxy

Source: slideshare.net

Description

Oracle is the most powerful DBMS in the world. However, Oracle's expensive and complex replication makes it difficult to build highly available applications or move data in real-time to data warehouses and popular databases like MySQL. In this webinar you will learn how Continuent Tungsten solves problems with Oracle replication at a fraction of the cost of other solutions and with less management overhead too – think "Oracle GoldenGate without the price tag!" We will demo constructing a highly available site using Oracle-to-Oracle replication. We will then show you how to replicate data in real time from Oracle to MySQL as well as load a data warehouse.

Replicate Oracle to Oracle, Oracle to MySQL , and Oracle to Analytics Linas Virbalas, Senior Software Engineer Robert Hodges, CEO ©Continuent 2014

Introducing Continuent • The leading provider of clustering and replication for open source DBMS • Our Product: Continuent Tungsten • Clustering - Commercial-grade HA, performance scaling and data management for MySQL • Replication - Flexible, high-performance data movement ©Continuent 2014 2

What is Tungsten Replicator? Tungsten Replicator: a high-performance open source database replication engine ! GPL V2 license - 100% open source Download from https://code.google.com/p/tungsten-replicator/ Annual support subscription available from Continuent ® without the Price Tag” “GoldenGate ©Continuent 2014 3

Tungsten Replicator Overview Master Download transactions via network DBMS Logs Slave Replicator (Transactions + Metadata) Replicator Apply using JDBC ©Continuent 2014 THL THL (Transactions + Metadata) 4

Tungsten Replication Service Pipeline Stage Extract Filter Master DBMS ©Continuent 2014 Stage Apply Extract Filter Transaction History Log Stage Apply Extract Filter Queue 5 Apply Slave DBMS

Filters and Parallel Apply Pipeline Stage Extract Filter Master DBMS ©Continuent 2014 Stage Stage Apply Extract Filter Transaction History Log Apply Extract Filter Extract Filter Extract Filter In-Memory Queue 6 Apply Apply Apply Slave DBMS

Multiple Services per Replicator Replicator Service frommysql Replicator frommysql fromoracle Aggregated Service fromoracle Replicator ©Continuent 2014

master-slave MySQL Oracle fan-in slave ©Continuent 2014 Oracle MySQL all-masters Heterogeneous Oracle MySQL Oracle star MySQL

Heterogeneous Replication ©Continuent 2014

Steps to Heterogeneous Replication 1. Prepare (translate) schema for the slave DBMS 2. Set up replication ddlscan tpm 3. Provision initial data
 
 MySQL Blackhole Parallel Apply Parallel Extract 4. Continue real-time replication Tungsten Replicator ©Continuent 2014

Replicating from MySQL to Oracle ©Continuent 2014

Use Case: Real-Time Sales/Order Fulfillment Web-based Sales ~1M Xacts/ Day ©Continuent 2014 Purchase Ordering Real-Time Replication 3M Order Items

1. Translating schema for the slave ©Continuent 2014

Translating Schema • Goal - set up heterogeneous replication Replication ©Continuent 2014 14

Translating Schema • Beginning - how to convert tables? empty Tables ©Continuent 2014 15

Translating Schema • Beginning - how to convert tables? empty •Data types? •Column lengths? •Naming conventions? •Reserved words? Tables ©Continuent 2014 16

Translating Schema • ddlscan automates these conversions ddlscan Tables ©Continuent 2014 17 empty

ddlscan • • • • ©Continuent 2014 Part of Tungsten Replicator, GPL v2 Translates schema with replication in mind Provides errors and warnings Can rename schema/tables/columns 18

Usage (MySQL to Oracle Example) $ cd tungsten-replicator/bin ! $ ./ddlscan -db test -template ddl-mysql-oracle.vm -user tungsten -pass secret ©Continuent 2014 19

Translating Schema • ddlscan looks into source schema empty Tables ©Continuent 2014 20

Translating Schema • ddlscan translates and renders DDL commands empty Tables oracle-ddl.sql ©Continuent 2014 21

Result of ddlscan CREATE TABLE 3colors /* ERROR: table starts with a number, use rename option and a filter */ ( id NUMBER(10, 0), color VARCHAR2(1) /* ENUM('R','G','B') */, enabled NUMBER(1) /* BIT(1) - no constraints for 0 or 1 */, acolumnthatdoesntfittooracleat NUMBER(10, 0) /* WARN: truncated column name exceeding 30 characters (acolumnthatdoesntfittooracleatall) */ ! /* ERROR: table must have a primary key! */ ); ! CREATE TABLE talks ... ©Continuent 2014 22

Translating Schema • You run resulting SQL file on Oracle empty Tables oracle-ddl.sql ©Continuent 2014 23

Translating Schema • Tables are ready! Tables ©Continuent 2014 Translated tables (empty) 24

2. Set Up Replication ©Continuent 2014

Replicator Installation ./tools/tpm configure my_ora --enable-heterogenous-service=true --install-directory=/opt/mysql_to_ora/continuent --members=alpha,bravo --master=alpha ! ./tools/tpm configure my_ora --hosts=alpha --replication-user=tungsten --replication-password=secret ! ./tools/tpm configure my_ora --hosts=bravo --replication-user=tungsten_frommysql --replication-password=secret --datasource-type=oracle --datasource-oracle-service=ORCL --svc-applier-filters=dropstatementdata ! ./tools/tpm install my_ora ! ./tools/tpm start my_ora ! ©Continuent 2014 26

Replication Under the Hood MySQLExtractor OracleApplier Tungsten Master Replicator Service my_ora MySQL Binlog binlog_format=row ©Continuent 2014 Tungsten Slave Replicator Service my_ora MySQLExtractor Special Filters • Transform ENUM to string • Transform SET to string • Column names and signed flag Special Filters • Drop DDL • Map names to upper case • Rename too long or reserved objects • Optimize updates to remove unchanged columns • Ignore extra tables 27

3. Provisioning an Oracle slave ©Continuent 2014

Provisioning (2) ddlscan table structure table structure table data table structure table data table data ©Continuent 2014 29 table structure table structure table structure

Provisioning (3) MySQL Sandbox table structure table structure table data table structure table data table data table structure table structure table structure CONVERT to BLACKHOLE ©Continuent 2014 30

Provisioning (4) not replicating MySQL Sandbox table structure table structure table data table structure table data table data backup ©Continuent 2014 31

Provisioning (5) (ROW) backup ©Continuent 2014 Master Tungsten Replicator THL 32 4 bin logs 1 2 3 MySQL Sandbox Slave Tungsten Replicator

4. Continue Real-Time Replication ©Continuent 2014

Provisioning (6) MySQL Sandbox table structure table structure table data table structure table data table data Master Tungsten Replicator bin logs THL ©Continuent 2014 34 Slave Tungsten Replicator

Replicating from Oracle to MySQL ©Continuent 2014

Use Case: Web Content Publishing Backend Office Web-Based Catalog Real-Time Publication ©Continuent 2014

How Does Tungsten Do That? OracleCDCExtractor MySQLApplier Tungsten Master Replicator Tungsten Slave Replicator Service ora_my Service ora_my OracleCDCExtractor No Special Filters demo schema demo_pub schema Oracle CDC (Synchronous or Async Hotlog) ©Continuent 2014 37 Special Filters • Map names to lower case • Ignore extra tables • Heartbeat table renaming

Replicating from Oracle to Oracle ©Continuent 2014

How Does Tungsten Do That? OracleCDCExtractor OracleApplier Tungsten Master Replicator Tungsten Slave Replicator Service ora_ora Service ora_ora OracleCDCExtractor No Special Filters demo schema demo_pub schema Oracle CDC (Synchronous or Async Hotlog) ©Continuent 2014 39 Special Filters No Special Filters

DEMO DEMO.* DEMO2.* ©Continuent 2014 demo.* demo2.*

Replicating from Oracle to Vertica ©Continuent 2014

Use Case: Web Content Publishing Backend Office Analytic Reports Near Real-Time Publication Feeds from other data sources ©Continuent 2014

How Does Tungsten Do That? OracleCDCExtractor SimpleBatchApplier Tungsten Master Replicator Tungsten Slave Replicator Service ora_ver Service ora_ver OracleCDCExtractor No Special Filters demo schema demo_pub schema Oracle CDC (Synchronous or Async Hotlog) ©Continuent 2014 43 Special Filters • Map names to lower case • Ignore extra tables • Rename data tables (if required)

Batch Loading--The Gory Details Replicator Transactions from master Service ora_ver COPY to stage tables CSV CSV CSV Files Files Files Merge Script ©Continuent 2014 44 (or) COPY directly to base tables Staging Staging Staging Tables Tables Tables SELECT to base tables Base Base Base Tables Tables Tables

DEMO ©Continuent 2014

Coming Attractions ©Continuent 2014

New Replication Features in 2014 • • ©Continuent 2014 Parallel extraction for fast provisioning • • • Select data as of specific SCN Pull data across in multiple streams Matches parallel apply on slave side ! Hadoop data loading • • • Direct loading from MySQL/Oracle to HDFS View data using Hive Generation of materialized views 47

560 S. Winchester Blvd., Suite 500 San Jose, CA 95128 Tel +1 (866) 998-3642 Fax +1 (408) 668-1009 e-mail: sales@continuent.com Our Blogs: http://flyingclusters.blogspot.com http://scale-out-blog.blogspot.com http://datacharmer.blogspot.com http://continuent-tungsten.blogspot.com Continuent Web Page: http://www.continuent.com ! Documentation: https://docs.continuent.com ! Tungsten Replicator: http://code.google.com/p/tungsten-replicator ©Continuent 2014 48

Add a comment

Related presentations

Related pages

Replicate from Oracle to Oracle, Oracle to MySQL, and ...

Want to watch this again later? Sign in to add this video to a playlist. Oracle is the most powerful DBMS in the world. However, Oracle's ...
Read more

Planet MySQL - Archives - Replicate from Oracle to Oracle ...

... Replicate from Oracle to Oracle, Oracle to MySQL, ... Oracle to MySQL, and Oracle to analytics ... solves problems with Oracle replication at a ...
Read more

Replicate from Oracle to Oracle, Oracle to MySQL, and ...

... Oracle's expensive and complex replication makes it ... Oracle to MySQL, and Oracle to analytics ... from Oracle and MySQL into data ...
Read more

Replication between MySQL and Oracle Databases | Oracle ...

Now I have to implement some sort of replication/data synchronization between MySQL and Oracle databases. ... Replication between MySQL and Oracle Databases.
Read more

6.1.1. Prepare: MySQL to Oracle Replication - pubs.vmware.com

Deploying MySQL to Oracle Replication ... In MySQL to Oracle deployments the DDL can be read from the MySQL server and generated for the Oracle ...
Read more

Realtime Heterogeneous MySQL Replication using Oracle ...

Realtime Heterogeneous MySQL Replication using Oracle GoldenGate. ... MySQL.com is using Oracle SSO for authentication. If you already have an Oracle Web ...
Read more

Replicating in Real-Time Between Oracle and Oracle, and ...

Linas has extensive experience in developing heterogeneous replication solutions between MySQL, Oracle and PostgreSQL. Implemented support for MySQL to ...
Read more

Replication from oracle db 11g to MySQL | Oracle Community

... able to find "Installing Oracle Database Gateway for MySQL." First tell me am i on correct path for for replication of oracle database table to MySQL?
Read more