[Oracle-l] ARCHIVE LOG QUESTION

Denise,
I'm assuming you want to change the results from the query and not
remove the entries from V$ARCHIVED_LOG? Try...

SQL> select thread#, dest_id, sequence#,
decode(deleted,'YES','<deleted>',name) name
2 from (select thread#, dest_id, sequence#, name, deleted,
row_number() over (partition by thread#, dest_id order by sequence#
desc) rn
3 from v$archived_log)
4 where rn <= 3
5 order by 1, 2, 3 desc;

THREAD# DEST_ID SEQUENCE# NAME
---------- ---------- ----------
--------------------------------------------------------------------------------
1 1 64409
/u99/oradata/PRD/archive/1_64409_677946855.arc
1 1 64408 <deleted>
1 1 64407 <deleted>
2 1 65132
/u99/oradata/PRD/archive/2_65132_677946855.arc
2 1 65131
/u99/oradata/PRD/archive/2_65131_677946855.arc
2 1 65130
/u99/oradata/PRD/archive/2_65130_677946855.arc


Not sure if that is "graceful" or not?

Hope this helps? Thanks!

-Tim

Tim,
Thank you. That certainly gives me cleaner, shorter results. Now may I
physically delete those archive logs that are not listed? I get the job
of cleanup crew.

Denise Gwinn
DBA WVNET

I should explain that we have been manually backing up the database
files including the archive logs. There's around two months of logs
sitting out there which I don't think rman knows about. I'm hoping to
get this automated since our backing up of the files seems to not be in
synch with the deletions.

Denise Gwinn
DBA WVNET

Denise,
In my previous reply, I wanted to clarify and separate your evident
reporting requirement from the entirely separate issue of how you should
manage your archived redo log files. I don't think that the two should
be entwined in any way.

First, to make RMAN "aware" of archived redo logfiles, please check the
documentation on the RMAN "catalog" command. It can be as easy as...

RMAN> *catalog archivelog '**/file-name/**';*

...where /*file-name*/ is the full pathname of the archived redo log
file. Once the archived redo logfiles are being managed by RMAN, then
there are several ways to control their retention after they've been
backed up. I strongly recommend that you read the RMAN documentation
online (you can Google for it), in particular the RMAN commands
CONFIGURE ... RETENTION, CROSSCHECK, and DELETE.

Here is one example I use to first verify the status of archived redo
logfiles on disk, then to remove the missing (expired) catalog entries,
and then to remove the "obsolete" files and entries according to the
configured retention policy (from memory, no access to systems at
present)...

RMAN> *allocate channel for maintenance type disk;*
RMAN> *crosscheck backup of backup;*
RMAN> *crosscheck backup of controlfile;*
RMAN> *crosscheck backup of archivelog;*
RMAN> *delete expired;*
RMAN> *delete obsolete;*
RMAN> *release channel;*


Hope this helps...

Thanks!

-Tim

Tim,
At the moment I am not using a recovery catalog so I am assuming I can't
"catalog" the archivelogs. From what I am reading online it appears
that maybe I should create a catalog. Our databases are small, but we
do have around 45-50 of them. Management wants to be able to use Grid
Control for monitoring and administrating all the databases from one
location. For the record, we have hired an outside consultant, but he
went on vacation last month. And I'm still waiting for the promised
classes to be scheduled, but this project needs to be completed.

I have found the Oracle documentation on "Managing the RMAN Repository
Without a Recovery Catalog" as well as the steps on how to create the
recovery catalog. The Oracle documentation does include a section on
"Crosschecking Backups."

Thanks for the suggestions.

Denise Gwinn
DBA WVNET

!ü¨»§¶Ú&j)íj)ò¢êáŠËh¯/Ú³*.¶¶§²+b¢{hDÀ
ÿ*.r‹¥uìi–'"¶\‚
08kazšâqéh‚Ëajܨº¯zf§¹©eɶœ‘çn§ó­…êðŠÇÿŽë-ué^µïÞ™©ÝºÇ‘0`¢)à~Šðj·ÿIéí~º&›#(¶Šè• D…ÿÿþ¢pý?ÿÿÿή("©fzˁïÿÿÿÅ®‰ÿ
éâ±á°Šyÿuéâ±ïæj)ÿÂùÞ·÷»ôèük¢–W«š€%jph®ß܉Ç"»[kø&j)ÿr‰ÿ Ïÿ¢¶œ•ïåýúÞzX¬¶Ïè®ÿ¢¶œ•ïåýúÞzX¬¶Ïè®Òz{ÿNÿ6‹õÓý´×]5ÿoÿßAŒOí4ÿM¹¸ÞrßÑ{ðr³†AALƒ"ÈhºW^Æ™ZŠ{ajÜ…«Þm秙©îjYrm§$Šx.¦Ø^u«Zm«~)^²)Ü–çbžayªÜ†+Þ–ˆ,ý8^­ïìjº.Ûp¢j'¶(~Z ²È­¶)à¢ëm…êÞÂœ„‡hŸûm†)ä®f§’z0±¦èºßÈþhh¦)ඈ¶Øb±«­¢f­yÛ"Ç¨º¶ÚrH§‚êh~Ø^~)^²ÇžšËhž‹[z)ìÊw!Â+a¶zW­Š‰ìü7§ŠÇ†Â)çTÑ:}uÿßöÓ]|ÿ^3ñ®ŠY^®j.•×%y©í…ëâ{©ÿÿ܅竳ð%jÿÿÿÿþm§ÿÿÃÿ~·ž–+-³ú+ƒün– {ú+iÉ^

Mark,
I like your idea about just deleting 'em and going forward with rman.
However, I guess I better read up on CATALOG next and see if I need to
add that into the mix.
Denise Gwinn
DBA WVNET

Denise, you do not delete from v$archive_log. The control file area that keeps track of the archived redo logs is used in a circular fashion.