Good Morning,
We are having issues with one Database and its Fast recovery area.
It is a SE2 Instance version 12.2.0.1.0 - 64bit for SPARK on SOLARIS
The images copies of our incremental cumulative backup are not deleted from Fast Recovery Area, even if them are marked as deleted and are actually deleted from filesystem .
Backup Configuration:
run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA\$OEM_LEVEL_0';
backup incremental level 1 cumulative copies=1 for recover of copy with tag 'ORA\$OEM_LEVEL_0' database;
}
RMAN> SHOW ALL;
RMAN configuration parameters for database with db_unique_name DPROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
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 MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_DPROD.f'; # default
Image copies from RMAN:
RMAN> LIST DATAFILECOPY ALL;
using target database control file instead of recovery catalog
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - ------------------- ---------- ------------------- ------
1848 1 A 2019-01-23 03:35:23 74665081 2019-01-22 03:36:20 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_system_fnbqf1ml_.dbf
Tag: ORA\$OEM_LEVEL_0
325 2 A 2018-08-08 03:51:27 7032417 2018-08-08 03:51:19 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_factory_fpnm4qtw_.dbf
Tag: ORA\$OEM_LEVEL_0
268 2 A 2018-08-06 03:30:12 5729306 2018-08-05 03:30:17 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprod__fnco15sd_.dbf
Tag: ORA\$OEM_LEVEL_0
1845 3 A 2019-01-23 03:35:14 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_sysaux_fnbqf919_.dbf
Tag: ORA\$OEM_LEVEL_0
1843 4 A 2019-01-23 03:31:10 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_undotbs1_fnbqfd9g_.dbf
Tag: ORA\$OEM_LEVEL_0
1846 5 A 2019-01-23 03:35:14 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprod_fncny7p0_.dbf
Tag: ORA\$OEM_LEVEL_0
1847 7 A 2019-01-23 03:35:22 74665081 2019-01-22 03:36:20 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_users_fnbqffb1_.dbf
Tag: ORA\$OEM_LEVEL_0
1842 8 A 2019-01-23 03:31:09 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprod__fnco1fsw_.dbf
Tag: ORA\$OEM_LEVEL_0
1844 9 A 2019-01-23 03:35:06 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprodh_fnco1o1q_.dbf
Tag: ORA\$OEM_LEVEL_0
326 10 A 2018-08-08 03:51:42 7032423 2018-08-08 03:51:34 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_factoryp_fpnm56ym_.dbf
Tag: ORA\$OEM_LEVEL_0
269 10 A 2018-08-06 03:30:12 5729306 2018-08-05 03:30:17 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprodh_fnco1wcl_.dbf
Tag: ORA\$OEM_LEVEL_0
1840 11 A 2019-01-23 03:30:33 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprodh_fnco23hq_.dbf
Tag: ORA\$OEM_LEVEL_0
1841 12 A 2019-01-23 03:30:33 74665046 2019-01-22 03:34:55 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_interact_fpnm3hjp_.dbf
Tag: ORA\$OEM_LEVEL_0
324 13 A 2018-08-08 03:51:12 7032411 2018-08-08 03:51:04 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_mks_fpnm48nv_.dbf
Tag: ORA\$OEM_LEVEL_0
328 14 A 2018-08-08 03:51:53 7032431 2018-08-08 03:51:53 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_segnalaz_fpnm5s7f_.dbf
Tag: ORA\$OEM_LEVEL_0
327 15 A 2018-08-08 03:51:51 7032429 2018-08-08 03:51:50 NO
Name: /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_segnalaz_fpnm5p2z_.dbf
Tag: ORA\$OEM_LEVEL_0
Images Copy from "V$DATAFILE_COPY" view:
SELECT RECID,STAMP,NAME,TAG,DELETED, STATUS FROM V$DATAFILE_COPY order by DELETED;
RECID STAMP NAME TAG DEL S ---------- ---------- ----------------------------------------------------------------------------------------------------------------------
268 983417412 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprod__fnco15sd_.dbf ORA\$OEM_LEVEL_0 NO A
1848 998278523 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_system_fnbqf1ml_.dbf ORA\$OEM_LEVEL_0 NO A
324 983591472 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_mks_fpnm48nv_.dbf ORA\$OEM_LEVEL_0 NO A
325 983591487 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_factory_fpnm4qtw_.dbf ORA\$OEM_LEVEL_0 NO A
326 983591502 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_factoryp_fpnm56ym_.dbf ORA\$OEM_LEVEL_0 NO A
327 983591511 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_segnalaz_fpnm5p2z_.dbf ORA\$OEM_LEVEL_0 NO A
328 983591513 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_segnalaz_fpnm5s7f_.dbf ORA\$OEM_LEVEL_0 NO A
1840 998278233 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprodh_fnco23hq_.dbf ORA\$OEM_LEVEL_0 NO A
1841 998278233 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_interact_fpnm3hjp_.dbf ORA\$OEM_LEVEL_0 NO A
1842 998278269 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprod__fnco1fsw_.dbf ORA\$OEM_LEVEL_0 NO A
1843 998278270 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_undotbs1_fnbqfd9g_.dbf ORA\$OEM_LEVEL_0 NO A
1844 998278506 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprodh_fnco1o1q_.dbf ORA\$OEM_LEVEL_0 NO A
1845 998278514 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_sysaux_fnbqf919_.dbf ORA\$OEM_LEVEL_0 NO A
1846 998278514 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprod_fncny7p0_.dbf ORA\$OEM_LEVEL_0 NO A
1847 998278522 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_users_fnbqffb1_.dbf ORA\$OEM_LEVEL_0 NO A
269 983417412 /u03/fast_recovery_area/DPROD/DPROD/datafile/o1_mf_Dprodh_fnco1wcl_.dbf ORA\$OEM_LEVEL_0 NO A
270 983417412 ORA\$OEM_LEVEL_0 YES D
271 983417413 ORA\$OEM_LEVEL_0 YES D
272 983417413 ORA\$OEM_LEVEL_0 YES D
273 983417413 ORA\$OEM_LEVEL_0 YES D
274 983417414 ORA\$OEM_LEVEL_0 YES D
275 983503813 ORA\$OEM_LEVEL_0 YES D
276 983503813 ORA\$OEM_LEVEL_0 YES D
277 983503813 ORA\$OEM_LEVEL_0 YES D
<...> there are 1,581 rows here! All with DEL=YES and Name=NULL
Fast Recovery Area datafiles copy on Disk:
-bash-4.4$ ls -lrt
total 37032522
drwxr-x--- 6 oracle oinstall 6 Jul 11 2018 ..
-rw-r----- 1 oracle oinstall 1073750016 Aug 6 03:30 o1_mf_Dprod__fnco15sd_.dbf
-rw-r----- 1 oracle oinstall 1073750016 Aug 6 03:30 o1_mf_Dprodh_fnco1wcl_.dbf
-rw-r----- 1 oracle oinstall 1073750016 Aug 8 03:51 o1_mf_mks_fpnm48nv_.dbf
-rw-r----- 1 oracle oinstall 838868992 Aug 8 03:51 o1_mf_factory_fpnm4qtw_.dbf
-rw-r----- 1 oracle oinstall 838868992 Aug 8 03:51 o1_mf_factoryp_fpnm56ym_.dbf
-rw-r----- 1 oracle oinstall 62922752 Aug 8 03:51 o1_mf_segnalaz_fpnm5p2z_.dbf
drwxr-x--- 2 oracle oinstall 18 Aug 8 03:51 .
-rw-r----- 1 oracle oinstall 52436992 Aug 8 03:51 o1_mf_segnalaz_fpnm5s7f_.dbf
-rw-r----- 1 oracle oinstall 1073750016 Jan 23 03:30 o1_mf_Dprodh_fnco23hq_.dbf
-rw-r----- 1 oracle oinstall 1073750016 Jan 23 03:30 o1_mf_interact_fpnm3hjp_.dbf
-rw-r----- 1 oracle oinstall 3221233664 Jan 23 03:31 o1_mf_Dprod__fnco1fsw_.dbf
-rw-r----- 1 oracle oinstall 3764396032 Jan 23 03:31 o1_mf_undotbs1_fnbqfd9g_.dbf
-rw-r----- 1 oracle oinstall 9663684608 Jan 23 03:35 o1_mf_Dprodh_fnco1o1q_.dbf
-rw-r----- 1 oracle oinstall 13736353792 Jan 23 03:35 o1_mf_sysaux_fnbqf919_.dbf
-rw-r----- 1 oracle oinstall 5251072 Jan 23 03:35 o1_mf_users_fnbqffb1_.dbf
-rw-r----- 1 oracle oinstall 912269312 Jan 23 03:35 o1_mf_system_fnbqf1ml_.dbf
-rw-r----- 1 oracle oinstall 16106135552 Jan 24 03:30 o1_mf_Dprod_fncny7p0_.dbf
Space usage on Fast Recovery Area:
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;
FLASH_IN_GB FLASH_USED_IN_GB FLASH_RECLAIMABLE_GB PERCENT_OF_SPACE_USED
----------- ---------------- -------------------- ---------------------
70 64,93 0 92,75
What i tryed:
CROSSCHECK COPY;
CROSSCHECK DATAFILECOPY ALL;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED DATAFILECOPY ALL;
No use.
Considering that i inherited this situation and i don't know what have been done, ex an hard delete of datafile copies from disk or similiar.
- Why there is this disalignment between what i see from RMAN or directly into the Disk and what "V$DATAFILE_COPY" shows?
- If not with a CROSSCHECK how can i solve that issue?
Thanks in advance!
Pasquale