啟動資料庫至open狀態報錯：ORA-03113: end-of-file on communication channel

dayong2015發表於2016-04-20
資料庫
環境描述：
      DB環境：RAC雙節點 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
      OS版本：Red Hat Enterprise Linux Server release 5.7 (Tikanga)
問題描述：
     啟動資料庫的時候報如下錯誤提示：
     ORA-03113: end-of-file on communication channel
      Process ID: 10138
     Session ID: 14 Serial number: 3
檢視資料庫的相關服務狀態：
[oracle@rac53 trace]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora.DATA.dg    ora....up.type 0/5    0/     ONLINE    ONLINE    rac53
ora.FLASH.dg   ora....up.type 0/5    0/     ONLINE    ONLINE    rac53
ora.GRIDDG.dg  ora....up.type 0/5    0/     ONLINE    ONLINE    rac53
ora....ER.lsnr ora....er.type 0/5    0/     ONLINE    ONLINE    rac53
ora....N1.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac53
ora.asm        ora.asm.type   0/5    0/     ONLINE    ONLINE    rac53
ora.cvu        ora.cvu.type   0/5    0/0    ONLINE    ONLINE    rac53
ora.gsd        ora.gsd.type   0/5    0/     OFFLINE   OFFLINE
ora.hbang.db   ora....se.type 0/2    0/1    ONLINE    ONLINE    rac53
ora....network ora....rk.type 0/5    0/     ONLINE    ONLINE    rac53
ora.oc4j       ora.oc4j.type  0/1    0/2    ONLINE    ONLINE    rac53
ora.ons        ora.ons.type   0/3    0/     ONLINE    ONLINE    rac53
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac53
ora....53.lsnr application    0/5    0/0    ONLINE    ONLINE    rac53
ora.rac53.gsd  application    0/5    0/0    OFFLINE   OFFLINE
ora.rac53.ons  application    0/3    0/0    ONLINE    ONLINE    rac53
ora.rac53.vip  ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac53
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac54
ora....54.lsnr application    0/5    0/0    ONLINE    ONLINE    rac54
ora.rac54.gsd  application    0/5    0/0    OFFLINE   OFFLINE
ora.rac54.ons  application    0/3    0/0    ONLINE    ONLINE    rac54
ora.rac54.vip  ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac54
ora....ry.acfs ora....fs.type 0/5    0/     ONLINE    ONLINE    rac53
ora.scan1.vip  ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac53
正常，沒問題；
處理步驟：
1. 分佈啟動資料庫至nomount、mount、open狀態，檢視是哪個環節報的錯誤提示
SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11752
Session ID: 16 Serial number: 1
2. 退出剛才那個會話視窗，重新啟動到mount狀態，檢視告警日誌資訊
SQL> show parameter background_dump_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/hbang/hbang1/trace
SQL>
檢視最新的告警日誌資訊：hbang1_arc2_12195.trc
25 Initial buffer sizes: read 1024K, overflow 832K, change 805K
26 ORA-19815: WARNING: db_recovery_file_dest_size of 4621074432 bytes is 100.00    % used, and has 0 remaining bytes available.
27 ************************************************************************
28 You have following choices to free up space from recovery area:
29 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
30    then consider changing RMAN ARCHIVELOG DELETION POLICY.
31 2. Back up files to tertiary device such as tape using RMAN
32    BACKUP RECOVERY AREA command.
33 3. Add disk space and increase db_recovery_file_dest_size parameter to
34    reflect the new space.
35 4. Delete unnecessary files using RMAN DELETE command. If an operating
36    system command was used to delete files, then use RMAN CROSSCHECK and
37    DELETE EXPIRED commands.
38 ************************************************************************
39 *** 2016-04-20 15:30:19.345 4329 krsh.c
40 ARC2: Error 19809 Creating archive log file to '+FLASH'
41 *** 2016-04-20 15:30:19.345 2917 krsi.c
42 krsi_dst_fail: dest:1 err:19809 force:0 blast:1
43 DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
44 ORA-00312: online log 3 thread 2: '+FLASH/hbang/onlinelog/group_3.259.907536    373'
45 DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
46 ORA-00312: online log 3 thread 2: '+DATA/hbang/onlinelog/group_3.266.9075363    73'
47 ORA-00312: online log 3 thread 2: '+FLASH/hbang/onlinelog/group_3.259.907536    373'
問題分析，閃回空間不足；
3. 檢視閃回空間大小
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FLASH
db_recovery_file_dest_size           big integer 4407M
解決辦法有三個，如下：
     1> 設定新的歸檔路徑，操作語句為 SQL> alter system set log_archive_dest=new_location;
     2> 轉儲或刪除閃回恢復區裡的歸檔日誌；
     3> 增大閃回恢復區，操作語句為 SQL> alter system set db_recovery_file_dest_size=new_size;
如下采用刪除過舊歸檔備份進行處理。
4. RMAN方式登入，手動刪除不需要的歸檔日誌（eg：刪除三天以前的歸檔記錄）
[oracle@rac53 trace]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 20 15:49:22 2016

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

connected to target database: HBANG (DBID=1821727253, not open)

RMAN>delete archivelog all completed before 'sysdate - 3';
......
Do you really want to delete the above objects (enter YES or NO)?YES
......
RMAN> quit
5. 重新開啟資料庫驗證
SQL> alter database open;

Database altered.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29634949/viewspace-2084785/，如需轉載，請註明出處，否則將追究法律責任。

相關文章