Oracle 入門之Oracle啟動(dòng)報(bào)錯(cuò)“ORA-03113”

早上連接Oracle,發(fā)現(xiàn)oracle無法正常工作，無法shutdown immediate方式關(guān)閉，shutdown abort方式關(guān)閉之后再啟動(dòng)，出現(xiàn)錯(cuò)誤提示“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 字節(jié) (共 4070572032 字節(jié)) 已使用 100.00%, 尚有 0 字節(jié)可用。
************************************************************************
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: 超出了恢復(fù)文件數(shù)的限制
ORA-19804: 無法回收 36931584 字節(jié)磁盤空間 (從 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: 超出了恢復(fù)文件數(shù)的限制
ORA-00312: 聯(lián)機(jī)日志 1 線程 1: ’/u01/oradata/online/redo01.log’
USER (ospid: 1544): terminating the instance due to error 16038
Instance terminated by USER, pid = 1544

可以看出已經(jīng)達(dá)到db_recovery_file_dest_size規(guī)定的限制大小，處理方法如下：

1：在操作系統(tǒng)上刪除相關(guān)的文件，啟動(dòng)數(shù)據(jù)庫，依然報(bào)前面的錯(cuò)誤信息

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

3：由修改過的pfile生成spfile，啟動(dòng)數(shù)據(jù)庫

4：使用rman，清理過期的數(shù)據(jù)

[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動(dòng)態(tài)性能視圖

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初始化參數(shù)定義的值，一般用來存放歸檔日志(archivelog)和rman的備份集(backupset)，Oracle 11g r2 64位安裝完后，db_recovery_file_dest_size為3882M，為了防止oracle因?yàn)檫@個(gè)原因崩潰，應(yīng)當(dāng)根據(jù)需要適當(dāng)調(diào)整這個(gè)值的大小，并定期清理過期的日志信息

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

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]