标签归档：truncate IDL_UB1$

truncate IDL_UB1$导致数据库open hang

发表于 2024 年 5 月 25 日由惜分飞

在一次数据库恢复中,发现IDL_UB1$表被truncate,然后数据库在open过程中会hang住,而且不报任何错误,这里通过试验进行重现.对于这类问题,以前有过类似处理测试：truncate IDL_UB1$恢复
试验数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select object_name,object_type,owner,object_id,data_object_id,
2   last_ddl_time from dba_objects where object_name like 'IDL_UB%';

OBJECT_NAME   OBJECT_TYPE         OWNER     OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM
------------- ------------------- -------- ---------- -------------- ------------
IDL_UB1$      TABLE               SYS             225            225 24-AUG-13
IDL_UB2$      TABLE               SYS             227            227 24-AUG-13

truncate IDL_UB1$表

SQL> truncate table IDL_UB1$;
truncate table IDL_UB1$
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1090
Session ID: 125 Serial number: 5

alert日志报错信息

Sat May 25 20:23:11 2024
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98DA2B7, hshuid()+273] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_1090.trc  (incident=60156):
ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98DA2B7] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_60156/xifenfei_ora_1090_i60156.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

检查确认IDL_UB1$表被truncate成功

SQL> select count(1) from IDL_UB1$;

  COUNT(1)
----------
         0

SQL> select object_id,data_object_id,last_ddl_time from dba_objects where object_name like 'IDL_UB%';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------

       225          87377

重启数据库hang住

新会话查询信息
数据库open状态,但是无法执行ddl,无法执行exp/expdp等导出操作

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 25 20:29:01 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

--操作hang住
SQL> create table t1 as select * from dba_objects;

--exp操作hang住
[oracle@xifenfei ~]$ exp "'/ as sysdba'" tables=obj$

Export: Release 11.2.0.4.0 - Production on Sat May 25 20:39:03 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

--expdp操作hang住
[oracle@xifenfei ~]$ expdp "'/ as sysdba'" tables=obj$

Export: Release 11.2.0.4.0 - Production on Sat May 25 20:39:35 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

数据库在open 被hang的过程中等待事件信息
一直卡在db file sequential read等待事件上，而且等待的block信息一直不变，读取对象为IDL_UB2$

SQL> select username,program,event,P1,P2,P3 from v$session where wait_class#<>6 and username='SYS'
  2  AND SID NOT IN(SELECT DISTINCT SID FROM V$MYSTAT);

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2         P3
---------- ----------
SYS                            sqlplus@xifenfei (TNS V1-V3)
db file sequential read                                                   1
      1531          1


SQL> /

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2         P3
---------- ----------
SYS                            sqlplus@xifenfei (TNS V1-V3)
db file sequential read                                                   1
      1531          1


SQL> /

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2         P3
---------- ----------
SYS                            sqlplus@xifenfei (TNS V1-V3)
db file sequential read                                                   1
      1531          1

--读取对象
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 1531
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 1531 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
SYS
IDL_UB2$
TABLE              SYSTEM

trace数据库启动过程发现卡在select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# 语句部分.

PARSING IN CURSOR #140737276517064 len=132 dep=2 uid=0 oct=3 lid=0 tim=1716640552642714 hv=4260389146 ad='80963c78' 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 #140737276517064:c=17,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642713
BINDS #140737276517064:
 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=7ffff35f73a0  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=7ffff35f7370  bln=24  avl=01  flg=05
  value=0
 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=7ffff35f7340  bln=24  avl=06  flg=05
  value=184549376
EXEC #140737276517064:c=72,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642819
FETCH #140737276517064:c=4,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642832
STAT #140737276517064 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=5 us cost=3 size=44 card=2)'
STAT #140737276517064 id=2 cnt=0 pid=1 pos=1 obj=236 op='INDEX RANGE SCAN I_IDL_UB11 (cr=1 pr=0 pw=0 time=4 us cost=2 size=0 card=2)'
CLOSE #140737276517064:c=2,e=2,dep=2,type=0,tim=1716640552642858
=====================
PARSING IN CURSOR #140737276517064 len=135 dep=2 uid=0 oct=3 lid=0 tim=1716640552642876 hv=1115215392 ad='808c4c68' sqlid='c6awqs517jpj0'
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140737276517064:c=12,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552642875
BINDS #140737276517064:
 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=7ffff35f73a0  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=7ffff35f7370  bln=24  avl=01  flg=05
  value=0
 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=7ffff35f7340  bln=24  avl=06  flg=05
  value=184549376
EXEC #140737276517064:c=73,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552642977
WAIT #140737276517064: nam='db file sequential read' ela= 10 file#=1 block#=1610 blocks=1 obj#=237 tim=1716640552643001
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1522 blocks=1 obj#=226 tim=1716640552643027
WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=1521 blocks=1 obj#=226 tim=1716640552643051
FETCH #140737276517064:c=81,e=80,p=3,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643065
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1524 blocks=1 obj#=226 tim=1716640552643124
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=1523 blocks=1 obj#=226 tim=1716640552643143
FETCH #140737276517064:c=40,e=40,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643150
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1526 blocks=1 obj#=226 tim=1716640552643216
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1525 blocks=1 obj#=226 tim=1716640552643232
FETCH #140737276517064:c=36,e=36,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643239
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8200 blocks=1 obj#=226 tim=1716640552643405
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1527 blocks=1 obj#=226 tim=1716640552643420
FETCH #140737276517064:c=31,e=31,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643427
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8202 blocks=1 obj#=226 tim=1716640552643583
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8201 blocks=1 obj#=226 tim=1716640552643600
FETCH #140737276517064:c=34,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643608
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8204 blocks=1 obj#=226 tim=1716640552643766
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8203 blocks=1 obj#=226 tim=1716640552643785
FETCH #140737276517064:c=35,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643792
WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=8206 blocks=1 obj#=226 tim=1716640552644038
WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=8205 blocks=1 obj#=226 tim=1716640552644062
FETCH #140737276517064:c=49,e=49,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644070
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8208 blocks=1 obj#=226 tim=1716640552644296
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8207 blocks=1 obj#=226 tim=1716640552644313
FETCH #140737276517064:c=36,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644320
WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8209 blocks=1 obj#=226 tim=1716640552644479
FETCH #140737276517064:c=20,e=20,p=1,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644487
FETCH #140737276517064:c=2,e=1,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552644545
STAT #140737276517064 id=1 cnt=9 pid=0 pos=1 obj=226 op='TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=20 pr=10 pw=0 time=53 us cost=3 size=21 card=1)'
STAT #140737276517064 id=2 cnt=9 pid=1 pos=1 obj=237 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=11 pr=1 pw=0 time=46 us cost=2 size=0 card=1)'
CLOSE #140737276517064:c=3,e=3,dep=2,type=0,tim=1716640552644579
=====================
PARSING IN CURSOR #140737276517064 len=132 dep=2 uid=0 oct=3 lid=0 tim=1716640552644609 hv=1684122946 ad='80924938' sqlid='39m4sx9k63ba2'
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140737276517064:c=17,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=1716640552644609
BINDS #140737276517064:
 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=7ffff35f73a0  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=7ffff35f7370  bln=24  avl=01  flg=05
  value=0
 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=7ffff35f7340  bln=24  avl=06  flg=05
  value=184549376
EXEC #140737276517064:c=92,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=1716640552644735
WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1618 blocks=1 obj#=238 tim=1716640552644761
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1530 blocks=1 obj#=227 tim=1716640552644778
WAIT #140737276517064: nam='db file sequential read' ela= 10 file#=1 block#=1529 blocks=1 obj#=227 tim=1716640552644799
FETCH #140737276517064:c=60,e=60,p=3,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=1716640552644807
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1532 blocks=1 obj#=227 tim=1716640552644924
WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1531 blocks=1 obj#=227 tim=1716640552644940
FETCH #140737276517064:c=33,e=33,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=1716640552644947

发表在 Oracle | 标签为 hshuid, open hang, ORA-07445 hshuid, Oracle open hang处理, truncate IDL_UB1$ | 评论关闭

truncate IDL_UB1$恢复

发表于 2018 年 5 月 20 日由惜分飞

世界之大无奇不有,已经记不清这是第几个客户咨询IDL_UB1$ 被truncate之后导致数据库无法启动的case了.idl_ub1$表是用来存储PL/SQL的代码单元的，包括DIANA等，IDL在这里代表Interface Definition Language. 在数据库的启动过程中通过10046跟踪可以知道,有类似: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#的查询语句,由于该表被truncate之后,导致数据库启动无法绕过该sql,而hang住无法完全open成功.下午闲着没事通过模拟,可以对该故障实现正常open,并且导出数据
模拟业务数据

create user xff identified by oracle;
grant dba to xff;
conn xff/oracle
create table t_xifenfei as select * from dba_objects;
create index i_xifenfei on t_xifenfei(object_id);
create view v_xifenfei as select * from t_xifenfei;
create or replace procedure proc1(
para1 varchar2,
para2 out varchar2,
para3 in out varchar2
) as
v_name varchar2(20);
begin
 v_name :='xifenfei';
 para3 := v_name;
dbms_output.put_line('para3:'||para3);
end;
/
alter system checkpoint;

创建xff账户,并且创建有代表性的表,索引,存储过程,视图等.

模拟truncate IDL_UB1$表

SQL> conn / as sysdba
Connected.
SQL> truncate table IDL_UB1$;
truncate table IDL_UB1$
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 31325
Session ID: 177 Serial number: 7

重启数据库

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area 7499329536 bytes
Fixed Size                  2267832 bytes
Variable Size            1409287496 bytes
Database Buffers         6073352192 bytes
Redo Buffers               14422016 bytes
Database mounted.

数据库在mount之后,一直处于hang住状态,查看alert日志

Sun May 20 17:02:34 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98D94A7, hshuid()+273] [flags: 0x0, count: 1]
Errors in file /home/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_31325.trc  (incident=21781):
ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98D94A7] [Address not mapped to object] []
Incident details in: /home/u01/app/oracle/diag/rdbms/test/test/incident/incdir_21781/test_ora_31325_i21781.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun May 20 17:02:37 2018
Sweep [inc][21781]: completed
Sweep [inc2][21781]: completed
Sun May 20 17:02:37 2018
Dumping diagnostic data in directory=[cdmp_20180520170237], requested by (instance=1, osid=31325), summary=[incident=21781].
Sun May 20 17:02:55 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98D94A7, hshuid()+273] [flags: 0x0, count: 1]
Errors in file /home/u01/app/oracle/diag/rdbms/test/test/trace/test_m000_31373.trc  (incident=21821):
ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98D94A7] [Address not mapped to object] []
Incident details in: /home/u01/app/oracle/diag/rdbms/test/test/incident/incdir_21821/test_m000_31373_i21821.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun May 20 17:02:56 2018
Dumping diagnostic data in directory=[cdmp_20180520170256], requested by (instance=1, osid=31373 (M000)), summary=[incident=21821].

这类问题比较明显,正常方法无法打开,通过工具分析system文件,发现虽然truncate IDL_UB1$操作报错了,但是IDL_UB1$和对应的index I_IDL_UB11 obj#,dataobj#均已经改变,而且相关对象的segment header也变化为新dataobj#(truncate之后的),也就是说truncate在数据库中的主要更改操作已经完成.现在在缺少记录的情况下,数据库执行如下sql无法获取记录,从而无法open

PARSING IN CURSOR #140342551421712 len=132 dep=2 uid=0 oct=3 lid=0 tim=1526843464635335 hv=4260389146 ad='21af73218' 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 #140342551421712:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1526843464635335
BINDS #140342551421712:
 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=7fa40bec7d80  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=7fa40bec7d50  bln=24  avl=01  flg=05
  value=0
 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=7fa40bec7d20  bln=24  avl=06  flg=05
  value=184549376
EXEC #140342551421712:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1526843464635449
FETCH #140342551421712:c=0,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1526843464635462
STAT #140342551421712 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=4 us cost=3 size=44 card=2)'
STAT #140342551421712 id=2 cnt=0 pid=1 pos=1 obj=236 op='INDEX RANGE SCAN I_IDL_UB11 (cr=1 pr=0 pw=0 time=4 us cost=2 size=0 card=2)'
CLOSE #140342551421712:c=0,e=2,dep=2,type=0,tim=1526843464635496

通过对数据库采用技术欺骗手段,让数据库启动相关sql能够获取到记录(和正常查询的相同),从而实现数据库正常open

SQL> startup mount
ORACLE instance started.

Total System Global Area 7499329536 bytes
Fixed Size                  2267832 bytes
Variable Size            1409287496 bytes
Database Buffers         6073352192 bytes
Redo Buffers               14422016 bytes
Database mounted.
SQL> alter database open;

Database altered.

open成功之后,后台报大量的ORA-08103: object no longer exists,通过分析是由于truncate IDL_UB1$没有完全成功,导致出现该错误.解决方法就是对Oracle数据字典进行人工更新,把没有完成的truncate操作在数据库中给予完成.
导出数据
exp导出数据成功

但是expdp无法执行

[oracle@bogon oradata]$ expdp '"/ as sysdba"' schemas=xff file=1.dmp

Export: Release 11.2.0.4.0 - Production on Sun May 20 17:10:53 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

暂时未去研究对这个表进行重建,使用exp导出然后再imp导入是比较理想的办法

发表在非常规恢复 | 标签为 IDL_UB1$, IDL_UB1$ truncate, truncate IDL_UB1$ | 评论关闭

17813235971

QQ 咨询

标签归档：truncate IDL_UB1$

truncate IDL_UB1$导致数据库open hang

truncate IDL_UB1$恢复

文章分类

最近发表

标签归档：truncate IDL_UB1$

truncate IDL_UB1$导致数据库open hang

truncate IDL_UB1$恢复

标签云

文章分类

最近发表