RMAN archive log best practices

Hello and thanks in advance for any help you can give.

I am finally joining the world of RMAN and need some help determining the best way to handle the archive logs.

The question is at the end, so please keep reading.

Thanks,

Evan

Which is better, "keep until" or set management class to retain forever and delete the unneeded logs.

Here are our requirements:
We need to take

    daily (keep 14 days),
    weekly (keep 5 weeks),
    monthly (keep 12 months) and
    annual (keep 2-7 years depending on app) backups.

We need to minimize our tape usage (management has spoken).

We need point in time recovery to X months (depending on application), then snapshot recovery (to end of month, end of year) after that.

We need to clean out the logs frequently due to disk constraints, so we currently copy logs to tape, then delete logs as often as every 15 minutes.

Our environment is

    Oracle 9.2.0.x to 10.1.0.3 on Solaris, 64 bit.     TDPO (IBM Tivoli tape interface) 5.2     The management class parameters include "Keep forever, can delete".

Problem:

1. Backup all archives (delete off disk, keep forever):

	backup filesperset 10
	format '%d_arch_%t_%s_%p'
	tag 'SIDNAME arch'
	(archivelog like '/u01/app/oracle/admin/SIDNAME/arch/%'
         delete input);

2) Take yearly database backup (keep logs for retain time, don't delete)

	backup database
	keep until time='sysdate+$RETAIN' logs
	format '%d_DB_%t_%s_%p_%T' tag '$DB $DAY $DESC';

	$RETAIN may be 1095(365*3).

If the archive backup gets to the log first, the database backup does not find it. If the database backup gets to it first, then there are two copies in the catalog (and on tape).

3) When the expire job runs, how do we know which logs to expire? After the end of year one, we need to keep the ones needed for the annual restore for year 3, but no other logs.

Do you use KEEP UNTIL?
Do you let it go forever, but then run expire jobs? Are we completely off base?

Is there a web page I should go to to find information (if OTN, please include search criteria, as I get too many hits, or none, depending on my selection criteria)? Received on Wed Jun 01 2005 - 08:36:03 CDT