More Related Content
What's hot
What's hot (20)
Similar to Replicate Oracle to Oracle, Oracle to MySQL, and Oracle to Analytics
Similar to Replicate Oracle to Oracle, Oracle to MySQL, and Oracle to Analytics (20)
Recently uploaded
Recently uploaded (20)
Replicate Oracle to Oracle, Oracle to MySQL, and Oracle to Analytics
- 1. Replicate Oracle to Oracle, Oracle to MySQL , and Oracle to Analytics Linas Virbalas, Senior Software Engineer Robert Hodges, CEO ©Continuent 2014
- 2. 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
- 3. 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
- 4. 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
- 5. 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
- 6. 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
- 7. Multiple Services per Replicator Replicator Service frommysql Replicator frommysql fromoracle Aggregated Service fromoracle Replicator ©Continuent 2014
- 10. 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
- 11. Replicating from MySQL to Oracle ©Continuent 2014
- 12. Use Case: Real-Time Sales/Order Fulfillment Web-based Sales ~1M Xacts/ Day ©Continuent 2014 Purchase Ordering Real-Time Replication 3M Order Items
- 13. 1. Translating schema for the slave ©Continuent 2014
- 14. Translating Schema • Goal - set up heterogeneous replication Replication ©Continuent 2014 14
- 15. Translating Schema • Beginning - how to convert tables? empty Tables ©Continuent 2014 15
- 16. Translating Schema • Beginning - how to convert tables? empty •Data types? •Column lengths? •Naming conventions? •Reserved words? Tables ©Continuent 2014 16
- 17. Translating Schema • ddlscan automates these conversions ddlscan Tables ©Continuent 2014 17 empty
- 18. 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
- 19. Usage (MySQL to Oracle Example) $ cd tungsten-replicator/bin ! $ ./ddlscan -db test -template ddl-mysql-oracle.vm -user tungsten -pass secret ©Continuent 2014 19
- 20. Translating Schema • ddlscan looks into source schema empty Tables ©Continuent 2014 20
- 21. Translating Schema • ddlscan translates and renders DDL commands empty Tables oracle-ddl.sql ©Continuent 2014 21
- 22. 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
- 23. Translating Schema • You run resulting SQL file on Oracle empty Tables oracle-ddl.sql ©Continuent 2014 23
- 24. Translating Schema • Tables are ready! Tables ©Continuent 2014 Translated tables (empty) 24
- 25. 2. Set Up Replication ©Continuent 2014
- 26. 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
- 27. 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
- 28. 3. Provisioning an Oracle slave ©Continuent 2014
- 29. Provisioning (2) ddlscan table structure table structure table data table structure table data table data ©Continuent 2014 29 table structure table structure table structure
- 30. 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
- 31. Provisioning (4) not replicating MySQL Sandbox table structure table structure table data table structure table data table data backup ©Continuent 2014 31
- 34. 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
- 35. Replicating from Oracle to MySQL ©Continuent 2014
- 36. Use Case: Web Content Publishing Backend Office Web-Based Catalog Real-Time Publication ©Continuent 2014
- 37. 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
- 38. Replicating from Oracle to Oracle ©Continuent 2014
- 39. 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
- 41. Replicating from Oracle to Vertica ©Continuent 2014
- 42. Use Case: Web Content Publishing Backend Office Analytic Reports Near Real-Time Publication Feeds from other data sources ©Continuent 2014
- 43. 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)
- 44. 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
- 47. 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
- 48. 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