Case: Corrupted Oracle exp DMP File Recovery by extracting data tables from DMP using the CPFL tool

Oracle Database Logic export EXP DMP file corruption, extracting data from the table in the DMP file through unconventional recovery

In some cases, the exp DMP file is damaged for some reason (such as a disk exception, exp process corruption, etc.), resulting in IMP import cannot continue, the following processing method (directly read the DMP file) to the DMP file for salvage recovery, minimize loss of data loss

1. Create exp DMP file and use DD to destroy

Sql> CREATE TABLE T_xifenfei as SELECT * from Dba_objects; Table created.  Sql> Select COUNT (*) from T_xifenfei; COUNT (*)----------90915[[email protected] ~]$ exp chf/[email protected] File=/tmp/t_xifenfei.dmp tables=t  _xifenfeiexport:release 12.1.0.2.0 on Sun Apr 21:39:26 2014Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bitwith The partitioning, OLAP, Advanced Analy Tics and Real application testing optionsexport done in Us7ascii character set and al16utf16 NCHAR character Setserver use S we8mswin1252 Character set (possible charset conversion) about to export specified tables via conventional Path ..... Exporting table T_xifenfei 90915 rows exportedexp-00091:exporting questionable statistics. Export terminated successfully with warnings. [[email protected] ~]$ od-x/tmp/t_xifenfei.dmp |head-100000000 0003 4501 5058 524f 3a54 3156 2e32 31300000020 302e 0a30 4344 4648 520a 4154 4c42 53450000040 380a 3931 0a32 0a30 3237 300a 000a 00010000060 07b2 00  d0 0001 0000 0000 0000 0000 00130000100 2020 2020 2020 2020 2020 2020 2020 2020*0000140 2020 2020 2020 2020 7553 206e 7041 20720000160 3732 3220 3a31 3933 323a 2036 3032 34310000200 742f 706d 742f 785f 6669 6e65 6566 2e690000220 6d64 0070 0000 0000 0000 0000 0000 0000--strings Command See dmp file [[email protected] ~]$ strings/tmp/t_xifenfei.dmp |head-50export: v12.01.00dchfrtables8192 Tue APR 0:39:49 2014/tmp/t_xifenfei.dmp#g#g#g#g+08:00b Yteunusedinterpreteddisable:allmetricsttable "T_xifenfei" CREATE TABLE "T_xifenfei" ("OWNER" VARCHAR2 (+), "object_ NAME "VARCHAR2", "Subobject_name" VARCHAR2 (+), "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 (+), "Sharing" VARCHAR2 (1), "editionable" VARCHAR2, "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 Nocompressinsert 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,: Ten,: One,:),: All,: +,:,: +,: +) Publicv$datagu Ard_configsynonym2014-04-22:17:51:05validmetadata linkv_$dataguard_statsview2014-04-22:17:51:05--destroys exp dmp file [[ Email protected] ~]$ dd if=/dev/zero of=/tmp/t_xifenfei.dmp bs=1024 count=1 conv=notrunc1+0 Records in1+0 Records OU t1024 bytes (1.0 kB) copied, 6.0291e-05 seconds, 17.0 Mb/s[[emaiL protected] ~]$ od-x/tmp/t_xifenfei.dmp |head-100000000 0000 0000 0000 0000 0000 0000 0000 0000*0020000 0064 0000 6000 2401 050f 0c0b 0c03 050c0020020 0504 060d 0709 0508 0505 0505 050f 05050020040 0505 050a 0505 0505 0504 0706 0808 47 230020060 4723 1108 0823 4111 47b0 8300 b200 d0070020100 0003 0000 0000 0000 0000 0000 0000 00000020120 0000 0000 0000 000 0 0000 0000 0000 00000020140 0000 0000 0000 0064 0000 6000 2401 050f0020160 0c0b 0c03 050c 0504 060d 0709 0508 0505--damaged D MP files use the strings command to see [[email protected] ~]$ strings/tmp/t_xifenfei.dmp |head-50#g#g#g#g+ 08:00byteunusedinterpreteddisable:allmetricsttable "T_xifenfei" CREATE TABLE "T_xifenfei" ("OWNER" VARCHAR2 (128), " object_name "VARCHAR2", "Subobject_name" VARCHAR2 (+), "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 (+), "Sharing" VARCHAR2 (1), "Editionable" VARCHAR2 (1) ) PCTFREE pctused Initrans 1 Maxtrans 255 STORAGE (INITIAL 13631488 NEXT 1048576 minextents 1 freelists 1 FREELIST G Roups 1 buffer_pool DEFAULT) tablespace "USERS" LOGGING Nocompressinsert into "T_xifenfei" ("OWNER", "object_name", "Subob Ject_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,: Ten,: one,: +,: All,: (+),: +,: +,: +) publicv$dataguard_configsynonym201 4-04-22:17:51:05validmetadata linkv_$dataguard_stats--imp Importing DMP file failed [[email protected] ~]$ imp chf/[email   Protected] file=/tmp/t_xifenfei.dmp full=yimport:release 12.1.0.2.0-on Sun Apr 22:02:40 2014Copyright (c) 1982,  Oracle and/or its affiliates. All Rights ReservEd. Connected to:oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit with the partitioning, OLAP, advanced anal Ytics and Real Application testing Optionsimp-00037:character set marker Unknownimp-00000:import terminated unsuccessful Ly

Here the analysis can be known, although the exp DMP file is damaged a bit, but through the strings command, the related records still exist, so you can read the exp DMP file through the tool, and then analyze the relevant data

2. Recover corrupted exp DMP file Data

Cpfl> SEARCH table T_xifenfei from expfile/tmp/t_xifenfei.dmp8461:table "T_xifenfei" 8480:create TABLE "T_XIFENFEI" ("OWNER" VARCHAR2 (+), "object_name" VARCHAR2 (+), "Subobject_name" VARCHAR2 (+), "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 (+), "sharing" VARCHAR2, "Editionable" VARCHAR2 (1), "oracle_maintained" VARCHAR2 (1)) PCTFREE pctused 40 Initrans 1 Maxtrans 255 STORAGE (INITIAL 8388608 NEXT 1048576 minextents 1 freelists 1 FREELIST GROUPS 1 buffer_pool defau LT) tablespace "USERS" LOGGING Nocompress9145:insert into "T_xifenfei" ("OWNER", "object_name", "Subobject_name", "objec t_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,: +,: +) conventional export9644:start of table data12331252:table "T_xifenfei" 12331349:endtablecpfl> UNLOAD tab LE "T_xifenfei" ("OWNER" VARCHAR2 (+), "object_name" VARCHAR2 (+), "Subobject_name" VARCHAR2 (+), "object_id" number , "data_object_id" number, "Object_type" VARCHAR2, "CREATED" date, "Last_ddl_time" date, "TIMESTAMP" VARCHAR2 (), "S Tatus "VARCHAR2 (7)," temporary "VARCHAR2 (1)," GENERATED "VARCHAR2 (1)," secondary "VARCHAR2 (1)," NAMESPACE "number,"  Edition_name "VARCHAR2", "Sharing" VARCHAR2, "Editionable" VARCHAR2 (1), "oracle_maintained" VARCHAR2 (1)) Expfile/tmp/t_xifenfei.dmp from 8480 until 12331349--because exp DMP file corruption records CPFL:Error:column 1 length 21059 exceeds max Bind Size 1280000000000 45415445 20544142 4c452022 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 failed8480:row 1 failedrow Conversio n failure, retrying from offset 8481cpfl:error:zero (illegal) length column number 2 ..... CPFL:Error:Zero (illegal) length column number 19644:succesful conversion 1164 bytes skipped due to conversion pro Blems131877:row, Ok253310:row, ....... 12200617:row 90000 okunloaded 90915 rows, end of table marker at 12322835[[email protected] cpfl]$ ls-ltr T_XIFENFE i.*-rw-r--r--1 Oracle oinstall 17230747 Apr 22:12 t_xifenfei.dat-rw-r--r--1 Oracle oinstall 1489 Apr 22:17 T_X Ifenfei.ctl

3. Import data and compare

Sql> CREATE TABLE T_xifenfei_exp as SELECT * from T_xifenfei where 1=0; Table created. [[email protected] cpfl]$ more t_xifenfei.ctl load datacharacterset utf8infile ' t_xifenfei.dat ' insertinto table "T _xifenfei_exp "---modified to t_xifenfei_exp table fields terminated by whitespace (" OWNER "CHAR (+) Enclos Ed by X ' 7C ', "object_name" char (+) enclosed by x ' 7C ', "Subobject_name" char (+) E  nclosed 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" Enclos Ed by X ' 7C ', "TIMESTAMP" char (+) enclosed by x ' 7C ', "STATUS" char (5) ENC losed 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 (all) enclosed by x ' 7C ', "editionable" CHA R (1) enclosed by x ' 7C ', "oracle_maintained" CHAR (1) enclosed by x ' 7C ', "Unexp_status" F ILLER CHAR (3) enclosed by X ' 7C ') [[email protected] cpfl]$ sqlldr chf/[email protected] Control=t_ Xifenfei.ctl sql*loader:release 12.1.0.1.0 on Sun Apr 22:17:54 2014Copyright (c) 1982, the Oracle and/or its a  Ffiliates. All rights reserved. Path Used:conventionalcommit Point reached-logical Record Count 64Commit point reached-logical Record Count 128C Ommit Point reached-logical Record Count 192 ...... Commit Point reached-logical Record Count 90887Commit Point reached-logical Record Count 90915Table "T_xifenfei_exp": 90915 Rows successfully loaded. Check the log file:T_XIFENFEI.logfor more information about the load.  [[email protected] cpfl]$ sqlplus chf/[email protected]sql*plus:release 12.1.0.2.0 Beta on Sun APR 27 22:18:08  2014Copyright (c) 1982, Oracle. All rights reserved. Last successful login Time:sun Apr 22:17:54 +08:00connected to:oracle Database 12c Enterprise Edition Release 12. 1.0.1.0-64bit with the partitioning, OLAP, Advanced Analytics and Real application testing optionssql> Select Cou  NT (*) from T_xifenfei_exp; COUNT (*)----------90915sql> select * from T_xifenfei 2 minus 3 select * from T_xifenfei_exp;no rows selected

As can be seen here, in the case of the exp DMP file is partially damaged, or can be directly read the DMP file to recover all or part of the exp DMP file content (the specific amount of recovery depends on the degree of damage to the DMP file)

--------------------------------------ORACLE-DBA----------------------------------------

The most authoritative and professional case of Oracle Case resource Summary: Oracle EXP DMP file Corruption recovering data tables in DMP by CPFL tool

Original URL: http://www.oracleplus.net/arch/oracle-20160525-224.html

Oracle Research Center Key Words:Oracle exp DMP file Corruption Recovery of data tables in DMP by CPFL tool recover exp Exported file corruption How to import data

Case: Corrupted Oracle exp DMP File Recovery by extracting data tables from DMP using the CPFL tool