oracle闪回区管理

Errors in file /home/oracle/diag/rdbms/orarpt/orarpt/trace/orarpt_mmon_22508.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 98.55% used, and has 31102976 remaining bytes available.
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************

今天数据库重启时，查看alert日志发现上面的错误，虽然现在数据库可以正常启动运行，但当这个db_recovery_file_dest_size使用100%的时候，
将会报错ORA-19809: limit exceeded for recovery files，数据库将会因无法归档而挂起。

查看闪回区的信息

select * from v$recovery_file_dest;

NAME              /home/oracle/flash_recovery_area
SPACE_LIMIT       2147483648
SPACE_USED        2116380672
SPACE_RECLAIMABLE 0
NUMBER_OF_FILES   1

查看alert告警，以及oracle给出的处理建议。

select reason,object_type,suggested_action  from dba_outstanding_alerts;

REASON            db_recovery_file_dest_size 字节 (共 2147483648 字节) 已使用 98.55%, 尚有 31102976 字节可用。
OBJECT_TYPE        RECOVERY AREA
SUGGESTED_ACTION "可以选择以下操作之一从快速恢复区释放空间:
                  1. 考虑更改 RMAN RETENTION POLICY。如果使用的是 Data Guard,
                  则考虑更改 RMAN ARCHIVELOG DELETION POLICY。
                     2. 使用 RMAN
                  BACKUP RECOVERY AREA 命令将文件备份到磁带之类的三级存储。
                     3. 添加磁盘空间并增大 db_recovery_file_dest_size 参数以反映
                     这个新空间。
                     4. 使用 RMAN DELETE 命令删除不需要的文件。如果使用了操作系统
                     命令来删除文件, 则使用 RMAN CROSSCHECK 和
                     DELETE EXPIRED 命令。"

这里我只试了第3、4种方法

第3种修改方法，扩展闪回区

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 2G

SQL> alter system set db_recovery_file_dest_size=4G scope=both;

系统已更改。

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 4G

现在已经将闪回区空间扩充到了4G,也就是新增了2G。

第4种修改方法，清空闪回区
注意，刚才直接将db_recovery_file_dest目录下的文件删除，SPACE_USED也不会释放。

oracle@linux-35:~> rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Wed Oct 12 12:35:09 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORARPT (DBID=2356255833)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1138 device type=DISK
validation failed for archived log
archived log file name=/home/oracle/flash_recovery_area/ORARPT/archivelog/2011_06_08/o1_mf_1_11_6yz1mw4l_.arc RECID=2 STAMP=753316072
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf RECID=3 STAMP=753397594
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf RECID=4 STAMP=753404604
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf RECID=5 STAMP=753490060
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf RECID=6 STAMP=753530695
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf RECID=7 STAMP=753599698
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf RECID=8 STAMP=753646377
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_18_750871897.dbf RECID=9 STAMP=753747854
validation failed for archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_19_750871897.dbf RECID=10 STAMP=753809369
validation failed for archived log
……

RMAN> delete expired archivelog all;

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

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
2       1    11      X 07-JUN-11
        Name: /home/oracle/flash_recovery_area/ORARPT/archivelog/2011_06_08/o1_mf_1_11_6yz1mw4l_.arc

3       1    12      X 08-JUN-11
        Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf

4       1    13      X 09-JUN-11
        Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf

5       1    14      X 09-JUN-11
        Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf

6       1    15      X 10-JUN-11
        Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf

7       1    16      X 11-JUN-11
        Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf

8       1    17      X 12-JUN-11
        Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf
        ……
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/home/oracle/flash_recovery_area/ORARPT/archivelog/2011_06_08/o1_mf_1_11_6yz1mw4l_.arc RECID=2 STAMP=753316072
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf RECID=3 STAMP=753397594
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf RECID=4 STAMP=753404604
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf RECID=5 STAMP=753490060
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf RECID=6 STAMP=753530695
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf RECID=7 STAMP=753599698
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf RECID=8 STAMP=753646377
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_18_750871897.dbf RECID=9 STAMP=753747854


RMAN> exit


Recovery Manager complete.

再查看闪回区的信息，SPACE_USED已经释放。

select * from v$recovery_file_dest;

NAME	            /home/oracle/flash_recovery_area
SPACE_LIMIT	   2147483648
SPACE_USED	   0
SPACE_RECLAIMABLE	   0
NUMBER_OF_FILES	   0