5 Replies Latest reply: Sep 30, 2013 7:45 AM by Anar Godjaev RSS

    How to purge RMAN info?

    user130038

      Hi there

       

      When the backups taken through RMAN have been deleted using RMAN and they are no longer available, is there a way to purge the information of such backups to be removed/purged from the database?

       

      Please let me know.

       

      Best regards

        • 1. Re: How to purge RMAN info?
          Javier Francisco Ruiz

          If the backup was deleted using RMAN the information on the backup should no longer appear in the controlfile or catalog.

           

          Try a crosscheck and then do a delete expired.

           

          RMAN>crosscheck;

           

          RMAN>delete expired backup;

          • 2. Re: How to purge RMAN info?
            user130038

            OK thanks - Perhaps the RMAN Job info stays in the database even though the backup no longer exists.

             

            I am using following SQL command to monitor RMAN jobs:

             

            select

              j.session_recid, j.session_stamp,

              to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,

              to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,

              round(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,

              decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',

                                                 3, 'Tuesday', 4, 'Wednesday',

                                                 5, 'Thursday', 6, 'Friday',

                                                 7, 'Saturday') dow,

              j.elapsed_seconds, j.time_taken_display,

              x.cf, x.df, x.i0, x.i1, x.l,

              ro.inst_id output_instance

            from V$RMAN_BACKUP_JOB_DETAILS j

              left outer join (select

                                 d.session_recid, d.session_stamp,

                                 sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,

                                 sum(case when d.controlfile_included = 'NO'

                                           and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,

                                 sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,

                                 sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,

                                 sum(case when d.backup_type = 'L' then d.pieces else 0 end) L

                               from

                                 V$BACKUP_SET_DETAILS d

                                 join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count

                               where s.input_file_scan_only = 'NO'

                               group by d.session_recid, d.session_stamp) x

                on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp

              left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id

                               from GV$RMAN_OUTPUT o

                               group by o.session_recid, o.session_stamp)

                ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp

            order by j.start_time;

             

            And it is listing those backups that I have already deleted using RMAN. The "RMAN> list backup summary;" command does not display the same information as this query. The backups already deleted using RMAN are not displayed when I run the "RMAN> list backup summary;" command.

             

            I think I am looking at two different sets of information - right?

             

            Best regards

            • 3. Re: How to purge RMAN info?
              Hemant K Chitale

              I think that tracking the backups (using CROSSCHECK and DELETE) is different from tracking the backup job logs / run info.   So you may still be able to query information about backup jobs even if the created backups have been deleted.

               

               

              Hemant K Chitale

              • 4. Re: How to purge RMAN info?
                Javier Francisco Ruiz

                Yes two different set of data. The tables will contain more data the via rman it is looking at the data in the controlfile and when you issue a delete that data is removed from controlfile but will remain in the tables.

                 

                If you only want to see data for the past view days add the date column to the where clause.

                • 5. Re: How to purge RMAN info?
                  Anar Godjaev

                  Do not use rm to remove files. You must do it via RMAN.

                   

                  $ export NLS_DATE_FORMAT='DD-MM-YY HH24:MI'

                   

                  $ rman target / NOCATALOG

                   

                  RMAN > crosscheck backupset;

                  This command will verify whether backup file is still on media.

                  If it is unavailable, RMAN will mark it as UNAVAILABLE or EXPIRED.

                   

                  RAMN > delete expired backupset;

                  or

                  RMAN > delete expired backup;

                   

                  Note : If you manually rename or zip RMAN backup files, you must manually remove it from disk since RMAN

                  does not recognize them.

                   

                  RMAN > report obsolete;

                  The command lists all backups rendered obsolete based on rentention policy.

                  Current Retention Policy is 'Recovery WINDOW OF 30 DAYS'.

                   

                  RMAN > delete obsolete;

                   

                  RMAN > list backup summary;

                  It will show all backupset info kept in RMAN repository.

                  If you want to see what RMAN keeps in each backupset, run 'list backupset N' where N is Backupset ID.

                   

                  RMAN > delete backupset N;

                  or

                  RMAN > delete backupset; (to delete all backups)

                   

                  Once fair amount of space is reclaimed, do full backup.

                  Not sure how much full back space is needed but not less than 20G.

                   

                  RMAN > backup database;

                   

                  RMAN > list backup;

                  Full backup may have more than one backupset.

                  Look for last backupsets. It will list backup db files.

                   

                  RMAN > delete obsolete;

                  Rerun this command to delete unwanted existing backupsets (if have) after completed full backup.

                   

                  Please check this link:  http://www.snowfrog.net/2008/12/18/oracle-how-to-purge-old-rman-backups/

                   

                  Tahnk's