Oracle 入門之Oracle啟動報錯“ORA-03113”

早上連接Oracle,發現oracle無法正常工作，無法shutdown immediate方式關閉，shutdown abort方式關閉之后再啟動，出現錯誤提示“ORA-03113 end-of-file on communication channel”

分析alter日志信息如下：
Errors in file /u01/diag/rdbms/online/online/trace/online_ora_1544.trc: -
 

ORA-19815: 警告: db_recovery_file_dest_size 字節 (共 4070572032 字節) 已使用 100.00%, 尚有 0 字節可用。
************************************************************************
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/online/online/trace/online_ora_1544.trc:
ORA-19809: 超出了恢復文件數的限制
ORA-19804: 無法回收 36931584 字節磁盤空間 (從 4070572032 限制中)
ARCH: Error 19809 Creating archive log file to ’/u01/flash_recovery_area/ONLINE/archivelog/2010_09_25/o1_mf_1_109_%u_.arc’
Errors in file /u01/diag/rdbms/online/online/trace/online_ora_1544.trc:
ORA-16038: 日志 1 sequence# 109 無法歸檔
ORA-19809: 超出了恢復文件數的限制
ORA-00312: 聯機日志 1 線程 1: ’/u01/oradata/online/redo01.log’
USER (ospid: 1544): terminating the instance due to error 16038
Instance terminated by USER, pid = 1544

可以看出已經達到db_recovery_file_dest_size規定的限制大小，處理方法如下：

1：在操作系統上刪除相關的文件，啟動數據庫，依然報前面的錯誤信息

2：由spfile生成pfile，增大pfile中的db_recovery_file_dest_size參數的值

3：由修改過的pfile生成spfile，啟動數據庫

4：使用rman，清理過期的數據

[Oracle@localhost ~]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 25 14:18:53 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ONLINE (DBID=2094135012)

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

5：查看recovery_file_dest動態性能視圖

SQL> desc v$recovery_file_dest;
名稱                                      是否為空? 類型
----------------------------------------- -------- ------------------------

NAME                                               VARCHAR2(513)
SPACE_LIMIT                                        NUMBER
SPACE_USED                                         NUMBER
SPACE_RECLAIMABLE                                  NUMBER
NUMBER_OF_FILES                                    NUMBER

SQL> col name format a50;
SQL> select * from v$recovery_file_dest;

NAME                                               SPACE_LIMIT SPACE_USED
-------------------------------------------------- ----------- ----------
SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------- ---------------
/u01/flash_recovery_area                            5242880000 755426304
                0              19

分析：flash_recovery_area初始化參數定義的值，一般用來存放歸檔日志(archivelog)和rman的備份集(backupset)，Oracle 11g r2 64位安裝完后，db_recovery_file_dest_size為3882M，為了防止oracle因為這個原因崩潰，應當根據需要適當調整這個值的大小，并定期清理過期的日志信息

SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]