連結： http://blog.itpub.net/28602568/viewspace-1304392/

標題： Oracle startup報錯ORA-03113: end-of-file on communication channel 解決方案

[oracle@lottery ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 20 10:38:28 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1820540928 bytes
Fixed Size                  2229304 bytes
Variable Size            1291848648 bytes
Database Buffers          520093696 bytes
Redo Buffers                6369280 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 11210
Session ID: 1144 Serial number: 5

SQL>

檢視告警日誌：

cat $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

Mon Oct 20 10:38:31 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =167
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/mao/u02/app/oracle/product/11.2.0.3.0/dbhome_1
System name:    Linux
Node name:   lottery
Release:        2.6.32-279.el6.x86_64
Version:        #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine:        x86_64
Using parameter settings in server-side spfile /opt/mao/u02/app/oracle/product/11.2.0.3.0/dbhome_1/dbs/spfilelottery.ora
System parameters with non-default values:
processes                = 1000
sessions                 = 1524
memory_target            = 1744M
control_files            = "/opt/mao/u02/app/oracle/oradata/lottery/control01.ctl"
control_files            = "/opt/mao/u02/app/oracle/flash_recovery_area/lottery/control02.ctl"
db_block_size            = 8192
compatible               = "11.2.0.0.0"
log_archive_format       = "ARC%S_%R.%T_%D.log"
db_recovery_file_dest    = "/opt/mao/u02/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3882M
undo_tablespace          = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = ""
dispatchers              = "(PROTOCOL=TCP) (SERVICE=lotteryXDB)"
audit_file_dest          = "/opt/mao/u02/app/oracle/admin/lottery/adump"
audit_trail              = "DB"
db_name                  = "lottery"
open_cursors             = 300
diagnostic_dest          = "/opt/mao/u02/app/oracle"
Mon Oct 20 10:38:32 2014
PMON started with pid=2, OS id=11147
Mon Oct 20 10:38:32 2014
PSP0 started with pid=3, OS id=11149
Mon Oct 20 10:38:33 2014
VKTM started with pid=4, OS id=11151 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Oct 20 10:38:33 2014
GEN0 started with pid=5, OS id=11155
Mon Oct 20 10:38:33 2014
DIAG started with pid=6, OS id=11157
Mon Oct 20 10:38:33 2014
DBRM started with pid=7, OS id=11159
Mon Oct 20 10:38:33 2014
DIA0 started with pid=8, OS id=11161
Mon Oct 20 10:38:33 2014
MMAN started with pid=9, OS id=11163
Mon Oct 20 10:38:33 2014
DBW0 started with pid=10, OS id=11165
Mon Oct 20 10:38:33 2014
LGWR started with pid=11, OS id=11167
Mon Oct 20 10:38:33 2014
CKPT started with pid=12, OS id=11169
Mon Oct 20 10:38:33 2014
SMON started with pid=13, OS id=11171
Mon Oct 20 10:38:33 2014
RECO started with pid=14, OS id=11173
Mon Oct 20 10:38:33 2014
MMON started with pid=15, OS id=11175
Mon Oct 20 10:38:34 2014
MMNL started with pid=16, OS id=11177
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/mao/u02/app/oracle
Mon Oct 20 10:38:34 2014
ALTER DATABASE   MOUNT
Mon Oct 20 10:38:44 2014
Successful mount of redo thread 1, with mount id 4016329578
Database mounted in Exclusive Mode
Lost write protection disabled
Mon Oct 20 10:38:45 2014
Completed: ALTER DATABASE   MOUNT
Mon Oct 20 10:38:45 2014
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Mon Oct 20 10:38:47 2014
ARC0 started with pid=20, OS id=11212
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Oct 20 10:38:48 2014
ARC1 started with pid=21, OS id=11214
Mon Oct 20 10:38:48 2014
ARC2 started with pid=22, OS id=11216
Mon Oct 20 10:38:48 2014
ARC3 started with pid=23, OS id=11218
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /opt/mao/u02/app/oracle/diag/rdbms/lottery/lottery/trace/lottery_arc0_11212.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file /opt/mao/u02/app/oracle/diag/rdbms/lottery/lottery/trace/lottery_arc2_11216.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file /opt/mao/u02/app/oracle/diag/rdbms/lottery/lottery/trace/lottery_ora_11210.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and has 0 remaining bytes available.
****************************************************************************************************
You have following choices to free up space from recovery area:
You have following choices to free up space from recovery area:
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
2. Back up files to tertiary device such as tape using RMAN
2. Back up files to tertiary device such as tape using RMAN   BACKUP RECOVERY AREA command.

   BACKUP RECOVERY AREA command.   BACKUP RECOVERY AREA command.3. Add disk space and increase db_recovery_file_dest_size parameter to

3. Add disk space and increase db_recovery_file_dest_size parameter to   reflect the new space.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   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 and4. 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.   system command was used to delete files, then use RMAN CROSSCHECK and   DELETE EXPIRED commands.

********************   DELETE EXPIRED commands.*********************

************************************************************************
ARC2: Error 19809 Creating archive log file to '/opt/mao/u02/app/oracle/flash_recovery_area/LOTTERY/archivelog/2014_10_20/o1_mf_1_89_%u_.arc'
ARC0: Error 19809 Creating archive log file to '/opt/mao/u02/app/oracle/flash_recovery_area/LOTTERY/archivelog/2014_10_20/o1_mf_1_90_%u_.arc'
ARCH: Error 19809 Creating archive log file to '/opt/mao/u02/app/oracle/flash_recovery_area/LOTTERY/archivelog/2014_10_20/o1_mf_1_88_%u_.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance lottery - Archival Error
ORA-16038: log 2 sequence# 89 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/opt/mao/u02/app/oracle/oradata/lottery/redo02.log'
Errors in file /opt/mao/u02/app/oracle/diag/rdbms/lottery/lottery/trace/lottery_ora_11210.trc:
ORA-16038: log 1 sequence# 88 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/mao/u02/app/oracle/oradata/lottery/redo01.log'
USER (ospid: 11210): terminating the instance due to error 16038
Mon Oct 20 10:38:52 2014
System state dump requested by (instance=1, osid=11210), summary=[abnormal instance termination].
System State dumped to trace file /opt/mao/u02/app/oracle/diag/rdbms/lottery/lottery/trace/lottery_diag_11157.trc
Dumping diagnostic data in directory=[cdmp_20141020103852], requested by (instance=1, osid=11210), summary=[abnormal instance termination].
Instance terminated by USER, pid = 11210

...從告警日誌中完全可以看出歸檔路徑空間不足【解決在mount 更改歸檔路徑大小】

如下：

[oracle@lottery~ ]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 20 10:39:35 2014

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

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1820540928 bytes
Fixed Size                  2229304 bytes
Variable Size            1291848648 bytes
Database Buffers          520093696 bytes
Redo Buffers                6369280 bytes
Database mounted.
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 3882M
SQL> alter system set db_recovery_file_dest_size =10G scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL> SELECT * FROM v$flash_recovery_area_usage f where f.file_type='ARCHIVED LOG';

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------------- -------------------------------------- -----------------------

ARCHIVED LOG 53.71 0 30

SQL>

--檢視告警日誌啟動情況：
[oracle@lottery trace]$ cat alert_lottery.log

Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Oct 20 10:41:38 2014
db_recovery_file_dest_size of 10240 MB is 35.60% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Oct 20 10:41:38 2014
ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;

Mon Oct 20 10:41:53 2014
alter database open
Mon Oct 20 10:41:54 2014
LGWR: STARTING ARCH PROCESSES
Mon Oct 20 10:41:54 2014
ARC0 started with pid=21, OS id=11397
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Oct 20 10:41:55 2014
ARC1 started with pid=22, OS id=11399
Mon Oct 20 10:41:55 2014
ARC2 started with pid=23, OS id=11401
Mon Oct 20 10:41:55 2014
ARC3 started with pid=24, OS id=11403
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Oct 20 10:42:39 2014
Archived Log entry 27 added for thread 1 sequence 90 ID 0xef3a8838 dest 1:
Mon Oct 20 10:42:41 2014
Archived Log entry 28 added for thread 1 sequence 89 ID 0xef3a8838 dest 1:
Mon Oct 20 10:42:43 2014
Trying to expand controlfile section 11 for Oracle Managed Files
Expanded controlfile section 11 from 28 to 56 records
Requested to grow by 28 records; added 1 blocks of records
Archived Log entry 29 added for thread 1 sequence 88 ID 0xef3a8838 dest 1:
Mon Oct 20 10:42:50 2014
Thread 1 advanced to log sequence 92 (thread open)
Thread 1 opened at log sequence 92
Current log# 1 seq# 92 mem# 0: /opt/mao/u02/app/oracle/oradata/lottery/redo01.log
Successful open of redo thread 1
Mon Oct 20 10:42:52 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Oct 20 10:42:52 2014
SMON: enabling cache recovery
Mon Oct 20 10:43:06 2014
Archived Log entry 30 added for thread 1 sequence 91 ID 0xef3a8838 dest 1:
Mon Oct 20 10:43:06 2014
[11365] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:3977911514 end:3977920644 diff:9130 (91 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Oct 20 10:43:06 2014
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Oct 20 10:43:11 2014
QMNC started with pid=25, OS id=11427
Completed: alter database open
[oracle@lottery trace]$

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle startup報錯ORA-03113: end-of-file on communication channel 解決方案

相關文章