[20250506]drop table的恢复2.txt

[20250506]drop table的恢复2.txt

–//尝试drop table的恢复,前提没有备份的情况不得已为之的方法,前面测试提到drop table时清除段头里面的Extent Map以及

–//Auxillary Map的信息。这样通过建立新表的方式也只能像truncate table的方式通过rowid扫描数据块来恢复,做一个完整的测试说

–//明问题:

1.环境:

SCOTT@book01p> @ ver2

==============================

PORT_STRING                   : x86_64/Linux 2.4.xx

VERSION                       : 21.0.0.0.0

BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

Version 21.3.0.0.0

BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

CON_ID                        : 0

PL/SQL procedure successfully completed.

2.测试环境建立:

SCOTT@book01p> create table t1 as select * from all_objects;

Table created.

SCOTT@book01p> create table t1_bak as select * from all_objects;

Table created.

–//t1_bak注意目的为了检验drop table的情况。

SCOTT@book01p> @ o2 t1

SCOTT@book01p> @ pr

==============================

O_OWNER                       : SCOTT

O_OBJECT_NAME                 : T1

O_OBJECT_TYPE                 : TABLE

SEG_PART_NAME                 :

O_STATUS                      : VALID

OID                           : 126494

D_OID                         : 126494

CREATED                       : 2025-05-06 14:48:10

LAST_DDL_TIME                 : 2025-05-06 14:48:10

PL/SQL procedure successfully completed.

SCOTT@book01p> @ seg2 t1

SCOTT@book01p> @ pr

==============================

SEG_MB                        : 12

SEG_OWNER                     : SCOTT

SEG_SEGMENT_NAME              : T1

SEG_PARTITION_NAME            :

SEG_SEGMENT_TYPE              : TABLE

SEG_TABLESPACE_NAME           : USERS

BLOCKS                        : 1536

HDRFIL                        : 12

HDRBLK                        : 178

PL/SQL procedure successfully completed.

3.恢复测试:

SCOTT@book01p> drop table t1 purge ;

Table dropped.

SCOTT@book01p> create table emp_xxx as select * from emp ;

Table created.

SCOTT@book01p> @ seg2 emp_xxx

SCOTT@book01p> @ pr

==============================

SEG_MB                        : 0

SEG_OWNER                     : SCOTT

SEG_SEGMENT_NAME              : EMP_XXX

SEG_PARTITION_NAME            :

SEG_SEGMENT_TYPE              : TABLE

SEG_TABLESPACE_NAME           : USERS

BLOCKS                        : 8

HDRFIL                        : 12

HDRBLK                        : 178

PL/SQL procedure successfully completed.

–//原来的段头覆盖另外有1个数据块也被破坏,基本破坏了1块记录。

4.确定drop table的段头。

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s ” ” versions_operation=’D’

VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V       OBJ#   DATAOBJ# NAME

——————– ——————– —————– ————— —————- – ———- ———- ——

2025-05-06 14:49:09.                               37973932                 07000D00BF1E0000 D     126494     126494 T1

–//OBJ#=126494就是需要恢复的表,实际上你可以通过logminer确定drop table原来的数据段号。

–//昏,第1次测试OBJ#=125494,非常容易混淆。

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s ” ” versions_operation=’I’

VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V       OBJ#   DATAOBJ# NAME

——————– ——————– —————– ————— —————- – ———- ———- ——-

2025-05-06 14:48:11. 2025-05-06 14:49:09.          37973692        37973932 07000B00B81E0000 I     126494     126494 T1

2025-05-06 14:48:17.                               37973818                 02002000C01E0000 I     126495     126495 T1_BAK

2025-05-06 14:49:18.                               37973982                 060017006F1E0000 I     126496     126496 EMP_XXX

SYS@book01p> @ as_of tab$ OBJ#,DATAOBJ#,ts#,file#,block# s 37973692 obj#=126494

ROWID                    OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#

—————— ———- ———- ———- ———- ———-

AAAAACAABAAAH1AAAA     126494     126494          5         12        178

SYS@book01p> @as_of seg$ * s 37973692  HWMINCR=126494

SYS@book01p> @ pr

==============================

ROWID                         : AAAAAIAABAAAJClAAC

FILE#                         : 12

BLOCK#                        : 178

TYPE#                         : 5

TS#                           : 5

BLOCKS                        : 1536

EXTENTS                       : 27

INIEXTS                       : 8

MINEXTS                       : 1

MAXEXTS                       : 2147483645

EXTSIZE                       : 128

EXTPCT                        : 0

USER#                         : 109

LISTS                         : 0

GROUPS                        : 0

BITMAPRANGES                  : 2147483645

CACHEHINT                     : 0

SCANHINT                      : 0

HWMINCR                       : 126494

SPARE1                        : 4325633

SPARE2                        :

PL/SQL procedure successfully completed.

–//获取drop 前的信息。

–//建立与原来数据结构一样的表,修改段号,在扫描数据块应该可以恢复相关信息。

5.注意覆盖问题:

–//如何解决在建表覆盖的问题.可以在原表空间增加一个数据文件:

ALTER TABLESPACE USERS

  ADD DATAFILE ‘/u01/oradata/BOOK/book01p/users02.dbf’

  SIZE 100M

  AUTOEXTEND ON

  NEXT 4M

  MAXSIZE UNLIMITED;

SCOTT@book01p> alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ offline ;

alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

 

SYS@book01p> alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ offline drop;

Database altered.

–//注:因为我的测试环境没有打开归档,导致直接offline报错,加入drop没有问题,注意并非真正意思删除。

SYS@book01p> recover datafile 12;

Media recovery complete.

–//顺手先recover datafile 12;以后可以直接online。

SCOTT@book01p> create table t1_drop SEGMENT CREATION IMMEDIATE as select * from all_objects where 0=1;

Table created.

SCOTT@book01p> select * from dba_extents where segment_name=’T1_DROP’;

SCOTT@book01p> @ pr

==============================

OWNER                         : SCOTT

SEGMENT_NAME                  : T1_DROP

PARTITION_NAME                :

SEGMENT_TYPE                  : TABLE

TABLESPACE_NAME               : USERS

EXTENT_ID                     : 0

FILE_ID                       : 16

BLOCK_ID                      : 128

BYTES                         : 65536

BLOCKS                        : 8

RELATIVE_FNO                  : 16

PL/SQL procedure successfully completed.

–//没有使用数据文件/u01/oradata/BOOK/book01p/users01.dbf。

–//建立表空间TSP_AUDIT略。

–//ALTER USER SCOTT QUOTA UNLIMITED ON TSP_AUDIT;

SCOTT@book01p> create table bak_t1 tablespace TSP_AUDIT as select * from scott.t1_drop where 0=1;

Table created.

–//建立新表在原来表空间,只要没有记录插入,不会覆盖,另外建立的表要加入SEGMENT CREATION IMMEDIATE,不然oracle认为是空表,无

–//法通过rowid方式读取。扫描获得信息插入的表bak_t1放在另外的表空间,避免覆盖原来的数据文件信息。

SCOTT@book01p> @ o2 t1_drop

SCOTT@book01p> @ pr

==============================

O_OWNER                       : SCOTT

O_OBJECT_NAME                 : T1_DROP

O_OBJECT_TYPE                 : TABLE

SEG_PART_NAME                 :

O_STATUS                      : VALID

OID                           : 126498

D_OID                         : 126498

CREATED                       : 2025-05-06 15:02:48

LAST_DDL_TIME                 : 2025-05-06 15:02:48

PL/SQL procedure successfully completed.

–//修改t1_drop的数据段号等于原来t1表的数据段号。

SYS@book01p> update (select OBJ#,   DATAOBJ#  ,   OWNER#, NAME from obj$ where obj#=126498) set DATAOBJ#=126494;

1 row updated.

SYS@book01p> commit ;

Commit complete.

SYS@book01p> alter system flush shared_pool;

System altered.

SYS@book01p> alter database datafile ‘/u01/oradata/BOOK/book01p/users01.dbf’ online ;

Database altered.

SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,180,0);

OWNER                          OBJECT_NAME

—————————— ——————————

SYS                            HIST_HEAD$

SYS@book01p> set timing on

SYS@book01p> @ txt/truncT.txt SCOTT T1_DROP SCOTT BAK_T1

PL/SQL procedure successfully completed.

Elapsed: 00:01:07.88

SYS@book01p> set timing off

SCOTT@book01p> select count(*) from bak_t1;

  COUNT(*)

———-

     69571

SCOTT@book01p> select count(*) from t1_bak;

  COUNT(*)

———-

     69886

–//69886-69571 = 315,丢失的了315行。

SCOTT@book01p> select * from bak_t1 minus select * from t1_bak ;

no rows selected

–//说明恢复的数据问题。

6.疑问:

SCOTT@book01p> select rowid from t1_bak where rownum=1;

ROWID

——————

AAAe4fAAMAAAGA7AAA

SCOTT@book01p> select count(*) from t1_bak where rowid between ‘AAAe4fAAMAAAGA7AAA’ and ‘AAAe4fAAMAAAGA7BBB’;

  COUNT(*)

———-

        66

–//按照道理仅仅破坏1个数据块,为什么丢失了316/66 = 4.78,将近5块呢,实际上建立表exp_xxx时,建立1个extents占用8块。

SCOTT@book01p> select * from dba_extents where segment_name=’EMP_XXX’;

SCOTT@book01p> @ pr

==============================

OWNER                         : SCOTT

SEGMENT_NAME                  : EMP_XXX

PARTITION_NAME                :

SEGMENT_TYPE                  : TABLE

TABLESPACE_NAME               : USERS

EXTENT_ID                     : 0

FILE_ID                       : 12

BLOCK_ID                      : 176

BYTES                         : 65536

BLOCKS                        : 8

RELATIVE_FNO                  : 12

PL/SQL procedure successfully completed.

–//176,177,178没有数据。179已经被emp_xxx占用,剩下180,181,182,183块还是有数据,看看能否恢复。

SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,180,0);

OWNER OBJECT_NAME

—– ———–

SYS   HIST_HEAD$

SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,181,0);

OWNER OBJECT_NAME

—– ———–

SYS   MIGRATE$

SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,182,0);

OWNER OBJECT_NAME

—– ———–

SYS   CDB_TS$

SCOTT@book01p> select OWNER,OBJECT_NAME from t1_drop where rowid = dbms_rowid.rowid_create(1,126494,12,183,0);

OWNER OBJECT_NAME

—– ———–

SYS   I_JIJOIN$

–//而使用truncT.sql脚本无法扫描这些数据块。通过我前面的bbed扫描确定数据段号的方式就没有问题。

7.改用bbed扫描数据文件看看:

–//$ cd bbed ;

–//$ rm  log.bbd

–//确定最大块号

SYS@book01p> select * from dba_DATA_FILES where file_id=12

  2  @ pr

==============================

FILE_NAME                     : /u01/oradata/BOOK/book01p/users01.dbf

FILE_ID                       : 12

TABLESPACE_NAME               : USERS

BYTES                         : 267386880

BLOCKS                        : 32640

STATUS                        : AVAILABLE

RELATIVE_FNO                  : 12

AUTOEXTENSIBLE                : YES

MAXBYTES                      : 34359721984

MAXBLOCKS                     : 4194302

INCREMENT_BY                  : 160

USER_BYTES                    : 266338304

USER_BLOCKS                   : 32512

ONLINE_STATUS                 : ONLINE

LOST_WRITE_PROTECT            : OFF

PL/SQL procedure successfully completed.

–//确定那些数据块的段号等于126494。

$ seq 128 1 32640 | xargs -IQ echo p /d dba 12,Q ktbbhsid.ktbbhsg1 | rlbbed  > /dev/null

$ grep -B1  ”  126494$” log.bbd | grep ktbbhsid.ktbbhsg1 | head -7

BBED> p /d dba 12,180 ktbbhsid.ktbbhsg1

BBED> p /d dba 12,181 ktbbhsid.ktbbhsg1

BBED> p /d dba 12,182 ktbbhsid.ktbbhsg1

BBED> p /d dba 12,183 ktbbhsid.ktbbhsg1

BBED> p /d dba 12,184 ktbbhsid.ktbbhsg1

BBED> p /d dba 12,185 ktbbhsid.ktbbhsg1

BBED> p /d dba 12,186 ktbbhsid.ktbbhsg1

–//将需要扫描的数据块保存在文本scan.txt文件中。

$ grep -B1 126494$ log.bbd | grep ktbbhsid.ktbbhsg1 >| scan.txt

SYS@book01p> create table scott.scanblock ( file_id number,block_id number  )  tablespace TSP_AUDIT;

Table created.

–//注意建立的新表一定不能使用原来的表空间,避免覆盖。

$ awk ‘{print $5}’ scan.txt |  sed ‘s/^/insert into scanblock values (/;s/$/);/’ >| scan1.txt

$ head -2 scan1.txt ; tail -2 scan1.txt

insert into scanblock values (12,180);

insert into scanblock values (12,181);

insert into scanblock values (12,24630);

insert into scanblock values (12,24631);

–//执行@scan1.txt.注意提交。

–//SCOTT@book01p> truncate table BAK_T1;

–//Table truncated.

SYS@book01p> set timing on

SYS@book01p> @ truncT.txt SCOTT T1_DROP  SCOTT BAK_T1

PL/SQL procedure successfully completed.

Elapsed: 00:00:56.70

SYS@book01p> set timing off

SCOTT@book01p> select count(*) from bak_T1;

  COUNT(*)

———-

     69819

SCOTT@book01p> select count(*) from T1_BAK;

  COUNT(*)

———-

     69886

–//69886-69819 = 67,这样恢复丢失67条。(注:实际丢失66条)。

–//还是存在小小疑问。

SCOTT@book01p> select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) b from t1_bak  where rownum<=1;

ROWID                       B

—————— ———-

AAAe4fAAMAAAGA7AAA      24635

SCOTT@book01p> select count(*) from t1_bak where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=24635;

  COUNT(*)

———-

        66

SCOTT@book01p> select * from ( select * from (select count(*) a ,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) b from t1_bak  group by DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) )) where a=67 ;

no rows selected

–//保存的备份表也没有67条记录的数据块。噢回看才明白问题在那里,t1_bak建立如下:

SCOTT@book01p> create table t1_bak as select * from all_objects;

Table created.

–//这样多1条记录。应该写成:

create table t1_bak as select * from t1;

–//测试疏忽了。

SCOTT@book01p> select count(*) from t1_bak where object_name=’T1′;

  COUNT(*)

———-

         1

SCOTT@book01p> select count(*) from bak_t1 where object_name=’T1′;

  COUNT(*)

———-

         0

SCOTT@book01p> select * from bak_t1 minus select * from t1_bak;

no rows selected

–//说明恢复的数据没有任何问题。

–//理论讲确定扫描那些数据块的方法以及原始方法执行实际差别不大,但是前者丢失数据要少一些。

8.收尾还原:

SYS@book01p> update (select OBJ#,   DATAOBJ#  ,   OWNER#, NAME from obj$ where obj#=126498) set DATAOBJ#=126498;

1 row updated.

SYS@book01p> commit ;

Commit complete.

SYS@book01p> alter system flush shared_pool;

System altered.

9.执行脚本另外写1个文章贴出。

来源链接:https://www.cnblogs.com/lfree/p/18865071

© 版权声明
THE END
支持一下吧
点赞14 分享
评论 抢沙发
头像
请文明发言!
提交
头像

昵称

取消
昵称表情代码快捷回复

    暂无评论内容