FAQ
I have set up rman using flash_recovery. I've run a couple of backups
for testing. When I do the command:
SQL> archive log list

It returns oldest online sequence of 926; next log sequence to archive
is 928.

However when I do:
SQL> select name from v$archived_log;

It returns 664 rows.

Is there a graceful way to get rid of the first 661 rows?

Denise Gwinn
DBA WVNET

Search Discussions

  • Tim Gorman at Nov 10, 2011 at 12:05 am
    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

    On 11/9/2011 2:51 PM, Denise Gwinn wrote:
    I have set up rman using flash_recovery. I've run a couple of backups
    for testing. When I do the command:
    SQL> archive log list

    It returns oldest online sequence of 926; next log sequence to archive
    is 928.

    However when I do:
    SQL> select name from v$archived_log;

    It returns 664 rows.

    Is there a graceful way to get rid of the first 661 rows?

    Denise Gwinn
    DBA WVNET
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l
  • Denise Gwinn at Nov 10, 2011 at 12:59 am
    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
    On 11/9/2011 7:04 PM, Tim Gorman wrote:
    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

    --
    http://www.freelists.org/webpage/oracle-l
  • Denise Gwinn at Nov 10, 2011 at 1:29 am
    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
    On 11/9/2011 8:14 PM, Guillermo Alan Bort wrote:
    do the logs physically exist? how about a "backup archivelog <...>
    delete input? I'm not sure that would clean the view up.

    cheers
    Alan.-

    --
    http://www.freelists.org/webpage/oracle-l
  • Tim Gorman at Nov 10, 2011 at 4:50 pm
    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

    On 11/9/2011 6:31 PM, Denise Gwinn wrote:
    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
    On 11/9/2011 8:14 PM, Guillermo Alan Bort wrote:
    do the logs physically exist? how about a "backup archivelog<...>
    delete input? I'm not sure that would clean the view up.

    cheers
    Alan.-

    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l
  • Denise Gwinn at Nov 10, 2011 at 5:38 pm
    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
    On 11/10/2011 11:48 AM, Tim Gorman wrote:
    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Nov 10, 2011 at 2:37 am
    !ü¨»§¶Ú&j)íj)ò¢êáŠËh¯/Ú³*.¶¶§²+b¢{hDÀ
    ÿ*.r‹¥uìi–'"¶\‚0 8kazšâ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É^
  • Denise Gwinn at Nov 10, 2011 at 3:09 am
    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
    On 11/9/2011 9:36 PM, Bobak, Mark wrote:
    If you want to maintain your history, as you transition to RMAN, you
    could explicitly CATALOG the price logs that you have manually backed
    up. Otherwise, just delete 'em and use RMAN going forward.

    /Sent from my Motorola ATRIXâ„¢ 4G on AT&T/


    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark at Nov 10, 2011 at 2:15 pm
    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.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Denise Gwinn
    Sent: Wednesday, November 09, 2011 4:52 PM
    To: oracle-l@freelists.org
    Subject: ARCHIVE LOG QUESTION

    I have set up rman using flash_recovery. I've run a couple of backups
    for testing. When I do the command:
    SQL> archive log list

    It returns oldest online sequence of 926; next log sequence to archive
    is 928.

    However when I do:
    SQL> select name from v$archived_log;

    It returns 664 rows.

    Is there a graceful way to get rid of the first 661 rows?

    Denise Gwinn
    DBA WVNET
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 9, '11 at 9:52p
activeNov 10, '11 at 5:38p
posts9
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase