removing arch files from standby DB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: removing arch files from standby DB

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    removing arch files from standby DB

    Currently, I am using RMAN to bacup my standby DB's. With in RMAN,once the DB and arch logs are backedup I remove the arch logs.

    I am considering not backing up my standby DB's since the are basically a copy of my primary DB's there the cleaning up of the arch files will not happen.

    Is there a query I can run that can figure out the arch files that were applied to the standby DB. Once applied I want to remove those arch files from disk.

    thanks to all who answer this post

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    V$archived_log

    Try:
    Code:
    SELECT SEQUENCE#, THREAD#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#, THREAD#;


    PS: You can save cpu's & storage in primary if you do your backups from standby only.
    .
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2009
    Posts
    91
    And this will give you the name of the archive file or do I have to write some code to parse the seq number from the archive file based on the output of the query.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Talking Seq#

    If you are smart, you would have configured the archive log format (name) to contain the SEQUENCE# and THREAD#, kinda like this:

    Code:
    log_archive_format='orcl_%t_%s_%r.dbf'
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Feb 2009
    Posts
    91
    I did have the foresight to include the seq number as part of my arch name. I can certainly parse the name and get the right answer but I was hoping there was some table that stored the name of the whole arhive file so I can use that data instead of parsing every name.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Talking desc?

    Quote Originally Posted by PaulH View Post
    I did have the foresight to include the seq number as part of my arch name. I can certainly parse the name and get the right answer but I was hoping there was some table that stored the name of the whole arhive file so I can use that data instead of parsing every name.
    Did you try desc v$archived_log?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Feb 2009
    Posts
    91
    I will check out that table and see if it has what I need.

    Thanks for your help

  8. #8
    Join Date
    Feb 2009
    Posts
    91
    Let me try this again. I want to be able to run a script to removed all applied
    archive files that are part of my stand by database.

    When I query the V$ARCHIVED_LOG, the colums where APPLIED='YES' no
    longer have the archive file path associated with them. The name becomes
    is changed to my database name.

    Example:

    NAME APP
    stdby YES

    Those files, which have not been applied still have the valid path name (see
    below)

    NAME APP
    /t4data/oracle_data/pri/arch/arch_1347_1_684356798.arc NO

    Does anybody know how I can generate a script to remove arc files from
    disk that have been applied ONLY!!!

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Have you tried this

    in standby do
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
    and then

    from RMAN at the standby database
    DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO SBT COMPLETED BEFORE ‘SYSDATE-7’


    if you go down the route currently you are on you still dont need the archivelog names just use

    RMAN>delete archivelog until squence#

    or
    RMAN>delete archivelog until squence# noprompt


    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width