1

I have two 12.1 databases on the same server that I've set up with rman and have been running okay for the last year or so (with numerous restores to a seperate test server - so at least restore/recovery wise rman is set up okay.).

These databases are both around 100G in size, with the recovery area (originally) sized at around 200G. (Currently bumped up to 300+G to give a little more lee-way time whilst I work out what's going wrong).

Just recently one of the databases has started filling up the db_recovery_area with backup pieces - normally this has been fairly static increasing over the day and then dropping back to zero when I run the rman level 1 backup over night (or level 0 over the weekend).

However, over the last couple of weeks, the recovery space has trended upwards eventually getting to the point where I need to delete all backups/archivelogs out of rman to avoid a production database freezing due to lack of space.

There's been no configuration change or upgrades on this server for some time and I can't work why the behaviour has changed - but only for one of the databases.

Looking at V$RECOVERY_AREA_USAGE the only thing consuming a substantial amount of space is the backup pieces.

FILE_TYPE       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE             0             0           0          0
REDO LOG                 0             0           0          0
ARCHIVED LOG           .26             0          25          0
BACKUP PIECE         25.57             0           7          0
IMAGE COPY               0             0           0          0
FLASHBACK LOG            0             0           0          0
FOREIGN ARCHIVED LOG     0             0           0          0
AUXILIARY DATAFILE COPY  0             0           0          0

The parameters for rman for this database are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/rman/r1db/rman/%U',   '/mnt/share/rman/r1db/rman/%U' MAXPIECESIZE 10 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_r1db.f'; # default

And the backup script I am using is:

RUN {
  BACKUP INCREMENTAL LEVEL 1 TAG 'R1INC' DATABASE;
  BACKUP DEVICE TYPE DISK TAG 'R1INC' ARCHIVELOG ALL NOT BACKED UP DELETE ALL INPUT;
  DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
  DELETE NOPROMPT EXPIRED BACKUP;
}  

(Weekly I run a level 0, but the script is other wise unchanged)

The backup list looks as I'd expect (had to purge the backups on the 5th due to space constraints), with the tagged level 1s and 0s interspersed with the Full autobackups.

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
933     B  1  A DISK        05/08/2017 11:05:53 1       1       NO         R1INC
934     B  0  A DISK        05/08/2017 11:16:17 1       1       NO         R1INC
935     B  A  A DISK        05/08/2017 11:16:24 1       1       NO         R1INC
936     B  F  A DISK        05/08/2017 11:16:27 1       1       NO         TAG20170805T111627
937     B  1  A DISK        06/08/2017 11:16:13 1       1       NO         R1INC
938     B  A  A DISK        06/08/2017 11:17:22 1       1       NO         R1INC
939     B  F  A DISK        06/08/2017 11:17:30 1       1       NO         TAG20170806T111729

On the other database on this server (that is behaving as I think it should), using identical config/scripts the recovery area usage looks like

02:13:59 [SYS@r2db] SQL> SELECT * FROM V$RECOVERY_AREA_USAGE;

FILE_TYPE       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE             0             0           0          0
REDO LOG               .07             0           3          0
ARCHIVED LOG           2.7             0         128          0
BACKUP PIECE           .04             0           6          0
IMAGE COPY               0             0           0          0
FLASHBACK LOG            0             0           0          0
FOREIGN ARCHIVED LOG     0             0           0          0
AUXILIARY DATAFILE COPY  0             0           0          0

8 rows selected.

After the same number of level 1 backups, the backup piece usage is sitting at a few meg, instead of almost 100G as is the misbehaving database.

Why are the backup pieces on this database sitting in the recovery area eventually filling it up completely? Is this normal behaviour with the other database being the abnormal one? This is not a high traffic database - should I need to have the recovery area set to more than 3 times the size of the database?

Thanks

  • Are you taking incremental cumulative? or differential? – JSapkota Aug 7 '17 at 4:21
  • @JSapkota it should be differential as that is the default? (or have I missed a configurable option some where?) – Dave Smylie Aug 7 '17 at 5:03
  • 1
    If you didn't use the cumulative clause then its defaults to the differential. – JSapkota Aug 7 '17 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.