oracle dba best practices
DESCRIPTION
DBA Best PracticesTRANSCRIPT
-
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
-
Audience Those considering DBA career
DBAs who want to improve
Those forced to tolerate DBAs
-
Theres no asset more important than a companys data. You cant give that responsibility to
someone who doesnt 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 dont offer a degree.
Developer DB developer
System Administrator
-
DBA is a role Like a policeman or teacher
Unstated responsibilities,
expectations
Most people dont 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,attends reg.
On theboard
Studies Admin.Commands
Concepts Architecture
Manuals No time Readsmanuals
Veryfamiliar
Learning Takesclasses
OCP, books OCM,author,conferences
Onlineforums
No time Lurks, asks Answersquestions
New Oraclefeatures
Uses anyfeature
Wary ofnew feature
Createstests to findproblems
-
OCP
Misconception: you are an expert
Multiple choice, so simple
questions
Broad, not deep
Career benefit - cant 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 isnt 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
Cant self-study
Caution: Vendor doesnt 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
Dont 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 its 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 Dads 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
shouldnt 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 4s - 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:
Only when 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, doesnt 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?
Dont 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 cant 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 managers 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 dont mind paying for what they value, but resist paying for
what they dont consider valuable
If your manager is a DBA, less
people skills are required, but this
is career-limiting
-
Respect
Do you respect someone who
doesnt 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
Dont know why? Assume stupidity
A DBA appreciates a heads-up,
why shouldnt others?
-
When youre 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