Oracle DBA Best Practices Oracle

Document Sample
Oracle DBA Best Practices Oracle Powered By Docstoc
					Oracle DBA Best
Practices
   Dennis Williams
   Senior Database Administrator
   Lifetouch, Inc.
Audience
   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
Agenda
   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
    workforce.
   6.46% unemployed
   There are 797,445 Software
    Engineers (5.3% unemployed)
   DBA supports about 10
    developers.
   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,
    expectations
   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
                  Koopman
Assignment
   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
           Commands
Manuals    No time  Reads         Very
                    manuals       familiar
Learning   Takes    OCP, books    OCM,
           classes                author,
                                  conferences
Online     No time    Lurks, asks Answers
forums                            questions
New Oracle Uses any   Wary of     Creates
features   feature    new feature tests to find
                                  problems
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
Types of DBAs:
   Development DBA
   May carry developer title
   Develop code ( PL/SQL )
   “Hunter” personality (find/fix/forget)
   Best practices: Code Complete by
    McConnell
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
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
    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
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
    unauthorized
   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
    consequences?
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
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
Bind variables
   Ensure developers use bind
    variables
   Audit v$sql in test to verify
   In Java, PreparedStatement
   In .net, CommandText,
    CreateParameter, Append
   Connection pooling
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
Goal of tuning
   What matters to the business user
   Good chance to sell value
   End-to-end analysis
   DBA must prove innocence
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
    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
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
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
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
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
Tech. Learning List
   Application interface methods -
    Microsoft, Java, OCBC
   Server tuning
   Storage methods and options,
    strengths and weaknesses

   Effective communication proceeds
    from shared knowledge
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?
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
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
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?
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
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
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
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
Database Audit Form
   Periodically audit each database
   Checklist for problems
   Form is terse, so I included a sheet
    describing the purpose of each
    check

				
DOCUMENT INFO
Shared By:
Stats:
views:119
posted:2/13/2012
language:English
pages:42