ORA-19809: exceeds the limit on the number of recovery files

ORA-19809: exceeds the limit on the number of recovery files

I. Fault symptom:
RMAN> backup database;
Start backup from
Use the target database control file to replace the recovery directory
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 158 devtype = DISK
Channel ORA_DISK_1: Start all data file backup Sets
Channel ORA_DISK_1: Specifies the data file in the backup set.
Input data file fno = 00001 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSTEM01.DBF
Input data file fno = 00005 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ HW_WH01.DBF
Input data file fno = 00003 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSAUX01.DBF
Input data file fno = 00002 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ UNDOTBS01.DBF
Input data file fno = 00006 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ GZ_DATA01.DBF
Input data file fno = 00007 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SZ_DATA01.DBF
Input data file fno = 00004 name = D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ USERS01.DBF
Channel ORA_DISK_1: Starting segment 1 from
MAN-03009: backup command (ORA_DISK_1 channel, at 16:38:37,) failed
ORA-19809: exceeds the limit on the number of recovery files
ORA-19804: unable to reclaim 52428800 bytes of disk space (from 2147483648 limit)
Continue to execute other job steps and will not re-run the failed job
Channel ORA_DISK_1: Start all data file backup Sets
Channel ORA_DISK_1: Specifies the data file in the backup set.
The backup set includes the current control file
Include the current SPFILE in the backup set
Channel ORA_DISK_1: Starting segment 1 from
Channel ORA_DISK_1: Completed segment 1 from-14
Segment handle = D: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORCL \ BACKUPSET \ 2014_12_08 \ tags tag = TAG20141208T163642 annotation = NONE
Channel ORA_DISK_1: Backup set completed. elapsed time: 00: 00: 02
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================

Ii. Fault Analysis
When using rman for a full database backup, A ORA-19809 error occurs: exceeds the limit on the number of recovery files. The following error is returned:
ORA-19809: limit exceeded for recovery files
Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST _
SIZE was exceeded.
Action: The error is accompanied by 19804. See message 19804 for further details.
Here we can see that the document clearly states that ORA-19809 errors are accompanied by ORA-19804, And then we look at the reasons for the emergence of ORA-19804 and solutions:
ORA-19804: cannot reclaim string bytes disk space from string limit
Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.
Action: There are five possible solutions: 1) Take frequent backup of recovery area
Using RMAN. 2) Consider changing RMAN retention policy. 3) Consider changing
RMAN archivelog deletion policy. 4) Add disk space and increase DB _
RECOVERY_FILE_DEST_SIZE. 5) Delete files from recovery area using RMAN.
The above has clearly given the cause of this error and the five-point Solution
The simplest and most common method is to expand the settings of DB_RECOVERY_FILE_DEST_SIZE.
First, check the current settings of the database:
SYS @ ORCL (159)> archive log list
Database Log mode archiving Mode
Enable automatic archiving
Archiving end point USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 163
Next archive log sequence 165
Current Log sequence 165
SYS @ ORCL (159)>

We can see that the default flash back area of the database is used to store archives. Next, let's take a look at the location, size, and usage of the flash back area.
SYS @ ORCL (159)> show parameter db_recovery_file_dest
NAME TYPE VALUE
----------------------------------------------------------------------------------------------
Db_recovery_file_dest string D: \ oracle \ product \ 10.2.0/flash_recovery_area
Db_recovery_file_dest_size big integer 2G
SYS @ ORCL (159)> select NAME, SPACE_LIMIT/1024/1024 "total (MB)", SPACE_USED/1024/1024 "used (MB )"
From v $ recovery_file_dest;
NAME total (MB) used (MB)
--------------------------------------------------------------------
D: \ oracle \ product \ 10.2.0/flash_recovery_area 3072 2078.77051
For db_recovery_file_dest_size, the system defaults to 2 GB. If the space is insufficient to accommodate the backup set, the preceding error is returned.

Iii. solution:
For the above error, we expanded db_recovery_file_dest_size and still encountered an error (expanded from 2 GB to 3 GB ), it can be seen that a full backup is not enough even if the scale-up is performed (the actual size of this backup set is 2.05 GB later)
1. Expand the db_recovery_file_dest_size parameter settings
Alter system set db_recovery_file_dest_size = 5G;
2. view the current settings
Show parameter db_recovery_file_dest
3. Execute the rman backup again.
RMAN> backup database;
......
RMAN> list backupset;
BS keyword Type LV Size Device type time completed
--------------------------------------------------------
14 Full 2.05g disk 00:02:19-14
BP Keyword: 14 Status: AVAILABLE compressed: NO mark: TAG20141209T141548
Segment name: D: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORCL \ BACKUPSET \ 2014_12_09 \ O1_MF_NNNDF_TAG20141209T141548_7G2ZC4L2_.BKP
List of data files in backup set 14
File LV type Ckp SCN Ckp time name
----------------------------------
1 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSTEM01.DBF
2 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ UNDOTBS01.DBF
3 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSAUX01.DBF
4 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ USERS01.DBF
5 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ HW_WH01.DBF
6 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ GZ_DATA01.DBF
7 Full 5170484-14 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SZ_DATA01.DBF
BS keyword Type LV Size Device type time completed
--------------------------------------------------------
15 Full 6.83 m disk 00:00:02-14
BP Keyword: 15 Status: AVAILABLE compressed: NO mark: TAG20141209T141548
Segment name: D: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORCL \ BACKUPSET \ 2014_12_09 \ O1_MF_NCSNF_TAG20141209T141548_7G2ZHQFO_.BKP
Included control file: Ckp SCN: 5170574 Ckp time:-14
Included SPFILE: modification time: 09-12 months-14
The problem has been resolved.