Forum Stats

  • 3,825,012 Users
  • 2,260,455 Discussions
  • 7,896,382 Comments

Discussions

RMAN How to do a periodic full database backup to delete archivelogs

OlgaTogi
OlgaTogi Member Posts: 19
edited Apr 15, 2020 8:37AM in Recovery Manager (RMAN)

Hello,

I am a newby in RMAN.

My DB is in the Archive Mode:

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        USE_DB_RECOVERY_FILE_DEST

Archive logs are accumulated and I can not allow them to take all disc space.

Periodically I run:

backup archivelog until time 'sysdate-60' delete all input;

But backupsets are accumulated too. And the disc space of FRA is increasing.

Should I to do a periodic full database backup and delete all backupsets and archivelogs?

How to do this?

Or should I do something else?

Thanks,

Olga.

Tagged:
OlgaTogiEdStevens

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Apr 13, 2020 12:07PM
    OlgaTogi wrote:Hello, I am a newby in RMAN.My DB is in the Archive Mode:SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTArchive logs are accumulated and I can not allow them to take all disc space.Periodically I run: backup archivelog until time 'sysdate-60' delete all input;But backupsets are accumulated too. And the disc space of FRA is increasing.Should I to do a periodic full database backup and delete all backupsets and archivelogs?How to do this?Or should I do something else?Thanks,Olga.

    You definately should be doing a periodic full database backup. How else do you ever expect to be able to recover a broken database?

    Standard, textbook practice is to schedule an incramental level 0 backup once a week. (an incremental level 0 is a full backup that is also part of a regimine of incrementals).  Take an incremental level 1 (differential or cumulative) the other 6 days.  Every backup job should include backup of archivelogs with a DELETE INPUT option.  It should also include a DELETE NOPROMPT OBSOLETE BACKUP;  That will keep your backupsets cleaned out, resulting in stable use of FRA.

    Also, I'm not a fan of deleting archivelogs based on a date or age.  That can go haywire if you miss a scheduled job.  Instead, look at 'not backed up x times'.

    OlgaTogi
  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Apr 13, 2020 2:14PM

    You use FRA, in which case Oracle server will automatically remove obsolete backup if there is free space pressure based on your RMAN retention and archive log deletion policy.

    Your backup could simply be:

    RMAN> backup incremental level 0 database plus archivelog delete input;

    You can do a level 0 every Sunday, and level 1 any other day, for example.

    Then define a reasonable RMAN retention policy by recovery window or redundancy (please see the documentation), as well as an archivelog deletion policy if required (or leave the default, which is none). Be sure to configure the control_file_record_keep_time parameter accordingly, which must be higher than your retention policy. When you are running out of free space in FRA, oracle will automatically delete obsolete backups.

    OlgaTogi
  • OlgaTogi
    OlgaTogi Member Posts: 19
    edited Apr 14, 2020 9:28AM

    Thank you very much.

    It was very helpful.

    Can you answer a couple of additional questions:

    - Backup incremental level 0 or level 1 won't delete archive log and backupset files themself without any additional commands?

    - Do backup incremental level 0 or level 1 change the current sequence# of archivelogs? Will the archivelogs structure be affacted by the incremental full backups?

    - That is the directory where backup incremental level 0 or level 1 physical files will be created?

    - DELETE NOPROMPT OBSOLETE BACKUP will delete backupset files from the disk? Will it affect archive log files?

    - Will next backup incremental level 0 clean out the previous results of incremental level 0 or incremental level 1? Or should I delete them manually?

    Thanks, Olga.

  • OlgaTogi
    OlgaTogi Member Posts: 19
    edited Apr 14, 2020 9:29AM

    Thank you very much.

    It was very helpful.

    Can you answer a couple of additional questions:

    - Backup incremental level 0 or level 1 won't delete archive log and backupset files themself without any additional commands?

    - Do backup incremental level 0 or level 1 change the current sequence# of archivelogs? Will the archivelogs structure be affacted by the incremental full backups?

    - That is the directory where backup incremental level 0 or level 1 physical files will be created?

    - DELETE NOPROMPT OBSOLETE BACKUP will delete backupset files from the disk? Will it affect archive log files?

    - Will next backup incremental level 0 clean out the previous results of incremental level 0 or incremental level 1? Or should I delete them manually?

    Thanks, Olga.

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Apr 14, 2020 11:49AM

    You are not going to make friends packing a billion questions in one thread. Please educate yourself about RMAN fundamentals, otherwise it will be waste of time responding, and a frustrating experience on your end. There is plenty of documentation available:

    For example:

    https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-backup-concepts.html#GUID-B3380142-ABCD-437F-9E…

  • OlgaTogi
    OlgaTogi Member Posts: 19
    edited Apr 14, 2020 11:53AM
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Apr 14, 2020 4:49PM
    OlgaTogi wrote:Thank you very much.It was very helpful.Can you answer a couple of additional questions:- Backup incremental level 0 or level 1 won't delete archive log and backupset files themself without any additional commands?

    A BACKUP command creates a backup.  Full stop.  It does not delete anything.  If truly using the FRA, and a BACKUP causes space issues on the FRA, the FRA mechanism might delete any OBSOLETE backups to relieve that space pressure.  But that is an FRA issue, not something with the rman BACKUP command.

    - Do backup incremental level 0 or level 1 change the current sequence# of archivelogs? Will the archivelogs structure be affacted by the incremental full backups?

    If I recall correctly, certain configuration options will cause a backup to archive the current online redo and back up the resulting archivelogs.  I don't know what you mean by "archivelogs structure". 

    - That is the directory where backup incremental level 0 or level 1 physical files will be created?

    What directory are you asking about?  The FRA?  The backups are written wherever you specify.

    - DELETE NOPROMPT OBSOLETE BACKUP will delete backupset files from the disk? Will it affect archive log files?

    It will delete obsolete backups.  Some of those backups may be backups of archivelog files.  It will not delete the archivlog files themselves. 

    - Will next backup incremental level 0 clean out the previous results of incremental level 0 or incremental level 1? Or should I delete them manually?

    As stated earlier, BACKUP only creates backups. It does not do any housekeeping.  You should not delete any backups "manually", if by "manually" you mean by using OS commands to delete the  backups files.  Use rman DELETE OBSOLETE and let Uncle Oracle figure out what can be safely deleted.

    Everyone who reads the thread can read the entire thread.  There is nothing to be gained (and some reputation to be lost) by posting exactly the same response multiple times, as responses to multiple people (me and Dude!, in this case)

    And as Dude! said, you really need to spend some time in the two RMAN manuals - Reference and User Guide.  The docs can be overwhelming to a beginner and sometimes confusing to even a seasoned pro.  There's no shame in asking for clarification of a point you read in the docs.  But as a professional, the docs should be your starting point.

  • Dude!
    Dude! Member Posts: 22,829 Black Diamond
    edited Apr 14, 2020 10:09PM

    I'd like to add that RMAN can be very confusing for beginners because database recovery follows concepts that are very different compared to backup and restoring a file system. Most people will not understand the difference between RMAN restore and RMAN or SQL recovery. Trial on error with RMAN can easily produce misconceptions. You cannot use RMAN unless you know what's really happening. It is very important to understand RMAN fundamentals. The best answer isn't going to be useful when it won't be understood because the background doesn't exist, no denigration intended.

    EdStevens
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Apr 15, 2020 8:37AM
    Dude! wrote:I'd like to add that RMAN can be very confusing for beginners because database recovery follows concepts that are very different compared to backup and restoring a file system. Most people will not understand the difference between RMAN restore and RMAN or SQL recovery. Trial on error with RMAN can easily produce misconceptions. You cannot use RMAN unless you know what's really happening. It is very important to understand RMAN fundamentals. The best answer isn't going to be useful when it won't be understood because the background doesn't exist, no denigration intended.

    Very true.  I'd actually forgotten that I really struggled with rman when first approaching it.  It came back to what I observed about Oracle docs in general when I first start working with Oracle.  That is, when reading Oracle docs it often seems that one has to understand everything else before one can understand any one thing.  Chicken and Egg.  Between the rman Reference and rman User Guide, I was spinning my wheels.  Finally bought a copy of Oracle Database Backup and Recovery (version-specific editions) by Robert Freeman and Matthew Hart.  That was much more readable and really helped clarify a lot.