- 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