Case: The Oracle exp DMP file has a bad block and is corrupted using CPFL to skip the bad block and successfully import the recovery

Oracle Database EXP Export DMP file corrupt existence bad block/corruption skip DMP bad block by CPFL tool import

In some cases, it is known that data can be recovered from a table of damaged DMP files through Dul, but there are many problems with this method, especially for many data types, such as Lob,long raw type, etc., and the possibility of recovering large amounts of data loss, I through the analysis of the DMP structure, using a number of special techniques, can be implemented for the damaged DMP file, by skipping the exception of the bad block table, continue to restore the subsequent table, thereby minimizing the damage

1. Create an Oracle Test table

Sql> Conn xifenfei/"www.xifenfei.com" connected.sql> create TABLE T_xifenfei1 as SELECT * from Dba_objects; Table created. Sql> CREATE TABLE T_xifenfei2 as SELECT * from V$sql; Table created. Sql> CREATE TABLE T_xifenfei3 as SELECT * from Dba_tables; Table created. Sql> select * from tab; Tname                          tabtype  clusterid-----------------------------------------------t_xifenfei1                    tablet_xifenfei2                    Tablet_xifenfei3                    Tablesql> Select COUNT (*) from T_XIFENFEI1;  COUNT (*)----------     86275sql>  Select COUNT (*) from T_xifenfei2;  COUNT (*)----------      3387sql>  Select COUNT (*) from T_xifenfei3;  COUNT (*)----------      2800

2. Export the DMP file using the EXP command

[[email protected] ~]$ exp "'/As Sysdba '" Owner=xifenfei file=/data/temp/t_xifenfei.dmp log=/data/temp/exp_t_  Xifenfei.logExport:Release 11.2.0.4.0-production on Tue 22:08:30 2015Copyright (c) 1982, +, Oracle and/or its  Affiliates. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith The partitioning, OLAP, Dat A Mining and Real application testing optionsexport done in ZHS16GBK character set and al16utf16 NCHAR character setabout To export specified users .... exporting Pre-schema procedural objects and actions. Exporting foreign function library names for user Xifenfei. Exporting public type synonyms. Exporting private type synonyms. Exporting object type definitions for user Xifenfei about to export Xifenfei ' s objects .... Exporting database links. Exporting sequence numbers. Exporting cluster definitions. About to export Xifenfei ' s tables via conventional Path .....                    Exporting tableT_xifenfei1 86275 rows exported. . Exporting table T_xifenfei2 3387 rows exported. . Exporting table T_xifenfei3 2800 rows exported. Exporting synonyms. Exporting views. Exporting stored procedures. Exporting operators. Exporting referential integrity constraints. exporting triggers. Exporting Indextypes. Exporting bitmap, functional and extensible indexes. Exporting posttables actions. Exporting materialized views. Exporting snapshot logs. Exporting job queues. Exporting refresh groups and children. Exporting dimensions. Exporting Post-Schema procedural objects and actions. Exporting Statisticsexport terminated successfully without warnings.

3. Get the location of the T_XIFENFEI2 in the DMP file

You plan to break through DD on this table, so you need to know beforehand where the DMP file location of the table is located

Cpfl> SEARCH TABLE t_xifenfei2 from expfile/tmp/t_xifenfei.dmp9980561:table "T_xifenfei2" 9980581:create TABLE "T_XI  FENFEI2 "(table definition ignored) 9985356:bind information for columns col[1] Type 1 max length, Cset 852 (ZHS16GBK) Form 1 col[ 2] Type Cset max length 852 (zhs16gbk) Form 1 col[3] Type 1 max length Cset 852 (ZHS16GBK) Form 1 col[4] Typ E 2 max length col[5] Type 2 max length col[6] Type 2 max length col[7] Type 2 max length col[8] Type 2 max length col[9] Type 2 max length col[] Type 2 max length [col[] Type 2 max length [col[] Type 2 Max length col[] Type 2 max length col[] Type 2 max length [col[] Type 2 max length [col[] Type 2 ma x length col[] Type 1 max length Cset 852 (ZHS16GBK) Form 1 col[] Type 2 max length [col[] Type 2 max len Gth col[] Type 2 max length col[] Type 2 max length col[] Type 2 max length [col[] Type 2 max Lengt H col[] Type 2 max Length col[] Type 2 max length col[] Type 2 max length col[] Type 2 max length [col[] Type 2 max l Ength col[] Type 2 max length [col[] Type 2 max length [col[] Type 1 max length ten Cset 852 (ZHS16GBK) for M 1 col[] Type 2 max length col[) type max length [col[] Type 2 max length [col[] Type 2 max Lengt h [col[] Type 2 max length col[PNS] Type 1 max length Cset 852 (ZHS16GBK) Form 1 col[] Type 2 max length 22 col[] Type Max length 8 col[] Type Max length 8 col[] Type 2 max length [col[] Type 2 max length of CO L[] Type 2 max length col[) type 2 max length [col[] Type 1 max length 46 Cset 852 (ZHS16GBK) Form 1 col[ ] Type 2 max length [col[] Type 1 max length Cset 852 (ZHS16GBK) Form 1 col[] Type 2 max length [col[] Typ E 1 max length Cset 852 (ZHS16GBK) Form 1 col[] Type 2 max length [col[] Type 2 max Length (col[) type 1 m Ax length Cset 852 (ZHS16GBK) Form 1 col[) type 2 max length col[[] Type 2 max length 23 col[] Type 2 max length [col[] Type Max length 8 col[] Type 2 max length col[) Type 1 max length 1 Cset 852 (ZHS16GBK) Form 1 col[] Type 1 max le Ngth Cset 852 (ZHS16GBK) Form 1 col[] Type 2 max length [col[] Type 1 max length of Cset 852 (ZHS16GBK) Form 1 Col[] Type 1 max length 1 Cset 852 (ZHS16GBK) Form 1 col[[] Type 1 max length 1 Cset 852 (ZHS16GBK) Form 1 col[] t YPE 1 Max length 1 Cset 852 (ZHS16GBK) Form 1 col[+] Type 1 max length 1 Cset 852 (ZHS16GBK) Form 1 col["Type 2 Max" length [col[] Type 1 max length Cset 852 (ZHS16GBK) Form 1 col["type 1 max Length" Cset 852 (ZHS16GBK) Form 1 col[] Type 1 max length Cset 852 (ZHS16GBK) Form 1 col[] Type 2 max Length (col[) [Type 2 max length] Col Type 2 max Length (col[) type 2 max length col[) type max length 7 col[] Type max length L[] Type 2 max length [col[] Type 2 max length col[) type 2 max length [col[] Type 2 max length col[] Type 2 max length 2 2 col[Bayi] Type 2 max length col[[2 max length] [col[] Type 2 max length col[] Type 2 max length 22  col[[] Type 2 max length col[] Type 2 max length [col[] Type 2 max length 22Conventional Export9986063:start of table data19675141:table "T_xifenfei3"

4. Use the DD command to destroy the DMP file where the T_xifenfei2 is located

[[email protected] ~]$ dd If=/dev/zero of=/data/temp/t_xifenfei.dmp bs=1024 count=2 conv=notrunc seek=97472+0 Records in2 +0 Records out2048 bytes (2.0 kB) copied, 1.6e-05 seconds, MB/s

5. Try imp to import the corrupted DMP file data

[[email protected] ~]$ imp "'/As Sysdba '" Fromuser=xifenfei touser=xifenfeinew >file=/data/temp/t_ Xifenfei.dmp log=/data/temp/imp_t_xifenfei.logimport:release 11.2.0.4.0-production on Tue 22:35:09 2015Copyrigh  T (c) 1982, the Oracle and/or its affiliates. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith The partitioning, OLAP, Dat A Mining and Real application testing Optionsexport file created by export:v11.02.00 via conventional pathimport do in Z HS16GBK character set and Al16utf16 NCHAR character set. Importing Xifenfei ' s objects into xifenfeinew. . Importing table "T_xifenfei1" 86275 rows importedimp-00017:following statement failed with ORACLE E Rror 1740: "CREATE TABLE" T_xifenfei2 "(" Sql_text "VARCHAR2 (+)," Sql_fulltext "CLOB," "" sql_id "VARCHAR2 ()," sharabl E_mem "number," Persistent_mem "number," RU "" Ntime_mem "number," sorts "number," loaded_versions "number," OPen_versions "" "Number," users_opening "number," fetches "number," executions "number," P "" x_servers_executions "number , "End_of_fetch_cou" imp-00003:oracle error 1740 encounteredora-01740:missing double quote in Identifierimp-00008:unrec  Ognized statement in the export file:imp-00008:unrecognized statement in the export file:

Import report IMP-00003 ORA-01740 IMP-00008, because the dmp file is broken by DD (and the location of the destruction is where the t_xifenfei2 is), so when IMP is imported to T_xifenfei2, a large number of exceptions are thrown, IMP terminates

6. Check the Oracle Database IMP Import table Case

Sql> Conn xifenfeinew/"www.xifenfei.com" connected.sql> select * from tab; Tname                          tabtype  clusterid-----------------------------------------------t_xifenfei1                    tablesql> Select COUNT (*) from T_XIFENFEI1;  COUNT (*)----------     86275

As expected, table t_xifenfei1 is imported, but T_xifenfei2 is not imported due to bad block reasons, because T_xifenfei3 is not imported after T_xifenfei2.

7. Extracting normal DMP files using the CPFL program

Cpfl>getdmp '/data/temp/t_xifenfei.dmp ' skip table ' T_xifenfei2 ' >/data/temp/t_xifenfeinew.dmp

Re-importing DMP files

[[Email protected] ~]$ imp "'/As Sysdba '" Fromuser=xifenfei touser=xifenfeinew >file=/data/temp/t_xifenfeinew.dmp  Log=/data/temp/imp_t_xifenfeinew.logimport:release 11.2.0.4.0-production on Tue 22:41:04 2015Copyright (c) 1982, , Oracle and/or its affiliates.  All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith The partitioning, OLAP, Dat A Mining and Real application testing Optionsexport file created by export:v11.02.00 via conventional pathimport do in Z HS16GBK character set and Al16utf16 NCHAR character set. Importing Xifenfei ' s objects into xifenfeinew. . Importing table                  "T_xifenfei1"      86275 rows imported: Importing table                  "T_xifenfei3"       2800 rows Importedimport terminated successfully no warnings.

Import the T_xifenfei1,t_xifenfei3, and sure enough t_xifenfei2 be skipped

8. Validating Import Data

[Email protected] ~]$ sqlplus xifenfeinew/"www.xifenfei.com" Sql*plus:release 11.2.0.4.0 Production on Tue 18 22:41:3 2 2015Copyright (c) 1982, Oracle.  All rights reserved.  Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith The partitioning, OLAP, Data Mining and Real Application testing optionssql> select * from tab; Tname                          tabtype  clusterid-----------------------------------------------t_xifenfei1                    tablet_xifenfei3                    tablesql> Select COUNT (*) from T_XIFENFEI1;  COUNT (*)----------     86275sql> Select COUNT (*) from T_xifenfei3;  COUNT (*)----------      

By validating the data, the CPFL perfectly skips the table of the bad block and achieves the perfect recovery of the subsequent data.

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

The most authoritative and professional Oracle Case Resource Summary case: The Oracle exp DMP file has a bad block and is corrupted using CPFL to skip the bad block and successfully import the recovery

Original URL: http://www.oracleplus.net/arch/oracle-20160524-223.html

Oracle Research Center Key Words:Oracle exp DMP file has bad blocks and corruption cannot be imported using CPFL Skip DMP file bad block and successfully import recovery

Case: The Oracle exp DMP file has a bad block and is corrupted using CPFL to skip the bad block and successfully import the recovery