Oracle Exp DMP file corruption how to recover

Source: Internet
Author: User
Tags character set commit reserved oracle database

Create an exp DMP file and use DD to destroy

The code is as follows Copy Code

Sql> CREATE TABLE T_xifenfei as SELECT * from Dba_objects;

Table created.

Sql> Select COUNT (*) from T_xifenfei;

COUNT (*)
----------
90915

[Oracle@localhost ~]$ exp CHF/XIFENFEI@PDB1 file=/tmp/t_xifenfei.dmp Tables=t_xifenfei

Export:release 12.1.0.2.0 on Sun APR 27 21:39:26 2014

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


Connected to:oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit
with the partitioning, OLAP, A dvanced Analytics and real application testing options
Export do in Us7ascii character set and Al16utf16 NCHAR Cter Set
Server uses we8mswin1252 character set (possible charset conversion)

About to export specified tables via conventional Path ...
. . Exporting table T_xifenfei 90915 rows Exported
Exp-00091:exporting questionable statistics.
Export terminated successfully with warnings.
[Oracle@localhost ~]$ od-x/tmp/t_xifenfei.dmp |head-10
0000000 0003 4501 5058 524f 3a54 3156 2e32 3130
0000020 302e 0a30 4344 4648 520a 4154 4c42 5345
0000040 380a 3931 0a32 0a30 3237 300a 000a 0001
0000060 07b2 00d0 0001 0000 0000 0000 0000 0013
0000100 2020 2020 2020 2020 2020 2020 2020 2020
*
0000140 2020 2020 2020 2020 7553 206e 7041 2072
0000160 3732 3220 3a31 3933 323a 2036 3032 3431
0000200 742f 706d 742f 785f 6669 6e65 6566 2e69
0000220 6d64 0070 0000 0000 0000 0000 0000 0000

--strings command to see DMP file
[Oracle@localhost ~]$ strings/tmp/t_xifenfei.dmp |head-50
export:v12.01.00
Dchf
Rtables
8192
Tue APR 0:39:49 2014/tmp/t_xifenfei.dmp
#G #g
#G #g
+08:00
BYTE
Unused
Interpreted
Disable:all
Metricst
TABLE "T_xifenfei"
CREATE TABLE "T_xifenfei" ("OWNER" VARCHAR2 (128), "object_name" VARCHAR2 (128), "Subobject_name" VARCHAR2 (128), "object_ ID "number," data_object_id "number," Object_type "VARCHAR2," CREATED "date," Last_ddl_time "date," TIMESTAMP "Varcha R2 (), "STATUS" VARCHAR2 (7), "temporary" VARCHAR2 (1), "generated" VARCHAR2 (1), "secondary" VARCHAR2 (1), "NAMESPACE" Number, "Edition_name" VARCHAR2 (128), "Sharing" VARCHAR2 (+), "Editionable" VARCHAR2 (1), "oracle_maintained" VARCHAR2 ( 1) PCTFREE pctused Initrans 1 Maxtrans 255 STORAGE (INITIAL 13631488 NEXT 1048576 minextents 1 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT) tablespace "USERS" LOGGING nocompress
INSERT into "T_xifenfei" ("OWNER", "object_name", "Subobject_name", "object_id", "data_object_id", "object_type", "" CREATED "," Last_ddl_time "," TIMESTAMP "," STATUS "," temporary "," generated "," secondary "," NAMESPACE "," Edition_name "," Sharing "," editionable "," oracle_maintained ") VALUES (: 1,: 2,: 3,: 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13,: 14,: 15,: 1 6,: 17,: 18)
Public
V$dataguard_config
Synonym
2014-04-22:17:51:05
VALID
METADATA LINK
V_$dataguard_stats
VIEW
2014-04-22:17:51:05

--Destroy Exp DMP file
[Oracle@localhost ~]$ dd if=/dev/zero of=/tmp/t_xifenfei.dmp bs=1024 count=1 conv=notrunc
1+0 Records in
1+0 Records out
1024 bytes (1.0 kB) copied, 6.0291e-05 seconds, 17.0 MB/s
[Oracle@localhost ~]$ od-x/tmp/t_xifenfei.dmp |head-10
0000000 0000 0000 0000 0000 0000 0000 0000 0000
*
0020000 0064 0000 6000 2401 050f 0c0b 0c03 050c
0020020 0504 060d 0709 0508 0505 0505 050f 0505
0020040 0505 050a 0505 0505 0504 0706 0808 4723
0020060 4723 1108 0823 4111 47b0 8300 b200 d007
0020100 0003 0000 0000 0000 0000 0000 0000 0000
0020120 0000 0000 0000 0000 0000 0000 0000 0000
0020140 0000 0000 0000 0064 0000 6000 2401 050f
0020160 0c0b 0c03 050c 0504 060d 0709 0508 0505

--After the damaged DMP file use the strings command to see
[Oracle@localhost ~]$ strings/tmp/t_xifenfei.dmp |head-50
#G #g
#G #g
+08:00
BYTE
Unused
Interpreted
Disable:all
Metricst
TABLE "T_xifenfei"
CREATE TABLE "T_xifenfei" ("OWNER" VARCHAR2 (128), "object_name" VARCHAR2 (128), "Subobject_name" VARCHAR2 (128), "object_ ID "number," data_object_id "number," Object_type "VARCHAR2," CREATED "date," Last_ddl_time "date," TIMESTAMP "Varcha R2 (), "STATUS" VARCHAR2 (7), "temporary" VARCHAR2 (1), "generated" VARCHAR2 (1), "secondary" VARCHAR2 (1), "NAMESPACE" Number, "Edition_name" VARCHAR2 (128), "Sharing" VARCHAR2 (+), "Editionable" VARCHAR2 (1), "oracle_maintained" VARCHAR2 ( 1) PCTFREE pctused Initrans 1 Maxtrans 255 STORAGE (INITIAL 13631488 NEXT 1048576 minextents 1 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT) tablespace "USERS" LOGGING nocompress
INSERT into "T_xifenfei" ("OWNER", "object_name", "Subobject_name", "object_id", "data_object_id", "object_type", "" CREATED "," Last_ddl_time "," TIMESTAMP "," STATUS "," temporary "," generated "," secondary "," NAMESPACE "," Edition_name "," Sharing "," editionable "," oracle_maintained ") VALUES (: 1,: 2,: 3,: 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13,: 14,: 15,: 1 6,: 17,: 18)
Public
V$dataguard_config
Synonym
2014-04-22:17:51:05
VALID
METADATA LINK
V_$dataguard_stats

--imp Import DMP file failed
[Oracle@localhost ~]$ imp chf/xifenfei@pdb1 file=/tmp/t_xifenfei.dmp full=y

Import:release 12.1.0.2.0-beta on Sun APR 27 22:02:40 2014

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


Connected to:oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit
With the partitioning, OLAP, Advanced Analytics and real application testing options

Imp-00037:character Set Marker Unknown
Imp-00000:import terminated unsuccessfully

Here through analysis you can know that the exp DMP file, although a bit corrupted, but through the strings command to see that the relevant records still exist, so you can read the exp DMP file through the tool, and then analyze the relevant data

Recover corrupted exp DMP file data

The code is as follows Copy Code

Cpfl> SEARCH TABLE T_xifenfei from Expfile/tmp/t_xifenfei.dmp
8461:table "T_xifenfei"
8480:create TABLE "T_xifenfei" ("OWNER" VARCHAR2 (128), "object_name" VARCHAR2 (128), "Subobject_name" VARCHAR2 (128), " object_id "number," data_object_id "number," Object_type "VARCHAR2," CREATED "date," Last_ddl_time "date," TIMESTAMP "VARCHAR2", "STATUS" VARCHAR2 (7), "temporary" VARCHAR2 (1), "generated" VARCHAR2 (1), "secondary" VARCHAR2 (1), " NAMESPACE "number," Edition_name "VARCHAR2 (128)," Sharing "VARCHAR2 (+)," Editionable "VARCHAR2 (1)," oracle_maintained "VARCHAR2 (1)) PCTFREE pctused Initrans 1 Maxtrans 255 STORAGE (INITIAL 8388608 NEXT 1048576 minextents 1 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT) tablespace "USERS" LOGGING nocompress
9145:insert into "T_xifenfei" ("OWNER", "object_name", "Subobject_name", "object_id", "data_object_id", "object_type", "CREATED", "Last_ddl_time", "TIMESTAMP", "STATUS", "temporary", "generated", "secondary", "NAMESPACE", "Edition_name", "Sharing", "editionable", "oracle_maintained" VALUES (: 1,: 2,: 3, 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13,: 14,: 15,: 16,: 17,: 18)
Conventional export
9644:start of table data
12331252:table "T_xifenfei"
12331349:endtable
cpfl> unload  TABLE "T_xifenfei" ("OWNER" VARCHAR2 (128), "object_name" VARCHAR2 (128), "Subobject_name" VARCHAR2 (128), "object_id" number, "data_object_id" number, "Object_type" VARCHAR2 (), "CREATED" date, "Last_ddl_time" date, "TI Mestamp "VARCHAR2", "STATUS" VARCHAR2 (7), "temporary" VARCHAR2 (1), "generated" VARCHAR2 (1), "secondary" VARCHAR2 (1), "NAMESPACE" number, "Edition_name" VARCHAR2 (128), "Sharing" VARCHAR2 (+), "Editionable" VARCHAR2 (1), "Oracle_ Maintained "VARCHAR2 (1))   expfile /tmp/t_xifenfei.dmp from 8480 until 12331349

--Because exp DMP file corruption record
CPFL:Error:column 1 length 21059 exceeds max bind size 128
0000000000 45415445 20544142 4c4 52022 545f5849 eate  tab LE "T_xi
0000000016 46454e46 45492220 28224f57 4e455222 fenf EI"   ("OW NER"
0000000032 20564152 43484152                     VAR char          
8480: Column 1 type VARCHAR2 size 21059 failed
8480:row 1 failed
Row conversion failure, retrying from offset 8481
CPFL:Error:Zero (illegal) length column number 2
.....
CPFL:Error:Zero (illegal) Length column number 1
9644:succesful conversion      1164 Bytes skipped due to conversion problems
131877:row 1000 ok
253310:row OK
.....
12200617:row 90000 OK
unloaded 90915 rows, end of table marker at 12322835

[Oracle@localhost cpfl]$ ls-ltr t_xifenfei.*
-rw-r--r--1 Oracle oinstall 17230747 APR 22:12 t_xifenfei.dat
-rw-r--r--1 Oracle Oinstall 1489 APR 22:17 t_xifenfei.ctl

Import data and compare

The code is as follows Copy Code

Sql> CREATE TABLE T_xifenfei_exp as SELECT * from T_xifenfei where 1=0;

Table created.

[Oracle@localhost cpfl]$ more T_xifenfei.ctl

Load data
CHARACTERSET UTF8
InFile ' T_xifenfei.dat '
Insert
into table "T_XIFENFEI_EXP"---modified to t_xifenfei_exp table
Fields terminated by whitespace
(
"OWNER" CHAR (128) enclosed by X ' 7C '
, "object_name" CHAR (128) enclosed by X ' 7C '
, "Subobject_name" CHAR (enclosed by X ' 7C ')
, "object_id" CHAR (5) enclosed by X ' 7C '
, "data_object_id" CHAR (5) enclosed by X ' 7C '
, "Object_type" CHAR () enclosed by X ' 7C '
, "CREATED" DATE "dd-mon-yyyy AD HH24:MI:SS" enclosed by X ' 7C '
, "Last_ddl_time" DATE "dd-mon-yyyy AD HH24:MI:SS" enclosed by X ' 7C '
, "TIMESTAMP" CHAR () enclosed by X ' 7C '
, "STATUS" CHAR (5) enclosed by X ' 7C '
, "temporary" CHAR (1) enclosed by X ' 7C '
, "Generated" CHAR (1) enclosed by X ' 7C '
, "secondary" CHAR (1) enclosed by X ' 7C '
, "NAMESPACE" CHAR (2) enclosed by X ' 7C '
, "Edition_name" CHAR (1) enclosed by X ' 7C '
, "sharing" CHAR (enclosed by X ' 7C ')
, "Editionable" CHAR (1) enclosed by X ' 7C '
, "oracle_maintained" CHAR (1) enclosed by X ' 7C '
, "Unexp_status" Filler CHAR (3) enclosed by X ' 7C '
)

[Oracle@localhost cpfl]$ Sqlldr chf/xifenfei@pdb1 control=t_xifenfei.ctl

Sql*loader:release 12.1.0.2.0-beta on Sun APR 27 22:17:54 2014

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

Path used:conventional
Commit Point Reached-logical Record Count 64
Commit Point reached-logical Record Count 128
Commit Point Reached-logical Record Count 192
............
Commit Point reached-logical Record Count 90887
Commit Point reached-logical Record Count 90915

Table "T_xifenfei_exp":
90915 Rows successfully loaded.

Check the log file:
T_xifenfei.log
For more information about the load.
[Oracle@localhost cpfl]$ Sqlplus CHF/XIFENFEI@PDB1

Sql*plus:release 12.1.0.2.0 Beta on Sun APR 27 22:18:08 2014

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

Last Successful login Time:sun APR 27 2014 22:17:54 +08:00

Connected to:
Oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit Beta
With the partitioning, OLAP, Advanced Analytics and real application testing options

Sql> Select COUNT (*) from T_xifenfei_exp;

COUNT (*)
----------
90915

Sql> SELECT * from T_xifenfei
2 minus
3 SELECT * from T_xifenfei_exp;

No rows selected

It can be seen from here that the contents of all or part of the exp DMP file can be recovered by directly reading the DMP file in the case of partial corruption of the exp DMP file (the amount of recovery depends on the degree of DMP file corruption)

Original from: http://www.xifenfei.com/5269.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.