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

2020-02-03 10:51:48

这个小文儿是对《【实战】使用“基表修改法”恢复被“set unused”方式误删除的列》的补充
文章地址：http://space.itpub.net/?uid-519536-action-viewspace-itemid-616174

因为在实验中我们是模拟的故障场景，并且提前记录了几张核心表中的数据，也可以使用下面这种“简单粗暴”的方法进行恢复

1.查看实验表数据
sec@ora10g> select * from t;

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

2.删除x列
sec@ora10g> alter table sec.t set unused column x cascade constraints;

Table altered.

3.删除x列后的效果
sec@ora10g> select * from t;

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

4.在SYS用户下使用事先准备的脚本重新初始化obj$基表（这一步骤不是必须的）
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> delete from obj$ where OBJ#=16416;

1 row deleted.

sys@ora10g> Insert into SYS.OBJ$
2     (OBJ#, DATAOBJ#, OWNER#, NAME, NAMESPACE,
3      SUBNAME, TYPE#, CTIME, MTIME, STIME,
4      STATUS, REMOTEOWNER, LINKNAME, FLAGS, OID$,
5      SPARE1, SPARE2, SPARE3, SPARE4, SPARE5,
6      SPARE6)
7   Values
8     (16416, 16416, 40, 'T', 1,
9      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'),
10      1, NULL, NULL, 0, NULL,
11      6, 1, NULL, NULL, NULL,
12      NULL);

1 row created.

5.在SYS用户下使用事先准备的脚本重新初始化tab$基表
sys@ora10g> delete from TAB$ where OBJ#=16416;

1 row deleted.

sys@ora10g> Insert into SYS.TAB$
2     (OBJ#, DATAOBJ#, TS#, FILE#, BLOCK#,
3      BOBJ#, TAB#, COLS, CLUCOLS, PCTFREE$,
4      PCTUSED$, INITRANS, MAXTRANS, FLAGS, AUDIT$,
5      ROWCNT, BLKCNT, EMPCNT, AVGSPC, CHNCNT,
6      AVGRLN, AVGSPC_FLB, FLBCNT, ANALYZETIME, SAMPLESIZE,
7      DEGREE, INSTANCES, INTCOLS, KERNELCOLS, PROPERTY,
8      TRIGFLAG, SPARE1, SPARE2, SPARE3, SPARE4,
9      SPARE5, SPARE6)
10   Values
11     (16416, 16416, 5, 5, 11,
12      NULL, NULL, 2, NULL, 10,
13      40, 1, 255, 1073741825, '--------------------------------------',
14      NULL, NULL, NULL, NULL, NULL,
15      NULL, NULL, NULL, NULL, NULL,
16      NULL, NULL, 2, 2, 536870912,
17      0, 736, NULL, NULL, NULL,
18      NULL, TO_DATE('10/09/2009 08:18:59', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

6.在SYS用户下使用事先准备的脚本重新初始化obj$基表
sys@ora10g> delete from COL$ where OBJ#=16416;

2 rows deleted.

sys@ora10g> Insert into SYS.COL$
2     (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
3      NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
4      SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
5      PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
6      SPARE3, SPARE4, SPARE5, SPARE6)
7   Values
8     (16416, 1, 1, 22, 0,
9      'X', 2, 22, 0, NULL,
10      NULL, 0, NULL, NULL, 1,
11      0, 0, 0, 0, 0,
12      0, NULL, NULL, NULL);

1 row created.

sys@ora10g> Insert into SYS.COL$
2     (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
3      NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
4      SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
5      PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
6      SPARE3, SPARE4, SPARE5, SPARE6)
7   Values
8     (16416, 2, 2, 22, 0,
9      'Y', 2, 22, 0, NULL,
10      NULL, 0, NULL, NULL, 2,
11      0, 0, 0, 0, 0,
12      0, NULL, NULL, NULL);

1 row created.

sys@ora10g> commit;

Commit complete.

7.重新启动数据库
sec@ora10g> conn / as sysdba
Connected.
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.

8.重现表t的原有风貌
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from t;

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

9.恢复完成，这个小文儿只是一个补充，详细的常规恢复内容敬请参见《【实战】使用“基表修改法”恢复被“set unused”方式误删除的列》http://space.itpub.net/?uid-519536-action-viewspace-itemid-616174

-- The End --