Re: ORACLE Backups

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>                                   |
------------------------------------------------------------------------------