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
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
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?
SMASH the Bash bug! Apple and Red Hat scramble for patch batches
'Applying multiple security updates is extremely difficult'
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

Providing a secure and efficient Helpdesk
A single remote control platform for user support is be key to providing an efficient helpdesk. Retain full control over the way in which screen and keystroke data is transmitted.
Intelligent flash storage arrays
Tegile Intelligent Storage Arrays with IntelliFlash helps IT boost storage utilization and effciency while delivering unmatched storage savings and performance.
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.
Secure remote control for conventional and virtual desktops
Balancing user privacy and privileged access, in accordance with compliance frameworks and legislation. Evaluating any potential remote control choice.