Oracle DBA Best Practices

1. Oracle DBA Best Practices Dennis Williams Senior Database Administrator Lifetouch, Inc.

2. About Me • TCOUG member since 1990 • 8 years only DBA at Lifetouch, Inc. • Lawson Software Database Research Engineer • Consultant - Fingerhut • Control Data • Contact me at dwilliams@lifetouch.com

3. Audience • Those considering DBA career • DBAs who want to improve • Those forced to tolerate DBAs

4. There’s no asset more important than a company’s data. You can’t give that responsibility to someone who doesn’t know how to handle it. Malcom Fields, CIO Hon Industries

5. Agenda • Time - Until lunch is ready • Left-brain portion - tech. Issues • Right-brain portion - softer issues • Database audit form

6. 75,610 DBAs • U.S. Bureau of Labor Statistics in 2003 began tracking the IT workforce. • 6.46% unemployed • There are 797,445 Software Engineers (5.3% unemployed) • DBA supports about 10 developers. • 66% DBA growth by 2010

7. Where do DBAs come from? • Schools don’t offer a degree. • Developer DB developer • System Administrator

8. DBA is a role • Like a policeman or teacher • Unstated responsibilities, expectations • Most people don’t know what a DBA does.

9. DBA time breakdown • 22% Fire Fighting • 33% Monitoring • 12% Perf. Tuning / Change Mgt. • 33% Development Support • 0% Planning for growth • 0% Backup & Recovery Planning • 0% Learning Koopman

10. Assignment • Keep a personal time log - 1 week • Learn your time distribution • Target improvements • Study time management

11. Levels of Oracle DBA

12. OCP • Misconception: you are an expert • Multiple choice, so simple questions • Broad, not deep • Career benefit - can’t hurt • Can help you go deeper in Oracle

13. Types of DBAs: Development DBA • May carry developer title • Develop code ( PL/SQL ) • “Hunter” personality (find/fix/forget) • Best practices: Code Complete by McConnell

14. Production Support DBA • Keep it running • “shepherd / farmer” personality • Where best practices really make a difference • The hero isn’t the DBA that battles problems through the weekend, but the one that avoided the problem

15. Application DBA • Supports a business critical app. like Oracle Financials, SAP, Peoplesoft, Lawson • Production support, some dev. • Key skill: understands how the app. Interacts with Oracle • Can’t self-study • Caution: Vendor doesn’t operate databases in production

16. Consultant DBA • Fastest way to get expertise • Varied work opportunities • Understand economic cycles

17. Backup is Job #1 • Ensuring data is never lost • Don’t trust an untested backup configuration • Be ready to perform any type of recovery at any time • Communicate vulnerabilities, recovery times to management • Archivelog mode by default • Cold or Hot backups, which best fits your environment

18. Export: the DBAs friend • Enhance recoverability • Easy to recover a single table, avoid full recovery • Checks block corruption • Teach your developers to request special exports • Validate, no errors, Unix strings command, last line should be EXIT

19. Security is Job #2 • DBA is responsible for making data available to authorized users • Keeping data inaccessible to unauthorized • Role: policy, implementation • Know security options, implications • Know how application affects security, data sensitivity

20. Making it work Dev to Prod • ITIL • 3 environments - test/stage/prod • Clone DB from backup • Developers provide scripts to change production • Simple application login test • Always have a backout plan • Is this change worth the possible consequences?

21. Oracle Performance Tuning - Types • SQL Statements - explain plan, tkprof, 10053 events • Application disasters - 10046 trace, Cary Millsap (TCOUG Hotsos seminar) • Proactive tuning and hanging - STATSPACK, utlestat/utlbstat

22. Server statistics • If you have a performance problem, either your database is working too hard, or it’s not being allowed to work. - Jonathan Lewis • You must know what your server is doing. • You must know the O.S. • Unix Admin. - your best friend

23. Bind variables • Ensure developers use bind variables • Audit v$sql in test to verify • In Java, PreparedStatement • In .net, CommandText, CreateParameter, Append • Connection pooling

24. Not your Dad’s Oracle tuning • Rules of thumb, ratios to mathematical foundations • Counts to measured times in microseconds • Aggregate by instance to process details • V$ tables are either too summarized or point-in-time

25. Goal of tuning • What matters to the business user • Good chance to sell value • End-to-end analysis • DBA must prove innocence

26. Monitoring • There is nothing so useless as doing efficiently that which shouldn’t be done at all - Peter F. Drucker • Use LMT, uniform extents, vs. rebuild • Use autoextend vs. monitoring • Email exceptional conditions

27. Oracle Versions • Be not the first by whom the new are tried, nor yet the last to lay the old aside - Alexander Pope • Good choice saves downtime • Support by appl, O.S. • Rule of 4’s - 7.3.4, 8.1.7.4, 9.2.0.4 • Timing - can I skip versions? • Appl. quality requirements • What are others saying? Metalink • Only change one major component

28. Oracle Patches • Two philosophies: • Only when bug arises • Immediately (security) • Vendor testing hierarchy: • Version • Patch set • Individual patch

29. Oracle Licensing • Install defaults to all - know what costs extra • Consider Standard Version • Understand the organization budget mechanisms • Knowledgeable if not authorized

30. Troubleshooting • Never worked? Check installation • Worked earlier, doesn’t work now? What changed? • Intermittent? (worst kind) How does it vary? With time, system load, hardware? From Oracle Networking 101 by Marlene Theriault

31. Documentation • Key: What is essential? • Don’t document what can easily be figured out • Keeping up-to-date is the crucial aspect. • Obsolete documentation is treacherous • Self-documenting where possible

32. Data Modeling • O-O Developer: Database is just a means to persist classes • DBA: Good data modeling can ensure the data is usable across the enterprise • Data architect is a DBA career path • Participate in initial design where your leverage is large • Learn data modeling software

33. Tech. Learning List • Application interface methods - Microsoft, Java, OCBC • Server tuning • Storage methods and options, strengths and weaknesses • Effective communication proceeds from shared knowledge

34. Self-Study • When you can’t get experience • Be aware of the limits • Well-defined area? (books?) • Can earn credentials? • Area where you can gain significant knowledge with a reasonable effort? • Average experience of competitors is short • Enjoy after doing 10 years?

35. Know your learning style • Left-brain vs. right-brain • Four primary learning styles: • Visual (diagrams) • Auditory (reading) • Tactile (hands-on) • Kinesthetic (body) • Your manager’s learning style

36. People Skills • Few people besides a DBA understand what a DBA does • This makes it hard to communicate your value to the organization • People don’t mind paying for what they value, but resist paying for what they don’t consider valuable • If your manager is a DBA, less people skills are required, but this is career-limiting

37. Respect • Do you respect someone who doesn’t respect you? • The root of much conflict is a mutual lack of respect • Smartness paradox • We are the hero of our own movie, we justify ourselves, never the bad guy • Really care about the people you work with

38. How to avoid problems • Many bad reactions stem from a lack of warning • Emotions play a large role in decision-making • Work hard to warn people ahead of time of what will be coming • Don’t know why? Assume stupidity • A DBA appreciates a heads-up, why shouldn’t others?

39. When you’re wrong, admit it, quickly • Any man worth his salt will stick up for what he believes right, but it takes a slightly better man to acknowledge instantly and without reservation that he is in error - Andrew Jackson

40. Stress • Feeling no control • Constant interruptions • DBA syndrome - responsible for everything, control nothing, nobody understands • You hear yourself saying “nobody appreciates me” • Antidote: study stress control, exercise • visit clients, future changes

41. That Problem Person • Sandwich / Oreo approach by Rachel Carmichael • 1-on-1 outside the office • Begin with a complement • Make statements addressing the problem, starting with “I” - “I feel”, “I think”, “what I see is”. Listen • Wrap with team-building “we can work together on this”, “I need your help” • End with a nice complement

42. Simple Explanations • The most valuable people are those who can understand complex issues and explain them simply to others • Makes other feel good about themselves • Albert Einstein, Steve Hawking, Carl Sagan, Richard Feynman

43. Database Audit Form • Periodically audit each database • Checklist for problems • Form is terse, so I included a sheet describing the purpose of each check