Feeds

Database down! DBA ninjas to the rescue

Handy 101 guide for Oracle administrators

Workshop

Protecting yourself with incremental backups

Incremental backups are a good way to keep you protected while minimising the performance impact on the database. They work by backing up only the changes made to the database since the last backup, and they form part of a backup strategy suggested by Oracle. It suggests daily backups for this.

There are two types of incremental backup; level 0, and level 1. Level 0 is a full backup, but it's designed to be added to incrementally. Level 1 backups are simply the deltas from the previous incremental backup.

Start by creating an incremental level 0 datafile copy backup. That gives you the entire database's contents in a separate set of files.

Then, the next day, you'll create a level 1 incremental backup, containing the blocks changed since the first backup. That gives you the ability to recover your database to the point when that backup was made. Now, you'll have two sets of files: the level 0 backup, and the level 1 backup.

From now on, each day, you apply yesterday's incremental backup to the level 0 backup. That will create a level 0 backup file that you can use to restore your database to yesterday's state. Then, you create a new incremental level 1 backup, containing the changes made to the database from yesterday.

That way, if your database goes down, you can apply the latest level 1 backup to the level 0 backup, restoring it to the start of the current day.

Salvation in the REDO logs

That's database restoration covered. That's all well and good, but a restored database isn't necessarily up to date. What about if you business can't afford to lose the data stored since the last incremental backup, and needs you to recover your data to some point after the backup that happened at the beginning of today? Luckily, REDO logs are there to help.

REDO logs store changes that are made to the database. When backing up a database, you can run in ARCHIVELOG mode to back these up, too. When you're performing a hot backup, you are forced to do this.

REDO logs cover the gap between the last incremental backup, and the current point in time. Once you've restored your database, you can use these files to roll forward all changes made to the database. Then, you'll roll back those changes that were requested, but which hadn't been committed to the database when it went down. This recovers the data lost after your restoration. It gets the database to the exact state that it was in when it went down.

Protecting these REDO logs is important. ARCHIVELOG will archive those that have been filled with changes, but multiple copies of online ones should be stored on separate disk spindles.

Make this recovery process faster by using the Fast Recovery Area (FRA). This is a single disk location where Oracle will store and automatically manage all recovery-related files (including RMAN backups).

Tools

There are user-managed options for backing up Oracle data, but RMAN is really your go-to tool here. There are third party tools that can help you to work with this utility, though.

Quest Backup Reporter for Oracle, a component of the Toad tool for Oracle DBAs, increases the visibility of your Oracle backup jobs. It's a read-only reporting tool that allows you to see all of your executed RMAN backups in one screen, along with their execution status. You can look at a backup schedule, and also check your RMAN policy definition, seeing if there are any violations of your backup policies.

Finally, don't forget to validate your backups using BACKUP VALIDATE, which will check to ensure that all of the necessarily files needed during a backup have been copied, and are intact. And perform regular test restorations to separate non-production hardware, so that you can sure the database would actually restore in the event of a disaster.

You can script that test, too - and with all of this in place, you'll be sleeping a lot more soundly at night. ®

The Register is running a series of Oracle DBA workshop articles in association with Dell Software. Interested in learning more? Check out these Dell whitepapers.

More from The Register

next story
Why has the web gone to hell? Market chaos and HUMAN NATURE
Tim Berners-Lee isn't happy, but we should be
Apple promises to lift Curse of the Drained iPhone 5 Battery
Have you tried turning it off and...? Never mind, here's a replacement
Microsoft boots 1,500 dodgy apps from the Windows Store
DEVELOPERS! DEVELOPERS! DEVELOPERS! Naughty, misleading developers!
Eat up Martha! Microsoft slings handwriting recog into OneNote on Android
Freehand input on non-Windows kit for the first time
Linux turns 23 and Linus Torvalds celebrates as only he can
No, not with swearing, but by controlling the release cycle
Scratched PC-dispatch patch patched, hatched in batch rematch
Windows security update fixed after triggering blue screens (and screams) of death
This is how I set about making a fortune with my own startup
Would you leave your well-paid job to chase your dream?
prev story

Whitepapers

Best practices for enterprise data
Discussing how technology providers have innovated in order to solve new challenges, creating a new framework for enterprise data.
Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Advanced data protection for your virtualized environments
Find a natural fit for optimizing protection for the often resource-constrained data protection process found in virtual environments.
How modern custom applications can spur business growth
Learn how to create, deploy and manage custom applications without consuming or expanding the need for scarce, expensive IT resources.
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?