I have a new Oracle 12.1.0.2 database with ASM storage on RHEL 7.1. During RMAN backup of a 2 TB file to a recovery area with 13 TB free space I get ORA-919809.
No files have been manually deleted, everything is managed using RMAN commands. Please note I am NOT using flashback logging which is normally associated with this error. Archivelog mode enabled but flashback logging is not. Here are my recovery settings:
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 16000G
I previously backed up the database plus archivelogs. On a later backup of a large tablespace (2 TB) the backup failed with ORA-19809. Checking the space I had about 14 TB of free space in the recovery area. I then used RMAN commands to delete all backupsets and image copies, and I did the usual crosscheck stuff. Before trying to backup again I check my system. You can see I have no backups, and I have zero files in the recovery area.
RMAN> list backup;
specification does not match any backup in the repository
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
RMAN> sql select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 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
RMAN> sql select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------- ---------- ----------------- --------------- ----------
+RECO
17179869184 15728640 0 1 0
I then backed up as image copies all tablespaces except my application tablespaces, and now the space looks like this:
SQL> @list_reco_space
NAME TOTAL_SPACE_GB USED_GB RECLAIMABLE_GB number
--------------- -------------- ---------- -------------- ----------
+RECO 16000 230.438477 0 9
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 4 0
IMAGE COPY 1.44 0 4 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
Now, I will backup my first application tablespace which is 1 TB.
RMAN> backup as copy section size 100G tablespace tpcctab;
And re-list the space utilization:
SQL> @ list_reco_space
NAME TOTAL_SPACE_GB USED_GB RECLAIMABLE_GB number
--------------- -------------- ---------- -------------- ----------
+RECO 16000 1254.5 0 11
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 5 0
IMAGE COPY 7.84 0 5 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
Now, I will backup my second application tablespace which is 2 TB. It runs for a few minutes and then fails.
RMAN> backup as copy section size 100G tablespace tpchtab;
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_6 channel at 10/16/2015 08:58:18
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2473902211072 bytes disk space from 17179869184000 limit
Again, I check space and there is plenty of room! Less than 4 TB of my 16 TB is full.
SQL> @ list_reco_space
NAME TOTAL_SPACE_GB USED_GB RECLAIMABLE_GB number
--------------- -------------- ---------- -------------- ----------
+RECO 16000 3558.5 0 12
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 5 0
IMAGE COPY 22.24 0 6 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
At this point I looked at the actual files on my ASM disks and found old image copies still on the ASM diskgroup RECO consuming space even though the corresponding backups were deleted in RMAN a long time ago, and you can see from the commands at the start of this thread there were no backups or copies known to RMAN. Solution, I went into asmcmd and manually deleted the old files. After manually deleting junk from the ASM diskgroup let's update RMAN,
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 1 days
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
no obsolete backups found
RMAN> delete expired copy;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4861 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=5806 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=5941 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=6076 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=6211 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=6346 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
Also, let's delete the failed backup,
RMAN> delete copy tag TAG20151016T093714;
Before we try backing up again, let's review the space situation. I am only using 1.3 TB out of 17 TB. Plenty of space available.
ASMCMD> lsdg reco
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 18310542 16994507 0 16994507 0 N RECO/
SQL> @list_reco_space
NAME TOTAL_SPACE_GB USED_GB RECLAIMABLE_GB number
--------------- -------------- ---------- -------------- ----------
+RECO 16000 1254.5 0 13
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 7 0
IMAGE COPY 7.84 0 5 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
RMAN> sql select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------- ---------- ----------------- --------------- ----------
+RECO
17179869184 1346988146 0 13 0
Now, let's try backing up my application tablespace and see if it works or not ...
RMAN> backup as copy section size 100G tablespace tpchtab;
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_6 channel at 10/16/2015 09:39:46
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2473902211072 bytes disk space from 17179869184000 limit