hot backups question for DBA's

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.

• 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 Tue Mar 06 2001 - 23:04:04 CST