Understanding Oracle GoldenGate

+44 (0) 844 811 3600 [email protected] www.e-dba.com A RedStack Technology
Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Understanding Oracle GoldenGate BGOUG, June 2014 Sandanski

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 “The more I learn, the more I realize how much I don't know.” - Albert Einstein So Many Oracle Manuals, So Little Time – Iggy Fernandez blog

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Pictures VS Bullets

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Who Am I • (Sve)toslav Gyurov • Principal consultant at e-DBA • Working mainly on Exadata and migrations • GTD and 7 Ps (Proper Planning and Preparation Prevents Piss Poor Performance ) • I’m tall – 202cm (6 feet 717⁄32 inches) • Futurama and Friends big fan • I blog – http://sve.to and tweet - @sgyurov

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Customers Some of our Customers

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Introduction

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Introduction • GoldenGate Software founded in 1995 • Acquired by Oracle in September 2009 • Now part of the Oracle Fusion Middleware product family • Near real-time transactional data replication in a heterogeneous computing environment

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Introduction • Supported topologies: – Unidirectional (reporting or migration) – Bi-directional (active-passive or active-active) – Broadcast (data distribution) – Consolidation (DWH consolitation)

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Supported databases • Oracle Database 9.2i, 10g, 11g, 12c • TimesTen 7.0.5 • MySQL 4.1, 5.0 • IBM DB2 8.1, 8.2, 9.1, 9.5 • Microsoft SQL Server 2000, 2005, 2008 • Teradata V2R5, V2R6 • Sybase ASE 12.5.4, 15.0

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Architecture

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Oracle GoldenGate for Oracle Database

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 System requirements • Supported platforms • Memory and disk requirements • Network configuration • Database version • Supported data types and objects

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Supported version • Oracle GoldenGate 11g supports: – Oracle Database Oracle 10.2.0.4+, Oracle 11.1.0.6+, Oracle 11.2.0.1+ • Oracle GoldenGate 12c supports: – Oracle Database 11.1.0.6+, 11.2.0.1+, 12.1.0.1+ • Source and target GG must be same versions!

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Extract (capture) • Data pump (routing) • Replicat (delivery) • Trails or extract files • Checkpoints • Manager • Collector

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Extract – Capture mechanism of Oracle GoldenGate – Initial load or change synchronization – Extract captures DML and DDL operations – Only captures committed transactions – Writes to trail files

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Extract continued – Log-based – Filtering – Bounded Recovery – 4H – CACHEMGR – Classic capture (trigger based) – Integrated capture (11.2.0.4)

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configuration options

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Classic capture • Was the original capture method • Trigger based • Restricted support for the complex data types • Supports any database except multitenant container database • Redolog/archive log mining

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Integrated capture • Integrated within the database • Interacts directly with a database logmining server • Supports more data and storage types • Only mode that supports multitenant db • Support for Basic, OLTP, EHCC compression • Compatibility must be set to 11.2.0.4

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Data pump – Not Oracle Datapump – Just another extract process – Reads trail and send it over network (TCP/IP) – Transfer is always ASYNCHRONOUS – Compression and encryption are optional – pass-through mode

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Replicat – Runs on the target – Reads trail and reconstructs DML or DDL – Performs data filtering, mapping, and conversion – Apply can be delayed – COMMIT NOWAIT - starting v11.1.1.1 – Integrated and nonintegrated replicat (trigger based)

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Trail – Local and remote trail – Extract writes to trail – Data pump and Replicate reads from trail – Stored in ./dirdat/ by default – Names are automatically generated (lt) and (rt) – Unique six-digit sequence number from 000000 to 999999 – The data in this trail file is in commit order sequence!

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Manager – Controls Golden Gate – Runs on each system – Maintain port numbers – Performs trail management

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Checkpoint – Store current read or write position – Extract – checkpoint file – Replicate – checkpoint table

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Components • Collector – Communicates with remote Extract and Manger – Receive extracted db changes – Writes changes to the trail – Uses TCP/IP

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Supported Data Types • Data types supported by integrated and classic capture mode: – NUMBER – BINARY FLOAT – BINARY DOUBLE – CHAR – VARCHAR2 – LONG – NCHAR – NVARCHAR2 – RAW – LONG RAW – DATE – TIMESTAMP – CLOB – NCLOB – BLOB – SECUREFILE and BASICFILE

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Supported Data Types • The following data types are no supported: – REFs – ANYDATA – ANYDATASET – ANYTYPE – BFILE – MLSLABEL – ORDDICOM – TIMEZONE_ABBR – URITYPE – UROWID (classic capture)

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 RAC support • RAC is supported – Limited support with classic capture – Fully supported with integrated capture • Shared storage support: – DBFS – ACFS – OCFS

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 High Availability • Oracle GI integration – Oracle Grid Infrastructure Bundled Agent (starting 11.2.0.3) – Manually create agent script (prior 11.2.0.3) • Golden Gate AUTOSTART of groups – Upon start Manager will start Extract, Data Pump and Replicat

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Security • Oracle GoldenGate security options: – Trail File encryption (AES) – Password encryption (no clear-text passwords) – TCP/IP encryption (AES or Blowfish) – Trusted connection (low sec to high sec) – Command authentication

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configuration

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Installation • Environment: – OEL 6.4 – Oracle Database 11.2.0.4 – Oracle GoldenGate 12.1.2.0.0 – GoldenGate Integrated capture and apply! SQL> desc sve.orders; Name Null? Type ---------------------------- -------- ---------------------------- ORDER_ID NOT NULL NUMBER ORDER_DATE DATE CUSTOMER_NAME VARCHAR2(64)

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Installation • Latest version – 12.1.2 and just 326 MB! unzip -q 121200_fbo_ggs_Linux_x64_shiphome.zip cd fbo_ggs_Linux_x64_shiphome/Disk1/ ./runInstaller

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Installation

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Pre checks • Oracle GoldenGate database Complete Database Profile check script for Oracle DB (All Schemas) (Doc ID 1298562.1) • Oracle GoldenGate database Schema Profile check script for Oracle DB (Doc ID 1296168.1)

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Database requirements • Configure logging properties – Archivelog mode – Force logging – Minimal supplemental logging – Table-level logging (supplemental log groups) – Enable Flashback – 11.2.0.4 • ENABLE_GOLDENGATE_REPLICATION=true

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configure Database SQL> alter database add supplemental log data; SQL> alter database force logging; SQL> create tablespace ggs_data datafile '/oracle/oradata/orcl/orcl_ggs_data_01.dbf' size 128m autoextend on next 16m maxsize 4g; SQL> create user ggs_owner identified by Welcome1 default tablespace ggs_data temporary tablespace temp; SQL> grant connect, resource, dba to ggs_owner; SQL> alter system set enable_goldengate_replication=true; SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( - grantee => 'ggs_owner', privilege_type => 'capture', grant_select_privileges=> true, do_grants => TRUE);

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configure GoldenGate • Create extract param file vi vi $GG_HOME/dirprm/orcle1.prm EXTRACT ORCLE1 USERID ggs_owner PASSWORD Welcome1 LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT DDL INCLUDE MAPPED EXTTRAIL /oracle/product/12.1.2.GG/dirdat/lt TABLE sve.*;

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configure GoldenGate • Create pump param file using ggsci [oracle@oel2 12.1.2.GG]$ ./ggsci GGSCI (oel2) 2> edit params orclp1 EXTRACT ORCLP1 USERID ggs_owner PASSWORD Welcome1 RMTHOST my-remote-host.net, MGRPORT 7809 RMTTRAIL /oracle/product/12.1.2.GG/dirdat/rt PASSTHRU TABLE sve.*;

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configure GoldenGate • Create replicat param file [oracle@oel1 12.1.2.GG]$ ./ggsci GGSCI (oel1) 2> edit params orclr1 REPLICAT ORCLR1 DBOPTIONS INTEGRATEDPARAMS(parallelism 6) USERID ggs_owner PASSWORD Welcome1 ASSUMETARGETDEFS MAP sve.*, TARGET sve.*;

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configure GoldenGate • Add supplemental logging and register extract [oracle@oel2 12.1.2.GG]$ ./ggsci GGSCI (oel2) 1> DBLOGIN USERID ggs_owner password Welcome1 Successfully logged into database. GGSCI (oel2) 2> ADD TRANDATA sve.* Logging of supplemental redo data enabled for table SVE.ORDERS. TRANDATA for scheduling columns has been added on table 'SVE.ORDERS'. GGSCI (oel2) 3> register extract orcle1 database Extract ORCLE1 successfully registered with database at SCN 962593.

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Behind the scenes Thu Jun 12 11:22:07 2014 Logminer Bld: Build started ALTER SYSTEM SWITCH ALL LOGFILE start (orcl) Thu Jun 12 11:22:25 2014 Logminer Bld: Done 2014-06-12 11:22:43.735000 +01:00 GoldenGate CAPTURE CP01 for OGG$CAP_ORCLE1 with pid=30, OS id=4272 is in combined capture and apply mode. Capture OGG$CAP_ORCLE1 is handling 1 applies. -- capture is running in apply-state checkpoint mode. 2014-06-12 11:22:47.259000 +01:00 Starting persistent Logminer Session with sid = 1 for GoldenGate Capture OGG$CAP_ORCLE1

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Are we there yet ? • Checklist: – Database – Extract process – Data pump process – Replicat process • Start the processes and enjoy life!

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Start capture GGSCI (oel2) 22> start extract orcle1 Sending START request to MANAGER ... EXTRACT ORCLE1 starting GGSCI (oel2) 23> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ORCLE1 00:00:10 00:00:02 • Start Extract

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Trail files SQL> insert into orders (order_id, order_date, customer_name) values (1,to_date('2014-06-11', 'YYYY- MM-DD'), 'Sve'); SQL> commit; [oracle@oel2 12.1.2.GG]$ strings /opt/oracle/product/11.2.1.GG/dirdat/lt000000 | tail -6 SVE.ORDERS 2014-06-11:00:00:00 SveT AAAVVDAAEAAAACvAAA 9862636 3.18.926Z

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Oracle Database 12c

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Oracle Database 12c • Extract – capture from multiple pluggable dbs to single trail – Must operate in integrated capture mode – Must connect to root container • Replicat – Apply only to one pluggable db – Integrated delivery

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Configure GoldenGate cat > $GG_HOME/dirprm/orclp1.prm EXTRACT ORCLE1 USERID ggs_owner PASSWORD Welcome1 LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT DDL INCLUDE MAPPED EXTTRAIL /oracle/product/12.1.2.GG/dirdat/lt SOURCECATALOG pdb1 TABLE sve.*; SEQUENCE sve.seq*; SOURCECATALOG pdb2 TABLE scott.*; SEQUENCE scott*.seq*; • Extract in 12c

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Trail files, logdump and GoldenGate records

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Trail file FileHeader and TrailInfo MachineInfo DatabaseInfo Commited transaction ProducerInfo

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Logdump • Mainly used by Oracle Support • Swiss army knife of troubleshooting GoldenGate • Enables search, read and save data in a trail file • Identify a transaction for a given SCN in Oracle • Identify the before images of a row • Count the number of changes table

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 GoldenGate record Operation and time of record Object Before or after image Record data in HEX Length of record and RBA in trail Column information with data Record data in ASCII Header, transaction information

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Monitoring

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Monitoring • Command line – ggssi – info, status, lag, view report – Monitoring lag – Process report – Error log – Discard file

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Monitoring GGSCI (oel2) 12> info orcle1 EXTRACT ORCLE1 Last Started 2014-06-12 11:40 Status RUNNING Checkpoint Lag 00:00:09 (updated 00:00:08 ago) Process ID 4449 Log Read Checkpoint Oracle Integrated Redo Logs 2014-06-12 13:54:10 SCN 0.986714 (986714) GGSCI (oel2) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ORCLE1 00:00:10 00:00:02

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Monitoring • Management Pack for Oracle GoldenGate • Oracle GoldenGate Plug-in on OEM 12c Cloud Control

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Monitoring • SQL Developer – Bobby Curtis http://dbasolved.com/

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Best Practices

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 • Run it in your environment and find the best solution for you, but keep in mind: – Monitor the processes – Purge the logs – Keep enough archivelogs – Use integrated modes where possible

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Good news everyone

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 License • Of course it is licensed • And yes, both databases must be licensed • Processor license cost - $17500 • The Oracle GoldenGate product includes license cover for Active Data Guard.

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Statement of direction Oracle Streams continues to be supported but will not be enhanced to support any new database features in future releases. This means that Streams will continue to support exactly what it supports today (11.2) in future releases. It also means that any new database features, data types, or capabilities will not be supported by Streams. Customers who want to take advantage of the new data types and features will need to license Oracle GoldenGate.

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Bottom line • It worked for me! • Integrated capture/apply vs nonintegrated • Same version on source and target • Remember the 7Ps rule!

Company Oracle EMEA Database Partner of the Year • Oracle Partner of the Year 2010 & 2012 & 2013 • Oracle Engineered Systems Partner of the Year 2014 Thank you!

Understanding Oracle GoldenGate

Understanding Oracle GoldenGate

More Decks by Svetoslav Gyurov

Other Decks in Technology

Featured

Transcript