當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

ORA-03113: end-of-file on communication channel Process ID: 252 Session ID: 1 Serial number: 3

發(fā)布時(shí)間：2025/3/21 编程问答 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 ORA-03113: end-of-file on communication channel Process ID: 252 Session ID: 1 Serial number: 3 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

進(jìn)入字符界面：

sqlplus? / as sysdba

startup nomount;

alter database mount;

alter database open;

報(bào)如下錯(cuò)誤：

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 252
Session ID: 1 Serial number: 3

我也從來(lái)沒(méi)有遇到這個(gè)錯(cuò)誤，只好上google 搜索，看了一堆資料。

其中有一篇文章

http://www.dbanotes.net/Oracle/ORA-03113.htm

提醒了我，其實(shí) 遇到數(shù)據(jù)庫(kù)出錯(cuò)的時(shí)候，最先需要做的事就是查看

alter_orcl.log 這個(gè)文件。這個(gè)不是技術(shù)，而是方法論

然后去找 ?app/admin/orcl/bdump 目錄下的alter 告警文件。

c:/>? sqlplus / nolog

sql> conn / as sysdba

　　　　Connected to an idle instance.

sql> startup nomount

ORACLE instance started.

Total System Global Area? 426852352 bytes
Fixed Size????????????????? 　　1375060 bytes
Variable Size??????????????????? 301991084 bytes
Database Buffers???????????? 117440512 bytes
Redo Buffers??????????????????? 6045696 bytes

sql> alter database? mount;

Database altered.

到這一個(gè)步驟，就停下來(lái)。

大家知道

nomount 狀態(tài) 數(shù)據(jù)庫(kù)獲取spfile的配置來(lái)啟動(dòng)數(shù)據(jù)庫(kù)，其實(shí)這個(gè)時(shí)候我就可以

通過(guò) show parameter? 查看 background_dump_dest? 這個(gè)參數(shù) 來(lái)了解

alter 文件到底存放在那里了。

mount 狀態(tài)，數(shù)據(jù)庫(kù)實(shí)例已經(jīng) 獲取了控制文件。控制文件里面有scn

有數(shù)據(jù)文件的指針，等等很多信息。

background_dump_dest? d:/app/diag/rdbms/orcl/orcl/trace

知道告警文件在什么地方，那就去查看和分析吧。

Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile D:/APP/PRODUCT/11.2.0/DBHOME_1/DATABASE/SPFILEORCL.ORA
System parameters with non-default values:
? processes??????????????? = 150
? memory_target??????????? = 408M
? control_files??????????? = "D:/APP/ORADATA/ORCL/CONTROL01.CTL"
? control_files??????????? = "D:/APP/FLASH_RECOVERY_AREA/ORCL/CONTROL02.CTL"
? db_block_size??????????? = 8192
? compatible?????????????? = "11.2.0.0.0"
? log_archive_format?????? = "ARC%S_%R.%T"
? db_recovery_file_dest??? = "D:/app/flash_recovery_area"
? db_recovery_file_dest_size= 4977M
? undo_tablespace????????? = "UNDOTBS1"
? remote_login_passwordfile= "EXCLUSIVE"
? db_domain??????????????? = ""
? dispatchers????????????? = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
? audit_file_dest????????? = "D:/APP/ADMIN/ORCL/ADUMP"
? audit_trail????????????? = "DB"
? db_name????????????????? = "orcl"
? open_cursors???????????? = 300
? diagnostic_dest????????? = "D:/APP"
Fri Feb 18 19:11:07 2011
PMON started with pid=2, OS id=2188
Fri Feb 18 19:11:07 2011
VKTM started with pid=3, OS id=2788 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri Feb 18 19:11:07 2011
GEN0 started with pid=4, OS id=3896
Fri Feb 18 19:11:07 2011
DIAG started with pid=5, OS id=248
Fri Feb 18 19:11:07 2011
DBRM started with pid=6, OS id=936
Fri Feb 18 19:11:07 2011
PSP0 started with pid=7, OS id=2972
Fri Feb 18 19:11:07 2011
DIA0 started with pid=8, OS id=2976
Fri Feb 18 19:11:07 2011
MMAN started with pid=9, OS id=3044
Fri Feb 18 19:11:07 2011
DBW0 started with pid=10, OS id=1308
Fri Feb 18 19:11:07 2011
LGWR started with pid=11, OS id=2092
Fri Feb 18 19:11:07 2011
CKPT started with pid=12, OS id=2264
Fri Feb 18 19:11:07 2011
SMON started with pid=13, OS id=3516
Fri Feb 18 19:11:07 2011
RECO started with pid=14, OS id=664
Fri Feb 18 19:11:07 2011
MMON started with pid=15, OS id=2164
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Feb 18 19:11:08 2011
MMNL started with pid=16, OS id=3788
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:/app
Fri Feb 18 19:11:42 2011
alter database mount
Fri Feb 18 19:11:46 2011
Successful mount of redo thread 1, with mount id 1271263854
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Fri Feb 18 19:12:49 2011
alter database open
Fri Feb 18 19:12:49 2011
LGWR: STARTING ARCH PROCESSES
Fri Feb 18 19:12:49 2011
ARC0 started with pid=20, OS id=2072
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Feb 18 19:12:50 2011
ARC1 started with pid=21, OS id=356
Fri Feb 18 19:12:50 2011
ARC2 started with pid=22, OS id=3132
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Fri Feb 18 19:12:50 2011
ARC3 started with pid=23, OS id=1020
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_arc1_356.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
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 d:/app/diag/rdbms/orcl/orcl/trace/orcl_arc1_356.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47236096 bytes disk space from 5218762752 limit
ARC1: Error 19809 Creating archive log file to 'D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_179_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_arc1_356.trc:
ORA-16038: log 2 sequence# 179 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:/APP/ORADATA/ORCL/REDO02.LOG'
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4068.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
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 d:/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4068.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44871680 bytes disk space from 5218762752 limit
ARCH: Error 19809 Creating archive log file to 'D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_178_%U_.ARC'
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4068.trc:
ORA-16038: log 1 sequence# 178 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'D:/APP/ORADATA/ORCL/REDO01.LOG'
USER (ospid: 4068): terminating the instance due to error 16038
Instance terminated by USER, pid = 4068
Fri Feb 18 19:13:49 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled

閃回區(qū)目錄滿了，并且 oracle 提供了相應(yīng)的解決方法。

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.

四選一，我選擇四。

在操作系統(tǒng)層，刪除歸檔文件。

在操作系統(tǒng)層面刪除目錄 D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/

下時(shí)間靠前的歸檔日志。

然后通過(guò)rman 來(lái)刪除歸檔信息。

c:> set oracle_sid=orcl

c:> rman target /

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
validation failed for archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_68_6OW6HZXS_.ARC RECID=1 STAMP=743439880
validation failed for archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_69_6OW6JNJM_.ARC RECID=2 STAMP=743439903
validation failed for archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_70_6OW6K7J3_.ARC RECID=3 STAMP=743439916
validation failed for archived log

......

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key???? Thrd Seq???? S Low Time
------- ---- ------- - ---------
1?????? 1??? 68????? X 18-FEB-11
??????? Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_68_6OW6HZXS_.ARC

2?????? 1??? 69????? X 18-FEB-11
??????? Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_69_6OW6JNJM_.ARC

3?????? 1??? 70????? X 18-FEB-11
??????? Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_70_6OW6K7J3_.ARC

4?????? 1??? 71????? X 18-FEB-11
??????? Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_71_6OW6KOPG_.ARC

5?????? 1??? 72????? X 18-FEB-11
??????? Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_72_6OW6LB89_.ARC

......

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_68_6OW6HZXS_.ARC RECID=1 STAMP=743439880
deleted archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_69_6OW6JNJM_.ARC RECID=2 STAMP=743439903
deleted archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_70_6OW6K7J3_.ARC RECID=3 STAMP=743439916
deleted archived log

......

Deleted 103 EXPIRED objects

RMAN> exit

Recovery Manager complete.。

rman target /?? 是用的就是控制文件來(lái)記錄備份信息。刪除無(wú)用的歸檔信息，估計(jì)控制文件里面也會(huì)被修改

SQL> alter database open;

Database altered.

SQL>

ok，數(shù)據(jù)庫(kù)啟動(dòng)了。

轉(zhuǎn)載于:https://www.cnblogs.com/uniqueness/archive/2013/05/05/3061066.html

總結(jié)

以上是生活随笔為你收集整理的ORA-03113: end-of-file on communication channel Process ID: 252 Session ID: 1 Serial number: 3的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： js社交分享功能
下一篇： [Step By Step]SAP HA