1 / 43

Oracle DBA Best Practices

Oracle DBA Best Practices. Dennis Williams Senior Database Administrator Lifetouch, Inc. 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. Audience.

Download Presentation

Oracle DBA Best Practices

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 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

More Related