3

I'm using Oracle's Recovery Catalog to track my db's backups metadata (RC ver. is 12.01.00.02 )

I need to collect historical backup sizes but then I've realized there's no metadata information about completed backups beyond 60 days. (however, there's for FAILED and incompleted backups)

e.g. I'm trying to collect this data from rc_rman_backup_job_details view. This RC's was used since Q1 2015, but only 60 days of completed backups are shown.

RMAN@SERESCATALOG> SELECT status, MIN(start_time)
      2  FROM rc_rman_backup_job_details
      3  GROUP BY status
      4  ORDER by status;

STATUS      MIN(START_TIME)
----------- --------------------
COMPLETED   03-SEP-2016 01:31:34
FAILED      18-SEP-2015 09:03:50
RUNNING     28-MAY-2015 02:39:36

What controls this retention? There's any way to change it? Any documentation?

Regards.

Improve this question

2 Answers 2

Reset to default
4

This is described in the note:

How to Clean up The information in EM Backup Report (Doc ID 430601.1)

Edit recover.bsq, find the procedure cleanupRSR, change the default value 60 as needed:

  DELETE FROM rsr
        WHERE rsr_end < nowTime-60
          AND rsr.dbinc_key IN
              (select dbinc_key from dbinc
               where dbinc.db_key = this_db_key);

When finished:

RMAN> upgrade catalog;
Improve this answer
6
  • Why would you delete from the rsr table? Just because you can delete records from rsr, doesn't mean that you should. If the OP changes the two parameters, then the catalog will maintain itself, provided that he does a crosscheck backup and archivelog.
    – Gandolf989
    Commented Nov 2, 2016 at 18:16
  • 1
    @Gandolf989 I did not suggest a manual deletion, records are deleted automatically by the database by using this script. What you wrote about is unrelated to this problem.
    – Balazs Papp
    Commented Nov 2, 2016 at 19:58
  • @Gandolf989 backup retention is irrelevant here. In fact, my backup retention is 15 days max, and having more is just unreliable (DBs > 5TB) and out of scope. BalazsPapp , wow, that was accurate. I'll give it a shot in an test environment. But when this procedures are called?
    – vegatripy
    Commented Nov 3, 2016 at 12:03
  • There are two things to think about. The backup retention is the number of backups that you keep on on disk. for you that would be 15 days. However, the number of days of backups that you keep in the RMAN catalog is controlled by the recovery window and the number of days that the backup information is kept in the control file is controlled by the control_file_record_keep_time. Hence, my answer was correct.
    – Gandolf989
    Commented Nov 3, 2016 at 19:45
  • @Gandolf989 absolutely wrong. Retention Policy parameter is set to configure the backup retention, which can be set in terms of backup redundancy or a recovery window policy (please check docs.oracle.com/cd/B28359_01/backup.111/b28270/… ). You're right about the control_file_record_keep_time parameter, but is not relevant here because,.. well,. that's why I'm using a RC. I want my backup records on a RC, not in a HUGE controlfile (big mistake here, btw)... besides, if I recall correctly, backup records are deleted from controlfile once backups are deleted.
    – vegatripy
    Commented Nov 15, 2016 at 12:44
-1

Connect to your database using RMAN and try the following command. Where 90 is the number of days that you want to keep backup data in your RMAN catalog. 

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;

Also under sqlplus you can set the number of days for the control files to track the backup information for RMAN.

ALTER SYSTEM SET control_file_record_keep_time=90;
Improve this answer
1
  • If you change the recovery window than the backups are kept for 90 days. That's not what the OP wants. He only want to keep that backup information. (e.g. backup sizes)
    – miracle173
    Commented May 28, 2017 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.