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 [email protected]

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

Studies

Member, On the attends reg. board Concepts Architecture

Admin. Commands No time Reads manuals Takes OCP, books classes

Manuals Learning

Online No time forums New Oracle Uses any features feature

Very familiar OCM, author, conferences Lurks, asks Answers questions Wary of Creates 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: 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, 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