November 2008

November 2008

The Administrator’s Lament

All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

There’s not half the files there used to be,
And there’s a millstone hanging over me,
The system crashed so suddenly.

Complete Lyrics (with apologies to the Beatles)

Best Practices for Database BackupsThis month’s question came to us from a reader:

“In a back issue of The Specialist, you addressed the need for logical backups in addition to physical backups. Can you give us a list of other best practices for backups?”

Iggy Fernandez of the Database Specialists team responds:

Thank you for asking. I prefer the term “good ideas” instead of “best practices.” Here are some “good ideas” from my upcoming book on Oracle database administration.

The following practices apply to all database backups, not just Oracle backups:

  1. Establish a service level agreement (SLA) for backups and recovery. Clearly document the maximum amount of data loss that is permitted, backup retention policies, and how much time is allowed for database recovery.
  2. Document the backup methodology and have it formally reviewed. Publish the documentation to an audience that includes the database owner.
  3. Periodically test the recoverability of backups. Typically, this is done in a testing environment. A recovery test establishes how long it will take to recover the database.
  4. Be prepared to recover from user error, for example when a user inadvertently updates or deletes the wrong data.
  5. Have a contingency plan that covers damage to the operating system or hardware-be prepared to move the database to another server.
  6. Ensure the safety of backups. The traditional method is to send backup tapes offline. Newer methods involve backups to a backup appliance over the network. A combination of near-line (disk) and far-line (tape or network) backups can be used. Near-line backups reduce the time needed for recovery while far-line backups increase the safety of backups. The tape library should not be a single point of failure-ensure that data can be recovered even if the tape library is damaged.
  7. Retain multiple backups in case one set of backups is damaged.
  8. Backup scripts should incorporate error checking and an adequate amount of logging. The logs should be retained for an appropriate time. Notification of backup failures should be sent to the database administrators. Backup failures should be formally investigated.
  9. Reports of backup successes and failures, the amount of data backed up, and the time it took to perform backups should be generated and sent to an audience that includes the database administrators and the database owner.
  10. Changes to any aspect of backup procedures, whether temporary or permanent, should be performed under formal change control procedures.

The following practices apply specifically to Oracle database backups:

  1. Use Recovery Manager (RMAN) for backups. The advantages of RMAN are so numerous and valuable that it is hard to justify not using it. For example, RMAN checks data blocks for corruption while it is making a backup, single-block recovery is possible with RMAN, and so on. Backups performed using third-party software such as Network Appliance Snapshot can be integrated with RMAN.
  2. Prevent unlogged operations in databases that use ARCHIVELOG mode; use the ALTER DATABASE FORCE LOGGING command to do this.
  3. Backup all aspects of the database including the archived redo logs (for databases that use ARCHIVELOG mode), the control file, and the parameter file (spfile).
  4. Create logical backups (exports) to supplement physical backups. This creates a certain amount of protection from logical damage, such as data entry errors. Use a setting such as FLASHBACK_TIME to ensure the consistency of the exported data.
  5. Leverage Oracle’s flashback features by increasing the value of UNDO_RETENTION from the default value of 15 minutes to a more appropriate value such as 24 hours. This allows recovery from logical damage without having to resort to physical backups.
  6. Databases that run in ARCHIVELOG mode should set LAG_ARCHIVE_TARGET to an appropriate value, such as 15 minutes, to control maximum data loss.
  7. Incorporate the use of techniques that check for data corruption. These include initialization parameters, such as DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_UPDATE, and commands that scan the database, such as VALIDATE DATABASE. Oracle Database 11g introduced a single parameter called DB_ULTRA_SAFE that controls the values of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_UPDATE.
I hope this answer helps you. Best of luck to you and please don’t forget that development and testing databases also need an appropriate backup strategy.

Five Wonderful Books That Most People Ignore 

Some of the best Oracle books ever written are hardly read any more because their titles mention an old version of Oracle such as Oracle 8i or because they contain no mention of the newest version of Oracle. Mogens Norgaard is quoted as sayings: “Once upon a time Jonathan Lewis was asked if his book Practical Oracle8i would ever be published in an updated 9i version? His response was to suggest that you put a sticker with a ‘9’ over the ‘8’ on the cover of his book, because the advise [sic], methods, and techniques described in the book were still very valid with Oracle9i.”
Here is a list of five wonderful books that most people make the mistake of ignoring only because they contain no mention of Oracle 10g or Oracle 11g. Used copies of these masterful books can be purchased on for just a few dollars!
  1. Effective Oracle by Design by Tom Kyte. If you want your database or application to perform well, you must consciously take advantage of Oracle’s best features and you must consciously use best application development practices such as application instrumentation. There is no other way and Tom Kyte is the best guide you can have.
  2. Practical Oracle 8i: Building Efficient Databases by Jonathan Lewis. This book in the same league as Tom Kyte’s book. ’nuff said.
  3. Oracle 8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams. There has never been another book that comprehensively discusses internal Oracle mechanisms and there may never be another one. Why wait?
  4. Oracle 8i Backup & Recovery by Rama Velpuri. Rama Velpuri was Oracle’s foremost backup and recovery expert and this book reflects it, from the discussion of recovery internals to the now classic list of recovery scenarios.
  5. The Art and Science of Oracle Performance Tuning by Chris Lawson. Written by an ex-member of the Database Specialists team, it teaches a systematic five-step method of performance tuning.

The Mighty Minds of Old
Around me I behold,
Where’er these casual eyes are cast,
The mighty minds of old:
My never-failing friends are they,
With whom I converse day by day.
With them I take delight in weal,
And seek relief in woe;
And while I understand and feel
How much to them I owe,
My cheeks have often been bedewed
With tears of thoughtful gratitude.
His Books by Robert Southey (1774-1843)

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

David Wolff
CEO, Database Specialists, Inc.
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *