Re: archivelog hot and cold backup preparing for a smooth recovery

I've put some answers inline with your questions in the hope that this may help.

> for each important_tablespace in (select name from v$tablespace;)
> do
> alter tablespace important_tablespace begin backup;
>
> copy the datafiles* to somewhere safe
>
> alter tablespace important_tablespace endbackup;
>
> alter system switch logfile;

At this point you must backup the archive redo log files!

> done
>
> (pardon the pseudocode syntax)
>
> not sure of the frequency of this yet
>
> * datafiles -> I copy the name.V$DATAFILE right which are
> all the datafiles for the TS# which are listed when I
> v$tablespace for each of my tablespaces (I think)
>
> I thought that what alter tablespace my_data begin backup did, was
> to stop user processes from writing to the database datafile, so
> that a backup process could get a copy of it, in a consistent
> shape.

During a HOT BACKUP oracle does not stop writing to your datafiles! What it does is put extra information into the redo logs files to enable it to rebuild the data files during a recovery.

> Also I thought that the only things stored in
> log_archive_dest/log_archive_format was the old (now perhaps not
> all needed??) redo logs??
>

You can't backup your online redo log during a hot backup. When you are archive log mode, which you need to be to do a HOT BACKUP, and are finished with an online redo log oracle copies it to the archive log destination "log_archive_dest". To get all the information you now need to successfully recover after a HOT BACKUP you must issue a switch logfile and then backup as a minimum the archive logfiles created while the backup was in progress!! Most DBA's simply backup the entire contents of the archive log directory.

>
> Re-reading HJRs file more he suggests that all I need to do are
> back up these datafiles (I guess the online reDo Logs he says to
> ignore are the ones stored in log_archive_dest/log_archive_format
> or are there some others?), and the control files,
> password files, and prarmeter files.
>

No the online redo logs he says to ignore are the ones listed in v$logfile system views and you can read about them in chapter 6 of the Oracle 8i Administrators Guide found here
http://documentation.oracle.com/database_mp_8i.html. You can't backup your online redo log during a hot backup.

>
> as well as my HOT BACKUP, on one night per week, I was thinking of
> doing something like a full COLD DATABACK up with something like
> this below, but I wonder if there is any purpose?? Perhaps just a
> weekly export would be better here instead??
>

You do not need to do a cold backup to be able to recover your database.

>
> sqlplus <<EOF
> connect / as sysdba;
> shutdown immediate;
> exit;
> EOF
>
>
> then squirting these files I previously found in
> select name from V$DATAFILE ;
> select name from V$CONTROLFILE;
> select member from V$LOGFILE;
>
> into tar and off to somewhere safe
>
> start the database back up again with a
>
> sqlplus <<EOF
> connect / as sysdba;
> startup;
> exit;
> EOF
>
> (comments welcome on anything you feel like here)
>

This would work.

> Now, comes my question
>
> Do I need to keep my previous weeks archive redo information, if I
> am actually shutting down the database and doing a weekly cold
> backup? can I just keep the relevant weeks redo archives with each
> backup??
>

Yes you are correct and you can just keep the relevant weeks archive log redo information.

> In fact, is there any point in doing the HOT BACKUP at all if I am
> running in ARCHIVELOG mode, and doing a weekly COLD BACKUP (keeping
> that weeks archived redo logs). Then I will only need to roll
> forward from the last cold backup in the event of a failure? If
> there is no point in doing the hot backup, then I can mix the
> export and coldbackup at the weekly downtime, and just keep running
> in ARCHIVELOG mode to roll forward in the event of a crisis and
> requsite restore.
>

The point in doing a HOT BACKUP is that it is HOT and you don't need to shut your database down to do it and thus users are not inconvenienced! Otherwise you are correct that you can just do COLD BACKUP, however I would advise you that it is a good idea to backup to tape your archive log files on a regular basis as they are crucial to a complete recovery. Also your roll forward will take longer to do the more archive log files it has to go through so for business reasons you may be required to do the HOT BACKUP's to make the recovery quicker.

> (scratches head) There has to be something I have missed in this
> maybe its the flushing of the re-dolog to the archive ...
>

The risk you are running is that if you lose just one of your archive log files during this week, say on Tuesday, and then the database crashes, say on Friday, you will not be able to recover any further forward than the last archive log file you have on the Tuesday. Thus for safety you need to backup your archive log files.

>
> Thanks folks

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com Received on Fri Feb 01 2002 - 04:44:23 CST