ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实战】使用“基表修改法”恢复被“set unused”方式误删除的列

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

原创 Linux操作系统 作者:secooler 时间:2009-10-09 17:44:53 0 删除 编辑
在《【实验】列删除的一些细节探索》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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7974972