Backup Strategy

[default]

Hi,

I am not sure, what kind of aspects we need to consider when designing a backup strategy for a mission critical 24*7 database.
Can somebody please tell me what will be an ideal backup strategy for a mission critical 24*7 database?.

Thanks in Advance.

[sambavan]

RTD: http://www.oradoc.com/ora816/server.816/a76993/toc.htm

Sam

[marist89]

The ideal situation is a full hot backup every minute.

You need to look at two factors:
1. How much data can you lose if there is a failure?
2. How long do you have to recover should there be a failure?

You need to integrate your backup plan with your Disaster Recovery and Business Continuity plan. The needs of you business will dictate the answers to these questions.

Oh, and export/import is NOT a valid backup plan for a production database.

[Edited by marist89 on 09-25-2001 at 11:21 PM]

[grjohnson]

Originally posted by marist89
Oh, and export/import is NOT a valid backup plan for a production database.

Unless you have two 24 x 7 databases running in parallel in NOARCHIVELOG mode on two different machines in two different states, using failover.

Then exporting

a). FULL=Y ROWS=N
b). ESSENTIAL TABLE + ROWS = Y

Sorry, but this is a backup strategy I have implemented for a certan production system.

So, I realise that EXPORT/IMPORT is NOT always the best way. But in some cases, it may be the only strategy viable to a particular application. But granted, in MOST cercumstances a logical backup isn't enough.

Cheers,

[marist89]

Originally posted by grjohnson

Originally posted by marist89
Oh, and export/import is NOT a valid backup plan for a production database.

Unless you have two 24 x 7 databases running in parallel in NOARCHIVELOG mode on two different machines in two different states, using failover.

I especially disagree with running a production database (with data changes) in NOARCHIVELOG mode. Basically, you are relying on the fact that you will never have to recover because you have a failover server. Personally, I'm not confident enough of any technology to limit me to only one way out of a disaster.

If you ever have to recover your database, you will have to do it to the last valid export. If your last valid export is 23 hours old, you have lost a day's worth of data. Let alone if Oracle has a bug in the export or import program.

[julian]

If a DBA runs PROD in NOARCHIVELOG mode, then he should either consider a new career or attend some DBA classes at Oracle University, at least B&R :-)

[Sureshy]

Hi

If your prod database is in archivelog mode and downtime is intolerable/you want to protect against hardware failure - you may want to consider Standby Database or parallel server.
Bear in mind that this wil require extra dba maintenance and the budget available to purchase relevant hardware.

Suresh

[julian]

Originally posted by Sureshy
Hi

If your prod database is in archivelog mode and downtime is intolerable/you want to protect against hardware failure - you may want to consider Standby Database or parallel server.
Bear in mind that this wil require extra dba maintenance and the budget available to purchase relevant hardware.

Suresh

extra with big E and bugdget with big B :-)

[jrpm]

Two alternate strategies, depending on hardware:

1. Use mirrored drives with control software that will let you split the mirrors 'interactively' withouth bringing the db or system down. Put the database in hot backup mode, split the mirrors, take the database out of backup mode, Backup the 'offline' mirror, bring the offline mirror back online synching up to the 'active' set. (EMC is pushing this approach with TIme finder, but it is doable with other mirroring sets. I used to do it on Vaxen 8 years ago.)---exposure/risk time about 30 seconds, if any, while going into and out of backup mode.


2. Put database in hotbackup, backup the datafiles, come out of backup mode.


Both approaches require archivelog mode. Neither should prevent a periodic cold backup, say once a month or so.

If the database is in production status and is not read only, use archive log mode no matter what your backup strategy. You will save yourself a lot of headaches because Oracle will do a lot of recovery automatically if the logs are available.

[grjohnson]

Originally posted by julian
If a DBA runs PROD in NOARCHIVELOG mode, then he should either consider a new career or attend some DBA classes at Oracle University, at least B&R :-)

What, you don't think I've weighed up the options. The aove quotes leads me to the opinion that sombody has be working in a standard database environment to long, who doesn't open their eyes, and likes to criticise backup plans without knowing the database environment and data.

Personally I don't feel I need to justfy a backup plan to others don't know the situation.

I will say one thing though, management are aware of the backup procedure, they are NOT willing to allocate more resources, and ARE willing to accept the consequence or losing data > 24 hour period.

Sometimes, its the only alternative.

Thank you,