在误删除了表空间后成功恢复数据文件后,在使用odu、dul进行数据抽取时需要元数据。
- 可以使用odu进行恢复delete行.但是这个不一定可以成功.
- 使用logminer分析归档进行操作
- 即使flashback查询底层表
下面实验下flashback方式
1.创建环境准备环境
SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 8594751012 SQL> CREATE TABLE flb_test1 AS SELECT * FROM dba_tables; TABLE created. SQL> @o flb_test1 owner object_name object_type STATUS OID D_OID CREATED LAST_DDL_TIME ------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- ----------------- TRAVEL FLB_TEST1 TABLE VALID 23063 23063 20150212 16:38:45 20150212 16:38:45 |
2.查询数据
SQL> --查询obj$ SELECT 2 obj#||','|| 3 DATAOBJ#||','|| 4 OWNER# ||','|| 5 NAME ||','|| 6 SUBNAME||','|| 7 TYPE# 8 FROM sys.obj$ WHERE obj#=&object_id; Enter VALUE FOR object_id: 23063 OBJ#||','||DATAOBJ#||','||OWNER#||','||NAME||','||SUBNAME||','||TYPE# ------------------------------------------------------------------------------ 23063,23063,34,FLB_TEST1,,2 --查询tab$ SQL> SELECT 2 obj#||','|| 3 DATAOBJ#||','|| 4 ts#||','|| 5 file#||','|| 6 block#||','|| 7 TAB# 8 FROM sys.tab$ WHERE obj#=&object_id; Enter VALUE FOR object_id: 23063 OBJ#||','||DATAOBJ#||','||TS#||','||FILE#||','||BLOCK#||','||TAB# -------------------------------------------------------------------------------- 23063,23063,4,4,1690, --查询col$ SQL> SELECT 2 obj#||','|| 3 COL#||','|| 4 SEGCOL# ||','|| 5 NAME ||','|| 6 TYPE#||','|| 7 LENGTH||','|| 8 NULL||','||NULL||','|| 9 NULL$||','|| 10 INTCOL#||','|| 11 PROPERTY||','|| 12 CHARSETID||','|| 13 CHARSETFORM 14 FROM sys.col$ 15 WHERE obj#=&object_id; Enter VALUE FOR object_id: 23063 OBJ#||','||COL#||','||SEGCOL#||','||NAME||','||TYPE#||','||LENGTH||','||NULL||','||NULL||','||NULL$||','||INTCOL#||','||PROPERTY||','||CHARSETID||','||CHARSETFORM ---------------------------------------------------------------------- 23063,1,1,OWNER,1,30,,,1,1,0,852,1 23063,2,2,TABLE_NAME,1,30,,,1,2,0,852,1 23063,3,3,TABLESPACE_NAME,1,30,,,0,3,0,852,1 23063,4,4,CLUSTER_NAME,1,30,,,0,4,0,852,1 23063,5,5,IOT_NAME,1,30,,,0,5,0,852,1 23063,6,6,STATUS,1,8,,,0,6,0,852,1 23063,7,7,PCT_FREE,2,22,,,0,7,0,0,0 23063,8,8,PCT_USED,2,22,,,0,8,0,0,0 23063,9,9,INI_TRANS,2,22,,,0,9,0,0,0 23063,10,10,MAX_TRANS,2,22,,,0,10,0,0,0 23063,11,11,INITIAL_EXTENT,2,22,,,0,11,0,0,0 23063,12,12,NEXT_EXTENT,2,22,,,0,12,0,0,0 23063,13,13,MIN_EXTENTS,2,22,,,0,13,0,0,0 23063,14,14,MAX_EXTENTS,2,22,,,0,14,0,0,0 23063,15,15,PCT_INCREASE,2,22,,,0,15,0,0,0 23063,16,16,FREELISTS,2,22,,,0,16,0,0,0 23063,17,17,FREELIST_GROUPS,2,22,,,0,17,0,0,0 23063,18,18,LOGGING,1,3,,,0,18,0,852,1 23063,19,19,BACKED_UP,1,1,,,0,19,0,852,1 23063,20,20,NUM_ROWS,2,22,,,0,20,0,0,0 23063,21,21,BLOCKS,2,22,,,0,21,0,0,0 23063,22,22,EMPTY_BLOCKS,2,22,,,0,22,0,0,0 23063,23,23,AVG_SPACE,2,22,,,0,23,0,0,0 23063,24,24,CHAIN_CNT,2,22,,,0,24,0,0,0 23063,25,25,AVG_ROW_LEN,2,22,,,0,25,0,0,0 23063,26,26,AVG_SPACE_FREELIST_BLOCKS,2,22,,,0,26,0,0,0 23063,27,27,NUM_FREELIST_BLOCKS,2,22,,,0,27,0,0,0 23063,28,28,DEGREE,1,20,,,0,28,0,852,1 23063,29,29,INSTANCES,1,20,,,0,29,0,852,1 23063,30,30,CACHE,1,10,,,0,30,0,852,1 23063,31,31,TABLE_LOCK,1,8,,,0,31,0,852,1 23063,32,32,SAMPLE_SIZE,2,22,,,0,32,0,0,0 23063,33,33,LAST_ANALYZED,12,7,,,0,33,0,0,0 23063,34,34,PARTITIONED,1,3,,,0,34,0,852,1 23063,35,35,IOT_TYPE,1,12,,,0,35,0,852,1 23063,36,36,TEMPORARY,1,1,,,0,36,0,852,1 23063,37,37,SECONDARY,1,1,,,0,37,0,852,1 23063,38,38,NESTED,1,3,,,0,38,0,852,1 23063,39,39,BUFFER_POOL,1,7,,,0,39,0,852,1 23063,40,40,FLASH_CACHE,1,7,,,0,40,0,852,1 23063,41,41,CELL_FLASH_CACHE,1,7,,,0,41,0,852,1 23063,42,42,ROW_MOVEMENT,1,8,,,0,42,0,852,1 23063,43,43,GLOBAL_STATS,1,3,,,0,43,0,852,1 23063,44,44,USER_STATS,1,3,,,0,44,0,852,1 23063,45,45,DURATION,1,15,,,0,45,0,852,1 23063,46,46,SKIP_CORRUPT,1,8,,,0,46,0,852,1 23063,47,47,MONITORING,1,3,,,0,47,0,852,1 23063,48,48,CLUSTER_OWNER,1,30,,,0,48,0,852,1 23063,49,49,DEPENDENCIES,1,8,,,0,49,0,852,1 23063,50,50,COMPRESSION,1,8,,,0,50,0,852,1 23063,51,51,COMPRESS_FOR,1,12,,,0,51,0,852,1 23063,52,52,DROPPED,1,3,,,0,52,0,852,1 23063,53,53,READ_ONLY,1,3,,,0,53,0,852,1 23063,54,54,SEGMENT_CREATED,1,3,,,0,54,0,852,1 23063,55,55,RESULT_CACHE,1,7,,,0,55,0,852,1 |
55 rows selected.
2.删除表并flashback查询
--查看scn SQL> SELECT current_scn FROM v$database; CURRENT_SCN ----------- 8594751089 --删除表 SQL> DROP TABLE flb_test1 purge; TABLE dropped. --flashback scn SQL> SELECT 2 obj#||','|| 3 DATAOBJ#||','|| 4 OWNER# ||','|| 5 NAME ||','|| 6 SUBNAME||','|| 7 TYPE# AS obj 8 FROM sys.obj$ AS OF scn 8594751089 WHERE obj#=&object_id ; Enter VALUE FOR object_id: 23063 OBJ -------------------------------------------------------------------------------------- 23063,23063,34,FLB_TEST1,,2 --flashback time SQL> --flash query timestamp SQL> SELECT 2 obj#||','|| 3 DATAOBJ#||','|| 4 OWNER# ||','|| 5 NAME ||','|| 6 SUBNAME||','|| 7 TYPE# AS obj 8 FROM sys.obj$ AS OF TIMESTAMP to_timestamp('2015-02-12 16:40:00','yyyy-mm-dd hh24:mi:ss') 9 WHERE obj#=&object_id ; Enter VALUE FOR object_id: 23063 OBJ -------------------------------------------------------------------------------- 23063,23063,34,FLB_TEST1,,2 SQL> SELECT 2 obj#||','|| 3 DATAOBJ#||','|| 4 OWNER# ||','|| 5 NAME ||','|| 6 SUBNAME||','|| 7 TYPE# AS obj 8 FROM sys.obj$ WHERE obj#=&object_id ; Enter VALUE FOR object_id: 23063 no ROWS selected |
