不小心drop column了一个列,真的凉凉了吗?
前几天数据库恢复群的小伙伴问了个问题,说他一个客户不小心把一个表进行了drop column操作了。问我怎么办?能恢复吗?
据了解,这个库好像没有备份、没有归档。这可能大概率是凉凉了啊!
针对drop column的情况,实际上12年前就测过(http://www.killdb.com/2012/02/25/column-to-remove-recovery-testing-and-dont-try/)。只不过之前是在Oracle 10gR2测试的,这里在11.2中测下,供大家参考!
SQL> conn roger/roger
Connected.
SQL>
SQL>
SQL> create table test10 as select owner,object_name,object_type,object_id from dba_objects where rownum < 5;Table created.SQL> select rowid,2 dbms_rowid.rowid_object(rowid) object_id,3 dbms_rowid.rowid_relative_fno(rowid) file_id,4 dbms_rowid.rowid_block_number(rowid) block_id,5 dbms_rowid.rowid_row_number(rowid) num6 from test10;ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ---------- ---------- ---------- ----------
AAAVZIAAEAAAAF7AAA 87624 4 379 0
AAAVZIAAEAAAAF7AAB 87624 4 379 1
AAAVZIAAEAAAAF7AAC 87624 4 379 2
AAAVZIAAEAAAAF7AAD 87624 4 379 3SQL> select * from test10;OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------- ----------
SYS ICOL$ TABLE 20
SYS I_USER1 INDEX 46
SYS CON$ TABLE 28
SYS UNDO$ TABLE 15
drop column之前使用bbed查看一下block 数据
BBED> set file 4 block 379FILE# 4BLOCK# 379BBED> p *kdbr[1]
rowdata[43]
-----------
ub1 rowdata[43] @8142 0x2cBBED> x/rcccnnnnn
rowdata[43] @8142
-----------
flag@8142: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8143: 0x00
cols@8144: 4col 0[3] @8145: SYS
col 1[7] @8149: I_USER1
col 2[5] @8157: INDEX
col 3[2] @8163: 46 BBED>
接下来进行drop column 操作。
SQL> alter table test10 drop column OBJECT_TYPE;Table altered.SQL> alter tablespace users offline;Tablespace altered.
SQL>
drop column之后的block中实际数据和每行的偏移量,这里仍然通过bbed工具查看一下。
[oracle@oratest ~]$ bbed parfile=par.txt
Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Aug 24 04:50:03 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> set file 4 block 379FILE# 4BLOCK# 379BBED> p *kdbr[1]
rowdata[43]
-----------
ub1 rowdata[43] @8142 0x2cBBED> x /rccnnnnnnn
rowdata[43] @8142
-----------
flag@8142: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8143: 0x02
cols@8144: 3col 0[3] @8145: SYS
col 1[7] @8149: I_USER1
col 2[2] @8157: 46
大家会发现对于drop column的情况来讲,Oracle本质上是将后面一个列的数据挪到了前面一个列。大家注意看前面2次操作的行头的偏移量,仔细看offset 8157 就会发现。 在drop column之前,8157 offset本来是我们这个表的object_type列数据,然而drop column之后,这个偏移量的数据变成了我们的object_id列了。
所以,你可以理解为是这个drop column的数据实际上被抹掉了。
当然如果你是drop的表中的最后一个列,那么不影响,可以恢复的。很明显,如果是中间列,就如上面测试,就没戏了。
如果说一定要想恢复怎么办?实际上你通过10046 trace跟着到一系列递归sql,然后反向执行,然后再向col$插入被删除的列数据,实际上是可以的,但是你仍然知识恢复了表结构,被drop column的数据是无法恢复的。
除了利用数据库备份、闪回等技术之外,那么如果有时候真的像删除这个列,是不是有更好的方法呢?
实际上是有的,你可以选择先把列进行unused操作(unsed column和drop column一样,都需要获得表级拍它锁),然后再空闲时间再进行drop操作,如果后面发现还需要,还有可恢复的余地。
SQL> create table t1027 as select owner,object_name,object_type,object_id from dba_objects where object_id < 5;Table created.SQL> col object_name for a30
SQL> set lines 120
SQL> select * from t1027;OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------- ----------
SYS C_OBJ# CLUSTER 2
SYS I_OBJ# INDEX 3
SYS TAB$ TABLE 4SQL>
SQL> select rowid, 2 dbms_rowid.rowid_object(rowid) object_id,3 dbms_rowid.rowid_relative_fno(rowid) file_id,4 dbms_rowid.rowid_block_number(rowid) block_id,5 dbms_rowid.rowid_row_number(rowid) num6 from t1027;ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ---------- ---------- ---------- ----------
AAAVZQAAEAAAAsLAAA 87632 4 2827 0
AAAVZQAAEAAAAsLAAB 87632 4 2827 1
AAAVZQAAEAAAAsLAAC 87632 4 2827 2SQL> alter table t1027 set unused column object_type;Table altered.SQL> alter system checkpoint;System altered.
SQL> select * from t1027;OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
SYS C_OBJ# 2
SYS I_OBJ# 3
SYS TAB$ 4SQL>
BBED> set file 4 block 2827FILE# 4BLOCK# 2827BBED> p kdbr
sb2 kdbr[0] @142 8039
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7995BBED> p *kdbr[0]
rowdata[44]
-----------
ub1 rowdata[44] @8163 0x2cBBED> x /rcccnn
rowdata[44] @8163
-----------
flag@8163: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8164: 0x00
cols@8165: 4col 0[3] @8166: SYS
col 1[6] @8170: C_OBJ#
col 2[7] @8177: CLUSTER
col 3[2] @8185: 2
我们可以看到,当表进行unused column操作之后,尽管这个表的列已经不属于这个表了,再被重用之前,实际上这个列的数据都存在,通过上面的bbed查看即可发现这一点。
而这显然与drop column操作的立刻抹掉列数据有所不同。
实际上对于unused column的情况,这个列的数据要重用,也不是那么容易的,毕竟这这个数据只属于这个对象,其他对象都无法使用;且只有当这个列的前后都是可变长度的情况下,才容易被覆盖。
总的来讲,对于unused column这种无操作来讲,发现还需要数据,那么我们是可以想办法的,通过脚本将该列数据读取并转换即可;当然利用odu应该也是可以的(需要做一下改动)。