Oracle DBA Best Practices Oracle by SupremeLord

VIEWS: 119 PAGES: 42

									Oracle DBA Best
   Dennis Williams
   Senior Database Administrator
   Lifetouch, Inc.
   Those considering DBA career
   DBAs who want to improve
   Those forced to tolerate DBAs
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
   Time - Until lunch is ready
   Left-brain portion - tech. Issues
   Right-brain portion - softer issues
   Database audit form
75,610 DBAs
   U.S. Bureau of Labor Statistics in
    2003 began tracking the IT
   6.46% unemployed
   There are 797,445 Software
    Engineers (5.3% unemployed)
   DBA supports about 10
   66% DBA growth by 2010
Where do DBAs come from?
      Schools don’t offer a degree.
      Developer        DB developer
      System Administrator
DBA is a role
   Like a policeman or teacher
   Unstated responsibilities,
   Most people don’t know what a
    DBA does.
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
   Keep a personal time log - 1 week
   Learn your time distribution
   Target improvements
   Study time management
 Levels of Oracle DBA
           Rookie     Veteran Expert
TCOUG      No time    Member,      On the
                      attends reg. board
Studies    Admin.     Concepts     Architecture
Manuals    No time  Reads         Very
                    manuals       familiar
Learning   Takes    OCP, books    OCM,
           classes                author,
Online     No time    Lurks, asks Answers
forums                            questions
New Oracle Uses any   Wary of     Creates
features   feature    new feature tests to find
   Misconception: you are an expert
   Multiple choice, so simple
   Broad, not deep
   Career benefit - can’t hurt
   Can help you go deeper in Oracle
Types of DBAs:
   Development DBA
   May carry developer title
   Develop code ( PL/SQL )
   “Hunter” personality (find/fix/forget)
   Best practices: Code Complete by
Production Support DBA
   Keep it running
   “shepherd / farmer” personality
   Where best practices really make a
   The hero isn’t the DBA that battles
    problems through the weekend,
    but the one that avoided the
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
Consultant DBA
   Fastest way to get expertise
   Varied work opportunities
   Understand economic cycles
Backup is Job #1
   Ensuring data is never lost
   Don’t trust an untested backup
   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
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
Security is Job #2
   DBA is responsible for making data
    available to authorized users
   Keeping data inaccessible to
   Role: policy, implementation
   Know security options, implications
   Know how application affects
    security, data sensitivity
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
Oracle Performance
Tuning - Types
   SQL Statements - explain plan,
    tkprof, 10053 events
   Application disasters - 10046 trace,
    Cary Millsap (TCOUG Hotsos
   Proactive tuning and hanging -
    STATSPACK, utlestat/utlbstat
Server statistics
   If you have a performance
    problem, either your database is
    working too hard, or it’s not being
    allowed to work. - Jonathan
   You must know what your server is
   You must know the O.S.
   Unix Admin. - your best friend
Bind variables
   Ensure developers use bind
   Audit v$sql in test to verify
   In Java, PreparedStatement
   In .net, CommandText,
    CreateParameter, Append
   Connection pooling
Not your Dad’s Oracle
   Rules of thumb, ratios to
    mathematical foundations
   Counts to measured times in
   Aggregate by instance to process
   V$ tables are either too
    summarized or point-in-time
Goal of tuning
   What matters to the business user
   Good chance to sell value
   End-to-end analysis
   DBA must prove innocence
   There is nothing so useless as
    doing efficiently that which
    shouldn’t be done at all
              - Peter F. Drucker
   Use LMT, uniform extents, vs.
   Use autoextend vs. monitoring
   Email exceptional conditions
    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,,
   Timing - can I skip versions?
   Appl. quality requirements
   What are others saying? Metalink
   Only change one major component
Oracle Patches
   Two philosophies:
     Onlywhen bug arises
     Immediately (security)

   Vendor testing hierarchy:
     Version
     Patch  set
     Individual patch
Oracle Licensing
   Install defaults to all - know what
    costs extra
   Consider Standard Version
   Understand the organization
    budget mechanisms
   Knowledgeable if not authorized
   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
   Key: What is essential?
   Don’t document what can easily be
    figured out
   Keeping up-to-date is the crucial
   Obsolete documentation is
   Self-documenting where possible
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
   Participate in initial design where
    your leverage is large
   Learn data modeling software
Tech. Learning List
   Application interface methods -
    Microsoft, Java, OCBC
   Server tuning
   Storage methods and options,
    strengths and weaknesses

   Effective communication proceeds
    from shared knowledge
   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?
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
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
   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
   Really care about the people you
    work with
How to avoid problems
   Many bad reactions stem from a
    lack of warning
   Emotions play a large role in
   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?
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
   Feeling no control
   Constant interruptions
   DBA syndrome - responsible for
    everything, control nothing, nobody
   You hear yourself saying “nobody
    appreciates me”
   Antidote: study stress control,
   visit clients, future changes
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
   End with a nice complement
Simple Explanations
   The most valuable people are
    those who can understand
    complex issues and explain them
    simply to others
   Makes other feel good about
   Albert Einstein, Steve Hawking,
    Carl Sagan, Richard Feynman
Database Audit Form
   Periodically audit each database
   Checklist for problems
   Form is terse, so I included a sheet
    describing the purpose of each

To top