Oracle Drop表如何进行purge恢复ODU

这篇文章给大家介绍Oracle Drop表如何进行purge恢复ODU，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。Oracle Drop表(purge)恢复(ODU)通过ODU恢复drop掉的表(purge)基本步骤如下1:offline表所在表空间；2:通过logminer挖出被drop表对应object_id；3:使用ODU工具将表数据抽到文件中；4:使用sqlldr将数据加载到数据库；5:验证；一：主备测试数据1 创建测试表odu_testcreate table odu_test (a number,b varchar2(10),c nvarchar2(30),d varchar2(20),e date,f timestamp,g binary_float,h binary_double);2 插入测试数据insert into odu_test select rownum, lpad(‘x’, 10), ‘NC测试’ || rownum, ‘ZHS测试’ || rownum, sysdate + dbms_random.value(0, 100), systimestamp + dbms_random.value(0, 100), rownum + dbms_random.value(0, 10000), rownum + dbms_random.value(0, 10000) from dba_objects where rownum
commit;3 备份并删除表odu_test，模拟误删除create table odu1 as select * from odu_test;drop table odu_test purge;二：恢复1
offline表所在表空间alter tablespace users offline;2 使用logminer来查找被drop表的object_idselect group#,status from v$log;select member from v$logfile where group#=1;SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);SQL> select scn,timestamp,sql免费云主机域名_redo from v$logmnr_contents where operation=’DDL’ and sql_redo like ‘%odu_test%’ order by 2 ;…… 990001 2017/12/27 drop table odu_test purge;SQL>
select scn,timestamp,sql_redo from v$logmnr_contents where timestamp=to_date(‘2017-12-27′,’yyyy-mm-dd’) order by 1;SQL> create table logmnr_1 as (select * from v$logmnr_contents;SQL> exec sys.dbms_logmnr.end_logmnr;select *from sys.logmnr_1 where scn=’990001′; —DATA_OB# 87270select * from sys.logmnr_1 where /*operation=’DDL’ and*/ LOWER(sql_redo) like ‘%odu_test%’ order by 2 ;/*delete from “SYS”.”OBJ$” where
“OBJ#” = ‘87270’ and “DATAOBJ#” = ‘87270’ and
“OWNER#” = ’84’ and “NAME” = ‘ODU_TEST’ and
“NAMESPACE” = ‘1’ and “SUBNAME” IS NULL and
“TYPE#” = ‘2’ and “CTIME” = TO_DATE(’27-12月-17′, ‘DD-MON-RR’) and
“MTIME” = TO_DATE(’27-12月-17′, ‘DD-MON-RR’)
and “STIME” = TO_DATE(’27-12月-17′,
‘DD-MON-RR’) and “STATUS” = ‘1’ and “REMOTEOWNER” IS NULL
and “LINKNAME” IS NULL and “FLAGS” = ‘0’ and
“OID$” IS NULL and “SPARE1” = ‘6’ and “SPARE2” =
‘1’ and “SPARE3” = ’84’ and “SPARE4” IS NULL and “SPARE5”
IS NULL and “SPARE6” IS NULL and ROWID = ‘AAAAASAABAAAVKkABB’;*/3 修改原control.txt文件select d.TS# ts, d.FILE# fno, d.FILE# fno, d.NAME filename, d.BLOCK_SIZE block_size from v$datafile dorder by ts;0 1 1 D:APPADMINISTRATORORADATACJCSYSTEM01.DBF 81921 2 2 D:APPADMINISTRATORORADATACJCSYSAUX01.DBF 81922 3 3 D:APPADMINISTRATORORADATACJCUNDOTBS01.DBF 81924 4 4 D:APPADMINISTRATORORADATACJCUSERS01.DBF 8192
—control.txt4 登录odu5 扫描数据—企业版ODU需要授权—本次实验使用测试版ODU6 恢复表……生成创建表的语句和控制文件这个命令生成了如下文件ODU_0000087270.ctl和ODU_0000087270.sqlCREATE TABLE “ODU_0000087270”( “C0001” NUMBER , “C0002” VARCHAR2(4000) , “C0003” NVARCHAR2(2000) , “C0004” VARCHAR2(4000) , “C0005” DATE , “C0006” DATE , “C0007” BINARY_FLOAT , “C0008” BINARY_DOUBLE);7 online表空间alter tablespace users online;8 通过sqlldr加载数据9 验证数据select count(*) from ODU_0000087270; —10000查看恢复后表数据select * from ODU_0000087270;……查看备份表数据关于Oracle Drop表如何进行purge恢复ODU就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

相关推荐: standby database有哪些日常维护

本篇内容介绍了“standby database有哪些日常维护”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.停止Standbyselect process,…