Re: Is there a limit of rows in V$ARCHIVED

From: David Fitzjarrell <oratune_at_aol.com>
Date: Mon, 22 Jan 2001 15:42:44 GMT
Message-ID: <94hkdi$ltq$1@nnrp1.deja.com>

In article <94hfrs$n$1_at_oxygen.technet.net>, "Frank an der Heiden" <fh_at_energotec.de> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0018_01C08487.914407A0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> hi folks,
>
> I have a problem with the mentioned v$ view.
>
> In order to delete all obsolete and not longer necessarry
> archiv-logs, I wrote a little tool which gets the lowest
> sequence-number which is needed, to recover the oldest
> backup-set.=20
>
> with the help of the view v$archived log, i want to get the filenames
of =
> all
> archive-logs, whose sequence-number is LOWER then
> the lowest needed sequence-number. all these archiv-logs
> can be deleted without danger.
>
> for example: the lowest sequence number is 112.
>
> d:\orant\oradata\mdb\archiv\archiv000110 <- to delete
> d:\orant\oradata\mdb\archiv\archiv000111 <- to delete
> d:\orant\oradata\mdb\archiv\archiv000112
> d:\orant\oradata\mdb\archiv\archiv000113
> d:\orant\oradata\mdb\archiv\archiv000114
>
> normally this works without any problem, but now it happend
> two times, that not all existing archivelogs appear in the=20
> v$archived_log view -> not all logs are deleted.
>
> my question is: is there a limitation of rows in v$archived_log?
> how can it happen, that not all logs which are written by
> oracle appear in this view.
> is there a possibility, to delete them manually?
>
> thanx in advance
>
> --=20
> MfG / Best Regards
>
> Frank an der Heiden
> ESA - Energotec Systeme und Automation GmbH
> Tel: +49 (0)211 91440-19
> Fax:+49 (0)211 669343
> email: fh_at_energotec.de
> "I came, saw and installed everything new!"
>
> ------=_NextPart_000_0018_01C08487.914407A0
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; =
> charset=3Diso-8859-1">
> <META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3DArial size=3D2>hi folks,</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>I have a problem with the mentioned
v$=20
> view.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>In order to delete all obsolete and
not =
> longer=20
> necessarry</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>archiv-logs, I wrote a little tool =
> which gets the=20
> lowest</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>sequence-number which is needed, to =
> recover the=20
> oldest</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>backup-set. </FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>with the help of </FONT><FONT =
> face=3DArial size=3D2>the=20
> view v$archived log, i want to get the filenames of all</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>archive-logs, whose sequence-number
is =
> LOWER=20
> then</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>the lowest needed sequence-number.
all =
> these=20
> archiv-logs</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>can be deleted without =
> danger.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>for example: the lowest sequence
number =
> is=20
> 112.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial =
> size=3D2>d:\orant\oradata\mdb\archiv\archiv000110 <- to=20
> delete</FONT></DIV>
> <DIV>
> <DIV><FONT face=3DArial =
> size=3D2>d:\orant\oradata\mdb\archiv\archiv000111 <- to=20
> delete</FONT></DIV>
> <DIV>
> <DIV><FONT face=3DArial=20
> size=3D2>d:\orant\oradata\mdb\archiv\archiv000112</FONT></DIV>
> <DIV>
> <DIV><FONT face=3DArial=20
> size=3D2>d:\orant\oradata\mdb\archiv\archiv000113</FONT></DIV>
> <DIV>
> <DIV><FONT face=3DArial=20
> size=3D2>d:\orant\oradata\mdb\archiv\archiv000114</FONT></DIV>
> <DIV><FONT face=3DArial =
> size=3D2></FONT> </DIV></DIV></DIV></DIV></DIV>
> <DIV><FONT face=3DArial size=3D2>normally this works without any =
> problem, but now it=20
> happend</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>two times, that not all existing =
> archivelogs appear=20
> in the </FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>v$archived_log view -> not all
logs =
> are=20
> deleted.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>my question is: is there a
limitation =
> of rows in=20
> v$archived_log?</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>how can it happen, that not all logs
=
> which are=20
> written by</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>oracle appear in this =
> view.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>is there a possibility, to delete
them=20
> manually?</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>thanx in advance</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>MfG / Best =
> Regards</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Frank an der Heiden<BR>ESA -
Energotec =
> Systeme und=20
> Automation GmbH<BR>Tel: +49 (0)211 91440-19<BR>Fax:+49 (0)211 =
> 669343<BR>email:=20
> </FONT><A href=3D"mailto:fh_at_energotec.de"><FONT face=3DArial=20
> size=3D2>fh_at_energotec.de</FONT></A><BR><FONT face=3DArial size=3D2>"I
=
> came, saw and=20
> installed everything new!"</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV> </DIV></BODY></HTML>
>
> ------=_NextPart_000_0018_01C08487.914407A0--
>
>

The V$ARCHIVED_LOGS view displays archived log information from the control file including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN copy command.

An init<SID>.ora parameter that affects this is control_file_record_keep_time, which specifies the minimum number of days that must pass before a reusable record in the controlfile can be reused. I would imagine that the archive log records are reusable records, therefore any archive log over the control_file_record_keep_time will not appear in the controlfile. Check the dates on the 'missing' archive logs and determine if they coincide with the value set for control_file_record_keep_time. I expect that they will.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Mon Jan 22 2001 - 09:42:44 CST

Re: Is there a limit of rows in V$ARCHIVED_LOG