Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Which Archive-Logs are to delete?

Re: Which Archive-Logs are to delete?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sat, 7 Oct 2000 20:15:07 +1000
Message-ID: <39deea2e@news.iprimus.com.au>

The short answer is that you need to retain all the archives from the time that the last complete backup cycle was started. To this end, it makes no difference whether a 'backup cycle' is accomplished by copying all datafiles in a single 3-hour maintenance window, or if it takes you six weeks to do an entire backup, one tablespace at a time. It's the time at which you START the backup cycle that is critical.

For example, on a small database with three tablespaces, I have the following backup schedule:

MON: Tablespace 1
TUE Tablespace 2
WED Tablespace 3

THU Tablespace 1 (again)
FRI Tablespace 2 (again)
SAT Tablespace 3 (again)
MON Tablespace 1 (third time)
TUE Tablespace 2 (third time and so on)

On the second Monday, I can safely delete all the archives from the prior Monday to Wednesday (because if I had failure on the second Tuesday, I would restore from the earlier "again" cycle, and provided I kept all archives from Thursday onwards, I would be able to recover it.

When we get to the second Thursday, I can delete all the archives from the earlier Thursday to Saturday (because recovery would use files from the "Third time" cycle.

However, this has come up before: whilst it is technically possible to delete prior archives, you would be mad to do so (because unless your middle name is 'The Lord Almighty", you can't guarantee that the new cycle backup is going to be fine -so when I get the failure on the 2nd Tuesday, it's quite possible to find that the prior cycle is dodgy in some way, and you are forced to go one cycle further back than expected -which, of course, if you've been too eager isn't there any more.

Incidentally, it appears from the description of how you perform your backups that you FIRST delete the prior archives, and THEN do the new backup. This is extremely bad practice and renders you in a window of vulnerability. You should only ever delete archives when your new complete backup has successfully been taken (and has been verified as OK).

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------

"Frank an der Heiden" <fh_at_energotec.de> wrote in message
news:8rktii$5eu$1_at_oxygen.technet.net...

> Hi folks,
>
> I have a problem with an Oracle database (Version 8.1.5 NT)
> running in archive_log mode.
>
> In order not to waste harddisk-memory, I want to delete archivelogs which
> are not needed for the recovery procedure.
>
> The normal way was, I deleted my archive-logs, started the backup with
>
> alter system switch logfile (which does an 'alter system checkpoint')
> alter tablespace xxx begin backup
> copied the related datafile to my backup-destination
> finished the backup with alter tablespace xxx end backup.
>
> Now it happend two times during some tests, that the recovery of my backup
> failed, because oracle did await an archivelog which had an 2 days EARLIER
> date
> then the date when the backup was performed.
> For example the sequence_number of the first archivelog AFTER the backup
was
> 120,
> but Oracle requested an archivelog with the sequence number 117 (was
written
> 2 days before
> but deleted when the backup started => memory reasons).
> During the backup ALL datafiles where backed up!
>
> How can this happen?? Why does oracle request this old archivelog? After
> performing
> an 'alter system switch logfile' all transactions should be in the
> datafiles, or not??
>
> OK, I could even store also the old archivelogs, but I do not understand
why
> this
> happend.
>
> No I try to find out, which archive-logs are really necessarry for a
> successful recovery
> of my backup-set by using this join:
>
> SELECT A.SEQUENCE#
> FROM V$LOG_HISTORY A, V$DATABASE B
> WHERE A.FIRST_CHANGE# >= B.ARCHIVE_CHANGE#;
>
> Is this the correct way, has anybody experience or tips for an easier way
to
> find
> out, which archive-logs are possible to delete?
>
> Every help is very welcome because this really is a serious problem for me
>
> Thanx in advance
> Frank
>
>
Received on Sat Oct 07 2000 - 05:15:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US