1 / 66

Deploying Oracle GoldenGate with Oracle Multitenant Database

Deploying Oracle GoldenGate with Oracle Multitenant Database. Joydip Kundu Enterprise Replication Server Technologies Division Oracle Corporation.

zamir
Download Presentation

Deploying Oracle GoldenGate with Oracle Multitenant Database

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. Deploying Oracle GoldenGate with Oracle Multitenant Database Joydip Kundu Enterprise Replication Server Technologies Division Oracle Corporation

  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. Program Agenda • Overview of Oracle GoldenGate • Introduction to Oracle 12c Multitenant Database (CDB) • Integrated Capture on CDB • CDB-specific Infrastructure in Oracle GoldenGate • Examples • Summary

  4. Introduction to Oracle GoldenGate

  5. Oracle GoldenGate Differentiators Fast Flexible Reliable • Maintains transactional integrity • Resilient against interruptions and failures • Fast Recovery after process interruptions. • Low latency for data movement • Low-impact capture, routing, transformation, and delivery of transactional data • Modern, open, modular architecture • Supports heterogeneous sources and targets • Agile and adaptable to modern data integration architectures

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

  7. How Oracle GoldenGate Works Capture: committed transactions are captured as they occur (asynchronous capture from transaction logs). 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, transforming the data as required. Capture Pump Delivery Trail Files Trail Files Trail Files Trail Files Delivery Capture Pump TargetOracle & Non-OracleDatabase(s) SourceOracle & Non-OracleDatabase(s) Bi-directional

  8. Oracle GoldenGate: Integrated Capture GoldenGate Event Log Integrated Capture GoldenGateParameter File Messaging Subsystem Parser Checkpoint Manager Memory Manager GoldenGate Checkpoint File LCR Processor Formatting Trail File Generation Transformation and Mapping Filtering Database LogMiningServer GoldenGateTrail Files Metadata Manager Transformation Engine SQL Interface Database Access

  9. Program Agenda • Overview of Oracle GoldenGate • Introduction to Oracle 12c Multitenant Database (CDB) • Integrated Capture on CDB • CDB-specific Infrastructure in Oracle GoldenGate • Examples • Summary

  10. Introduction to Oracle 12c Multitenant Database

  11. Multitenant Architecture Multitenant Container Database Components of a Multitenant Container Database (CDB) PDBs 12.1 12.1 12.1 DW HCM Root 12.1 12.1 Pluggable Databases (PDBs) CRM ERP CDB ROOT

  12. Unplug / plug Simply unplug from the old CDB…

  13. Unplug / plug …and plug in to the new CDB… • Moving between CDBs is a simple case of moving a PDB’s metadata • An unplugged PDB carries with it lineage, opatch, encryption key info etc

  14. Unplug / plug Example Unplug alter pluggable database HCM unplug into '/u01/app/oracle/oradata/…/hcm.xml' Plug create pluggable database My_PDB using '/u01/app/oracle/oradata/…/hcm.xml'

  15. Common Users and Privileges Authorization is checked in the same way as in pre-12.1 • A common user can be granted privileges locally in a PDB (or root)and therefore differently in each container • A common user can, alternatively, be granted a system privilegecommonly – the grant is made in root and every PDB, present and future • You can create a common role • A common role can be granted to a common user commonly • Authorization is checked in the container where the SQL is attemptedconsidering only the privileges that the user has in that container

  16. Multitenant Architecture • Multitenant architecture can currently support up to 252 PDBs • A PDB feels and operates identically to a non-CDB • You cannot tell, from the viewpoint of a connected client, if you’re using a PDB or a non-CDB Database Link

  17. Oracle Multitenant Container Database (CDB) • CDB – • One root container (cdb$root) • Common User • can log into any container with appropriate privilege • Many user-created containers or pluggable databases (PDBs) • Target for OGG Capture/Apply

  18. Oracle Multitenant Database: How does it Affect Replication? • Each PDB is a different independent database • Data dictionary for a PDB contained within the PDB • Need to track multiple data dictionaries • PDBs in a CDB share the same redo stream • Need to filter out redo records for uninteresting PDBs • PDBs can be unplugged from one CDB and plugged into another CDB • Need to support this if replication was enabled.

  19. Program Agenda • Overview of Oracle GoldenGate • Introduction to Oracle 12c Multitenant Database (CDB) • Integrated Capture on CDB • CDB-specific Infrastructure in Oracle GoldenGate • Examples • Summary

  20. Integrated Capture:Support for Oracle 12c Multitenant Database

  21. Oracle 12c Multitenant Database Support • Available only in Integrated Capture Mode • One Extract can be configured to capture changes from multiple PDBs • Logmining server is only accessible in the root container (CDB$ROOT) • You can capture changes from multiple PDBs • Requires LOGMINING privilege • Must be a common user to attach to logmining server • C##GGADMIN • Need at least one Replicat per PDB to apply changes • All types of Replicat supported (Classic, Coordinated or Integrated)

  22. Program Agenda • Overview of Oracle GoldenGate • Introduction to Oracle 12c Multitenant Database (CDB) • Integrated Capture on CDB • CDB-specific Infrastructure in Oracle GoldenGate • Examples • Summary

  23. Oracle CDB Support • Login Command Syntax • Login command syntax remains same • DBLOGIN USERID user[@db] PASSWORD password • Default Login Datatabase • Default login database depends on configuration • Login to PDB that does not exist by common user when service login is used results to login to root DB

  24. Infrastructure for CDB Support • 3 Part Naming in GGSCI and Parameter file • Catalog.Schema.Table/Sequence etc • SourceCatalog to help with legacy parameter file • Strict checking by default • Native DDL Capture • No need to setup DDL triggers • Source transactions are unaffected • Allows support for more DDLs and quasi-DDLs that do not fire system triggers

  25. Oracle CDB Support • Extract • Requires root DB login by common user • Replicat • Requires PDB login • GGSCI • Requires root DB login by common user for root level operations • REGISTER/UNREGISTER • Requires PDB login for checkpoint and trace table operations • Metadata query such as list tables can be made by PDB login

  26. Oracle CDB Support • DEFGEN • Requires root DB login by common user for multiple PDB access • PDB login can only output table definition for the PDB

  27. 3-Part Naming • 3 Part Naming in GGSCI and Parameter file • Catalog.Schema.Table/Sequence etc • SourceCatalog to help with legacy parameter file • Strict checking by default • Native DDL Capture • No need to setup DDL triggers • Source DDL transactions are unaffected • Allows support for more DDLs and quasi-DDLs that do not fire system triggers

  28. Integrated DDL Support • Issues with Trigger-Based Support • Need to install DDL trigger • Quiesce required if trigger code needs change (bug fix, upgrade) • Impacts DDL transactions • Prone to user error (disable trigger by mistake) • Some DDL-like operations do not fire system trigger • Integrated DDL Capture • Asynchronous capture by logmining server • No impact on DDL transactions • No system quiesce needed for bug fix or upgrade • User cannot turn it off by mistake • Captures ALL metadata operations • Exposes API to GoldenGate Extract to query historical metadata

  29. Oracle GoldenGate: Integrated vs Trigger-based Metadata Capture Performance Flexible Reliable

  30. Integrated DDL Support • Pre-Requisites • Source database must be running RDBMS-11.2.0.4+ or higher • Source database compatibility must be 11.2.0.4+ • LogMining Server must have processed a data dictionary snapshot taken by RDBMS-11.2.0.4+

  31. Integrated DDL Support (How to Know) • Report file Snippet • 2013-09-03 09:24:28 INFO • OGG-02089 Source redo compatibility version is: 11.2.0.4. • 2013-09-03 09:24:37 INFO • OGG-02068 Integrated capture successfully attached to logmining server OGG$CAP_EXT2 using OGGCapture API. • 2013-09-03 09:24:37 INFO OGG-02086 Integrated Dictionary will be used.

  32. Integrated DDL Support (Transition) • Initial Configuration • RDBMS-11.2.0.3 + Bundled Patch (compatibility is 11.2.0.3) • OGG-11.2.1.0.10BP Integrated Capture in Downstream deployment • Upgrade Path • Upgrade downstream database to RDBMS-12c • Upgrade to OGG-12c • Capture will still run on trigger-based DDL support • Upgrade source database to 11.2.0.4 (+ compatibility set to 11.2.0.4) • As part of upgrade data dictionary snapshot will be taken • When Capture processes the redo, it will automatically detect a switch to integrated DDL capture mode is possible

  33. Program Agenda • Overview of Oracle GoldenGate • Introduction to Oracle 12c Multitenant Database (CDB) • Integrated Capture on CDB • CDB-specific Infrastructure in Oracle GoldenGate • Examples • Summary

  34. Setting Up Integrated Capture for CDB • Register with database with list of containers to capture • GGSCI> REGISTER EXTRACT ext1 DATABASE CONTAINER ('DW', 'CRM') • Add Extract • GGSCI> ADD EXTRACT ext1 INTEGRATED TRANLOG, BEGIN NOW • Parameter file needs to have three part names • SOURCECATALOG DW • Original table statements for HR database • SOURCECATALOG CRM • Original table statements for SALES database

  35. Specifying Containers in Initial Registration • Initial registration can have multiple containers specified • GGSCI> REGISTER EXTRACT ext1 DATABASE CONTAINER ('DW', 'CRM') • Containers must already exist and be open in CDB • Registration of future containers not allowed • Will take snapshot of data dictionary for both containers in the redo stream • Wildcard is not supported in container name

  36. What happens underneath a Registration • GGSCI> REGISTER EXTRACT ext1 DATABASE • A snapshot of data dictionary is taken in the redo logs of the source database • V$ARCHIVED_LOG.DICTIONARY_BEGIN = YES • This archived log contains the BEGIN of a data dictionary snapshot • FIRST_CHANGE# is important • V$ARCHIVED_LOG.DICTIONARY_END = YES • This archived log contains the END of a data dictionary snapshot • NEXT_CHANGE# is important • The data dictionary is contained within the SCN range • (first_change# of BEGIN log, next_change#-1 of END log)

  37. What happens underneath a Registration (CDB) • GGSCI> REGISTER EXTRACT ext1 DATABASE CONTAINERS (HR, SALES) • A snapshot of data dictionary is taken in the redo logs of the source database for both PDBs: HR and SALES • V$ARCHIVED_LOG.DICTIONARY_BEGIN = YES • This archived log contains the BEGIN of a data dictionary snapshot • FIRST_CHANGE# is important • V$ARCHIVED_LOG.DICTIONARY_END = YES • This archived log contains the END of a data dictionary snapshot • NEXT_CHANGE# is important • The data dictionary for both PDBs HR and SALES is contained within the SCN range • (first_change# of BEGIN log, next_change#-1 of END log)

  38. SourceCatalog • Short hand for adding a default Catalog name to all rules that follow • Only applies to rules without three part names • The most recent SourceCatalog acts on the rule set • DML • DDL

  39. Scenario #1: CDB to CDB Replication Replicat-DW LogMining Server in cdb$root Integrated Capture Replicat-CRM GoldenGate Trail Files Database Redo Logs

  40. Scenario #1: Integrated Capture on CDB RDBMS I/O Used for Fetch OCI API GoldenGate I/O LogMining Server in cdb$root Integrated Capture LCR + Meta Data Trail Files Database Redo Logs Checkpoint/BR Files

  41. Scenario #1: Integrated Capture on CDB • DDLINCLUDE ALL (Applies to all PDBs) • SOURCECATALOG DW • TABLE CUSTOMER.* * • SEQUENCE CUSTOMER.* • TABLEEXCLUDE CUSTOMER.TEST* • SOURCECATALOG CRM • TABLE CLIENT.* • SEQUENCE CLIENT.* • TABLEEXCLUDE CLIENT.TEST* Resolved for DW database Resolved for CRM database

  42. Scenario #1: Integrated Capture on CDB • DDLINCLUDE ALL (Applies to all PDBs) • SOURCECATALOG DW • TABLE CUSTOMER.* * • SEQUENCE CUSTOMER.* • TABLEEXCLUDE CUSTOMER.TEST* • SOURCECATALOG CRM • TABLE CLIENT.* • SEQUENCE CLIENT.* • TABLEEXCLUDE CLIENT.TEST* • TABLE DW.TEST.* (Three Part Name, Resolved fully) Resolved for DW database Resolved for CRM database

  43. Scenario #2: Adding a new PDB to Capture Set RDBMS I/O Used for Fetch OCI API GoldenGate I/O LogMining Server in cdb$root Integrated Capture LCR + Meta Data Trail Files Database Redo Logs Checkpoint/BR Files

  44. Scenario #2: Adding a new PDB to Capture Set • Stop Capture • GGSCI> STOP EXTRACT ext1 • Make sure SCHEMATRANDATA is on for the schemas of interest • GGSCI> ADD SCHEMATRANDATA ERP.* • Register interest in the PDB • GGSCI> REGISTER EXTRACT ext1 ADD CONTAINER (ERP); • Add rules in the parameter file • SOURCECATALOG ERP • TABLE *.* • Start Capture • GGSCI> START EXTRACT ext1

  45. Adding Containers to Existing Registration • You can register additional containers with the logmining server • GGSCI> REGISTER EXTRACT ext1 DATABASE ADD CONTAINER ('ERP') • Container ERP registered at SCN 13456172 • Extract needs to be stopped • Changes done on or after the registration SCN will be captured • Need to update parameter file to container specific rules • Multiple containers can be added with a single command • Wildcard is not supported in container name

  46. Scenario #3: Dropping a PDB from Capture Set RDBMS I/O Used for Fetch OCI API GoldenGate I/O LogMining Server in cdb$root Integrated Capture LCR + Meta Data Trail Files Database Redo Logs Checkpoint/BR Files

  47. Scenario #3: Dropping a PDB from Capture Set • Stop Capture • GGSCI> STOP EXTRACT ext1 • Unregister PDB from the logmining server • GGSCI> REGISTER EXTRACT ext1 DROP CONTAINER ('ERP'); • Start Capture • GGSCI> START EXTRACT ext1 • Capture will stop after applying the DROP CONTAINER operation in its metadata • Remove rules in the parameter file (not really needed for correctness) • SOURCECATALOG ERP • TABLE *.* • Need to restart Capture to commit the DROP CONTAINER operation in the logmining server’s metadata

  48. Scenario #3: Dropping a PDB from Capture Set • Report file snippet when Extract goes down • 2013-05-02 17:11:48 INFO • OGG-01631 BOUNDED RECOVERY: NEW VALID BR CHECKPOINT: • /oracle/ogg/BR/EXT1/CP.EXT1.000000013. • REGISTER...DROP CONTAINER... operation finished. • Extract is going down. • Please restart for the new PDB filtering to take place • Cannotissue another DROP container if one is in progress • GGSCI> register extract ext1 drop container ('CRM') • ERROR: Drop container for EXTRACT EXT1 is already in progress. • Please try later

  49. Scenario #3: Unplugging a PDB (Why does Capture Stop?) • Checkpoint and BR metadata is for the whole redo stream • Not container specific • Initial request for REGISTER is logged in Checkpoint file • Note Extract must be down when you issue REGISTER DROP • Extract starts (#1) • Notices DROP in checkpoint file • Waits for the next commit from another PDB after current checkpoint • Deletes from BR any data related to dropped container • Forces checkpoint and BR • Stops • Extract starts (#2) • Tells logmining server to remove dropped container

More Related