fast_recovery_area无剩余空间（ORA-19815）

一.问题现象

--执行日志切换时，夯住
SQL> alter database add logfile group 4 ('/u01/oradata/oracle/redo04.log') size 50m;
SQL> alter system switch logfile;

二.分析过程

1.怀疑磁盘空间满了（排除）

[root@oracle archivelog]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              19G   14G  4.5G  75% /
none                  506M  252M  254M  50% /dev/shm
You have new mail in /var/spool/mail/root
[root@oracle archivelog]

2.查看alert_beijing.log日志

alter database add logfile group 4 ('/u01/oradata/oracle/redo04.log') size 50m
Completed: alter database add logfile group 4 ('/u01/oradata/oracle/redo04.log') size 50m
Sat Nov 01 03:30:49 2014
Thread 1 advanced to log sequence 163 (LGWR switch)
  Current log# 4 seq# 163 mem# 0: /u01/oradata/oracle/redo04.log
Sat Nov 01 03:30:49 2014
Errors 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 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.
************************************************************************
Errors in file /u01/diag/rdbms/oracle/beijing/trace/beijing_arc0_31059.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 24801792 bytes disk space from 2147483648 limit

三.问题定位  

　　数据库归档日志放置在fast_recovery_area中，而空间已满

四.解决过程

1.如果数据库无法打开，则修改db_recovery_file_dest_size

SQL> startup nomount;
SQL> show parameter db_recovery_file_dest_size
SQL> Alter system set db_recovery_file_dest_size=2G scope=both;
SQL> alter database mount;
SQL> alter database open;

2.删除历史归档日志

2.1 手动删除归档日志

[root@oracle archivelog]# pwd
/u01/flash_recovery_area/ORACLE/archivelog
[root@oracle archivelog]# ls -l
drwxr-x---  2 oracle oinstall 4096 Nov  1 03:43 2014_10_29
drwxr-x---  2 oracle oinstall 4096 Nov  1 03:43 2014_10_30
drwxr-x---  2 oracle oinstall 4096 Nov  1 03:43 2014_10_31
drwxr-x---  2 oracle oinstall 4096 Nov  1 03:44 2014_11_01
[root@oracle archivelog]# rm -rf 2014_10_29
[root@oracle archivelog]# rm -rf 2014_10_30
[root@oracle archivelog]# rm -rf 2014_10_31

2.2 更新归档日志在存储仓库中的状态

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
validation failed for archived log
archived log file name=/u01/flash_recovery_area/ORACLE/archivelog/2014_10_31/o1_mf_1_159_b58hpwom_.arc RECID=155 STAMP=862437663
validation succeeded for archived log
archived log file name=/u01/flash_recovery_area/ORACLE/archivelog/2014_11_01/o1_mf_1_160_b593tqck_.arc RECID=158 STAMP=862458264
validation succeeded for archived log
archived log file name=/u01/flash_recovery_area/ORACLE/archivelog/2014_11_01/o1_mf_1_161_b593cqd6_.arc RECID=156 STAMP=862457783
validation succeeded for archived log
archived log file name=/u01/flash_recovery_area/ORACLE/archivelog/2014_11_01/o1_mf_1_162_b593crx1_.arc RECID=157 STAMP=862457784
Crosschecked 4 objects

RMAN>

crosscheck作用：更新存储仓库中的刚刚校验的对象的状态，便于后续操作处理

校验对象：核对数据库、表空间、数据文件、控制文件、归档日志、SPFILE的备份集

对象状态
1.expired: 对象不存在于磁盘或磁带。当一个备份集处于expired状态，则该备份集中所有的备份片同样处于expired状态
2.available: 对象处于可用状态。当一个备份集可用，则该备份集内的所有备份片同样可用
3.unavailabe: 对象处于不可用状态。当一个备份集不可用，则该备份集内的所有备份片同样不可用

2.3删除所有expired状态归档日志记录(实际文件前面已经被删除)

RMAN> DELETE EXPIRED  archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
List of Archived Log Copies for database with db_unique_name ORACLE
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
155     1    159     X 31-OCT-14
        Name: /u01/flash_recovery_area/ORACLE/archivelog/2014_10_31/o1_mf_1_159_b58hpwom_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/flash_recovery_area/ORACLE/archivelog/2014_10_31/o1_mf_1_159_b58hpwom_.arc RECID=155 STAMP=862437663
Deleted 1 EXPIRED objects


RMAN>