Oracle資料庫的歸檔日誌寫滿磁碟空間解決辦法

1、資料庫不能啟動

SQL> startup
ORACLE 常式已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              83886784 bytes
Database Buffers          197132288 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
ORA-16038: 日誌 2 序號 44 無法歸檔
ORA-19809: 超出了恢複檔案數的限制
ORA-00312: 聯機日誌 2 線程 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

2、查看$ORACLE_HOME/admin/SID/bdump/alert_SID.log日誌

Thu Feb 19 09:45:33 2009
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_660.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.95% used, and has 1129472 remaining bytes available.

Thu Feb 19 09:45:33 2009
Errors in file d:/oracle/product/10.2.0/admin/orcl/udump/orcl_ora_4708.trc:
ORA-19815: 警告: db_recovery_file_dest_size 位元組 (共 2147483648 位元組) 已使用 99.95%, 尚有 1129472 位元組可用。

Thu Feb 19 09:45:33 2009
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
Thu Feb 19 09:45:33 2009
************************************************************************
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
You have following choices to free up space from flash recovery area:
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   reflect the new space.
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
2. Back up files to tertiary device such as tape using RMAN
   DELETE EXPIRED commands.
************************************************************************
   BACKUP RECOVERY AREA command.
Thu Feb 19 09:45:34 2009
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_660.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44217856 bytes disk space from 2147483648 limit

從上面資訊知道是flash recovery area閃回恢複空間中預設2G的空間被歸檔日誌佔滿了，沒有空間進行存放歸檔日誌。

3、退出sqlplus,進入rman

SQL> exit
從 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開

C:/Documents and Settings/mengzhaoliang>rman target/

復原管理員: Release 10.2.0.1.0 - Production on 星期四 2月 19 09:57:48 2009

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

已串連到目標資料庫: ORCL (DBID=1193443472, 未開啟)

4、查看歸檔日誌
RMAN> list archivelog all;

使用目標資料庫控制檔案替代恢複目錄

已存檔的日誌副本列表
關鍵字     Thrd Seq     S 短時間     名稱
------- ---- ------- - ---------- ----
40      1    1       A 17-11月-08 D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/O
RCL/ARCHIVELOG/2008_11_18/O1_MF_1_1_4L44Z9CQ_.ARC
41      1    2       A 18-11月-08 D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/O
RCL/ARCHIVELOG/2008_11_18/O1_MF_1_2_4L4W4O0H_.ARC
42      1    3       A 18-11月-08 D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/O

5、可以先把歸檔記錄備份到其他的空間，然後用rman刪除歸檔日誌；

RMAN> delete archivelog all;

釋放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=151 devtype=DISK

已存檔的日誌副本列表
關鍵字     Thrd Seq     S 短時間     名稱
------- ---- ------- - ---------- ----
40      1    1       A 17-11月-08 D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/O
RCL/ARCHIVELOG/2008_11_18/O1_MF_1_1_4L44Z9CQ_.ARC
是否確定要刪除以上對象 (輸入 YES 或 NO)? yes
已刪除的存檔日誌
存檔記錄檔名 =D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/200
_11_18/O1_MF_1_1_4L44Z9CQ_.ARC 記錄 ID=40 時間戳記 =671101294

6、退出rman，進入sqlplus,開啟資料庫

RMAN> exit

復原管理員完成。

C:/Documents and Settings/mengzhaoliang>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 2月 19 10:03:02 2009

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

串連到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open;

資料庫已更改。

資料庫已經開啟。