Re: Which Archive-Logs are to delete?

I am confused. You talk about 'the requested' log, and then state that 'The database did not open with the message that a media recovery for a datafile is necessarry'. If it didn't state recovery is required, it didn't request a log, end of story.

You may be getting a bit confused about the use of the v$archived_log view, which -according to the description on technet- "shows archived logs, including their names. If a log is never archived, the V$ARCHIVED_LOG view does not return any row for the log. If a log is archived twice, V$ARCHIVED_LOG returns two rows for the log. "

In other words, v$archived_log shows you what *has been* archived, not what is actually required for recovery.

On that basis, the view would always show you the checkpoints associated with the archive taken prior to the shutdown of your database, and will always, therefore, be one log behind whatever the current checkpoint number is (because the current log won't have been archived yet).

Hope that helps
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:8rnh5i$738$1_at_oxygen.technet.net...


> hi,

>

> "Howard J. Rogers" <howardjr_at_www.com> schrieb im Newsbeitrag

> news:39deea2e_at_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.

> >

> Normally I do every day a complete online backup of ALL tablespaces of the

> database.

> (the whole database is only about 800 MB)

> After the backup I checked the column CHECKPOINT_CHANGED# in

> V$DATAFILE!

>

> The requested archivelog defineately had an LOWER sequence number

> (checked in V$ARCHIVED_LOG) than the sequence number related to the

> checkpoint written for all datafiles in V$DATAFILE.

>

> This is what I can't understand. Why did oracle request this file?

> I'am running Oracle 8.1.5 Server for NT 4.0, SP6a

>

> It also happend not during a "normal" backup-recover, but after rebooting

> the NT server workstation after updating a NIC driver.

> The database did not open with the message that a media recovery for

> a datafile is necessarry.

>

> > 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.

> >

>

> You are completely right and I already planned to it in this way.

>

> > 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).

> >

> Maybe, my description was not so good, sorry.

> For sure I do not physically remove these archivelogs before the backup

> did perform sucessfully. I only rename these files in order to be able to

> identify

> OLD archivelogs (which are not necessarry any longer) and NEW written

 ones.


>

> > Regards

> > HJR

> > --

>

> Thanx for prompt reply

> Frank

>

>

> --------------------------------------------------------------------------

> > 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

> > >

> > >

> >

> >

>

>