Re: hot backups question for experienced DBA's

Please refrain from posting the same message to multiple Oracle newsgroups, as you are not necessarily reaching a larger audience, but certainly reaching a large number of the same people multiple times.

Congratulations on your new job as DBA. As you know, one of the most important tasks is to protect the database from catastrophic data loss.

I salute your effort to test the recovery scenario... a mountain of backup tapes is useless if you can't recover the database with them... and the only way to know if you can recover the database is by testing several recovery scenarios.

In the case of the need to restore a backup datafile, as you experience in your scenario, the corrupted datafile(s) must be first restored, and then the database must be recovered.

When the database is recovered, Oracle will read all of the entries in log files, and apply all of the changes made to the datafile since the point in time it was backed up. In order to bring the datafile consistent with the other datafiles and the control files, Oracle needs *all* of the redo logs, both archived log files as well as the current online redo logs.

While it is possible to copy the online redo logs, this is not recommended. The reason that you don't back them up is that you never ever want to and never ever, never ever need to restore the online redo log files. (The one exception is in the case of cloning a second database instance as a target for SharePlex replication, but that's really setting up a new database, not recovering an existing database.)

In order to recover the database, Oracle needs all of the log files since the time the oldest restored backup file. All of the changes recorded in the log files are applied in sequence, starting with the oldest working towards the most current. The logs that were online at the time the backup was taken have probably already been archived by the time a datafile is restored... and the online redo log files have already been overwritten with more recent transactions... if you were to "restore" the online redo logs from a previous point it time, you would be overwriting transactions that are needed for complete recovery of the database.

If (and I hope this never happens to you) you are missing even a single archived redo log file asked for by the recovery, then you will not be able to perform a "complete recovery" of the database. In this case, performing an "incomplete recovery" may be possible... you would need to restore backup copies of *all* of the datafiles, each datafile would need to be restored to a point in time prior to the missing log file, and then perform the recovery up to the missing log file, then cancel the recovery, and open the database with the "resetlogs" option... which wipes out the (now unusable) online redo logs. This brings the database back to a consistent state, and allow you to get it up and running again, albeit, missing transactions.

I'd recommend that you include a "backup controlfile to trace" statement in your hot backup script... this file can be very helpful in case you ever need to perform an incomplete recovery.

I cannot over emphasize the importance of the archived redo log files. Mine are on mirrored logical volumes (two physical drives in two separate enclosures on 2 separate controllers) in order to protect them from loss in the event of a failure of a controller, cable, power supply, disk drive). The archived log files are also copied to tape with each backup set, so that multiple copies of the files are on several tapes.

The "Oracle 7.3 Backup and Recovery" from Oracle press has a good start on a robust hot backup script, as well as several recovery scenarios to test.

HTH "Richard Piasecki" <ogo_at_mailexcite.com> wrote in message news:3aa5c096$0$63729$45beb828_at_newscene.com...
> Greetings.
>
> This is my first post to this newsgroup. Please excuse the length.
> My question is somewhat involved and takes some time to explain.
>
> I am currently acting as the database administrator for a small
> company. I've been developing applications in Oracle for several
> years, but this is my first job as a DBA. I've been asked to develop
> a hot backup script but it fails every test, so I'd like to get some
> input from the DBA gurus out there.
>
> The script appears below this message. It assumes that the database
> is already in archive log mode. Basically, all it does is put each
> tablespace into backup mode and copy the datafiles to another disk
> (yes, they want the backups to go to a spare disk) along with the
> archived redo logs, the control file and the initialization parameter
> file.
>
> As a test, I run the backup script and then wait a few hours so that a
> couple more archived redo logs are created. I then run a script that
> wipes clean the entire database (simulating a catastrophic failure of
> all disks). All datafiles, control files, redo logs and archived redo
> logs are wiped out. I then copy back all the files from the spare disk
> and attempt a recovery. Invariably, Oracle ends up requesting one of
> the archived redo logs that was created AFTER the backup completed.
> This seems to make no sense to me since that particular archived redo
> log did not exist at the time of the backup. So how do the backed-up
> files know about it? Regardless, the recovery fails as a result.
>
> I'm using the Oracle 8i DBA handbook as a guide for this task, but the
> database is Oracle 8 (I don't think that makes a difference). The book
> provides a script for performing these backups and my script is mostly
> a copy of that one.
>
> One thing I find interesting is that the book makes no mention of the
> need to backup the online redo logs, just the archived ones. Why are
> the redo logs not necessary? Since they are the ones to which Oracle
> is writing and the archived logs are only created when an old redo log
> is about to be overwritten, it would seem logical that the current redo
> logs are far more important for recent recoveries than the archived
> logs. Right? Wrong? Do I need to backup the current online redo logs as
> well? If so, why does the DBA handbook not mention this fact?
>
> Anyway, here's the script. Any help from anyone who has done this or
> knows how to do it would be greatly appreciated. Thanks.
>
>
>
> --- Rich
>
>
> **** hot_backup.sh script ****
> #!/bin/ksh
>
> $ORACLE_HOME/bin/svrmgrl <<E1
> connect internal as sysdba
>
> alter tablespace system begin backup;
> !cp /u01/oradata/sp/system01.dbf /backup
> alter tablespace system end backup;
>
> alter tablespace temp begin backup;
> !cp /u01/oradata/sp/temp01.dbf /backup
> alter tablespace temp end backup;
>
> alter tablespace tools begin backup;
> !cp /u01/oradata/sp/tools01.dbf /backup
> alter tablespace tools end backup;
>
> alter tablespace rbs begin backup;
> !cp /u02/oradata/sp/rbs.dbf /backup
> alter tablespace rbs end backup;
>
> alter tablespace spdata begin backup;
> !cp /u03/oradata/sp/spdata.dbf /backup
> alter tablespace spdata end backup;
>
> alter tablespace spindex begin backup;
> !cp /u04/oradata/sp/spindex.dbf /backup
> alter tablespace spindex end backup;
>
> # backup the control file
> alter database backup controlfile to
> '/u01/exports/sp/SPcontrolfile.bck';
> !cp /u01/exports/sp/SPcontrolfile.bck /backup
>
>
> # Stop archive logging for the next step
> archive log stop
> exit
> E1
>
> # backup the archived redo log files
> cp /u01/archive/sp/* /backup
>
> # restart archive logging
> $ORACLE_HOME/bin/svrmgrl <<E2
> connect internal as sysdba
> archive log start;
> exit
> E2
>
> # backup the initsp.ora file
> cp $ORACLE_HOME/dbs/initsp.ora /backup
>
>
>
>
>
Received on Wed Mar 07 2001 - 23:34:26 CST