jump to navigation

Your Backups Are Probably Too Simple September 10, 2009

Posted by mwidlake in Architecture.

Following on from my post a few days back on When a Backup is Not a Backup I want to pick up on the topic of Simplicity, something Piet de Visser, Neil Chandler and I regularly discuss.

It’s very good to ask yourself the question “how to keep oracle backups simple“. A simple backup may not be the quickest to run or the fastest to recover from but it is a lot more likely to work. Which is the one critical defining feature of a backup!

However, the backing up of the Oracle database itself is not what I have in mind, after all any DBA who is beyond the trainee stage should know how to do it and there are many options with Oracle to achieve a good, simple backup (Cold backup, full export, manual hot backups, simple RMAN )

{As an aside, something that really bugs me about Oracle at the moment is their utter focus on RMAN in the backup documentation –  for example, the 10gR2 “Backup and Recovery Basics Manual” is almost dedicated to RMAN. It should not be, is not called the “Recovery Manager Basics Manual”. If you go to the Advanced guide it is all pretty much RMAN until you get to chapter 17, where you eventually find a reasonable set of information on creating a simple, user-controlled hot backup. Or use Google on “End backup Oracle” and there is endless information and help. Such a simple backup method should be made more of by Oracle Corp, I believe}

My concern is that the Oracle Database is very often only one component of the whole system.  Note  the “the.”

First up – do you use Database Links to read data between the database you are considering and others? More to the point, do you do two-phase commits between your database of concern and other oracle databases? If so, you may well need to keep the data in-synch between them. {Don’t forget – Your applications may independently update multiple database without using Database Links}. How exactly do you manage that? Have a policy that if database (a) has to be recovered, you also do database (b), both to a point-in-time?

Second, and probably most common, do you have files external to the database that are referenced, say as BFILES or via the application? If you do a recovery, do you need to make sure those files are in-synch with the database? You may well have the situation where files on the server/file store are backed up at one time and the database at another. Loss of the whole server will need a full recovery of both backups, which were taken at different times.

Three, are there other dependencies, such as password authentication, account creation, audit maintenance?

Four, If your system crashes and was half way through a batch process, how do you cleanly resume the batch or clear it down and start again (having made sure the consideration of external files above has been resolved)?

 I’m sure you get the idea now. Think about it.

Test recoveries are good at identifying the above issues, but only if the test recovery includes testing the application(s) that use the database {as opposed to the usual “select sysdate from dual – yep, it works” test}.

The above considerations are part of why I am a fan of keeping all the things to do with a business application in the Oracle database, even if it might not be the ideal from other perspectives (eg images make the database a lot larger and may not be processed as fast as they can be as external files).

All of the above are probably the first steps towards creating your Disaster Recovery Plan. “Disaster Recovery” is a whole topic in its own right. Actually, it’s a whole business and one people have very lucrative careers in. But if you are the DBA and have the backups nailed, then Disaster Recovery (what to do if the building burns down) is probably next on your list of things to worry about.


1. B. Polarski - September 10, 2009

It is years I have not seen an hotbackup script in action and the trends in 11gR2 is ASM. The only way to backup active db in ASM is Rman. I never tried a sync/disk backup on an ASM, should work also.

mwidlake - September 10, 2009

Thanks for that Bernard.

Yes, with ASM you are forced towards either RMAN or disc copying, but a lot of people still do not use ASM.

I am also not against RMAN as a method of backing up, it has some great features, but I think it can be a step too far.

As an example, I was working for one client where they had lost their Oracle DBA and the Sys Admin was having to cope. The Sys Admin had learnt a fair amount about Oracle and was keeping it all working, but rather than get them to learn about RMAN, I set up a simple Hot Backup script for them, which they understood immediately. It worked admirably

Ironically, it was only when they got an Oracle person again and set up using RMAN that the backups went all wrong 🙂

2. Surachart Opun - September 11, 2009

Nice article.
Backups Are Probably Too Simple 😉 But How to make sure Your backups can recover, when you need them.

Testing… How often we should test?

mwidlake - September 11, 2009

Thanks Surachart.

How often to test? As a bare minimum, as many times as it takes to successfully recover a working database from your backup! This might sound a little flippant but it would seem a lot of Oracle sites do not do this.

I kind of covered the need to do test recoveries in the previous post. If I had to recommend how often to test than I would say as a minimum (a) after any major change to the system eg upgrade to DB, OS or backup technology, or use of new architecture, such as partitions or standby or LOBs. (b) enough times so that more than one person feels able to recover from loss of any file from the database.

From then on I would say make test recoveries part of the monthly schedule of work for the DBA resource. Obviously, if you are one DBA looking after 10 systems this could be impossible, but when I am filling a DBA role, I want to feel that I can get my databases back from someone putting an axe through the top of the disk unit. Then I am happy.

3. Surachart Opun - September 12, 2009

Thank You your suggestion 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: