Fast_recovery_area no Space Left (ORA-19815)

I. Problem phenomena

-- when performing log switchover, tamper SQL>alterdatabaseaddGroup4 ('/u01/oradata /oracle/redo04.log') size 50m; SQL>alter system switch logfile;

Two. Analysis process

1. Suspected disk space is full (excluded)

DF -hfilesystem            Size  used Availuse% mountedon/dev/sda1              19G   14G  4. 5G  %/none                  506M  252M  254M  /dev/    in/var/spool/mail/root[[email protected] Archivelog]

2. View Alert_beijing.log Logs

ALTER DATABASE Add logfile Group 4 ('/u01/oradata/oracle/redo04.log ') size 50mcompleted:alter database add logfile Group  4 ('/u01/oradata/oracle/redo04.log ') size 50mSat Nov 03:30:49 2014Thread 1 advanced to log sequence 163 (LGWR switch) Current log# 4 seq# 163 mem# 0:/u01/oradata/oracle/redo04.logsat Nov 01 03:30:49 2014Errors in File/u01/diag/rdbms/oracle/beijing/trace/beijing_arc0_31059.trc:ora-19815:warning:db_recovery_file _dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.You are following choices to free up space From recovery area:1. Consider changing RMAN RETENTION POLICY. If you is using Data Guard, then consider changing RMAN ARCHIVELOG deletion policy.2. Back the 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 is used to the delete files, then use RMAN crosscheck and delete EXPIRED commands.******** Errors in file/u01/diag/rdbms/oracle/beijing/ Trace/beijing_arc0_31059.trc:ora-19809:limit exceeded for recovery filesora-19804:cannot reclaim 24801792 bytes Disk sp Ace from 2147483648 limit

Three. Problem locating

Database archive logs are placed in Fast_recovery_area, and the space is full

Four. Resolution process

1. If the database cannot be opened, modify the Db_recovery_file_dest_size

SQL>  startup Nomount; SQL>  show parameter Db_recovery_file_dest_sizesql>AlterSet db_recovery_file_dest_size=2G Scope=both; SQL>alterdatabase  mount; SQL>alterdatabaseopen;

2. Delete History Archive Log

2.1 Deleting archive logs manually

[Email protected] archivelog]#pwd/u01/flash_recovery_area/oracle/Archivelog[[email protected] archivelog]#ls-LDRWXR-X---2Oracle Oinstall4096Nov1 Geneva: +2014_10_29DRWXR-X---2Oracle Oinstall4096Nov1 Geneva: +2014_10_30DRWXR-X---2Oracle Oinstall4096Nov1 Geneva: +2014_10_31DRWXR-X---2Oracle Oinstall4096Nov1 Geneva: -2014_11_01[[email protected] archivelog]#RM-RF 2014_10_29[[email protected] archivelog]#RM-RF 2014_10_30[[email protected] archivelog]#RM-RF 2014_10_31

2.2 Update the status of archived logs in the storage warehouse

RMAN>Crosscheck Archivelog All; released channel:ora_disk_1allocated Channel:ora_disk_1channel Ora_disk_1:sid= theDevice type=DISKvalidation Failed forArchivedLogarchivedLog fileName=/U01/Flash_recovery_area/ORACLE/Archivelog/2014_10_ to/O1_mf_1_159_b58hpwom_.arc RECID=155STAMP=862437663Validation succeeded forArchivedLogarchivedLog fileName=/U01/Flash_recovery_area/ORACLE/Archivelog/2014_11_ on/O1_mf_1_160_b593tqck_.arc RECID=158STAMP=862458264Validation succeeded forArchivedLogarchivedLog fileName=/U01/Flash_recovery_area/ORACLE/Archivelog/2014_11_ on/O1_mf_1_161_b593cqd6_.arc RECID=156STAMP=862457783Validation succeeded forArchivedLogarchivedLog fileName=/U01/Flash_recovery_area/ORACLE/Archivelog/2014_11_ on/O1_mf_1_162_b593crx1_.arc RECID=157STAMP=862457784crosschecked4Objectsrman>

Crosscheck effect: Update the state of the object just checked in the storage warehouse, so that it is easy for subsequent operations to process the checksum object: Check database, tablespace, data file, control file, archive log, SPFile Backup set object status 1.expired: Object does not exist on disk or tape. When a backup set is in the expired state, all the backup slices in the backup collection are also in the expired state 2.available: The object is in the available state. When a backup set is available, all backup slices within that backup set are also available in 3.unavailabe: The object is in an unusable state. When a backup set is not available, all backup slices within that backup set are also unavailable

2.3 Delete all Expired status archive logging (the actual file was previously deleted)

Rman>DELETE EXPIRED archivelog all;released channel:ora_disk_1allocated channel:ora_disk_1channel ora_disk_1:sid= theDevice type=disklist of archived Log Copies fordatabase with Db_unique_name ORACLE=====================================================================Key thrd Seq S Low Time------- ---- ------- - ---------155     1    159X to-oct- -Name:/u01/flash_recovery_area/oracle/archivelog/2014_10_31/O1_mf_1_159_b58hpwom_.arcdo really want to delete the above objects (enter YES or NO)?yesdeleted archived logarchived LogfileName=/u01/flash_recovery_area/oracle/archivelog/2014_10_31/o1_mf_1_159_b58hpwom_.arc RECID=155stamp=862437663Deleted1EXPIRED Objectsrman>

Fast_recovery_area no Space Left (ORA-19815)