Re: Archive log questions

<argosy22_at_my-deja.com> wrote in message news:8idgk8$qf4$1_at_nnrp1.deja.com...
> Hi all,
>
> I've researched this a lot, but I just want to confirm some things.
>
> We run our databases in archive mode. I have an script
> to do a hot backup. I backup the tablespaces, control files,
> .ora files, and then log files.
>
> Questions:
>
> - should I do a:
> alter system switch logfile
> or
> alter system archive log current
> or
> alter system archive log all
> ???
>

It is recommended to do an 'alter system switch logfile' at the *end of each tablespace backup*, since this causes a checkpoint to happen, and this will ensure that the tablespace just taken out of hot backup mode will have its header correctly updated with the latest checkpoint sequence number (remember that the header checkpoint number is frozen from the point of issuing the 'begin backup' command -hence, until you force that checkpoint, there is a risk of an instance failure, and on subsequent startup, the tablespace will appear to be inconsistent with the rest of the database.

> - I am currently doing this after backing up the tablespaces,
> and before backing up the log files. Correct?
>

Log switch after each tablespace backup, that's correct.

Strictly speaking, it's not necessary, because if you wait long enough, a perfectly natural log switch will take place that will re-synch all the headers. It's just that you're in a window of vulnerability until that happens.

>
> - should I keep any of the archive logs? If so, how many,
> or how far back?
>

You need, at a bare minimum, to keep ALL of the archives *from the time of starting the database backup*. In other words, say you backed up a third of your database each night, starting Monday evening, and finishing with the last few tablespaces on Wednesday: in that case, you need to keep every single archive generated since Monday. If one of the datafiles that was backed up on Monday evening fails on Thursday morning, you'd restore it from Monday night's backup, and would need to bring it forward in time to Tuesday, then Wednesday... and from there, roll it forward to wherever the rest of the database got to on Thursday.

The general rule with archives is that you need to keep all of them from the point of completion of the last successful backup (because that's where you'd restore any failed datafiles from).

However, the practical rule is that you really ought to invest in plenty of hard disk space, and keep as many archives hanging around on disk as you possibly can -on the grounds that backups can sometimes fail, and you suddenly find yourself relying on one you took a while ago -and hence needing all the archives since then. It's also a mean time to recover issue: if every archive is whisked off to tape as quickly as possible, it's a pain in the neck to restore the damn things before you can start recovering your database!

> To recover to the point in time that I did the backup,
> I won't need any of the archive log files older than the backup
> time, will I?
>

Correct. But you are rather assuming that the latest backup went without a hitch (and that none of it will ever get accidentally erased, corrupted, over-written or whatever).

>
> However, if tommorrow, I want to recover the database
> to tommorrow's current time, I will need all the archive log
> files created since backup time, correct? In which case,
> I shouldn't need any archive logs previous to the backup,
> correct?

I think you are saying the same thing as before, in which case -you are still correct! But don't be mean with storage capacity for archives, and don't be too eager to get rid of them.
>
 

>
> I copy files to another drive, and then back them up to tape.
>
>

Perfectly good methodology.

> We run on Unix. In the script, I use:
>
> sqlplus ..... << THE_END
>
> @backupscript
>
> THE_END
>
> - is there a way to capture the return code from Oracle to
> verify that the backup worked correctly?
>
>

Not really, since the actual copying of data files is done with O/S commands. You could perhaps script a whole lot of dbverify jobs on the copies, and thereby check that they all exist, and that none of them have internal corruption problems. Dbverify outputs logs of its results.

Hope this helps,
HJR
> Yes, I should know all this. But, I keep getting more and
> more work to do, which keeps spreading me thin.
>
>
> Thanks,
>
> Argosy
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Jun 18 2000 - 00:00:00 CDT