ORA-03113: 通信通道的文件结尾

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-03113: 通信通道的文件结尾

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库启动报:ORA-03113: 通信通道的文件结尾

PS C:\Users\Administrator> sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 8月 3 11:05:03 2024

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

已连接到空闲例程。

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 2.0510E+10 bytes
Fixed Size                  2184632 bytes
Variable Size            1.1476E+10 bytes
Database Buffers         8992587776 bytes
Redo Buffers               40046592 bytes
SQL> shutdown immediate
ORA-01507: ??????


ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 2.0510E+10 bytes
Fixed Size                  2184632 bytes
Variable Size            1.1476E+10 bytes
Database Buffers         8992587776 bytes
Redo Buffers               40046592 bytes
数据库装载完毕。
ORA-03113: 通信通道的文件结尾
进程 ID: 4040
会话 ID: 1018 序列号: 7

这类错误,一般真正错误原因在alert日志中,查看alert日志

Sat Aug 03 08:15:12 2024
alter database mount exclusive
Successful mount of redo thread 1, with mount id 3557233552
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 11 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 745, block 80599, scn 7100295
Recovery of Online Redo Log: Thread 1 Group 1 Seq 745 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\XFF\REDO01.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 745, block 80599, scn 7120296
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
Sat Aug 03 08:15:19 2024
ARC0 started with pid=32, OS id=5496 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sat Aug 03 08:15:20 2024
ARC1 started with pid=33, OS id=3873072 
Sat Aug 03 08:15:20 2024
ARC2 started with pid=34, OS id=3873644 
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
sksasmowrt WriteConsole error 6
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_arc2_3873644.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 10737418240 字节) 已使用 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 d:\oracle\diag\rdbms\xff\xff\trace\xff_arc2_3873644.trc:
ORA-19809: 超出了恢复文件数的限制
ORA-19804: 无法回收 13760000 字节磁盘空间 (从 10737418240 限制中)
ARC2: Error 19809 Creating archive log file to 'D:\FRA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_744_%U_.ARC'
Sat Aug 03 08:15:20 2024
ARC3 started with pid=35, OS id=3873424 
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_ora_3873352.trc:
ORA-19815: ??: db_recovery_file_dest_size ?? (? 10737418240 ??) ??? 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 d:\oracle\diag\rdbms\xff\xff\trace\xff_ora_3873352.trc:
ORA-19809: ???????????
ORA-19804: ???? 12296704 ?????? (? 10737418240 ???)
ARCH: Error 19809 Creating archive log file to 'D:\FRA\XFF\ARCHIVELOG\2024_08_03\O1_MF_1_743_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_arc2_3873644.trc:
ORA-16038: 日志 3 sequence# 744 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 3 线程 1: 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_ora_3873352.trc:
ORA-16038: ?? 2 sequence# 743 ????
ORA-19809: ???????????
ORA-00312: ???? 2 ?? 1: 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'
USER (ospid: 3873352): terminating the instance due to error 16038
Sat Aug 03 08:15:27 2024
Instance terminated by USER, pid = 3873352

是由于闪回区满了,导致redo无法归档,从而使得数据库无法正常open,解决办法:
1. 清理以前归档日志
2. 把闪回区调大一些

数据库恢复遭遇ORA-00600[3705]

某个客户在一台机器上装3个oracle数据库,机器蓝屏后,使用pe拷贝出来所有数据文件,redo文件,控制文件等,在尝试恢复过程中,三个库都出现同样的ORA-600[3705]错误,在以前的数据库恢复中对于redo异常,使用过N次类似方法出来都未出问题,但是在ORACLE 9.2.0.1版本中确实出现诡异现象,这里记录处理过程和大家分享
尝试恢复数据库

C:\Documents and Settings\Administrator>set oracle_sid=telnet

C:\Documents and Settings\Administrator>sqlplus/nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期日 5月 25 13:04:29 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
已连接。
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-01113: 文件 2 需要介质恢复
ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\TELNET\UNDOTBS01.DBF'


SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00322: 日志 2 (线程 1) 不是当前副本
ORA-00312: 联机日志 2 线程 1: 'E:\ORACLE\ORADATA\TELNET\REDO02.LOG'

redo异常,尝试使用_allow_resetlogs_corruption参数启动数据库

SQL> startup mount pfile='c:\pfile.txt'
ORACLE 例程已经启动。

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 66763056 (在 05/12/2014 09:50:10 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\ORACLE\ORA92\RDBMS\ARC00312.001
ORA-00280: 更改 66763056 对于线程 1 是按序列 # 312 进行的


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件1需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\TELNET\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束

查看alert日志

Sun May 25 15:35:02 2014
ALTER DATABASE RECOVER    CANCEL  
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
Sun May 25 15:35:02 2014
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Sun May 25 15:35:09 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 66763056
Resetting resetlogs activation ID 0 (0x0)
Sun May 25 15:35:20 2014
Assigning activation ID 2117757301 (0x7e3a6975)
Sun May 25 15:35:20 2014
Errors in file e:\oracle\admin\telnet\bdump\telnet_lgwr_12140.trc:
ORA-00600: internal error code, arguments: [3705], [1], [1], [1], [1], [], [], []

Sun May 25 15:35:56 2014
Errors in file e:\oracle\admin\telnet\bdump\telnet_lgwr_12140.trc:
ORA-00600: internal error code, arguments: [3705], [1], [1], [1], [1], [], [], []

LGWR: terminating instance due to error 600
Instance terminated by LGWR, pid = 12140

创建控制文件继续恢复

SQL> recover database until cancel;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码,参数: [2130], [0], [1], [2], [], [], [], []

SQL> alter database backup controlfile to trace as 'c:\ctl.txt';

数据库已更改。

SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TELNET" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'E:\ORACLE\ORADATA\TELNET\REDO01.LOG'  SIZE 100M,
  9    GROUP 2 'E:\ORACLE\ORADATA\TELNET\REDO02.LOG'  SIZE 100M,
 10    GROUP 3 'E:\ORACLE\ORADATA\TELNET\REDO03.LOG'  SIZE 100M
 11  DATAFILE
 12    'E:\ORACLE\ORADATA\TELNET\SYSTEM01.DBF',
 13    'E:\ORACLE\ORADATA\TELNET\UNDOTBS01.DBF',
 14    'E:\ORACLE\ORADATA\TELNET\CWMLITE01.DBF',
 15    'E:\ORACLE\ORADATA\TELNET\DRSYS01.DBF',
 16    'E:\ORACLE\ORADATA\TELNET\EXAMPLE01.DBF',
 17    'E:\ORACLE\ORADATA\TELNET\INDX01.DBF',
 18    'E:\ORACLE\ORADATA\TELNET\ODM01.DBF',
 19    'E:\ORACLE\ORADATA\TELNET\TOOLS01.DBF',
 20    'E:\ORACLE\ORADATA\TELNET\USERS01.DBF',
 21    'E:\ORACLE\ORADATA\TELNET\XDB01.DBF'
 22  CHARACTER SET ZHS16GBK
 23  ;

控制文件已创建

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复

SQL> alter database open;

数据库已更改。