6-Node Active-Active Oracle GoldenGate

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!