Printable View
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.
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]
Unless you have two 24 x 7 databases running in parallel in NOARCHIVELOG mode on two different machines in two different states, using failover.Quote:
Originally posted by marist89
Oh, and export/import is NOT a valid backup plan for a production database.
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,
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.Quote:
Originally posted by grjohnson
Unless you have two 24 x 7 databases running in parallel in NOARCHIVELOG mode on two different machines in two different states, using failover.Quote:
Originally posted by marist89
Oh, and export/import is NOT a valid backup plan for a production database.
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.
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 :-)
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 :-)Quote:
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
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.
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.Quote:
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 :-)
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,
Quote:
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 :-)
I guess I should consider a new career, becuase I have tried my best to create a SLA and backup and recovery strategy. I was yelled at and called incompent by the lead system administrator, becuase he had a setup with the old DBA that he did not want changed. Also, the other people had never met me before the meeting, but knew the lead administrator, politics is also involved. The plan between the previous DBA and the admin is to use the redunacy of the system and reinstall during failure, which is acceptable to the customer. Becuase the newest data is our most important data and our application is in a heirachy with redunacy at each level. So, I have no other option, but to find a new career as suggested by Julian.
Come on, I was not serious when I suggested that :-)
Do not take the work stuff that deeply. There is much more to life than Oracle and backups of ones and zeroes.
Well... I'm pleased we worked that out. We all happy now :)
Put the db in archivelog mode and take the hotbackup every night. Backup the archived files every 5 minutes...........Assuming that ur running OPS.
I will agree with grj
Even I have earlier worked with a large company which has oracle running in NOARCHIVELOG mode and backup they were taking was export backup. Once in a week we used to shutdown the database for whole night the backup will run and next day in morning after coming our job was to again up the database.
The company which i am referring is one of the leading and largest company and if this can happen in that company it is quite possible that it may be happeninng in prodn db of companies working below the segment of my company.
Therefore I am requesting not to pass such remarks as there can be many aspects related to it
Amol
Ha? In Oracle Parallel Server you backup the a-files every 5th minute??Quote:
Originally posted by Youngdba
Put the db in archivelog mode and take the hotbackup every night. Backup the archived files every 5 minutes...........Assuming that ur running OPS.
Then you database would be busy with the backup and not the production :)Quote:
Originally posted by Youngdba
Put the db in archivelog mode and take the hotbackup every night. Backup the archived files every 5 minutes...........Assuming that ur running OPS.
Sorry, Julian if I came off a little hard and I am sorry. I was trying to be a little cheeky. I am a little upset that I cannot create a SLA and a backup and recovery strategy. By not creating these plans I feel that I am missing out and not being the best DBA that I can be. Again I am sorry if my bitterness was directed toward you in anyway.
A production db need not be in archivelog mode provided hardware level mirroring of the data to another site is configured. As of now, EMC has done it.
Archive log mode is *the only* mode that assures the recovery (that is with no or at least minimal amount of changed data loss) in the wide range of possible "errors" that can prevent data to be accessed.
Long-distance disk mirroring of noarchive mode database prevents only the data loss in case of some hardware failure (like disk crash), but for example can't help you in any way in case of human errors (like erroneous table drop or file deletion).
Archivelog mode with hot backups has *no* disadvatages compared to noarchive logs and cold backups. It has so many advantages, including the performance aspect, that in production database there realy shouldnt be any reason for running in noarchivelog.
I agree with you, Modic.
But most of the production datawarehouse sysems do not operate in ARCHIVELOG mode. The reason is data can be rebuilt from the source system at any time.
I come across some big system where backup is also not done for many weeks. When I asked the DBA what he would do when block corruption occurs, he simply said that he could extract maximum number of rows from the table and reorganize it. The reason is over a period of time it is very difficult to maintain backup copy of the tapes for a large system. In fact at one site the total cost of tapes the organizaion maintains exceeds the total cost of the entire system.
Hi Friends,
In my production database we are running in noarchive mode.
But this has very valid reasons.
First, everyday's insertions are around 42 gigs. We have no deletes or updates but the data is deleted after 45 days. And whenever there is a problem and the inserts are not done, no sweat. We have the data being written in to a flat file, using which and the sql loader we do the inserts again.Noarchive log helps in speedier system.
Second, who will agree to bear the extra cost involved in maintaining backups when the data is really huge (the total size of db exceeds 3 tb)and there are options like I mentioned above in reloading the data?
Thanks
manjunath
jrpm wrote:
1. Use mirrored drives with control software that will let you split the mirrors 'interactively' ....
Which reminds me of the time a certain large aerospace company I worked for used this procedure to do their backups, but one night when they re-synched they got it backwards and overwrote the "current" half of the mirror with the "old" half. That was an exciting morning ;-)
Tim