Re: hot backups question for DBA's

Hi Richard,

First, posting the same message twice might be an unfortunate error, but posting to three different groups is sheer overkill.

Second, comments are interspersed below...

"Richard Piasecki" <ogo_at_mailcity.com> wrote in message news:3aa5c095$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.

Putting a tablespace into hot backup mode causes Oracle to start generating block-sized bits of redo (ie, you update a single salary field, and the entire 8K block that record is in is written to the redo system). Accordingly, I'd expect that the mere act of starting to copy the datafiles would induce some extra log switches, and hence some extra archiving -all of which is recorded in the controlfile, which your script doesn't get to back up until *after* the datafiles. Hence, when the control file is restored, it knows about archives which weren't there when the backup started.

If you're telling me that it wants archives which weren't there when the controlfile copy itself was taken, I'd say that you were mistaken. It just can't be <waits to be proved horribly wrong in spectacular fashion>.

HOWEVER.... The disaster scenario you are inducing results in the loss of *all* ONLINE redo logs, correct? Well, clearly the current online redo log has never been archived, and the loss of that file will *always* mean that complete recovery is not possible. When you get prompted to supply an archive which you say shouldn't be requested, I'd be prepared to wager a small sum that it is in fact requesting the supply of that last little bit of redo in the current online log.

Given that that can never be supplied, you'll simply have to do a 'recover database until cancel', and when it prompts for the log that's not there, type the word 'CANCEL', followed by an 'alter database open resetlogs'.

There's nothing you can do about that. Except ask yourself how realistic is your recovery scenario... I hope you have your online redo logs mirrored? Preferably three ways. And how likely is it then that all three copies of the current redo log would be lost? Is this then a reasonable worry?

If you were really concerned about that, then in your script, just before you temporarily switch archiving off, you could issue the 'alter system switch log file' command, then switch off archiving, then copy the archives, then switch archiving back on. That will generate a final archive containing the last possible bit of redo, and that gets included in the backup set. However, you'd also have to arrange for the controlfile to be backed up *after* doing that: if you leave its backup where it currently sits in the script, the controlfile copy won't include that last log switch.

The other thing I'd be dubious about is the way your script backs up the controlfile. It's creating a binary copy of the controlfile, and ANY time you use one of them in a restore scenario, you are required to re-open the database with a resetlogs option... which instantly renders all prior archives and backups useless (except in one weird scenario that you really don't want to know about). Much better, I think, is to instead (or as well as, if you are paranoid) issue the 'alter database backup controlfile to trace' command, which creates a sql script that can reconstruct a missing controlfile and perform complete recovery (given all redo being available, of course) without the need for a resetlogs. In fact, I'd go so far as to say that the only time you ever need a binary backup of a controlfile is when the very structure of the database itself is in need of recovery (for example, if Junior DBA inadvertently drops an entire tablespace).

The only slight downer is that the trace file produced by that command has a weird and wonderful name that includes the current session's PID, and tracking the filename down for the purposes of automatic script-based copying to somewhere safe can be a pain in the butt.

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

Wrong question. They are *very* necessary. The real question is "why can't I back them up like I can the datafiles and controlfiles?" And the answer relates to consistency. A hot-copied datafile is internally inconsistent -but we can apply redo to it to make it consistent. A hot-copied controlfile is likewise inconsistent, but Oracle provided the 'backup controlfile to c:\blah.bkp' command so that we can artifically generate a consistent version. A hot-copied online redo log is also inconsistent, but we can make it consistent by....... er,..... well, ... er, sod-all actually. You can't apply redo to a redo log. And there's no sql command to take an artifically consistent image of one.

Hence, whilst redo logs are very important, it is physically impossible to take a meaningful copy of one. Unless you shut the database down, of course: all cold files produce beautifully consistent image copies.

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

Not quite. An archive is generated as you move OUT of an old redo log, not as you are about to overwrite one (if that's not true for you, it means you haven't got enough online redo log groups).

>it would seem logical that the current redo
> logs are far more important for recent recoveries than the archived logs.
> Right? Wrong?

Wrong. With the exception of the CURRENT redo log (which by definition hasn't been archived yet), then there is nothing in the online logs which isn't also in the archive copies of those logs.

The current redo log is a real bummer, however. Total loss of it WILL mean lost data and transactions. Hence the reason for mirroring it, and hence the 'alter system switch log file' or 'alter system archive log current' commands.

>Do I need to backup the current online redo logs as
> well? If so, why does the DBA handbook not mention this fact?
>

Because to backup hot redo logs is meaningless (see above). It's also very dangerous... I've seen a number of occasions where the DBA restored hot backed-up redo logs on top of the online redo logs, and thus lost far more data than he otherwise would have -before the restore, he had a functional current log. After it, he had a pile of useless poo sitting where the old logs had once been. Having the redo logs in your backup set prompts risky behaviour from DBAs when it comes to the frequently panicky business of conducting a recovery. Best not to have them there at all, and so avoid temptation.

Regards
HJR
> Anyway, here's the script. Any help from anyone who has done this or
> knows how to do it would be greatly appreciated. Thanks.
>
> **** 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 - 00:10:50 CST