【实战】使用“基表修改法”恢复被“set unused”方式误删除的列

在《【实验】列删除的一些细节探索》http://space.itpub.net/519536/viewspace-616123中谈到了可以通过手工修改tab$ col$基表的方式对被“set unused”方式误删除的列进行恢复。

通过这个文章，我来给大家深入的演示一下这个极端的恢复过程，供参考。

文中为了展示全貌，有一些不可回避的换行，影响了显示效果，如果您有深入研究的兴趣，可以将这篇文章拷贝到Ultraedit中并使用“Ctrl+W”调整折行模式进行研究比较。

1.创建实验用测试表t，并初始化数据
sec@ora10g> create table t (x number, y number);
sec@ora10g> insert into t values (1,1);
sec@ora10g> insert into t values (2,2);
sec@ora10g> insert into t values (3,3);
sec@ora10g> commit;

2.查看表结构和测试数据
sec@ora10g> desc t;
 Name       Null?    Type
 ---------- -------- ------------
 X                   NUMBER
 Y                   NUMBER

sec@ora10g> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

3.查看四个视图中关于t表的定义信息
1）查看dba_objects视图（为清晰的显示，SQL*Plus下要做精心的格式化）
sys@ora10g> col OWNER for a5
sys@ora10g> col OBJECT_NAME for a11
sys@ora10g> col SUBOBJECT_NAME for a3
sys@ora10g> col OBJECT_TYPE for a11
sys@ora10g> select * from dba_objects where object_name = 'T' and wner = 'SEC';

OWNER OBJECT_NAME SUB  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S
----- ----------- --- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - -
SEC   T                    16416          16416 TABLE       2009-10-09 08:18:59 2009-10-09 08:18:59 2009-10-09:08:18:59 VALID   N N N

2）查看obj$视图（为清晰的显示，SQL*Plus下要做精心的格式化）
sys@ora10g> set lines 2000
sys@ora10g> col OBJ# for 99999
sys@ora10g> col DATAOBJ# for 99999
sys@ora10g> col name for a4
sys@ora10g> col SUBNAME for a7
sys@ora10g> col TYPE# for 99
sys@ora10g> col REMOTEOWNER for a11
sys@ora10g> col LINKNAME for a8
sys@ora10g> col FLAGS for 99
sys@ora10g> col OID$ for a4
sys@ora10g> col SPARE1 for 99
sys@ora10g> col SPARE2 for 99
sys@ora10g> col SPARE3 for 999
sys@ora10g> col SPARE4 for a6
sys@ora10g> col SPARE5 for a6
sys@ora10g> col SPARE6 for a6
sys@ora10g> select * from obj$ where name = 'T';

  OBJ# DATAOBJ#     OWNER# NAME  NAMESPACE SUBNAME TYPE# CTIME               MTIME               STIME                   STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ -------- ---------- ---- ---------- ------- ----- ------------------- ------------------- ------------------- ---------- ----------- -------- ----- ---- ------ ------ ------ ------ ------ ------
 16416    16416         40 T             1             2 2009-10-09 08:18:59 2009-10-09 08:18:59 2009-10-09 08:18:59          1                          0       6       1

obj$视图对应的插入语句如下：
Insert into SYS.OBJ$
   (OBJ#, DATAOBJ#, OWNER#, NAME, NAMESPACE,
    SUBNAME, TYPE#, CTIME, MTIME, STIME,
    STATUS, REMOTEOWNER, LINKNAME, FLAGS, OID$,
    SPARE1, SPARE2, SPARE3, SPARE4, SPARE5,
    SPARE6)
 Values
   (16416, 16416, 40, 'T', 1,
    NULL, 2, TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'),
    1, NULL, NULL, 0, NULL,
    6, 1, NULL, NULL, NULL,
    NULL);

3）查看tab$视图（为清晰的显示，SQL*Plus下要做精心的格式化）
根据上面得到的OBJ#得到tab$视图中关于表t的信息
sys@ora10g> col flags clear
sys@ora10g> col flags for 99999999999
sys@ora10g> col SPARE6 clear
sys@ora10g> col AUDIT$ for a38
sys@ora10g> col SPARE1 clear
sys@ora10g> col SPARE1 for 999
sys@ora10g> col PROPERTY clear
sys@ora10g> select * from tab$ where obj# = 16416;

  OBJ# DATAOBJ#        TS#      FILE#     BLOCK#      BOBJ#       TAB#       COLS    CLUCOLS   PCTFREE$   PCTUSED$   INITRANS   MAXTRANS        FLAGS AUDIT$                                 ROWCNT BLKCNT EMPCNT AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETIME         SAMPLESIZE DEGREE INSTANCES INTCOLS KERNELCOLS   PROPERTY TRIGFLAG SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ -------------------------------------- ------ ------ ------ ------ ------ ------ ---------- ------ ------------------- ---------- ------ --------- ------- ---------- ---------- -------- ------ ------ ------ ------ ------ -------------------
 16416    16416          5          5         11                                2                    10         40          1        255   1073741825 --------------------------------------                                                                                                             2       2           536870912        0    736                             2009-10-09 08:18:59

tab$视图对应的插入语句如下：
Insert into SYS.TAB$
   (OBJ#, DATAOBJ#, TS#, FILE#, BLOCK#,
    BOBJ#, TAB#, COLS, CLUCOLS, PCTFREE$,
    PCTUSED$, INITRANS, MAXTRANS, FLAGS, AUDIT$,
    ROWCNT, BLKCNT, EMPCNT, AVGSPC, CHNCNT,
    AVGRLN, AVGSPC_FLB, FLBCNT, ANALYZETIME, SAMPLESIZE,
    DEGREE, INSTANCES, INTCOLS, KERNELCOLS, PROPERTY,
    TRIGFLAG, SPARE1, SPARE2, SPARE3, SPARE4,
    SPARE5, SPARE6)
 Values
   (16416, 16416, 5, 5, 11,
    NULL, NULL, 2, NULL, 10,
    40, 1, 255, 1073741825, '--------------------------------------',
    NULL, NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL, NULL,
    NULL, NULL, 2, 2, 536870912,
    0, 736, NULL, NULL, NULL,
    NULL, TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'));

4）查看col$视图（为清晰的显示，SQL*Plus下要做精心的格式化）
sys@ora10g> col DEFLENGTH for 9
sys@ora10g> set long 10
sys@ora10g> select * from col$ where obj# = 16416;

  OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$ DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM. SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ ---------- ---------- ------------ ---------- ---- ----- ---------- ------------ ---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------- ------ ------ ------ ------ ------ -------------------
 16416          1          1           22          0 X        2         22            0                                0                               1          0          0           0      0      0      0
 16416          2          2           22          0 Y        2         22            0                                0                               2          0          0           0      0      0      0

col$视图对应的插入语句如下：
Insert into SYS.COL$
   (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
    NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
    SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
    PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
    SPARE3, SPARE4, SPARE5, SPARE6)
 Values
   (16416, 1, 1, 22, 0,
    'X', 2, 22, 0, NULL,
    NULL, 0, NULL, NULL, 1,
    0, 0, 0, 0, 0,
    0, NULL, NULL, NULL);
Insert into SYS.COL$
   (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
    NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
    SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
    PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
    SPARE3, SPARE4, SPARE5, SPARE6)
 Values
   (16416, 2, 2, 22, 0,
    'Y', 2, 22, 0, NULL,
    NULL, 0, NULL, NULL, 2,
    0, 0, 0, 0, 0,
    0, NULL, NULL, NULL);

4.使用“set unused”语句将x列修改为不可用（背后的故事即将展现在我们的面前）
1）删除x列
sys@ora10g> alter table sec.t set unused column x cascade constraints;

Table altered.

2）变化一：表结构的变化
前：
sec@ora10g> desc t;
 Name       Null?    Type
 ---------- -------- ------------
 X                   NUMBER
 Y                   NUMBER

后：
sec@ora10g> desc t;
 Name       Null?    Type
 ---------- -------- ------------
 Y                   NUMBER

比较结果：显然，x列不见了。

2）变化二：从dba_tab_cols视图中观察一下修改前与修改后的区别
前：
sys@ora10g> select table_name,column_name,hidden_column from dba_tab_cols where table_name='T';

TABLE_NAME                     COLUMN_NAME                    HID
------------------------------ ------------------------------ ---
T                              X                              NO
T                              Y                              NO

后：
sys@ora10g> select table_name,column_name,hidden_column from dba_tab_cols where table_name='T';

TABLE_NAME                     COLUMN_NAME                    HID
------------------------------ ------------------------------ ---
T                              Y                              NO
T                              SYS_C00001_09100910:19:16$     YES

比较结果：显然，x列被系统自动标识为隐藏状态（注意，这里只是标注为隐藏，并没有真正的删除，否则就没有这个高级的恢复方式啦）。

3）变化三：比较一下dba_objects视图中的变化
前：
sys@ora10g> select * from dba_objects where object_name = 'T' and wner = 'SEC';

OWNER OBJECT_NAME SUB  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S
----- ----------- --- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - -
SEC   T                    16416          16416 TABLE       2009-10-09 08:18:59 2009-10-09 08:18:59 2009-10-09:08:18:59 VALID   N N N
后：
sys@ora10g> select * from dba_objects where object_name = 'T' and wner = 'SEC';

OWNER OBJECT_NAME SUB  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S
----- ----------- --- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - -
SEC   T                    16416          16416 TABLE       2009-10-09 08:18:59 2009-10-09 10:19:16 2009-10-09:10:19:16 VALID   N N N

比较结果：较显然，观察后，发现只有LAST_DDL_TIME和TIMESTAMP字段发生了变化，更新了一下最后DDL的时间。

4）变化四：比较一下obj$视图中的变化
前：
sys@ora10g> select * from obj$ where name = 'T';

  OBJ# DATAOBJ#     OWNER# NAME  NAMESPACE SUBNAME TYPE# CTIME               MTIME               STIME                   STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ -------- ---------- ---- ---------- ------- ----- ------------------- ------------------- ------------------- ---------- ----------- -------- ----- ---- ------ ------ ------ ------ ------ ------
 16416    16416         40 T             1             2 2009-10-09 08:18:59 2009-10-09 08:18:59 2009-10-09 08:18:59          1                          0       6       1
后：
sys@ora10g> select * from obj$ where name = 'T';

  OBJ# DATAOBJ#     OWNER# NAME  NAMESPACE SUBNAME TYPE# CTIME               MTIME               STIME                   STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ -------- ---------- ---- ---------- ------- ----- ------------------- ------------------- ------------------- ---------- ----------- -------- ----- ---- ------ ------ ------ ------ ------ ------
 16416    16416         40 T             1             2 2009-10-09 08:18:59 2009-10-09 10:19:16 2009-10-09 10:19:16          1                          0       6       2

比较结果：MTIME、STIME和SPARE2字段发生变化，前两个与结构修改有关，最后一个字段由原来的“1”修改成了现在的“2”。

5）变化五：比较一下tab$视图中的变化
前：
sys@ora10g> select * from tab$ where obj# = 16416;

  OBJ# DATAOBJ#        TS#      FILE#     BLOCK#      BOBJ#       TAB#       COLS    CLUCOLS   PCTFREE$   PCTUSED$   INITRANS   MAXTRANS        FLAGS AUDIT$                                 ROWCNT BLKCNT EMPCNT AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETIME         SAMPLESIZE DEGREE INSTANCES INTCOLS KERNELCOLS   PROPERTY TRIGFLAG SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ -------------------------------------- ------ ------ ------ ------ ------ ------ ---------- ------ ------------------- ---------- ------ --------- ------- ---------- ---------- -------- ------ ------ ------ ------ ------ -------------------
 16416    16416          5          5         11                                2                    10         40          1        255   1073741825 --------------------------------------                                                                                                             2       2           536870912        0    736                             2009-10-09 08:18:59
后：
sys@ora10g> select * from tab$ where obj# = 16416;

  OBJ# DATAOBJ#        TS#      FILE#     BLOCK#      BOBJ#       TAB#       COLS    CLUCOLS   PCTFREE$   PCTUSED$   INITRANS   MAXTRANS        FLAGS AUDIT$                                 ROWCNT BLKCNT EMPCNT AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETIME         SAMPLESIZE DEGREE INSTANCES INTCOLS KERNELCOLS   PROPERTY TRIGFLAG SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ -------------------------------------- ------ ------ ------ ------ ------ ------ ---------- ------ ------------------- ---------- ------ --------- ------- ---------- ---------- -------- ------ ------ ------ ------ ------ -------------------
 16416    16416          5          5         11                                1                    10         40          1        255   1073741825 --------------------------------------                                                                                                             2       2           537395200        0    736                             2009-10-09 10:19:16

比较结果：COLS、PROPERTY、SPARE6两个字段内容发生变化。重点关注COLS和PROPERTY列的变化，COLS列由原来的“2”变为了现在的“1”，因为原来表中包含2列x和y，修改后仅剩1列y；PROPERTY列用原来的“536870912”变为现在的“537395200”。

6）变化六：比较一下col$视图中的变化
前：
sys@ora10g> col name for a26
sys@ora10g> select * from col$ where obj# = 16416;

  OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME                       TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$ DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM. SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ ---------- ---------- ------------ ---------- -------------------------- ----- ---------- ------------ ---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------- ------ ------ ------ ------ ------ -------------------
 16416          1          1           22          0 X                              2         22            0                                0                               1          0          0           0      0      0      0
 16416          2          2           22          0 Y                              2         22            0                                0                               2          0          0           0      0      0      0
后：
sys@ora10g> select * from col$ where obj# = 16416;

  OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME                       TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$ DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM. SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
------ ---------- ---------- ------------ ---------- -------------------------- ----- ---------- ------------ ---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------- ------ ------ ------ ------ ------ -------------------
 16416          0          1           22          0 SYS_C00001_09100910:19:16$     2         22            0                                0                               1      32800          0           0      0      0      0
 16416          1          2           22          0 Y                              2         22            0                                0                               2          0          0           0      0      0      0

比较结果：COL#、NAME、PROPERTY三列发生明显变化。此三列均需重点关注。恢复时需要调整。

5.隆重推出使用“基表修改法”对被删除的字段进行恢复，请不要眨眼
重点修改的基表只有两个：tab$和col$

1）恢复前查看一下表中数据
sec@ora10g> select * from t;

         Y
----------
         1
         2
         3

2）恢复基表tab$的内容
（1）将cols字段修改为表原有的字段数，这里是2行
sys@ora10g> update tab$ set cols=2 where obj#=16416;

1 row updated.

（2）经试验证明，这一步骤可以不进行操作，修改亦无妨
update tab$ set PROPERTY=536870912 where obj#=16416;

3）恢复基表col$的内容
（1）将COL#字段更新后保持与INTCOL#字段内容相同
sys@ora10g> update col$ set COL#=INTCOL# where OBJ#=16416;

2 rows updated.

（2）恢复name字段为原有面目
sys@ora10g> update col$ set name='X' where obj#=16416 and SEGCOL#=1;

1 row updated.

（3）将PROPERTY字段统统的设置为“0”
sys@ora10g> update col$ set PROPERTY=0 where obj#=16416;

2 rows updated.

sys@ora10g> commit;

Commit complete.

4）恢复完成了，我们来Check一下恢复的效果，看到下面的内容是不是有一种崩溃的感觉，仿佛自己的一切付出都荡然无存！不要着急，不要着急，休息一下，经继续看我分解之。
sys@ora10g> select * from sec.t;

         Y
----------
         1
         2
         3

5）之所以上面没有看到被恢复的x字段内容，是因为我们没有完成一个既简单又不可或缺的步骤，那就是：重启数据库！！
重中之重：重新启动数据库。（为了恢复这个“小”错误，需要重启数据库，代价还是很大的！）
sys@ora10g> startup force;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2074152 bytes
Variable Size             486541784 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.
sys@ora10g> conn sec/sec
Connected.
sec@ora10g>
sec@ora10g> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

6）OK，到此，整个“分解麻雀”的过程完成了，目的达到，能收工了么？如果是在严峻的生产环境上遇到的这个问题，也许您还需要马上使用其他的手段备份一下这个表中的数据，以防不测。

7）还有一种“简单粗暴的恢复方式”，请参见《【实战】使用“基表修改法”恢复被“set unused”方式误删除的列（续）》http://space.itpub.net/519536/viewspace-616175

6.重要小结
上面介绍的是一种特殊的恢复方法，是在没有任何备份的情况下使用的极端恢复手段，对细节不了解的朋友要小心使用。

这个恢复案例对DBA的教训或警告是什么：
教训1：为什么会出现上面的情况，也许某些朋友对“set unused”的原理不是很熟悉，误用了这个命令。因此如果您是一名维护DBA，在操作数据库时，尤其是在维护生产数据库时，一定要谨小慎微，要头脑清醒到知道每一个“Enter”键背后的酸甜苦辣，否则您将极有可能因一次误操作玩火自焚，消失在茫茫的DBA梯队之中！！

教训2：“备份无比崇高”，如果您有一个可用的备份，恢复这个错误的手段就多，给自己留一个后路，不要对自己过于放纵和随意！！

教训3：在做任何操作之前，要做充分的测试，不允许在生产数据库上使用未经测试过的命令，即使这个命令表面上看上去是那么的自然，很多DBA也许就是因为这些“自然”现象消失了！！

教训4：慢慢补充吧，值得思考的内容很多。

请牢记：DBA是一种非常危险的动物！！珍爱DBA生命，远离数据库故障：）

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-616174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-616174/