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

First, avoid posting in HTML, 'cos most people don't and the resultant mess of tags makes reading very hard.

Second, when you created your database, the syntax runs like this:

Create database blah
maxdatafiles=30
maxlogmembers=16
MAXLOGHISTORY= something

In other words, the create database statement, about to generate a brand new controlfile as it is, is reserving space within that controlfile to store pointers to the relevant files -including possible archivelogs. I can't remember what the default is off hand, but knowing that the default for the maximum number of datafiles is a mere 30, I'm willing to bet it's trivially small.

Time, therefore, to re-create your controlfiles with a much larger maxloghistory setting.

So: issue the 'alter database backup controlfile to trace' command, shutdown the database, and locate the ensuing trace file in the relevant user_dump_dest directory. Strip out the rubbish at the top, stick in a connect string, and edit the relevant parameters, which should be listed in the body of the script.

Now blow away all existing controlfiles (you did take a backup first, didn't you?), fire up server manager, and give it the name of the edited trace file to run as a sql script (ie, "@C:\BLAH\script.sql").

That should re-create the controlfiles, open the database, and you should now see (over time) that old logs are not forgotten so quickly.

As to whether there's an optimal number you should set maxloghistory to, well -my advice is always to set it big. 500 or more will do. It only takes 180 bytes or so for each additional log space, so your controlfile won't get exactly huge.

Regards
HJR
"Frank an der Heiden" <fh_at_energotec.de> wrote in message
news:94hfrs$n$1_at_oxygen.technet.net...
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.

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

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