You most probably don't need an RMAN catalog database

1. You most probably don't need anRMAN catalog database Introduction to the whitepaper Yury Velikanov & Community Sunday, Sep 22, 2:15 PM - 3:15 PM Moscone West - 3014 Session ID# UGF9741 #OOW13 @yvelik

2. About Yury Google:Yury Oracle [phone|email] Twitter, LinkedIn, Blog, Slideshare, YouTube Oracle ACE Director Oracle Certified Master (9i/10g/11g + 15 OCP certificates) Oracle DBA 15+ years(42+ months working for Pythian) 10 Problems with your RMAN backup script (author) 12 000+ views on SlideShare.net

3. About Yury Google:Yury Oracle 10 Problems with your RMAN backup script (author) 12 000+ views on SlideShare.net

4. About Pythian • Recognized Leader: • Global industry-leader in remote database administration services and consulting for Oracle, Oracle Applications, MySQL and Microsoft SQL Server • Work with over 250 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments • Expertise: • Pythian’s data experts are the elite in their field. We have the highest concentration of Oracle ACEs on staff – 10 including 3 ACE Directors – and 2 Microsoft MVPs. • Pythian holds 7 Specializations under Oracle Platinum Partner program, including Oracle Exadata, Oracle GoldenGate & Oracle RAC • Global Reach & Scalability: • Around the clock global remote support for DBA and consulting, systems administration, special projects or emergency response

6. Community involvement

7. Agenda • Introduction • Why RMAN catalog is an overhead? / COST • Cases when catalog database is a MUST • Benefits of using RMAN catalog / VALUE • Catalog use-cases • Catalog & MML integration • Catalog & DR • Catalog & FS • Practical hints for catalog usage

8. CTL vs DB

9. DB Catalogdoesn’t provide moreHISTORY!

10. Introduction – Why? • Many DBAs use RMAN catalog because • It was a default option in 8.0 • It is recommended by Oracle • It was created before them • Many DBAs don’t • Use most of the catalog benefits • know how to assess costinvolved • know how to justify catalog existence @dbareactions

11. COST

12. Complexity

13. RMAN catalog is an overhead COST • IMPLEMENTATION COST • MAITENANCE & RUNNING COSTS • DEPENDENCIES • DOWNTIME • AVAILABILITY • TUNING • BACKUP • COST per database • More databases - less cost • Cost vs Risk

14. A MUST

15. RMAN catalog database is a MUST • Disaster Recovery • Data Guard (standby database) • Backups from both DR sides • Shared backups media

16. RMAN catalog database is a MUST • Control File size is way to big • control_file_record_keep_time • Manage retention (if applicable) • Restore alternative MML handle

17. RMAN catalog database is a MUST • KEEP • Keep a backup for longer than current retention policy allows • Long term backups need a special care and should be treated as archiving • MML retention should be adjusted as necessary • Other tape pool / file system than regular backups • oracle database software & operational system • There are alternative ways (file system) • RESTORE PREVIEW + OS copy • BACKUP + UNCATALOG

18. VALUE

19. RMAN catalog Benefits / VALUE • Additional bird level view - control level • Many databases • Small DBA team • Scheduling issues • Manager’s view

20. RMAN catalog Benefits / VALUE • Volumes& Throughput monitoring • Day to day monitoring • Report deviations (archived logs) • Troubleshooting data • Tuning (compression, IO vs CPU vs TIME) • Chargeback

21. Capacity planning / VALUE • Challenge(centralized NFS) • Used for many databases backups • Retention policy varies • Incremental backups + Weekly cycles • RAC multiple backups from different nodes • Solution – Catalog DB • Centralized data for all databases • Regular data snaps • Custom data analysis tables

22. RMAN catalog Benefits / VALUE • MML & Catalog DB • Tends to have longer retention policy • Often retention policy isn’t managed by RMAN • Simplifies and unifies controlfile recovery • There are alternatives, e.g. MML handle from log files

23. RMAN catalog Benefits / VALUE • RMAN scripts Pros • Standard way to backup • Centralized control / easer change Cons • Dependency from RMAN catalog • Limited syntax • About Stored Scripts: Any command that is legal within a RUN command is permitted in the stored script. • !shell rules!

24. VALUE • Additional high-level view of backups • Volumes & Throughput monitoring • Information for Capacity planning • Simplifies controlfile recovery (MML) • Repository for generic RMAN scripts Catalog Doesn’t • Keep longer history • Replace control file backups

25. CATALOG USE-CASES MML, DR, FS

26. Catalog & MML integration • Control File Auto recovery (challenge) • How it works • CONFIGURE CONTROLFILE AUTOBACKUP ON • standard handle name (%F format) c + {database ID} + {date} + {XX} • XX - hexadecimal incremental from 00 to FF • Restore • Restore freshest controlfile • RMAN Tries FF, FE, ….. 01, 00 • MML 1 round trip 2-5 minutes • 240 roundtrips = 8-20 hours maxseq and maxdays

27. Catalog & MML integration • Alternative control file restore options • MML REPOSITORY • RMAN LOG • HANDLE name • RMAN: “ENV=” or “format” • Catalog addresses • Difficulties in MML & DBA communications • Absence of MML access • Standard way to restore (easier)

28. Catalog, MML& RETENTION RMAN rarely used for retention management • Typically there are manyMML clients • File systems • MS Exchange / Lotus Domino • Other databases • There are poolsto send backups to • Weekly • Monthly • Custom There is a synchronization challenge

29. Catalog & Disaster Recovery • Diskbased backups are local • Accessible from one site only • Tape based backups are global • Accessible from both sites • Review the SITE_KEY column in RMAN catalog views • KEY: CATALOG disk based backups on other site “Backup and Recovery Reference 11g R2” • “RMAN Backups in a Data Guard Environment”

30. Catalog &File System => MML • Simple unless …. • Relies on file system to tape backups • Strong recommendation to use MML integration • Missing backups • Too many copies for SOME backup sets • If you use • Don’t use DELETE OBSOLETE • RESTORE … PREVIEW • Manual synchronization

31. PRACTICAL HINTS THE CATALOG USAGE

32. Practical hints for catalog usage • DBIDmust be different for all databases • Do not separate Development & Production • To use or not to use RMAN catalog stored scripts? • Use resync catalog • RMAN setup for catalogDB failures • Introduce two catalog DBs to ensureHA

33. CONCLUSIONS Whitepaper!

34. Catalog makes it simple • RMAN Catalog is an overhead • You may manage without it • Howeveran RMAN Catalog • simplifiesbackup management • addsadditional benefits • reduces risks • there are other special cases • Cost is opposite proportion to DB count • Consider leveraging other catalog DB benefits

35. If you don’t use catalog DB • Make sure that • control_file_record_keep_time is greater than maximum retention period you may use • You have an easy access to archive of RMAN LOG files • You have knowledge & access to MML server • at least you have a best friend in tapes management team • Assess catalog DB benefits on regular basis

36. CTL vs DB

37. Session ID# UGF9741 #OOW13 @yvelik Whitepaper! Google: Oracle Yury Blog, Twitter, Linkedin, ACE … email, phone number

38. THANK YOU Google: Oracle Yury Whitepaper!