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
Microsoft on the Threshold of a new name for Windows next week
Rebranded OS reportedly set to be flung open by Redmond
Business is back, baby! Hasta la VISTA, Win 8... Oh, yeah, Windows 9
Forget touchscreen millennials, Microsoft goes for mouse crowd
SMASH the Bash bug! Apple and Red Hat scramble for patch batches
'Applying multiple security updates is extremely difficult'
Apple: SO sorry for the iOS 8.0.1 UPDATE BUNGLE HORROR
Apple kills 'upgrade'. Hey, Microsoft. You sure you want to be like these guys?
ARM gives Internet of Things a piece of its mind – the Cortex-M7
32-bit core packs some DSP for VIP IoT CPU LOL
Lotus Notes inventor Ozzie invents app to talk to people on your phone
Imagine that. Startup floats with voice collab app for Win iPhone
prev story

Whitepapers

A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.
Storage capacity and performance optimization at Mizuno USA
Mizuno USA turn to Tegile storage technology to solve both their SAN and backup issues.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Beginner's guide to SSL certificates
De-mystify the technology involved and give you the information you need to make the best decision when considering your online security options.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.