Re: ORACLE Backups

People - this is all pretty simple really. When you do a cold backup, all you're doing is copying a bunch of static operating system files (the db IS down, right?). These files (data, redo, control) ARE your database. So, if you lose any of the original database files, you just replace everything with your backup files and presto, you've got the same database you had when you did the backup (file copy). No matter HOW the database was brought down, you'll end up with what you had. If you did a Shutdown Abort, when you restore the files, you'll be at the same place as if you had just done the Abort.

So, why bring it up and do a Normal or Immediate shutdown before doing the file copy? The main reason is that Abort simply kills the background processes (PMON, DBWR, etc). When you re-start the DB, instance recovery needs to take place. This can take some time (i've seen >30mins if there were a lot of pending transactions aborted). It's generally preferrable to do this when you're not in a hurry rather than when you're in panic mode trying to make the db available to users again. Another reason is peace of mind. Chances are that if Oracle shut cleanly (with Normal or Immediate), it'll also start cleanly.

I also saw the point made that you should test recovery as well as backup. If you're not doing this, please tell me how you know you have a valid backup. I asked this to one of my clients and they assured me their system was bullet-proof. After asking them for the 100th time whether the db was always down when they did the file copies, i was told 'Absolutely. Of course, sometimes the file copies don't complete before we have to bring ORACLE up again...'. I practically shit. Testing recovery would have brought this out. Even if your backup IS valid, without rehearsal, how fast are you gonna be able to get it back?

Sorry for going on forever here. B & R is just a big deal with me. Please post anything you can contribute to this.

barry

Johan Streun (jstreun_at_nl.oracle.com) wrote:
: R Glenn Stauffer (stauffer_at_cc.swarthmore.edu) wrote:
: :bzy_at_ornl.gov writes:
: :>
: :>
: :> Once you do the "shutdown abort",you should bring the database back
: :> up again and do a regular shutdown. Then do your file backups.
: :>
: :> Beverly
: :>
 

: : Why is this?
: : We were using a shutdown abort before backup and haven't experienced
: : problems. However I have been told that this can sometimes lead to
: : situations requiring database recovery. I have modified our backup
: : scripts to use shutdown immediate and notified our users that they must
: : quit their C/S application at day's end to ensure that they do not have
: : uncommitted transactions which will get rolled-back at backup time.
 

: : If this does not work reliably (our backup script chokes if the oracle
: : server doesn't shut down), I will have to go back to using shutdown abort.
: : If so, I'd be real interested in knowing whether my failure to start the
: : database and then do a shutdown normal is going to cause me some problems
: : either during backup or, worst of all, when I attempt recovery.
 

: : Thanks,
 

: : Glenn
 

: When you perform an offline (full) backup it is essential that all your
: database-files (and online logfiles) are in a consistent state. According
: to the Oracle7 database administrator manual the only way to ensure this
: is to do a NORMAL shutdown.
: The best thing to do in your backupscript (IMHO) is to either shutdown
: abort or immediate, startup in restricted mode and then do a normal shutdown.
 

: Your unix-script would look something like this (Oracle7 + ksh):
: sqldba <<EOF
: connect internal
: shutdown immediate
: startup restrict
: shutdown normal
: EOF
 
: In my experience as a DBA this was the method we used before we switched
: to online backups.
 

: Word of advice: Always test your backup-procedure by doing a restore
: especialy test the worst-case-scenarios.
 

: Hope this is helps,
 

: Johan van Streun

: ------------------------------------------------------------------------------
: | These opinions are just mine. I do not represent Oracle corporation in this|
: | Mailing. |
: | Johan van Streun <jstreun_at_nl.oracle.com> |
: ------------------------------------------------------------------------------
Received on Tue Aug 16 1994 - 02:06:41 CEST