Shulou(Shulou.com)05/31 Report--

This article introduces you how to use Oracle Drop table to recover ODU from purge. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Oracle Drop table (purge) recovery (ODU)

The basic steps for recovering drop lost tables (purge) through ODU are as follows

Table space where the 1:offline table is located

2: dig out the drop table corresponding to object_id through logminer

3: use the ODU tool to extract table data into a file

4: use sqlldr to load data into the database

5: verify

One: active and standby test data

1 create test table odu_test

Create 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 test data

Insert into odu_test

Select rownum

Lpad ('xboys, 10)

'NC Test' | | rownum

'ZHS Test' | | 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 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 order by 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# 87270

Select * 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-December-17th, 'DD-MON-RR') and "MTIME" = TO_DATE (' 27-December-17') 'DD-MON-RR') and "STIME" = TO_DATE (' 27-12-17 September, '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 modify the original control.txt file

Select d.TS# ts

D.FILE# fno

D.FILE# fno

D.NAME filename

D.BLOCK_SIZE block_size

From v$datafile d

Order by ts

01 1 D:\ APP\ ADMINISTRATOR\ ORADATA\ CJC\ SYSTEM01.DBF 8192

1 2 2 D:\ APP\ ADMINISTRATOR\ ORADATA\ CJC\ SYSAUX01.DBF 8192

2 3 3 D:\ APP\ ADMINISTRATOR\ ORADATA\ CJC\ UNDOTBS01.DBF 8192

4 4 4 D:\ APP\ ADMINISTRATOR\ ORADATA\ CJC\ USERS01.DBF 8192

-control.txt

4 Log in to odu

5 scan data

-Enterprise ODU requires authorization

-this experiment uses the beta version of ODU

6 restore the table

.

Generate statements and control files that create tables

This command generates the following files

ODU_0000087270.ctl and ODU_0000087270.sql

CREATE 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 tablespace

Alter tablespace users online

8 load data through sqlldr

9 verify data

Select count (*) from ODU_0000087270;-10000

View table data after recovery

Select * from ODU_0000087270

.

View backup table data

On the Oracle Drop table how to purge recovery ODU to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.