RMAN - removing old catalog entries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 15

Thread: RMAN - removing old catalog entries

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    RMAN - removing old catalog entries

    I want to remove RMAN catalog entries older than 30 days. The backup pieces have long since been moved off the system.

    I understand I can use the RMAN CHANGE command to remove just the catalog records but I can't find the syntax anywhere. Can anyone help me?

    Here's my best guess at piecing it together:
    CHANGE BACKUP COMPLETED BEFORE 'SYSDATE-30' UNCATALOG;

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Is this what you are looking for (CHANGE)?

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Quote Originally Posted by ebrian
    Is this what you are looking for (CHANGE)?
    Yea, I read the docs. They're just not clear on the syntax for the example above.

    Cheers
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

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

    Does this work

    CHANGE BACKUPSETS COMPLETED BEFORE 'SYSDATE-30' UNCATALOG;

    if that doesnt work one other way of doing that would be

    http://www.orafaq.com/scripts/unix/purgecat.txt

    Just change the line
    echo "CHANGE BACKUPSET $key DELETE;" >>$CMDFILE

    in that script to
    echo "CHANGE BACKUPSET $key UNCATALOG;" >>$CMDFILE

    and also this line
    list backupset of database
    from time 'SYSDATE-3000' until time 'SYSDATE-$DAYSTOKEEP'

    Hmm Frank Nuade has done some smart MetaProgramming i beleive ;-)

    regards
    Hrishy
    Last edited by hrishy; 07-02-2008 at 05:03 AM.

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Exactly what I was looking for. Thank you Hrishy!
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    RMAN> change backupset completed before 'sysdate-30' uncatalog;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of uncatalog command at 07/02/2008 12:55:34
    RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET

    Found this...

    Oracle Error : RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET
    Cause: The CHANGE BACKUPSET .. UNCATALOG command was entered. The UNCATALOG operation is not supported with backup set.
    Action: Use CHANGE BACKUPSET .. DELETE instead.

    Then did this:
    RMAN> change backupset completed before 'sysdate-30' delete;
    ...
    Do you really want to delete the above objects (enter YES or NO)? YES

    But when I "list backupset completed before 'sysdate-30';" they're all still there... urgh

    Any ideas?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    noprompt worked like a charm. Thanks.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  8. #8
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    For the benefit of posterity...

    run {
    crosscheck backup completed before 'sysdate-30';
    delete noprompt expired backup completed before 'sysdate-30';
    }
    exit;

    Logging the output was a little screwey. The command line LOG didn't work at all, "spool log to" worked in the script, but .. and here's the weird part .. only AFTER the "exit;". If "spool log off" was before the "exit;", it created a 0 byte file.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  9. #9
    Join Date
    Apr 2006
    Posts
    377
    Thanks for posting back your results.

    Not exactly sure how you are executing the RMAN script, but two examples to get logging to occur are as follows:

    If you have a file called rman.cmd as:

    Code:
    run {
    crosscheck backup completed before 'sysdate-30';
    delete noprompt expired backup completed before 'sysdate-30';
    }
    You can run the command with proper logging using:

    Code:
    rman target / log=/tmp/rman.log @/tmp/rman.cmd
    For the spooling option, you can create a rman.cmd file as (notice NO 'spool log off'):

    Code:
    spool log to /tmp/rman.log;
    run {
    crosscheck backup completed before 'sysdate-30';
    delete noprompt expired backup completed before 'sysdate-30';
    }
    And run it as follows:

    Code:
    rman target / @/tmp/rman.cmd

  10. #10
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Right. I tried it both ways, but in **this case only** spooling inside the script and with the exit prior to "spool log off" is the only way I can get it to spool the output.

    This is not typical behavior. Must be something about the contents of the script .. like noprompt or something.

    I have other rman scripts that behave as expected whether the logging is in the script or on the command line.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

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