DB: 11gR2 RAC 2 NODE on LINUX
SQL> select name from v$database;
NAME
---------
PERFDB
This Error will occur for various reasons, let see what is causing this error in my database
Alert log message: (Always check Alert log for this error)
ARC3: Error 19504 Creating archive log file to '+FRA'
Fri Oct 10 19:04:18 2014
Errors in file /u01/app/oracle/diag/rdbms/perfdb/PERFDB2/trace/PERFDB2_arc0_15765.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
From my alert log it is clear that my FRA disk group got exhausted
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 300G
recovery_parallelism integer 0
SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;
MAXGB USEDGB
---------- ----------
300 3.42773438 (have enough space)
SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim", NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;
Type % Used % Reclaim # Files
-------------------- ---------- ---------- ----------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 1.13 0 21
BACKUP PIECE .01 0 1
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
Database PERFDB is absolutely fine and recovery dest has space but +FRA is full because of archivelogs of other database which are sharing same ASM diskgroup
Here in this cluster 4 database are running and sharing same FRA diskgroup
ASMCMD> lsdg
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 1535979 16725 0 16725 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 1535979 539 0 539 0 N FRA/
MOUNTED EXTERN N 512 4096 1048576 494 130 0 130 0 N OCR/
MOUNTED EXTERN N 512 4096 1048576 494 131 0 131 0 N OCRM/
MOUNTED NORMAL N 512 4096 1048576 1482 1194 494 120 0 Y VOTE/
ASMCMD> pwd
+FRA/****DB/ARCHIVELOG
ASMCMD> rm -rf 2012_05*
ASMCMD> rm -rf 2012_06*
... ……….
(OR)
If you want to delete archive logs older than ‘x’ days, then
RMAN> delete archivelog until time 'SYSDATE-X'; (deleting older than X days)
Freed some space in FRA by removing old archive log files(Take backup if this is critical db), then problem resolved for PERFDB database.
SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;
GROUP_NUMBER NAME TOTAL_MB FREE_MB USABLE_FILE_MB
------------ ------------------- ---------- ------- ---------- ---------------
1 DATA 1535979 16725 16725
2 FRA 1535979 462046 462046
3 OCRM 494 131 131
4 OCR 494 130 130
5 VOTE 1482 1194 120
If your Alert log says
Alert log message:
ARC2: Error 19809 Creating archive log file to '+FRADG'
Errors in file /oratrace/diag/rdbms/DB/DBSID2/trace/DBSID_arc3_29957.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 6005194752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Then check the below link:
