1 / 86

6-Node Active-Active Oracle GoldenGate

6-Node Active-Active Oracle GoldenGate. Experiences and Lessons Learned. Luke Davies. Why Pythian. Recognized Leader:

druce
Download Presentation

6-Node Active-Active 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. 6-Node Active-Active Oracle GoldenGate • Experiences and Lessons Learned • Luke Davies

  2. Why Pythian • Recognized Leader: • Global industry leader in data infrastructure managed services and consulting with expertise in Oracle, Oracle Applications, Microsoft SQL Server, MySQL, big data and systems administration • Work with over 200 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments • Expertise: • One of the world’s largest concentrations of dedicated, full-time DBA expertise. Employ 8 Oracle ACEs/ACE Directors • Hold 7 Specializations under Oracle Platinum Partner program, including Oracle Exadata, Oracle GoldenGate & Oracle RAC • Global Reach & Scalability: • 24/7/365 global remote support for DBA and consulting, systems administration, special projects or emergency response

  3. About Luke Davies • Team Technical Lead • Oracle DBA since V6 (1990) • BSc Microbiology • With Pythian since 2008 • Main Focus • Oracle Core • Oracle Performance Tuning

  4. 6-Node Active-Active Oracle GoldenGate • Set Up

  5. Set Up • Business requirements • High availability • Easily Scalable • Application releases with zero downtime • Database patches/upgrades with zero downtime • Real-time reporting database with no impact to Online users • Other application entry points with no impact to Online users • Original Solution based on • Oracle Standard Edition 10gR2 • GoldenGate V10.4

  6. Set Up

  7. Set Up

  8. 6-Node Active-Active Oracle GoldenGate • Experiences and Lessons Learned

  9. Topics • Active/Active considerations • Supplemental Logging • DDL Replication • Conflict Avoidance • Conflict Detection • Release Management • Data comparison • General considerations • Trail file deletion • Archive log deletion

  10. Supplemental Logging • Required at the database level (Minimum Level) • ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; • Minimal Supplemental Logging “logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes” • Identification key logging (PK, UK, FK) • Table Level • Database Level • Method • SQL e.g. ALTER TABLE <> ADD SUPPLEMENTAL LOG DATA … • GoldenGate e.g. ADD TRANDATA <Table Name> • Cost of Omission Changes may fail

  11. Supplemental Logging Drop SL for all named columns

  12. Supplemental Logging No SL In Target TBL4 in Target

  13. Supplemental Logging SL for all columns is dropped SL for ID is added (PK)

  14. Supplemental Logging NO SL In Target YET !!

  15. Supplemental Logging What if we keep running replication this way

  16. Supplemental Logging What if we keep running replication this way

  17. Supplemental Logging What if we keep running replication this way

  18. Supplemental Logging What if we keep running replication this way Missing Value!

  19. Supplemental Logging What if supplemental logging is enabled on target

  20. Supplemental Logging What if supplemental logging is enabled on target

  21. Supplemental Logging What if supplemental logging is enabled on target

  22. Supplemental Logging What if supplemental logging is enabled on target

  23. Supplemental Logging What if supplemental logging is enabled on DB level

  24. Supplemental Logging What if supplemental logging is enabled on DB level

  25. Supplemental Logging What if supplemental logging is enabled on DB level

  26. Supplemental Logging What if supplemental logging is enabled on DB level

  27. Supplemental Logging Supplemental logging is critical to healthy replication specially for tables with update/delete changes PK UK Existing object KEYCOLs Supplemental logging ALL New object

  28. Supplemental Logging PK UK Existing object KEYCOLs Supplemental logging ALL New object DDLOPTIONS & ADDTRANDATA & GETREPLICATES & REPORT To add SL to source DB To replicat add SL to target

  29. Supplemental Logging

  30. Supplemental Logging

  31. Supplemental Logging No SL operations

  32. Supplemental Logging No SL operations

  33. Supplemental Logging • Problem documented in MOS 1472420.1 “If your TRANSLOGOPTIONS EXCLUDEUSER specified in the Extract is the same as the EXTRACT USERID, the DDL to add supplemental logging is not captured and sent to the target.” • Our extract settings were • USERID ggadmin • TRANLOGOPTIONS EXCLUDEUSER ggadmin Extract USER ID and Replicat USER ID should be different

  34. Supplemental Logging

  35. Supplemental Logging GETREPLICATES does not work as it is expected

  36. Supplemental Logging ADDTRANDATA on source for new object. Monitor replicated object on target and add SL manually before any DML change

  37. Supplemental Logging SCHEMATRANDATA In GoldenGate 11.1 OGG-01783 Cannot verify existence of table function that is required to enable schema level supplemental logging, failed to find function DB Patches needed to support SCHEMATRANDATA in OGG (Oracle GoldenGate), OGG-01783 Cannot verify existence of table function that is required to enable schema [MOS 1426440.1] To use SCHEMATRANDATA, please apply the fix in bug 13794550 Only on some platforms

  38. Supplemental Logging Lessons Learned • Supplemental logging is critical for proper function • Different users needed for extract and replicat • Add trandata manually for replicated nodes • Need to monitor schema for tables without trandata • Use SCHEMATRANDATA if you can

  39. Topics • Active/Active considerations • Supplemental Logging • DDL Replication • Conflict Avoidance • Conflict Detection • Release Management • Data comparison • General considerations • Trail file deletion • Archive log deletion

  40. DDL Replication

  41. DDL Replication

  42. DDL Replication

  43. DDL Replication

  44. DDL Replication

  45. DDL Replication

  46. DDL Replication

  47. DDL Replication

  48. DDL Replication

  49. DDL Replication

  50. DDL Replication Missing Value!

More Related