oracle数据库提示ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O
接到网友技术支持请求,win 2003 ntfs格式文件系统,Oracle 8.1.7版本,主机重启后,数据库无法正常启动,offline datafile 15,数据库open成功,但是datafile 无法正常online,报错为:ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file,请求协助处理
SQL> recover datafile 'D:/ORACLE/ORADATA/ORCL/ZSF_DATA.DBF';
ORA-00283: 恢复会话因错误而取消
ORA-01115: 从文件 15 读取块时出现 IO 错误 (块 # 1030071)
ORA-01110: 数据文件 15: 'D:/ORACLE/ORADATA/ORCL/ZSF_DATA.DBF'
ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O
OSD-04026: 无效的参数经过. (OS 1030071)
使用bbed,成功online datafile 15
Tue Oct 28 16:30:35 2014
ALTER DATABASE RECOVER datafile 15
Tue Oct 28 16:30:35 2014
Media Recovery Datafile: 15
Media Recovery Start
Media Recovery Log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 245110 Reading mem 0
Mem# 0 errs 0: D:/ORACLE/ORADATA/ORCL/REDO03.LOG
Media Recovery failed with error 1115
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 15 ...
Tue Oct 28 16:32:53 2014
Shutting down instance (abort)
License high water mark = 6
Instance terminated by USER, pid = 1548
Starting up ORACLE RDBMS Version: 8.1.7.0.0.
System parameters with non-default values:
processes = 600
shared_pool_size = 52428800
large_pool_size = 20971520
java_pool_size = 20971520
control_files = D:/oracle/oradata/ORCL/control01.ctl, D:/oracle/oradata/ORCL/control02.ctl
db_block_buffers = 19200
db_block_size = 8192
compatible = 8.1.0
log_buffer = 32768
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
db_files = 1024
db_file_multiblock_read_count= 8
max_enabled_roles = 30
remote_login_passwordfile= EXCLUSIVE
global_names = TRUE
distributed_transactions = 500
instance_name = ORCL
service_names = ORCL
mts_dispatchers = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
open_links = 4
sort_area_size = 65536
sort_area_retained_size = 65536
db_name = ORCL
open_cursors = 500
ifile = D:/oracle/admin/ORCL/pfile/init.ora
os_authent_prefix =
job_queue_processes = 4
job_queue_interval = 10
parallel_max_servers = 5
background_dump_dest = D:/oracle/admin/ORCL/bdump
user_dump_dest = D:/oracle/admin/ORCL/udump
max_dump_file_size = 10240
oracle_trace_collection_name=
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
SNP0 started with pid=8
SNP1 started with pid=9
SNP2 started with pid=10
SNP3 started with pid=11
Tue Oct 28 16:33:01 2014
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Oct 28 16:33:02 2014
ALTER DATABASE MOUNT
Tue Oct 28 16:33:06 2014
Successful mount of redo thread 1, with mount id 1389958722.
Tue Oct 28 16:33:06 2014
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Oct 28 16:33:49 2014
ALTER DATABASE RECOVER database until cancel
Tue Oct 28 16:33:49 2014
Media Recovery Start
Media Recovery Log
kcrrga: Warning. Log sequence in archive filename wrapped
to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.
Old log archive with same name might be overwritten.
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Tue Oct 28 16:34:03 2014
ALTER DATABASE RECOVER LOGFILE 'D:/ORACLE/ORADATA/ORCL/REDO02.LOG'
Tue Oct 28 16:34:03 2014
Media Recovery Log D:/ORACLE/ORADATA/ORCL/REDO02.LOG
Incomplete recovery applied all redo ever generated.
Recovery completed through change %s139866389
Media Recovery Complete
Completed: ALTER DATABASE RECOVER LOGFILE 'D:/ORACLE/ORADA
Tue Oct 28 16:34:29 2014
alter database datafile 15 online
Tue Oct 28 16:34:29 2014
Completed: alter database datafile 15 online
Tue Oct 28 16:34:36 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 139866389
Tue Oct 28 16:34:38 2014
Thread 1 opened at log sequence 1
Current log# 2 seq# 1 mem# 0: D:/ORACLE/ORADATA/ORCL/REDO02.LOG
Successful open of redo thread 1.
Tue Oct 28 16:34:38 2014
SMON: enabling cache recovery
Tue Oct 28 16:34:38 2014
Dictionary check beginning
Dictionary check complete
Tue Oct 28 16:34:39 2014
SMON: enabling tx recovery
Tue Oct 28 16:34:44 2014
Completed: alter database open resetlogs
数据库datafile 15 online成功后,客户操作业务继续发生ORA-600[ktsxs_add2]错误
Tue Oct 28 17:07:42 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02340.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
Tue Oct 28 17:07:53 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02340.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
Tue Oct 28 17:08:03 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02340.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
Tue Oct 28 17:08:16 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02340.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
Tue Oct 28 17:08:23 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02308.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
Tue Oct 28 17:08:31 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02340.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
Tue Oct 28 17:08:38 2014
Errors in file D:/oracle/admin/ORCL/udump/ORA02308.TRC:
ORA-00600: 内部错误代码，自变量: [ktsxs_add2], [14], [15], [42534], [5732], [5733], [], []
通过分析相关日志发现是insert插入表报错,很好理解,该库的datafile 15已经超过了系统的限制,现在继续插入数据,因此报错,查询可能异常对象
SQL> col segment_name for a20
SQL> SELECT distinct OWNER, SEGMENT_NAME, SEGMENT_TYPE, A.PARTITION_NAME
2 FROM DBA_EXTENTS A
3 WHERE FILE_ID = 15
4 AND 1030071 <= BLOCK_ID;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -------------------- ------------------
PARTITION_NAME
------------------------------
ZSF DETAIL TABLE
ZSF DETAIL1 INDEX
ZSF DETAIL2 INDEX
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -------------------- ------------------
PARTITION_NAME
------------------------------
ZSF DETAIL3 INDEX
ZSF DETAIL4 INDEX
ZSF FK_RECI_ORD INDEX
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -------------------- ------------------
PARTITION_NAME
------------------------------
ZSF PREPAY1 INDEX
ZSF RECEDETAIL1 INDEX
创建新表空间
Create tablespace zsf_new datafile 'D:/ORACLE/ORADATA/ORCL/ZSF_DATA_new01.dbf' size 4096m;
alter tablespace zsf_new add datafile 'D:/ORACLE/ORADATA/ORCL/ZSF_DATA_new02.dbf'
size 128m autoextend on next 128M maxsize 4096m;
迁移异常对象到新表空间
alter table ZSF.DETAIL move tablespace ZSF_new;
alter index ZSF.DETAIL1 rebuild tablespace ZSF_new;
alter index ZSF.DETAIL2 rebuild tablespace ZSF_new;
alter index ZSF.DETAIL3 rebuild tablespace ZSF_new;
alter index ZSF.DETAIL4 rebuild tablespace ZSF_new;
alter index ZSF.FK_RECI_ORD rebuild tablespace ZSF_new;
alter index ZSF.PREPAY1 rebuild tablespace ZSF_new;
alter index ZSF.RECEDETAIL1 rebuild tablespace ZSF_new;
然后对于datafile 15所在表空间增加新文件,因为已经迁移了异常对象,然后resize datafile 15小于8G,关闭自扩展,至此该数据库恢复完成
