适用范围

Oracle Database - Enterprise Edition - Version 11.2.0.4

问题概述

truncate table IDL_UB1$; <--执行后，重启数据库失败
shutdown immediate
SQL> startup
ORACLE instance started.

Total System Global Area 3123322880 bytes
Fixed Size                  2257312 bytes
Variable Size             721423968 bytes
Database Buffers         2382364672 bytes
Redo Buffers               17276928 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 34881
Session ID: 191 Serial number: 3
SQL> 

问题原因

### 1. 问题模拟
#### 1.1 信息检查
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,
  2  dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid) row_number,obj#,dataobj#,owner#,name from obj$ where name in
  3  ('IDL_UB1$','I_IDL_UB11');

   FILE_ID   BLOCK_ID ROW_NUMBER       OBJ#   DATAOBJ#     OWNER# NAME
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------
         1        243         23        225        225          0 IDL_UB1$
         1        243         34        236        236          0 I_IDL_UB11

SQL> 

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,
  2  dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid) row_number,obj#,dataobj# from tab$ where obj#=225;

   FILE_ID   BLOCK_ID ROW_NUMBER       OBJ#   DATAOBJ#
---------- ---------- ---------- ---------- ----------
         1        155          1        225        225

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,
  2  dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid) row_number,obj#,dataobj# from IND$ where obj#=236;

   FILE_ID   BLOCK_ID ROW_NUMBER       OBJ#   DATAOBJ#
---------- ---------- ---------- ---------- ----------
         1        155          5        236        236

SQL> 

#### 1.2 恢复代码生成
spool /home/oracle/IDL_UB1.txt
set serveroutput on
begin
for i in (select file_id,block_id,blocks from dba_extents where segment_name in ('IDL_UB1$','I_IDL_UB11') ) loop
for j in 0..i.blocks-1 loop
	dbms_output.put_line('cp dba 2,' || trim(i.block_id+j) || ' to dba 1,' || trim(i.block_id+j));
end loop;
end loop;
end;
/

#### 1.3 创建数据
alter database datafile 1 autoextend on;
alter tablespace system add datafile '/oradata/orcl/system02.dbf' size 200M autoextend on;
alter tablespace system add datafile '/oradata/orcl/system03.dbf' size 200M autoextend on;

create user hsql identified by abcd1234;
grant dba to hsql;

set serveroutput on
begin
for i in 1..1000 loop
execute immediate 'create table hsql.tab'||i||' as select * from dba_users';
end loop;
end;
/

#### 1.4 故障模拟
truncate table IDL_UB1$;
sqlplus / as sysdba
shutdown abort
SQL> startup
ORACLE instance started.

Total System Global Area 3123322880 bytes
Fixed Size                  2257312 bytes
Variable Size             721423968 bytes
Database Buffers         2382364672 bytes
Redo Buffers               17276928 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 34881
Session ID: 191 Serial number: 3


SQL> 

解决方案

### 1. 10046分析
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3123322880 bytes
Fixed Size                  2257312 bytes
Variable Size             721423968 bytes
Database Buffers         2382364672 bytes
Redo Buffers               17276928 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_34931.trc
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 34931
Session ID: 191 Serial number: 3

### 2. 启动过程日志
PARSING IN CURSOR #140115172650384 len=132 dep=2 uid=0 oct=3 lid=0 tim=1659507556629492 hv=4260389146 ad='119790930' sqlid='cvn54b7yz0s8u'
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140115172650384:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1659507556629492
BINDS #140115172650384:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f6f1b17d410  bln=22  avl=03  flg=05
  value=1310
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f6f1b17d3e0  bln=24  avl=02  flg=05
  value=1
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f6f1b17d3b0  bln=24  avl=06  flg=05
  value=184549376
EXEC #140115172650384:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1659507556629542
FETCH #140115172650384:c=0,e=2,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1659507556629549
STAT #140115172650384 id=1 cnt=0 pid=0 pos=1 obj=225 op='TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=1 pr=0 pw=0 time=1 us cost=3 size=44 card=2)'
STAT #140115172650384 id=2 cnt=0 pid=1 pos=1 obj=236 op='INDEX RANGE SCAN I_IDL_UB11 (cr=1 pr=0 pw=0 time=1 us cost=2 size=0 card=2)'
CLOSE #140115172650384:c=0,e=1,dep=2,type=1,tim=1659507556629569

### 3. 备份原数据文件
mkdir bak
cp system01.dbf bak/
### 4. copy正常数据文件system01.dbf到服务器
copy dba 2,243 to dba 1,243
copy dba 2,155 to dba 1,155
/home/oracle/IDL_UB1.txt
### 5. 启动数据库

### 6. 业务数据导出测试
exp \'/ as sysdba \' TABLES=hsql.tab99 file=/home/oracle/hsql.dmp log=/home/oracle/hsql.log

#### 7. 建议逻辑导出重建数据库

参考文档

### 系统对象IDL_UB1$表的含义及作用
https://www.modb.pro/db/17302