​[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个文章贴出。