One .1  BLOG Document structure chart

image

 

One .2   Preface

 

One .2.1   Guidance and notes

Technology enthusiasts , After reading this article , You can master the following skills , You can also learn something else you don't know ,~O(∩_∩)O~:

① truncate Recovery method after operation ( a key )

 

  Tips:

       ① If the article code format is wrong , Recommended QQ、 Sogou or 360 browser , You can also download pdf Format to view ,pdf Document download address :http://yunpan.cn/cdEQedhCs2kFz ( Extraction code :ed9b) 

       ②  This article BLOG I use gray background and pink font to show the output part of the command that needs special attention , For example, in the following example ,thread 1 The maximum archive log number for is 33,thread 2 The maximum archive log number for is 43 It's something that needs special attention ; Commands are usually marked with yellow background and red font ; Comments on the code or the output part of the code are generally in blue font .

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

[ZFXXDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZFXXDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

If there are any mistakes or imperfections in this article, please correct them ,ITPUB Message or QQ All can , Your criticism is the biggest motivation of my writing .

 

 

One .2.2   Links to related reference articles

 

 

One .2.3   Brief introduction

truncate Operation is a dangerous operation , Don't record redo, You can't retrieve data through flashback queries , But as long as all the blocks occupied by the segment are not re occupied , We can still find it in some special ways truncate Lost data , Because when Truncate After the order was issued ,Oracle It's not actually deleting data from the underlying data block , It's going to wait until it's reused to recycle this part of the data , So this gives us an opportunity to recover the database .

Overall speaking , The way to recover is through some tools written by Daniel , It's divided into charge and free , Let's explain below . In the experimental part, we only experiment fy_recover_data Bao He gdul Tools .

Some experiments were done a long time ago , This article was published too late , Because learning in the middle DUL and BBED Knowledge about .

One .3   Toll collection software

 

Here is a brief list , Please refer to relevant websites for details :

Tool name

Download address

author

Software

ODU

http://www.oracleodu.com/cn/

The old bear

Command line operations

PRM-DUL

http://www.parnassusdata.com/ 

Maclean Liu

Graphical interface operation

AUL/mydul

http://www.dbatools.net/mydul/

d.c.b.a/ Lou Fangxin

Command line

 

 

 

One .4   Freeware

One .4.1  fy_recover_data package

Personal information of the author :

WWW.HelloDBA.COM                                                   

Created By: Fuyuncat                                               

Created Date: 08/08/2012                                           

Email: Fuyuncat@gmail.com                                          

Copyright (c), 2014, WWW.HelloDBA.COM All rights reserved.         

Latest Version: http://www.HelloDBA.com/download/FY_Recover_Data.zip

The package uses pure plsql Statement recovery is truncate Lost watch , Simple operation , Download can go to the official website to download , Or the cloud disk shared directory of wheat seedlings .

 

Fy_Recover_Data It's using Oracle Table scanning mechanism 、 Data grafting mechanism recovery TRUNCATE Or a toolkit to corrupt data . From pure PLSQL To write , Schematic diagram is as follows :

wps33F2.tmp 

 

 

Package content :

wps3403.tmp

 

One .4.2  gdul Tools

GDUL It's a class developed by Lao Geng dul Tools , When the database cannot be opened for some reason , You can use GDUL Read the table data directly , For the download address of the tool, please refer to blog, Lao Geng's message is as follows :

*********************************************************************

  GDUL for ORACLE DB.

  Version 4.0.0.1, build date: 2016.04.12.

  Copyright (c) 2007, 2016. Andy Geng.  ALL RIGHTS RESERVED.

  Email: dbtool@aliyun.com

  WeChat official account: dbtool

  QQ group: 235019291

*********************************************************************

 

One .4.2.1  gDUL Functional characteristics

v  Full support for a variety of formats to export , Include expdp,exp,text Format . The categories on the market at present dul The tools are only gDUL Support expdp Format .

v  Support ASM file system , And built in asmcmd command .

v  Most column types are supported , Support common NUMBER,CHAR, VARCHAR2, DATE,LOB, LONG Other types .. among SecureFile LOB Support compression , De duplication and encryption are not yet supported .

v  Supports exporting general tables 、IOT、Cluster surface 、 Partition table 、 Compression meter .

v  Support truncated surface 、 Delete row recovery .

v  Supports regular table spaces and bigfile Table space .

v  Support mainstream hardware platforms (HP-UX,AIX, Solaris, Linux, Windows), Each platform only needs a single executable file , Easy to distribute .

v  The key is —— Use for free forever , There is no extra charge , Not open source .

 

 

 

One .4.3  dul

DUL  yes  Data Unloader  Abbreviation , It's a Dutch  Oracle  Engineers developed , His name is  Bernard Van Duijnen. DUL  It's a  C  Developed applet , After compiling, the whole program has only one file , It's only a few hundred in size  KB, It doesn't have to work  Oracle RDBMS  And anything  Oracle  The program 、 Components , It can read data directly from the data file of a broken database , Generate  IMP  or  SQL*Loader  Recognizable documents .

DUL  It's not a commercial product ,Oracle  Not for sale 、 It's not available or supported .DUL  Only in  Oracle  Can be downloaded from the internal website of , So there is only  Oracle  Of  Supporter  To download this tool , If the  Oracle  Of  Supporter  be familiar with , Maybe he'll give you one in private , As a result, some of these tools have been exiled to the people , By some people , As a treasure . 

Different platforms 、 Different versions of the database have corresponding  DUL  Software ,9.x  And before  DUL  It's not  License  The limit , That is to say, this tool can be used without limitation , But the latest  DUL  There has been improvement in this area ,kamus  Talk about the latest  DUL  It only takes one month to get it . 

To sum up a little bit , get  DUL  There are several ways : 

wps3404.tmp  If you are  Oracle  Of  Supporter , It can be downloaded from the internal website , The address is : http://www.nl.oracle.com/support/dul/ 

wps3405.tmp  If you have  Oracle  Of  Supporter  My friends can ask them for one ,itpub  A few bamboos are here  Oracle  了 , Such as coolyl,kamus,lunar. 

wps3406.tmp  some  dul  To the people , You can ask a friend who has this software for one , But they don't have to have the one you need . 

 

And so on DUL We don't explain too much .

 

One .4.4  bbed To restore

This is more complicated , If yes oracle Not familiar with or bbed This is not recommended if you are not familiar with it , Specific cases refer to :http://blog.itpub.net/26736162/viewspace-2080727/

 

 

Chapter two   Experimental part

Two .1   Introduction to the experimental environment

project

db

db  type

Single instance

db version

11.2.0.4.0

db  Storage

FS

host IP Address /hosts To configure

192.168.59.129

OS Version and kernel edition

AIX 7.1 64 position

Archiving mode

Archive Mode

ORACLE_SID

oralhr

 

 

Two .2   The goal of the experiment

take truncate The lost table data is retrieved successfully .

 

Two .3   Experimental process

 

Two .3.1  fy_recover_data Package recovery truncate Table of

[ZFXDESKDB1:oracle]:/oracle>ORACLE_SID=oraESKDB1

[ZFXDESKDB1:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 15:51:55 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SYS@oraESKDB1> set time on;

15:52:10 SYS@oraESKDB1> set timing on;

15:52:10 SYS@oraESKDB1> set serveroutput on;

15:52:10 SYS@oraESKDB1> create table scott.TB_0321    as SELECT * FROM dba_objects;

 

Table created.

 

Elapsed: 00:00:00.59

15:52:18 SYS@oraESKDB1> SELECT COUNT(1) FROM   scott.TB_0321;

 

  COUNT(1)

----------

     86651

 

Elapsed: 00:00:00.19

15:52:24 SYS@oraESKDB1> INSERT INTO scott.TB_0321 SELECT * FROM scott.TB_0321;

 

 

86651 rows created.

 

Elapsed: 00:00:00.26

15:52:30 SYS@oraESKDB1> COMMIT;

Commit complete.

 

Elapsed: 00:00:00.01

15:52:30 SYS@oraESKDB1> INSERT INTO scott.TB_0321 SELECT * FROM scott.TB_0321;

COMMIT;

 

173302 rows created.

 

Elapsed: 00:00:00.43

15:53:02 SYS@oraESKDB1> SELECT COUNT(1) FROM   scott.TB_0321;

 

  COUNT(1)

----------

  346604

 

Elapsed: 00:00:00.27

16:15:18 SYS@oraESKDB1> SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0321';

 

D.BYTES/1024/1024

-----------------

               40

 

Elapsed: 00:00:00.44

16:15:25 SYS@oraESKDB1> truncate table scott.TB_0321;

 

Table truncated.

 

Elapsed: 00:00:00.20

16:15:46 SYS@oraESKDB1> SELECT COUNT(1) FROM   scott.TB_0321;

 

  COUNT(1)

----------

         0

 

Elapsed: 00:00:00.01

 

====》 The data has been truncate It fell off , Next, let's recover

 

 

16:15:52 SYS@oraESKDB1> @/oracle/FY_Recover_Data.pck

 

Package created.

 

Elapsed: 00:00:00.06

 

Package body created.

 

Elapsed: 00:00:00.03

16:15:59 SYS@oraESKDB1> exec fy_recover_data.recover_truncated_table('scott','TB_0321');

16:16:06: Use existing Directory Name: FY_DATA_DIR

16:16:07: Recover Table: SCOTT.TB_0321$

16:16:09: Restore Table: SCOTT.TB_0321$$

16:16:24: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT1

16:16:24: begin to recover table SCOTT.TB_0321

16:16:24: Use existing Directory Name: TMP_HF_DIR

16:17:09: Recovering data in datafile +DATA/oraeskdb/datafile/users.351.902678817

16:17:09: Use existing Directory Name: TMP_HF_DIR

16:39:16: 4984 truncated data blocks found.

16:39:16: 346604 records recovered in backup table SCOTT.TB_0321$$

16:39:17: Total: 4984 truncated data blocks found.

16:39:17: Total: 346604 records recovered in backup table SCOTT.TB_0321$$

16:39:17: Recovery completed.

16:39:17: Data has been recovered to SCOTT.TB_0321$$

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:23:11.59

 

16:39:17 SYS@oraESKDB1> SELECT COUNT(1) FROM   scott.TB_0321$$;

 

  COUNT(1)

----------

   346604

 

Elapsed: 00:00:01.55

16:40:51 SYS@oraESKDB1>

16:40:51 SYS@oraESKDB1> alter table scott.TB_0321 nologging;

 

Table altered.

 

Elapsed: 00:00:00.03

16:41:43 SYS@oraESKDB1> insert /*+append*/ into scott.TB_0321 select * from scott.TB_0321$$;

 

346604 rows created.

 

Elapsed: 00:00:00.86

16:41:52 SYS@oraESKDB1> commit;

 

Commit complete.

 

Elapsed: 00:00:00.01

16:41:55 SYS@oraESKDB1> alter table scott.TB_0321 logging;

 

Table altered.

 

Elapsed: 00:00:00.02

16:42:06 SYS@oraESKDB1>

16:42:06 SYS@oraESKDB1> drop tablespace   FY_REC_DATA  including contents and datafiles;

 

Tablespace dropped.

 

Elapsed: 00:00:08.00

16:42:35 SYS@oraESKDB1> drop tablespace   FY_RST_DATA  including contents and datafiles;

 

Tablespace dropped.

 

Elapsed: 00:00:07.59

16:42:44 SYS@oraESKDB1>

 

 

Data recovered successfully .

 

 

Two .3.2  gdul recovery truncate Table of

set time on;

set timing on;

set serveroutput on;

drop table scott.TB_0322_05;

create table scott.TB_0322_05    as SELECT * FROM dba_objects;

 

SELECT COUNT(1) FROM   scott.TB_0322_05;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

SELECT COUNT(1) FROM   scott.TB_0322_05;

 

SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0322_05';

 

 

truncate table scott.TB_0322_05;

 

alter system checkpoint;

 

col ownere format a10

col DIRECTORY_NAME format a30

col DIRECTORY_PATH format a50

select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from  dba_directories;

 

 

bootstrap

desc scott.TB_0322_05

unload table  scott.TB_0322_05

scan tablespace 4

untrunc table  scott.TB_0322_05

 

cp SCOTT_TB_0322_05.dmp /oracle/app/oracle/admin/oralhr/dpdump/

impdp  scott/tiger directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05

 

15:41:04 SQL> set time on;

15:59:49 SQL> set timing on;

15:59:49 SQL> set serveroutput on;

15:59:49 SQL> drop table scott.TB_0322_05;

create table scott.TB_0322_05    as SELECT * FROM dba_objects;

 

SELECT COUNT(1) FROM   scott.TB_0322_05;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

 

Table dropped.

 

Elapsed: 00:00:00.07

15:59:49 SQL> COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;

COMMIT;

SELECT COUNT(1) FROM   scott.TB_0322_05;

 

SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0322_05';

 

 

truncate table scott.TB_0322_05;

 

alter system checkpoint;

 

Table created.

 

Elapsed: 00:00:00.97

15:59:50 SQL> 15:59:50 SQL>

  COUNT(1)

----------

     75707

 

Elapsed: 00:00:00.86

15:59:51 SQL>

75707 rows created.

 

Elapsed: 00:00:00.23

15:59:52 SQL>

Commit complete.

 

Elapsed: 00:00:00.17

15:59:52 SQL>

151414 rows created.

 

Elapsed: 00:00:00.50

15:59:52 SQL>

Commit complete.

 

Elapsed: 00:00:00.23

15:59:52 SQL>

302828 rows created.

 

Elapsed: 00:00:01.63

15:59:54 SQL>

Commit complete.

 

Elapsed: 00:00:00.22

15:59:54 SQL>

605656 rows created.

 

Elapsed: 00:00:06.19

16:00:00 SQL>

Commit complete.

 

Elapsed: 00:00:00.02

16:00:01 SQL>

  COUNT(1)

----------

   1211312

 

Elapsed: 00:00:00.07

16:00:01 SQL> 16:00:01 SQL>

D.BYTES/1024/1024

-----------------

              136

 

Elapsed: 00:00:00.17

16:00:01 SQL> 16:00:01 SQL> 16:00:01 SQL>

Table truncated.

 

Elapsed: 00:00:01.26

16:00:02 SQL> 16:00:02 SQL>

System altered.

 

Elapsed: 00:00:00.15

16:00:02 SQL>

16:00:02 SQL> SELECT COUNT(1) FROM   scott.TB_0322_05;

 

  COUNT(1)

----------

         0

 

Elapsed: 00:00:00.00

16:02:35 SQL>

 

[oracle@ZFFR4CB1101:/home/oracle/gdul]$ ./gdul

 

*********************************************************************

  GDUL for ORACLE DB.

  Version 3.5.0.1, build date: 2016.03.07.

  Copyright (c) 2007, 2016. Andy Geng.  ALL RIGHTS RESERVED.

  Email: gengyonghui@aliyun.com

  QQ group: 235019291, WeChat Official Account: dbtool

*********************************************************************

 

GDUL> bootstrap

Bootstrap finish.

GDUL> desc scott.TB_0322_05

 

object_id: 78302, dataobj#: 78303, cluster tab#: 0

segment header: (ts#: 4, rfile#: 4, block#: 682))

 

Seg Column#  Column#    Name                 Null?           Type     

------------ ---------- -------------------- --------------- --------------

1            1          OWNER                                VARCHAR2(30)

2            2          OBJECT_NAME                          VARCHAR2(128)

3            3          SUBOBJECT_NAME                       VARCHAR2(30)

4            4          OBJECT_ID                            NUMBER   

5            5          DATA_OBJECT_ID                       NUMBER   

6            6          OBJECT_TYPE                          VARCHAR2(19)

7            7          CREATED                              DATE     

8            8          LAST_DDL_TIME                        DATE     

9            9          TIMESTAMP                            VARCHAR2(19)

10           10         STATUS                               VARCHAR2(7)

11           11         TEMPORARY                            VARCHAR2(1)

12           12         GENERATED                            VARCHAR2(1)

13           13         SECONDARY                            VARCHAR2(1)

14           14         NAMESPACE                            NUMBER   

15           15         EDITION_NAME                         VARCHAR2(30)

 

GDUL> unload table  scott.TB_0322_05

2016-03-22 16:01:54...unloaded "SCOTT"."TB_0322_05"   0 rows

GDUL> scan tablespace 4

start scan tablespace 4...

scan tablespace completed.

GDUL> untrunc table  scott.TB_0322_05

2016-03-22 16:04:29...untruncating table TB_0322_05 1211312 rows unloaded.

GDUL>

 

16:02:35 SQL> select * from dba_directories;

 

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

------------------------------ ------------------------------ -----------------------------------------------------------------------

SYS                            SUBDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS                            SS_OE_XMLDIR                   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS                            LOG_FILE_DIR                   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS                            MEDIA_DIR                      /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS                            XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS                            DATA_FILE_DIR                  /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS                            DATA_PUMP_DIR                  /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/

SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

 

8 rows selected.

 

Elapsed: 00:00:00.00

16:05:29 SQL>

 

[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$ impdp  scott/tiger directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05

 

Import: Release 11.2.0.3.0 - Production on Tue Mar 22 16:16:48 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TB_0322_05"                        117.1 MB 1211312 rows

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:16:59

 

[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$

[oracle@ZFFR4CB2101:/home/oracle]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 16:17:39 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SQL> SELECT COUNT(1) FROM   scott.TB_0322_05;

 

  COUNT(1)

----------

   1211312

 

SQL>

 

Data recovered successfully .

 

 

 

Two .4   Summary of the experiment

 

On the whole, with fy_recover_data Package or GDUL The tools are very good ,fy_recover_data Can recover truncate The data of , But it can't recover drop The data of , and GDUL The tools are more comprehensive , For details, please refer to the introduction or Download document above , There are also relatively complete documents in the shared cloud disk of wheat seedlings .