Data Replication With Oracle GoldenGate - Home: …

1 BASEL BERN BRUGG D SSELDORF FRANKFURT FREIBURG GENEVA HAMBURG COPENHAGEN LAUSANNE MUNICH STUTTGART VIENNA ZURICHData Replication with Oracle GoldenGate Looking Behind The Scenes Robert Bialek Principal Consultant Partner Who Am I Principal Consultant, Partner and Trainer at Trivadis GmbH in Munich Focus: Oracle Database High Availability Database Architecture/Internals Backup/Recovery Troubleshooting/Performance Tuning Linux Administration Trainer for the following Trivadis courses Oracle Grid Infrastructure (O-GRINF) Oracle Real Application Cluster (O-RAC) Oracle data Guard (O-DG) 2 data Replication with Oracle GoldenGate - Looking Behind The Scenes Agenda 3 GoldenGate Architecture GoldenGate Topologies Integrated Extract/Replicat GoldenGate Licensing Messages data Replication with Oracle GoldenGate - Looking Behind The Scenes Introduction 4 data Replication with Oracle GoldenGate - Looking Behind The Scenes What is Oracle GoldenGate ?

2 Oracle strategic Middleware software product used for data extraction and Replication Use-cases: data /service high-availabillity, zero-downtime upgrades/migrations, easy fallback (minimize risk) Moves only committed transactions across TCP/IP network without Oracle Net Services In contrast to Oracle Supports asynchronous data transfer (DML, DDL) heterogeneous sources and targets (databases, operating systems) different extraction, propagation and apply methods different topologies, mappings, transformations, filtering, error handling, .. 5 data Replication with Oracle GoldenGate - Looking Behind The Scenes Why Oracle GoldenGate ? Oracle Streams is deprecated in the Oracle 12c version (but still supported!) According to the Oracle GoldenGate statement of direction Beginning with the version , the non-CDB architecture is also deprecated As of now, Streams is not supported with the new CDB architecture Oracle Streams continues to be supported but will not be enhanced to support any new database features in future releases.

3 This means that Streams will continue to support exactly what it supports today ( ) in future releases. It also means that any new database features, data types, or capabilities will not be supported by Streams. 6 data Replication with Oracle GoldenGate - Looking Behind The Scenes Oracle GoldenGate Architecture 7 data Replication with Oracle GoldenGate - Looking Behind The Scenes Oracle GoldenGate Architecture: Big Picture 8 data Replication with Oracle GoldenGate - Looking Behind The Scenes Source DB Target DB Primary (Regular) Extract Primary (Passive) Extract Secondary Extract data Pump Collector Replicat Local Trail Remote Trail Primary (Alias) Extract Optional Oracle GoldenGate Instance Oracle GoldenGate Instance Static Configuration Runtime Configuration Runtime Configuration Static Configuration Optional Optional Manager (VIP) Manager (VIP) MONITOR MONITOR Primary Extract Group.

4 Capture Primary Extract can run in either Classic Capture mode (all database versions) Integrated Capture mode (full support: +) Extracts and optionally filters/transforms the transactions from the source database Only for configured objects Many Extracts per source database can operate at the same time Only committed transactions (CSN) will be propagated to the target system Optionally written to a local trail (recommended) Without a local trail, the primary Extract send the data to the target system Not committed transactions will be discarded from memory or dirtmp directory 9 Primary (Regular) Extract Primary (Passive) Extract data Replication with Oracle GoldenGate - Looking Behind The Scenes Source DB Primary Extract Group.

5 Classic Capture Classic Capture mode Traditional mode, reads directly Oracle database redo logs Able to mine redo logs in ASM (two methods) All database versions supported with some restrictions. For example no support for: BASIC, OLTP, Exadata Hybrid Columnar compression PDML on RAC database CDB architecture! DDL, TDE support no out-of-the box support Not transparent to RAC configuration changes (#threads) 10 data Replication with Oracle GoldenGate - Looking Behind The Scenes Source DB Primary (Regular) Extract Secondary Extract data Pump Local Trail Redo Logs OS System Calls Primary Extract Group: Integrated Capture Integrated Capture mode Interacts with the database logmining server Required to capture data from a CDB architecture Fully transparent with RAC, TDE, ASM Full support for databases + Source database + does not require supplementary objects for DDL support Supports more complex data types than classic capture Integrated capture deployments Local or a downstream deployment The best choice for most deployments 11 data Replication with Oracle GoldenGate - Looking Behind The Scenes Source DB Primary (Regular)

6 Extract Secondary Extract data Pump Local Trail Redo Logs Logmaining Server LCR LCR Secondary Extract Group ( data Pump): Propagation Optional but recommended component Reads the local trail and propagates the data to the remote trail (via target collector process) data can be transferred as is (PASSTHROUGH) Optionally filters/transforms the transactions from the source database (NOPASSTHROUGH) Isolates the primary extract from TCP/IP timeouts The network transfer can be optionally encrypted Not necessary, in case the trail is already encrypted 12 data Replication with Oracle GoldenGate - Looking Behind The Scenes Encryption Secondary Extract data Pump Local Trail Replicat Process.

7 Apply The Replicat process can run in either Classic Replicat mode (nonintegrated) - all database versions Coordinated Replicat mode (nonintegrated) - all database versions Integrated Replicat mode database versions + The Replicat process reads the remote trail, reconstructs the SQL and applies them to the target database Optionally filters/transforms the data The application of the data can be optionally delayed (DEFERAPPLYINTERVAL parameter) 13 data Replication with Oracle GoldenGate - Looking Behind The Scenes Target DB Replicat Remote Trail Replicat Group: Classic/Coordinated Mode Classic Replicat Constructs SQL statements from the trails and applies the SQL through OCI Supported for all database versions Transactions are applied sequentially (single-threaded) Coordinated Replicat Transactions are applied in parallel (multi-threaded)

8 There is 1 coordinator plus many apply threads Supported for all database versions GoldenGate 12c required 14 data Replication with Oracle GoldenGate - Looking Behind The Scenes OCI SQL Target DB Replicat Remote Trail OCI SQL Target DB Remote Trail Coordinator Thread 1 Thread 2 Thread n Replicat Group: Integrated Mode Integrated Replicat Supported for target database versions + Constructs LCRs and transmits DMLs to the database inbound server Some changes ( DDL)

9 Are not supported directly by the inbound server In those cases the Replicat process disconnects from the inbound server and executes the transaction in direct apply mode through OCI with many transactions applied in direct apply mode it might be necessary to switch to nonintegrated Replicat 15 data Replication with Oracle GoldenGate - Looking Behind The Scenes LCR Target DB Replicat Remote Trail Database Inbound Server SQL Capture/Apply: CDB & Pluggable Databases Extract must operate in integrated mode Extract must connect to the CDB$ROOT container as a common user (c##) One Extract can capture from one or many PDBs to a single trail Replicat can connect and apply only to one PDB (one Apply/PDB, connect using service) Source DB CDB CDB$ROOT PDB1 PDB2 Target DB CDB CDB$ROOT PDB1 PDB2 Primary Extracts INTEGRATED data Pumps Collectors Replicats 16 data Replication with Oracle GoldenGate - Looking Behind The Scenes common_user_prefix ( ) Capture & Apply.

10 Different Combinations Possible Different combinations are possible Classic Extract with integrated or classic/coordinated Replicat Integrated Extract with integrated or classic/coordinated Replicat You can switch between the modes with some configuration changes 17 data Replication with Oracle GoldenGate - Looking Behind The Scenes Source DB Redo Logs Logmaining Server LCR LCR LCR Target DB Replicat(s) Database Inbound Server OS System Calls Primary Extract(s) OCI SQL Classic Integrated Integrated Classic Coordinated Oracle GoldenGate Topologies 18 data Replication with Oracle GoldenGate - Looking Behind The Scenes Oracle GoldenGate Topologies Active-Passive data Replication only in one direction Only one set of primary Extract, data Pump and Replicat groups Active/Standby Additional inactive primary Extract, data Pump and Replicat groups Switchover: Stop one set of process Start the inactive one 19 data Replication with Oracle GoldenGate - Looking Behind The Scenes Oracle GoldenGate Topologies Active-Active data Replication in both directions Two active sets of primary Extract, data Pump and Replicat groups with some restrictions.