Feeds

Database down! DBA ninjas to the rescue

Handy 101 guide for Oracle administrators

Workshop

Workshop Database administrators (DBAs) may not be given much attention ninety-nine per cent of the time. But when the database fails for some reason, they become ninjas, (hopefully) restoring the data, recovering the firm's ability to do business, and generally saving the day.

This all assumes that you've backed up your database properly, of course, and that you've validated the backups and understand the recovery process. This article explains to DBAs the steps involved in recovering from unexpected events quickly and smoothly.

First things first

Ideally, you'll want to avoid your database going down in the first place. Oracle divides causes of downtime into two broad sets: planned and unplanned. Planned downtime shouldn't be a problem, but unplanned is. Oracle further divides unplanned downtime into two areas: data failures, and computer failures. DBAs will be most interested in data failures – the four main categories are:

  • Storage error
  • Human error
  • Corruption
  • Site failure

DBAs have some control over storage error and human error, at least. Using Automatic Storage Management, and techniques such as good capacity planning, can help you to avoid exceeding storage limits and crashing your database. Protecting against human error is a combination of automating key tasks, and putting the proper privileges and access controls in place to stop unqualified people accidentally DROPing your customer tables.

While you can protect against some of these things, DBAs still haven't worked out how to control fire, flooding and other acts of god. We'll sure there'll be a third party plugin for those eventually, but for now, you're going to need a robust backup and recovery policy.

There are three things to back up in an Oracle database:

  • The server parameters file (SPFILE), which is a binary containing information about the server running the database, in key-value pairs.
  • The Control file. This is a binary file containing database structure information, including the database name, the names and locations of associated datafiles, time stamping and checkpoint info.
  • The data files themselves (generally considered quite important). Since Oracle 8, Oracle has used the recovery manager (RMAN) to handle backing up these things automatically.

Kinds of backup

There are two broad kinds of backup: a cold backup, and a hot one. Cold (offline) backups are the easiest to do. This backup of your entire database is designed to produce an exact, one-off copy, which is then easily restorable.

They're great, but for one thing. They're called offline backup for a reason: you have to stop the database to run them. If you're backing up every day, and you have a large database or an ecommerce site that needs to be up 24 x 7, then you don't really want to have to shut your system down daily for this process.

That leaves the alternative: a hot backup. This is trickier to do, because it backs up the database while it is running, but it has the advantage that you don't have to disrupt service. It does impact performance, though, so try to schedule it for periods of low activity.

Hot backups are good for recovering databases on the fly, rather than complete restoration from scratch. They are complex, so best automated via scripts. However, remember to update those scripts for new tablespaces when your schema changes.

More from The Register

next story
PEAK APPLE: iOS 8 is least popular Cupertino mobile OS in all of HUMAN HISTORY
'Nerd release' finally staggers past 50 per cent adoption
Microsoft to bake Skype into IE, without plugins
Redmond thinks the Object Real-Time Communications API for WebRTC is ready to roll
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
Mozilla: Spidermonkey ATE Apple's JavaScriptCore, THRASHED Google V8
Moz man claims the win on rivals' own benchmarks
Yes, Virginia, there IS a W3C HTML5 standard – as of now, that is
You asked for it! You begged for it! Then you gave up! And now it's HERE!
FTDI yanks chip-bricking driver from Windows Update, vows to fight on
Next driver to battle fake chips with 'non-invasive' methods
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Ubuntu 14.10 tries pulling a Steve Ballmer on cloudy offerings
Oi, Windows, centOS and openSUSE – behave, we're all friends here
prev story

Whitepapers

Why cloud backup?
Combining the latest advancements in disk-based backup with secure, integrated, cloud technologies offer organizations fast and assured recovery of their critical enterprise data.
Forging a new future with identity relationship management
Learn about ForgeRock's next generation IRM platform and how it is designed to empower CEOS's and enterprises to engage with consumers.
Reg Reader Research: SaaS based Email and Office Productivity Tools
Read this Reg reader report which provides advice and guidance for SMBs towards the use of SaaS based email and Office productivity tools.
Storage capacity and performance optimization at Mizuno USA
Mizuno USA turn to Tegile storage technology to solve both their SAN and backup issues.
Protecting users from Firesheep and other Sidejacking attacks with SSL
Discussing the vulnerabilities inherent in Wi-Fi networks, and how using TLS/SSL for your entire site will assure security.