sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle数据表列删除的恢复方法

时间:2016-11-29 22:58   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:分享一篇关于Oracle数据库中数据表误删除列的恢复方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 列删除的恢复测试 – 不要模仿

首先重申,这个测试是玩的,大家不要借鉴!测试源于群里一个网友的提问。

SQL> SHOW  USER
USER IS  ROGER

SQL> DROP TABLE t;
TABLE dropped.

SQL> CREATE TABLE t(a NUMBER, b varchar2(6));
TABLE created.

SQL> INSERT INTO t VALUES(1, 'roger');

1 ROW created.

SQL> /

1 ROW created.

SQL> /

1 ROW created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM t;

         A B
---------- ------
         1 roger
         1 roger
         1 roger


SQL> conn roger/roger
Connected.

SQL> DROP TABLE t3;

TABLE dropped.

SQL> CREATE TABLE t3 AS SELECT owner,object_name,object_type FROM dba_objects;

TABLE created.

SQL> CREATE TABLE t4 AS SELECT * FROM t3;

TABLE created.

SQL> SELECT owner,object_name,object_id
  2    FROM dba_objects
  3   WHERE object_name IN ('T3','T4');

OWNER                          OBJECT_NAME           OBJECT_ID
------------------------------ -------------------- ----------
ROGER                          T3                        54360
ROGER                          T4                        54361

SQL> analyze TABLE roger.t3 compute statistics;

TABLE analyzed.

SQL> analyze TABLE roger.t4 compute statistics;

TABLE analyzed.

开始在作了10046 trace发现了如下操作:

update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),
       intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,
       pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,
       avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),
       instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,
       spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35
where obj#=:1

update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,
       spare1=:16, spare2=:17
where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null
   and :4 is null)and(linkname=:5 or linkname is null and :5 is null)
   and (subname=:12 or subname is null and :12 is null)

delete com$ where obj#=:1 and col#=:2

delete from sys.col_usage$ where obj#= :1 and intcol#= :2

delete from objauth$ where obj#=:1 and col#=:2

delete from col$ where obj#=:1 and intcol#=:2  --

delete from idl_ub1$ where obj#=:1

delete from idl_char$ where obj#=:1

delete from idl_ub2$ where obj#=:1

delete from idl_sb4$ where obj#=:1

delete from error$ where obj#=:1

delete from superobj$ where subobj# = :1

delete from tab_stats$ where obj#=:1

SQL> SELECT * FROM com$ WHERE obj#=54360;

no ROWS selected

SQL> SELECT * FROM sys.col_usage$  WHERE obj#=54360;

no ROWS selected

SQL> SELECT COUNT(*) FROM objauth$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM col$ WHERE obj#=54360;

  COUNT(*)
----------
         3

SQL> SELECT COUNT(*) FROM idl_ub1$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM idl_char$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM idl_ub2$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM idl_sb4$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM error$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM tab_stats$ WHERE obj#=54360;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM superobj$;

  COUNT(*)
----------
         0

经过对比发现,对于列的删除,tab$ 只是改变了如下几个列:
SQL> SELECT COLS,INTCOLS,KERNELCOLS,AVGSPC,AVGRLN FROM tab$ WHERE obj# IN(54360,54361);

      COLS    INTCOLS KERNELCOLS     AVGSPC     AVGRLN
---------- ---------- ---------- ---------- ----------
         3          3          3        834         41
         2          2          2       2272         33

对于 obj$ 而言,如下3个列发生了改变:
SQL> SELECT ctime,mtime,stime,spare2 FROM obj$ WHERE obj# IN(54360,54361);

CTIME               MTIME               STIME                   SPARE2
------------------- ------------------- ------------------- ----------
2012-02-23 04:43:37 2012-02-23 04:43:37 2012-02-23 04:43:37          1
2012-02-23 04:43:46 2012-02-23 04:44:38 2012-02-23 04:44:38          2
对于 col$ 的删除。
SQL> SELECT property FROM col$ WHERE obj# IN(54360,54361) AND intcol#=2;

  PROPERTY
----------
     14336
         0

另外还涉及到几个索引,都是object_id 小于56的,不要随便动,下面我通过一个最简单的方式进行操作。

为了安全起见,我备份了一下数据库,如下:
SQL> archive log list;

Database log mode          Oracleо    Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log sequence     235
Next log sequence to archive   237
Current log sequence           237

[ora10g@killdb archivelog]$ ls -ltr | head -200 | awk '{print $9}'| xargs rm -rf

[ora10g@killdb ~]$ dbv file=/home/ora10g/oradata/roger/roger02.dbf

DBVERIFY: Release 10.2.0.5.0 - Production on Thu Feb 23 19:57:20 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/ora10g/oradata/roger/roger02.dbf
Page 548 is marked corrupt
Corrupt block relative dba: 0x01800224 (file 6, block 548)
Completely zero block found during dbv:

Page 50403 is marked corrupt
Corrupt block relative dba: 0x0180c4e3 (file 6, block 50403)
Completely zero block found during dbv:

Block Checking: DBA = 25217508, Block Type = KTB-managed data block
data header at 0xb7f5907c
kdbchk: row count in table index incorrect
Page 51684 failed with check code 6125


DBVERIFY - Verification complete

Total Pages Examined         : 64000
Total Pages Processed (Data) : 57061
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 5978
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 839
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 120
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Highest block SCN            : 2549808 (0.2549808)


RMAN> run {
2> set maxcorrupt for datafile 6 to 2;
3> backup full as compressed backupset database plus archivelog; }

executing command: SET MAX CORRUPT

Starting backup at 23-FEB-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=230 recid=206 stamp=775116934
input archive log thread=1 sequence=231 recid=207 stamp=775468322
input archive log thread=1 sequence=232 recid=208 stamp=775545128
input archive log thread=1 sequence=233 recid=209 stamp=775548250
input archive log thread=1 sequence=234 recid=210 stamp=775945249
input archive log thread=1 sequence=235 recid=211 stamp=775975475
input archive log thread=1 sequence=236 recid=212 stamp=776029642
input archive log thread=1 sequence=237 recid=213 stamp=776029875
input archive log thread=1 sequence=238 recid=214 stamp=776029937
input archive log thread=1 sequence=239 recid=215 stamp=776030013
input archive log thread=1 sequence=240 recid=216 stamp=776030070
input archive log thread=1 sequence=241 recid=217 stamp=776030315
channel ORA_DISK_1: starting piece 1 at 23-FEB-12
channel ORA_DISK_1: finished piece 1 at 23-FEB-12
piece handle=/home/ora10g/product/10.2/dbs/0qn42i3b_1_1 tag=TAG20120223T195835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:37
Finished backup at 23-FEB-12

Starting backup at 23-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home/ora10g/oradata/roger/roger01.dbf
input datafile fno=00002 name=/home/ora10g/oradata/roger/undotbs01.dbf
input datafile fno=00006 name=/home/ora10g/oradata/roger/roger02.dbf
input datafile fno=00001 name=/home/ora10g/oradata/roger/system01.dbf
input datafile fno=00003 name=/home/ora10g/oradata/roger/sysaux01.dbf
input datafile fno=00004 name=/home/ora10g/oradata/roger/users01.dbf
input datafile fno=00008 name=/home/ora10g/oradata/roger/sqlt_01.dbf
input datafile fno=00007 name=/home/ora10g/oradata/roger/test1.dbf
channel ORA_DISK_1: starting piece 1 at 23-FEB-12
channel ORA_DISK_1: finished piece 1 at 23-FEB-12
piece handle=/home/ora10g/product/10.2/dbs/0rn42i4g_1_1 tag=TAG20120223T195912 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-FEB-12
channel ORA_DISK_1: finished piece 1 at 23-FEB-12
piece handle=/home/ora10g/product/10.2/dbs/0sn42i7p_1_1 tag=TAG20120223T195912 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-FEB-12

Starting backup at 23-FEB-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=242 recid=218 stamp=776030460
channel ORA_DISK_1: starting piece 1 at 23-FEB-12
channel ORA_DISK_1: finished piece 1 at 23-FEB-12
piece handle=/home/ora10g/product/10.2/dbs/0tn42i7s_1_1 tag=TAG20120223T200100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-FEB-12
上面的2个坏块不用管,是以前做测试留下的,没有处理,回头有空再处理了,下面继续:
SQL> SELECT obj#,name FROM obj$ WHERE name IN('I_TAB1','I_OBJ3','I_COL1','I_COL2','I_COL3');

      OBJ# NAME
---------- ------------------------------
        38 I_OBJ3
        46 I_COL2
        33 I_TAB1
        47 I_COL3
        45 I_COL1

SQL> UPDATE tab$ SET cols=3 WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE tab$ SET intcols=3  WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE tab$ SET KERNELCOLS=3 WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE tab$ SET AVGSPC=834  WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE tab$ SET avgrln=41 WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE obj$ SET mtime='2012-02-23 04:43:46'   WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE obj$ SET stime='2012-02-23 04:43:46'  WHERE obj#=54361;

1 ROW updated.

SQL> UPDATE obj$ SET spare2=1 WHERE obj#=54361;

1 ROW updated.

SQL> INSERT INTO col$
  2  VALUES
  3    (54361,
  4     3,
  5     3,
  6     19,
  7     0,
  8     'OBJECT_TYPE',
  9     1,
10     19,
11     0,
12     '',
13     '',
14     0,
15     '',
16     '',
17     3,
18     14336,
19     852,
20     1,
21     0,
22     0,
23     19,
24     '',
25     '',
26     '');

1 ROW created.

SQL> commit;

Commit complete.


SQL> SET LINES 80
SQL> DESC roger.t3

Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
OBJECT_TYPE                                        VARCHAR2(19)

SQL> DESC roger.t4

Name                                      NULL     TYPE
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
OBJECT_TYPE                                        VARCHAR2(19)


SQL> SELECT COUNT(*) FROM t3;

  COUNT(*)
----------
     50918

SQL> SELECT COUNT(*) FROM t4;

  COUNT(*)
----------
     50918

SQL> SET LINES 120
SQL> col object_name FOR a20
SQL> col owner FOR a20
SQL> SELECT * FROM t3 WHERE rownum <2;

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- -------------------
SYS                  ICOL$                TABLE

SQL> SELECT * FROM t4 WHERE rownum <2;

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- -------------------
SYS                  ICOL$

SQL> SELECT * FROM t4 WHERE rownum <10;

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- -------------------
SYS                  ICOL$
SYS                  I_USER1
SYS                  CON$
SYS                  UNDO$
SYS                  C_COBJ#
SYS                  I_OBJ#
SYS                  PROXY_ROLE_DATA$
SYS                  I_IND1
SYS                  I_CDEF2

9 ROWS selected.

虽然成功恢复了这个删除的列,但是实际上该列的数据已经没了,因为drop column以后,该列的数据已经从datafile里面清除了,通过修改数据字典的方式,只是一个欺骗的作用,貌似ODU也不能恢复这种情况。

不过数据库使用闪回的话,应该是可以的,当然是闪回整个db。

补充:对于还没重用的情况,数据其实还在的,看下面的测试:
SQL> conn roger/roger
Connected.

SQL> CREATE TABLE drop_col(a varchar2(4),b varchar2(4));

TABLE created.

SQL> INSERT INTO drop_col VALUES('AA','BB');

1 ROW created.

SQL> commit;

Commit complete.

SQL> ALTER TABLE drop_col DROP COLUMN b;

TABLE altered.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
  2  FROM drop_col;

     FILE#       BLK#
---------- ----------
         5      29960

SQL> ALTER system dump datafile 5 block 29960;

System altered.

SQL> SELECT dump('AA',16) FROM dual;

DUMP('AA',16)
-------------------
Typ=96 Len=2: 41,41

SQL> SELECT dump('BB',16) FROM dual;

DUMP('BB',16)
-------------------
Typ=96 Len=2: 42,42
BBED> set file 5 block 29960

        FILE#           5
        BLOCK#          29960

BBED> find /c BB

File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 29960            Offsets: 8186 to 8191           Dba:0x01407508
------------------------------------------------------------------------
42420106 8268

<32 bytes per line>


BBED> p rowdata       

ub1 rowdata[0]                              @8179     0x2c
ub1 rowdata[1]                              @8180     0x02
ub1 rowdata[2]                              @8181     0x01
ub1 rowdata[3]                              @8182     0x02
ub1 rowdata[4]                              @8183     0x41
ub1 rowdata[5]                              @8184     0x41
ub1 rowdata[6]                              @8185     0x02
ub1 rowdata[7]                              @8186     0x42
ub1 rowdata[8]                              @8187     0x42

在这种情况下,应该是可以直接把rowdata里面的数据给抽取出来的,前面的02是行头,需要跳过。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle数据表列删除的恢复方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1344.html

Oracle研究中心

关键词:

Oracle 列删除的恢复测试

Oracle 表中的列误删除的恢复方法步骤