Re: Which Archive-Logs are to delete?

Incidentally, though I forgot to mention it, the view that *does* indeed show you what archives will be needed in any given recovery situation is something like v$recovery_log (though it might be v$recover_log -I can never remember which, because there's also a v$recover_file which shows you what datafiles require recovery -though that might be v$recovery_file. All I remember is that if one ends in '-ery', the other ends in '-er'. Lovely consistency on the part of Oracle, I agree.

Regards
HJR

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

"Howard J. Rogers" <howardjr_at_www.com> wrote in message
news:39dff31a_at_news.iprimus.com.au...


> 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

> > > >

> > > >

> > >

> > >

> >

> >

>

>