接到网友技术支持请求,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,关闭自扩展,至此该数据库恢复完成
