[20231013]rename IDL_UB1$的恢复.txt
[20231013]rename IDL_UB1$的恢复.txt
--//看了https://www.anbob.com/archives/7545.html链接,对方rename操作,导致无法建立表操作.
--//idl_ub1$表是用来存储PL/SQL的代码单元的，包括DIANA等，IDL在这里代表Interface Definition Language.
--//这个对象的含义可以从Ixora找到一点提示:
It is an intermediate language in which the structure of database tables and the logic of PL/SQL program units can
be consistently represented as attributed trees. Oracle uses the DIANA IDL, which comes from compilers for the Ada
programming language. DIANA stands for Descriptive Intermediate Attributed Notation for Ada. Anyway, this is one of
four tables in the data dictionary used to store the DIANA for PL/SQL program units, and the database objects that
they reference.
--//翻译:
它是一种中间语言，其中数据库表的结构和PL/SQL程序单元的逻辑可以一致地表示为属性树。Oracle使用DIANA IDL，它来自于Ada编程语
言的编译器。戴安娜代表Ada的描述性中间属性符号。无论如何，这是数据字典中用于存储PL/SQL程序单元的DIANA以及它们引用的数据库
对象的四个表之一。
--//对方rename后没有重启,通过修改数据字典obj$完成恢复,我先重复原始链接的测试。
1.环境:
SYS@book> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 10
/
BYTES SEGMENT_NAME SEGMENT_TYPE OWNER
---------- --------------- ------------ -----
285212672 IDL_UB1$ TABLE SYS
75497472 SOURCE$ TABLE SYS
32505856 IDL_UB2$ TABLE SYS
25165824 C_TOID_VERSION# CLUSTER SYS
13631488 I_SOURCE1 INDEX SYS
12582912 C_OBJ# CLUSTER SYS
12582912 ARGUMENT$ TABLE SYS
12582912 JAVA$MC$ TABLE SYS
11534336 IDL_CHAR$ TABLE SYS
9 rows selected.
--//285212672/1024/1024 = 272M.
2.事前准备:
SYS@book> select rowid,owner#,name from obj$ where name='IDL_UB1$';
ROWID OWNER# NAME
------------------ ---------- ----------------------------------------
AAAAASAABAAAADzAAX 0 IDL_UB1$
SYS@book> @ rowid AAAAASAABAAAADzAAX
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
18 1 243 23 0x4000F3 1,243 alter system dump datafile 1 block 243 ;
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1\$'| awk '{print int($1/8192), " ",$1-int($1/8192)*8192, " " ,$2}'
243 6325 IDL_UB1$
351 5692 IDL_UB1$
375 5692 IDL_UB1$
17086 4802 IDL_UB1$
34025 5594 SELECT
34029 5278 COMMIT"SELECT
36154 5278 COMMIT"SELECT
95511 4482 IDL_UB1$l
--//注：显示的第一个字段对应块号，第一个字段对应相应数据块的偏移.
--//主要关注数据块243,351,375,17086,95511.
SYS@book> @ find_objz 1 243 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 240 8 TABLE SYS OBJ$ 0 65536 SYSTEM 1 0 1 240
SYS@book> @ find_objz 1 351 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 344 8 INDEX SYS I_OBJ2 0 65536 SYSTEM 1 0 1 344
SYS@book> @ find_objz 1 375 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 368 8 INDEX SYS I_OBJ5 0 65536 SYSTEM 1 0 1 368
SYS@book> @ find_objz 1 17086 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 17024 128 CLUSTER SYS C_TOID_VERSION# 18 1048576 SYSTEM 1 0 1 3464
BBED> set dba 1,17086
DBA 0x004042be (4211390 1,17086)
BBED> map
File: /mnt/ramdisk/book/system01.dbf (1)
Block: 17086 Dba:0x004042be
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[3], 12 bytes @106
sb2 kdbr[3] @118
ub1 freespace[7842] @124
ub1 rowdata[222] @7966
ub4 tailchk @8188
--//offset =4802 在freespace 区间,不用修改.
SYS@book> @ ind2 obj$
Display indexes where table or index name matches obj$...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- ------------------------------ ----
SYS OBJ$ I_OBJ1 1 OBJ#
2 OWNER#
3 TYPE#
I_OBJ2 1 OWNER#
2 NAME
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 NAMESPACE
4 REMOTEOWNER
5 LINKNAME
6 SUBNAME
7 TYPE#
8 SPARE3
9 OBJ#
I_OBJ3 1 OID$
I_OBJ4 1 DATAOBJ#
2 TYPE#
3 OWNER#
I_OBJ5 1 SPARE3
2 NAME
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 NAMESPACE
4 TYPE#
5 OWNER#
6 REMOTEOWNER
7 LINKNAME
8 SUBNAME
9 OBJ#
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SYS OBJ$ I_OBJ1 NORMAL YES VALID NO N 2 250 87018 87018 1158 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ2 NORMAL YES VALID NO N 3 876 87018 87018 64485 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ3 NORMAL NO VALID NO N 2 16 3421 3421 249 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ4 NORMAL NO VALID NO N 2 383 9358 87018 3237 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ5 NORMAL YES VALID NO N 3 876 87018 87018 64473 2023-08-30 22:00:13 1 VISIBLE
--//I_OBJ2,I_OBJ5都是obj#的索引,里面都包含name字段,换一句话讲如果使用bbed恢复.这3块都需要恢复.
3.开始测试:
SYS@book> rename IDL_UB1$ to IDL_UB1X;
Table renamed.
SYS@book> alter system checkpoint;
System altered.
SYS@book> @ ddl scott.dept
BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY',true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
all_objects
*
ERROR at line 4:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//尝试改回原来的名字，操作如下：
SYS@book> rename IDL_UB1X to IDL_UB1$;
rename IDL_UB1X to IDL_UB1$
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//现在已经破坏,许多命令无法执行，主要设计一些包的执行命令都有问题，通过修改数据字典执行如下：
SYS@book> update obj$ set name='IDL_UB1$' where name='IDL_UB1X';
1 row updated.
SYS@book> commit ;
Commit complete.
--//再次重启数据库，问题消失，先自己尝试这样可以修复可行的。
--//下一个版本尝试使用bbed恢复,千万不要在生产系统做这样的测试!!
--//看了https://www.anbob.com/archives/7545.html链接,对方rename操作,导致无法建立表操作.
--//idl_ub1$表是用来存储PL/SQL的代码单元的，包括DIANA等，IDL在这里代表Interface Definition Language.
--//这个对象的含义可以从Ixora找到一点提示:
It is an intermediate language in which the structure of database tables and the logic of PL/SQL program units can
be consistently represented as attributed trees. Oracle uses the DIANA IDL, which comes from compilers for the Ada
programming language. DIANA stands for Descriptive Intermediate Attributed Notation for Ada. Anyway, this is one of
four tables in the data dictionary used to store the DIANA for PL/SQL program units, and the database objects that
they reference.
--//翻译:
它是一种中间语言，其中数据库表的结构和PL/SQL程序单元的逻辑可以一致地表示为属性树。Oracle使用DIANA IDL，它来自于Ada编程语
言的编译器。戴安娜代表Ada的描述性中间属性符号。无论如何，这是数据字典中用于存储PL/SQL程序单元的DIANA以及它们引用的数据库
对象的四个表之一。
--//对方rename后没有重启,通过修改数据字典obj$完成恢复,我先重复原始链接的测试。
1.环境:
SYS@book> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 10
/
BYTES SEGMENT_NAME SEGMENT_TYPE OWNER
---------- --------------- ------------ -----
285212672 IDL_UB1$ TABLE SYS
75497472 SOURCE$ TABLE SYS
32505856 IDL_UB2$ TABLE SYS
25165824 C_TOID_VERSION# CLUSTER SYS
13631488 I_SOURCE1 INDEX SYS
12582912 C_OBJ# CLUSTER SYS
12582912 ARGUMENT$ TABLE SYS
12582912 JAVA$MC$ TABLE SYS
11534336 IDL_CHAR$ TABLE SYS
9 rows selected.
--//285212672/1024/1024 = 272M.
2.事前准备:
SYS@book> select rowid,owner#,name from obj$ where name='IDL_UB1$';
ROWID OWNER# NAME
------------------ ---------- ----------------------------------------
AAAAASAABAAAADzAAX 0 IDL_UB1$
SYS@book> @ rowid AAAAASAABAAAADzAAX
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
18 1 243 23 0x4000F3 1,243 alter system dump datafile 1 block 243 ;
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1\$'| awk '{print int($1/8192), " ",$1-int($1/8192)*8192, " " ,$2}'
243 6325 IDL_UB1$
351 5692 IDL_UB1$
375 5692 IDL_UB1$
17086 4802 IDL_UB1$
34025 5594 SELECT
34029 5278 COMMIT"SELECT
36154 5278 COMMIT"SELECT
95511 4482 IDL_UB1$l
--//注：显示的第一个字段对应块号，第一个字段对应相应数据块的偏移.
--//主要关注数据块243,351,375,17086,95511.
SYS@book> @ find_objz 1 243 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 240 8 TABLE SYS OBJ$ 0 65536 SYSTEM 1 0 1 240
SYS@book> @ find_objz 1 351 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 344 8 INDEX SYS I_OBJ2 0 65536 SYSTEM 1 0 1 344
SYS@book> @ find_objz 1 375 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 368 8 INDEX SYS I_OBJ5 0 65536 SYSTEM 1 0 1 368
SYS@book> @ find_objz 1 17086 '' 1
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ ------------------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
1 17024 128 CLUSTER SYS C_TOID_VERSION# 18 1048576 SYSTEM 1 0 1 3464
BBED> set dba 1,17086
DBA 0x004042be (4211390 1,17086)
BBED> map
File: /mnt/ramdisk/book/system01.dbf (1)
Block: 17086 Dba:0x004042be
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[3], 12 bytes @106
sb2 kdbr[3] @118
ub1 freespace[7842] @124
ub1 rowdata[222] @7966
ub4 tailchk @8188
--//offset =4802 在freespace 区间,不用修改.
SYS@book> @ ind2 obj$
Display indexes where table or index name matches obj$...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ---------- ---------- ---- ------------------------------ ----
SYS OBJ$ I_OBJ1 1 OBJ#
2 OWNER#
3 TYPE#
I_OBJ2 1 OWNER#
2 NAME
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 NAMESPACE
4 REMOTEOWNER
5 LINKNAME
6 SUBNAME
7 TYPE#
8 SPARE3
9 OBJ#
I_OBJ3 1 OID$
I_OBJ4 1 DATAOBJ#
2 TYPE#
3 OWNER#
I_OBJ5 1 SPARE3
2 NAME
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 NAMESPACE
4 TYPE#
5 OWNER#
6 REMOTEOWNER
7 LINKNAME
8 SUBNAME
9 OBJ#
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
----------- ---------- ---------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SYS OBJ$ I_OBJ1 NORMAL YES VALID NO N 2 250 87018 87018 1158 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ2 NORMAL YES VALID NO N 3 876 87018 87018 64485 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ3 NORMAL NO VALID NO N 2 16 3421 3421 249 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ4 NORMAL NO VALID NO N 2 383 9358 87018 3237 2023-08-30 22:00:13 1 VISIBLE
OBJ$ I_OBJ5 NORMAL YES VALID NO N 3 876 87018 87018 64473 2023-08-30 22:00:13 1 VISIBLE
--//I_OBJ2,I_OBJ5都是obj#的索引,里面都包含name字段,换一句话讲如果使用bbed恢复.这3块都需要恢复.
3.开始测试:
SYS@book> rename IDL_UB1$ to IDL_UB1X;
Table renamed.
SYS@book> alter system checkpoint;
System altered.
SYS@book> @ ddl scott.dept
BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY',true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
all_objects
*
ERROR at line 4:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//尝试改回原来的名字，操作如下：
SYS@book> rename IDL_UB1X to IDL_UB1$;
rename IDL_UB1X to IDL_UB1$
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
--//现在已经破坏,许多命令无法执行，主要设计一些包的执行命令都有问题，通过修改数据字典执行如下：
SYS@book> update obj$ set name='IDL_UB1$' where name='IDL_UB1X';
1 row updated.
SYS@book> commit ;
Commit complete.
--//再次重启数据库，问题消失，先自己尝试这样可以修复可行的。
--//下一个版本尝试使用bbed恢复,千万不要在生产系统做这样的测试!!
