1 / 48

Oracle GoldenGate

Oracle GoldenGate. Jos van Lammeren. Jos.vanLammeren@LamkoDB.com. Agenda. Background GoldenGate Overview Setup Operations Troubleshooting Monitoring Challenges Q&A. Background. Using Oracle GoldenGate since 2010 At clients Oracle 9i, 10g near-zero downtime migrations to 11g RAC

wyman
Download Presentation

Oracle GoldenGate

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. Oracle GoldenGate Jos van Lammeren Jos.vanLammeren@LamkoDB.com www.LamkoDB.com www.MonitorMyDatabase.com

  2. Agenda • Background • GoldenGate • Overview • Setup • Operations • Troubleshooting • Monitoring • Challenges • Q&A www.LamkoDB.com www.MonitorMyDatabase.com

  3. Background • Using Oracle GoldenGate since 2010 • At clients • Oracle 9i, 10g near-zero downtime migrations to 11g RAC • Oracle 11g RAC setup for DR and EDW load • Upgrade OGG 10.4 to 11.2 for Oracle 11g clusters (no app downtime) • Oracle 9i, 10g, 11g EDW load • SQL Server 2008 to Oracle 11g EDW load • At LamkoDB • Oracle 10g, 11g, 12c, SQL Server 2008, 2012, MySQL tests with OGG 11g and 12c www.LamkoDB.com www.MonitorMyDatabase.com

  4. Overview • What GoldenGatedoes • Continuous data replication from one database to another • Source and target databases can be of different vendors/releases • Non-intrusive, low-impact, sub-second latency • Maintains transactional integrity - Resilient against interruptions and failures • Data transformation on the fly • Few or many tables in a schema • DDL replication if desired • Used for EDW feeds, DR, DB version upgrades, DB migrations • What GoldenGatedoes not • Is not a replacement for Data Guard www.LamkoDB.com www.MonitorMyDatabase.com

  5. Overview www.LamkoDB.com www.MonitorMyDatabase.com

  6. Overview • Use of replication www.LamkoDB.com www.MonitorMyDatabase.com

  7. Overview • Source Databases/Platforms supported www.LamkoDB.com www.MonitorMyDatabase.com

  8. Overview • Target Databases/Platforms supported www.LamkoDB.com www.MonitorMyDatabase.com

  9. Overview • Processes/Files involved • Classic Capture or Integrated Capture mode • NonintegratedReplicat or Integrated Replicat www.LamkoDB.com www.MonitorMyDatabase.com

  10. Overview • Observations • Not all data types are supported • Need data type translation in heterogeneous environments • Encryption of data in files and across network • Optional compression of data copied by pump process • Cryptic names for processes: 8 characters • Cryptic names of trail files e1xxxxxx (2 characters followed by incrementing number) • Administrator must like command line tool (similar to sqlplus) www.LamkoDB.com www.MonitorMyDatabase.com

  11. Setup • Installation GG software (source & target server) • Download software from Oracle site. Software to use is Oracle DBMS release specific. • On Linux/Unix create account that owns the software & add to dba group • Create profile to set $ORACLE_HOME, $PATH, etc. for the account • Unzip & untar software or use Oracle Universal Installer • Configure GLOBALS and mgr.prmfiles • Installation in DB • DB in archive log mode (at least source instance) • Turn off recyclebin (in source instance for DDL replication) • Put DB in supplemental logging mode (source instance) • Run OGG supplied installation scripts (source & target) www.LamkoDB.com www.MonitorMyDatabase.com

  12. Setup items to keep in mind • How many schemas/tables to replicate & how much data will be replicated? • Have tables the same structure in source and target database or do target tables have additional columns? • Do tables have PKs? • Are there referential constraints to deal with if not all tables are being replicated? • Delete Cascade constraints • When bi-directional replication • Sequences • Triggers firing on either database www.LamkoDB.com www.MonitorMyDatabase.com

  13. Replication Setup Example • Setup replication flow & initial load of data • Create/start Extract on source database • Create/start Pump process on source database • Do initial data load from source to target database • Create/start Replicat on target database www.LamkoDB.com www.MonitorMyDatabase.com

  14. Setup • Extract • Extract process E1ORA1 • E1ORA1 extracts data from MMDBORA1 (Oracle) to local trail files in directory/gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1 www.LamkoDB.com www.MonitorMyDatabase.com

  15. Setup EXTRACT E01ORA1.obey ADD EXTRACT E01ORA1, TRANLOG, BEGIN NOW, params /gg/product/11.2.1_11g/ggs/dirprm/MMDBORA1/E01ORA1.prm ADD EXTTRAIL /gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1, EXTRACT E01ORA1 E01ORA1.prm EXTRACT E01ORA1 . . . USERID ggate, PASSWORD abc EXTTRAIL /gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1 megabytes 100 . . . TABLE CHINOOK.*; start E01ORA1 www.LamkoDB.com www.MonitorMyDatabase.com

  16. Setup • Pump • Pump process P1ORA1 copies local trail file contents to remote trail files on another server in directory C:\gg\dirdat\MMDBORA1\p1 www.LamkoDB.com www.MonitorMyDatabase.com

  17. Setup PUMP P01ORA1.obey ADD EXTRACT P01ORA1, EXTTRAILSOURCE /gg/product/11.2.1_11g/ggs/dirdat/MMDBORA1/e1, params /gg/product/11.2.1_11g/ggs/dirprm/MMDBORA1/P01ORA1.prm ADD RMTTRAIL C:\gg\dirdat\MMDBORA1\p1, EXTRACT P01ORA1, MEGABYTES 100 P01ORA1.prm EXTRACT P01ORA1 RMTHOST 192.168.21.94, MGRPORT 7809 RMTTRAIL C:\gg\dirdat\MMDBORA1\p1, megabytes 100 PASSTHRU TABLE CHINOOK.*; start P01ORA1 www.LamkoDB.com www.MonitorMyDatabase.com

  18. Setup • Initial load of data • Export/Import • Data pump • RMAN • Load data over a DB link • bcp • OGG utilities • many more … www.LamkoDB.com www.MonitorMyDatabase.com

  19. Setup • Replicat • Replicatprocess R01ORA1(SQL Server 2012) reads the remote trail file contents and applies rows to the database www.LamkoDB.com www.MonitorMyDatabase.com

  20. Setup REPLICAT R01ORA1.obey DBLOGIN SOURCEDB GGATE, USERID ggate, PASSWORD abcadd replicat R01ORA1, exttrail C:\gg\dirdat\MMDBORA1\p1 params C:\gg\dirprm\CHINOOK\R01ORA1.prm R01ORA1.prm REPLICAT R01ORA1 SOURCEDB GGATE, USERID ggate, PASSWORD abc . . . MAP CHINOOK.ALBUM, TARGET dbo.ALBUM; MAP CHINOOK.ARTIST, TARGET dbo.ARTIST; MAP CHINOOK.CUSTOMER, TARGET dbo.CUSTOMER; MAP CHINOOK.TRACK, TARGET dbo.TRACK; start R01ORA1 www.LamkoDB.com www.MonitorMyDatabase.com

  21. Setup • Replication flow complete www.LamkoDB.com www.MonitorMyDatabase.com

  22. Operations • ggsci command line interface • info all • info * or info E*, etc • info E01ORA1 • info E01ORA1 detail • start E01ORA1 • stop E01ORA1 • stats E01ORA1 • send E01ORA1 status • many more … www.LamkoDB.com www.MonitorMyDatabase.com

  23. Operations ggsci commands GGSCI (MMDBLNXDB01) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E01ORA1 00:00:00 00:00:04 EXTRACT RUNNING E02ORA1 00:00:00 00:00:04 EXTRACT RUNNING P01ORA1 00:00:00 00:00:09 EXTRACT RUNNING P02ORA1 00:00:00 00:00:00 REPLICAT RUNNING R01SQL1 00:00:00 00:00:04 GGSCI (MMDBLNXDB01) 2> info E01ORA1 EXTRACT E01ORA1 Last Started 2014-02-20 15:49 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint Oracle Redo Logs 2014-03-13 21:52:56 Seqno 3726, RBA 25596928 SCN 0.127734271 (127734271) www.LamkoDB.com www.MonitorMyDatabase.com

  24. Operational Issues • Network issues causing pump to crash • Usually restart pump • Use AUTORESTART in mgr.prm www.LamkoDB.com www.MonitorMyDatabase.com

  25. Operational Issues • Network issues causing pump to crash • Usually restart pump • Use AUTORESTART in mgr.prm • Server reboot and processes not starting • Use AUTOSTART in mgr.prm www.LamkoDB.com www.MonitorMyDatabase.com

  26. Operational Issues • Network issues causing pump to crash • Usually restart pump • Use AUTORESTART in mgr.prm • Server reboot and processes not starting • Use AUTOSTART in mgr.prm • Extract performance bad • Use dedicated volumes where redo/archived logs are stored • Have separate volumes for local trail files • Use multiple extracts www.LamkoDB.com www.MonitorMyDatabase.com

  27. Operational Issues • Network issues causing pump to crash • Usually restart pump • Use AUTORESTART in mgr.prm • Server reboot and processes not starting • Use AUTOSTART in mgr.prm • Extract performance bad • Use dedicated volumes where redo/archived logs are stored • Have separate volumes for local trail files • Use multiple extracts • Pump performance bad • Compress data when sending data across • Have separate volumes for local/remote trail files • Use multiple extracts/pumps www.LamkoDB.com www.MonitorMyDatabase.com

  28. Operational Issues • Replicat performance bad • Check for tables with missing PKs • Use batchsql in prm file of replicat • Have separate volumes for remote trail files • Use multiple replicats www.LamkoDB.com www.MonitorMyDatabase.com

  29. Operational Issues • Replicat performance bad • Check for tables with missing PKs • Use batchsql in prm file of replicat • Have separate volumes for remote trail files • Use multiple replicats • Disk full for local and/or remote trail files • Occasionally need to setup replication again, with or without initial load • Monitor disk space usage by storage group or sysadmins • Have separate volumes for local and remote trail files • Use PURGEOLDEXTRACTS in mgr.prm www.LamkoDB.com www.MonitorMyDatabase.com

  30. Operational Issues • Crashing replicats when tables added/changed • Add/modify tables on target database, possibly update data definition file, and restart replicat • Make extract process stop when it notices DDL www.LamkoDB.com www.MonitorMyDatabase.com

  31. Operational Issues • Crashing replicats when tables added/changed • Add/modify tables on target database, possibly update data definition file, and restart replicat • Make extract process stop when it notices DDL • Extract process does not stop • Commit open transactions in DB • Kill session having open transaction • Force stop of Extract process if not many archived logs to re-read at next Extract startup www.LamkoDB.com www.MonitorMyDatabase.com

  32. Operational Issues • Crashing replicats when tables added/changed • Add/modify tables on target database, possibly update data definition file, and restart replicat • Make extract process stop when it notices DDL • Extract process does not stop • Commit open transactions in DB • Kill session having open transaction • Force stop of Extract process if not many archived logs to re-read at next Extract startup • Crashing replicats because of manual data changes in target database • Do more manual changes to fix the data • Possibly reload affected tables • Educate users www.LamkoDB.com www.MonitorMyDatabase.com

  33. Troubleshooting • Where to look for information • Report file of process (view report <process>) • Discard file • OGG Error log • Oracle alert log / SQL Server ERRORLOG • Compare source and target data after fixing issue • Veridata • select data from source table MINUS select data from target table • Oracle DBMS_COMPARISON • Other tools (TOAD, …) www.LamkoDB.com www.MonitorMyDatabase.com

  34. Monitoring • Setup Heartbeat in replication flow • Document ID: 1299679.1 OGG_Best_Practice_-_heartbeat_table_using_DBMS_SCHEDULER-_V11_0_ID1299679.1.pdf • Update heartbeat record in source database (every minute) • Extract picks up record in source database, adds information and write to local trail file • Pump picks up record, adds information and write to remote trail file • Replicat picks up record, adds information and writes to target database (as an insert) www.LamkoDB.com www.MonitorMyDatabase.com

  35. Monitoring • Monitor using Oracle tool (Director) • Need to install Java agent in every GG instance & maintain bunch of servers to keep tool running (Weblogic, …) • (Remote) Monitor using own developed tools • Monitor status of processes • Monitor number of rows replicated • Report on Heartbeat data • Queries against heartbeat table in target database • Graphs generated based on data in heartbeat table (lag times) • ggsci-like GG Dashboard for all GG instances monitored www.LamkoDB.com www.MonitorMyDatabase.com

  36. Monitoring • OGG Management Pack www.LamkoDB.com www.MonitorMyDatabase.com

  37. Monitoring • LamkoDB Monitoring www.LamkoDB.com www.MonitorMyDatabase.com

  38. Monitoring • Historical stats for Extract process www.LamkoDB.com www.MonitorMyDatabase.com

  39. Monitoring • Lag times for Extract/Pump/Replicat www.LamkoDB.com www.MonitorMyDatabase.com

  40. Monitoring • GG Dashboard www.LamkoDB.com www.MonitorMyDatabase.com

  41. Monitoring • GG process detail information www.LamkoDB.com www.MonitorMyDatabase.com

  42. Monitoring • GG process relationships www.LamkoDB.com www.MonitorMyDatabase.com

  43. Challenges • Who supports GoldenGate? • DBA • Middleware group www.LamkoDB.com www.MonitorMyDatabase.com

  44. Challenges • Who supports GoldenGate? • DBA • Middleware group • What to do about table structure changes • software releases need to be evaluated/tested/coordinated www.LamkoDB.com www.MonitorMyDatabase.com

  45. Challenges • Who supports GoldenGate? • DBA • Middleware group • What to do about table structure changes • software releases need to be evaluated/tested/coordinated • What do developers, support, clients know about OGG? • Educate personnel www.LamkoDB.com www.MonitorMyDatabase.com

  46. Challenges • Who supports GoldenGate? • DBA • Middleware group • What to do about table structure changes • software releases need to be evaluated/tested/coordinated • What do developers, support, clients know about OGG? • Educate personnel • Monitor what is running and what is broken • Have proper monitoring in place with alerting www.LamkoDB.com www.MonitorMyDatabase.com

  47. Challenges • Who supports GoldenGate? • DBA • Middleware group • What to do about table structure changes • software releases need to be evaluated/tested/coordinated • What do developers, support, clients know about OGG? • Educate personnel • Monitor what is running and what is broken • Have proper monitoring in place with alerting • Keeping track of new, existing and changing replication flows. Number of processes quickly becomes a spaghetti bowl. • Have automated tool to show relationships www.LamkoDB.com www.MonitorMyDatabase.com

  48. Q&A • Questions? Jos van Lammeren Jos.vanLammeren@LamkoDB.com www.LamkoDB.com www.MonitorMyDatabase.com

More Related