How RECOVERY SPACE AREA will impact RMAN BACKUP

Introduction:

This blog is intended for DBA’s who have issues in completing rman backup, we tried to take a rman full backup, but it failed with the below error message.

Error:

RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 67108864 bytes disk space from 21474836480 bytes limit

 

Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.

Solution: Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE as mentioned below.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size           big integer 4122M

Step :1 Check recovery area is showing improper space usage:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

———— —————— ————————- —————

CONTROLFILE       0    0    0

ONLINELOG         0    0    0

ARCHIVELOG    97.96    0   44

BACKUPPIECE       0    0    0

IMAGECOPY         0    0    0

FLASHBACKLOG      0    0    0

SQL> select name,round(space_limit / 1024 / 1024) size_mb,round(space_used  / 1024 / 1024) used_mb,decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used from v$recovery_file_dest order by name;

NAME                                        SIZE_MB    USED_MB   PCT_USED

—————————————- ———- ———- ———-

/u01/app/oracle/fast_recovery_area             4122       2102         51

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size           big integer 4122M

SQL> ALTER system  SET db_recovery_file_dest_size=4200M  scope=BOTH ;

System altered.

Step :2 Now will re-populate it correctly using RMAN.

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Fri Jun 6 13:24:23 2015

Copyright – 1982, 2015, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1377135549)

RMAN>

RUN {

change archivelog all crosscheck;

report obsolete orphan;

report obsolete;

crosscheck backup;

crosscheck copy;

crosscheck backup of controlfile;

delete noprompt expired backup;

delete noprompt expired archivelog all;

delete noprompt expired backup of controlfile;

delete force noprompt expired copy;

delete force noprompt obsolete orphan;

delete force noprompt obsolete;

}

SQL> select name,round(space_limit / 1024 / 1024) size_mb,round(space_used  / 1024 / 1024) used_mb,decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used from v$recovery_file_dest order by name;

NAME                                        SIZE_MB    USED_MB   PCT_USED

—————————————- ———- ———- ———-

/u01/app/oracle/fast_recovery_area             4122       2102         51

Conclusion:

I hope this blog would have helped to resolve the above issue in case if you encountered the same…

Happy Debugging!!