Oracle数据库exp导出dmp文件损坏存在坏块/corruption通过CPFL工具跳过dmp坏块进发导入
在有些情况下，大家都知道通过dul可以恢复损坏的dmp文件的表的数据,但是该方法有很多问题，特别是对很多数据类型的支持不够完美,比如lob,long raw类型等,而且还有可能恢复出来数据大量丢失,本人通过对dmp结构的分析,使用使用一些特殊的技巧方法,可以实现对于损坏的dmp文件,通过跳过异常坏块所在表,继续恢复后续表,从而最大程度减少损坏
1.创建Oracle测试表
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 TABLE
T_XIFENFEI2 TABLE
T_XIFENFEI3 TABLE
SQL> select count(*) from t_xifenfei1;
COUNT(*)
----------
86275
SQL> select count(*) from t_xifenfei2;
COUNT(*)
----------
3387
SQL> select count(*) from t_xifenfei3;
COUNT(*)
----------
2800
2.使用EXP命令导出dmp文件
[oracle@web103 ~]$ exp "'/ as sysdba'" owner=xifenfei file=/data/temp/t_xifenfei.dmp log=/data/temp/exp_t_xifenfei.log Export: Release 11.2.0.4.0 - Production on Tue Aug 18 22:08:30 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About 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 table T_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 statistics Export terminated successfully without warnings.
3.获取dmp file中的T_XIFENFEI2所在位置
计划在该表上通过dd进行破坏,因此需要事先知道该表所在的dmp文件位置范围
CPFL> SEARCH TABLE T_XIFENFEI2 FROM EXPFILE /tmp/t_xifenfei.dmp 9980561: TABLE "T_XIFENFEI2" 9980581: CREATE TABLE "T_XIFENFEI2" (表定义忽略) 9985356: BIND information for 87 columns col[ 1] type 1 max length 1000 cset 852 (ZHS16GBK) form 1 col[ 2] type 112 max length 86 cset 852 (ZHS16GBK) form 1 col[ 3] type 1 max length 13 cset 852 (ZHS16GBK) form 1 col[ 4] type 2 max length 22 col[ 5] type 2 max length 22 col[ 6] type 2 max length 22 col[ 7] type 2 max length 22 col[ 8] type 2 max length 22 col[ 9] type 2 max length 22 col[ 10] type 2 max length 22 col[ 11] type 2 max length 22 col[ 12] type 2 max length 22 col[ 13] type 2 max length 22 col[ 14] type 2 max length 22 col[ 15] type 2 max length 22 col[ 16] type 2 max length 22 col[ 17] type 1 max length 38 cset 852 (ZHS16GBK) form 1 col[ 18] type 2 max length 22 col[ 19] type 2 max length 22 col[ 20] type 2 max length 22 col[ 21] type 2 max length 22 col[ 22] type 2 max length 22 col[ 23] type 2 max length 22 col[ 24] type 2 max length 22 col[ 25] type 2 max length 22 col[ 26] type 2 max length 22 col[ 27] type 2 max length 22 col[ 28] type 2 max length 22 col[ 29] type 2 max length 22 col[ 30] type 2 max length 22 col[ 31] type 1 max length 10 cset 852 (ZHS16GBK) form 1 col[ 32] type 2 max length 22 col[ 33] type 23 max length 2000 col[ 34] type 2 max length 22 col[ 35] type 2 max length 22 col[ 36] type 2 max length 22 col[ 37] type 1 max length 30 cset 852 (ZHS16GBK) form 1 col[ 38] type 2 max length 22 col[ 39] type 23 max length 8 col[ 40] type 23 max length 8 col[ 41] type 2 max length 22 col[ 42] type 2 max length 22 col[ 43] type 2 max length 22 col[ 44] type 2 max length 22 col[ 45] type 1 max length 64 cset 852 (ZHS16GBK) form 1 col[ 46] type 2 max length 22 col[ 47] type 1 max length 64 cset 852 (ZHS16GBK) form 1 col[ 48] type 2 max length 22 col[ 49] type 1 max length 64 cset 852 (ZHS16GBK) form 1 col[ 50] type 2 max length 22 col[ 51] type 2 max length 22 col[ 52] type 1 max length 64 cset 852 (ZHS16GBK) form 1 col[ 53] type 2 max length 22 col[ 54] type 2 max length 22 col[ 55] type 2 max length 22 col[ 56] type 23 max length 8 col[ 57] type 2 max length 22 col[ 58] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 59] type 1 max length 19 cset 852 (ZHS16GBK) form 1 col[ 60] type 2 max length 22 col[ 61] type 1 max length 38 cset 852 (ZHS16GBK) form 1 col[ 62] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 63] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 64] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 65] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 66] type 2 max length 22 col[ 67] type 1 max length 64 cset 852 (ZHS16GBK) form 1 col[ 68] type 1 max length 30 cset 852 (ZHS16GBK) form 1 col[ 69] type 1 max length 30 cset 852 (ZHS16GBK) form 1 col[ 70] type 2 max length 22 col[ 71] type 2 max length 22 col[ 72] type 2 max length 22 col[ 73] type 2 max length 22 col[ 74] type 12 max length 7 col[ 75] type 23 max length 2000 col[ 76] type 2 max length 22 col[ 77] type 2 max length 22 col[ 78] type 2 max length 22 col[ 79] type 2 max length 22 col[ 80] type 2 max length 22 col[ 81] type 2 max length 22 col[ 82] type 2 max length 22 col[ 83] type 2 max length 22 col[ 84] type 2 max length 22 col[ 85] type 2 max length 22 col[ 86] type 2 max length 22 col[ 87] type 2 max length 22 Conventional export 9986063: start of table data 19675141: TABLE "T_XIFENFEI3"
4.使用dd命令破坏T_XIFENFEI2所在位置的dmp文件
[oracle@web103 ~]$ dd if=/dev/zero of=/data/temp/t_xifenfei.dmp bs=1024 count=2 conv=notrunc seek=9747 2+0 records in 2+0 records out 2048 bytes (2.0 kB) copied, 1.6e-05 seconds, 128 MB/s
5.尝试imp导入被破坏的dmp文件数据
[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew
>file=/data/temp/t_xifenfei.dmp log=/data/temp/imp_t_xifenfei.log
Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:35:09 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table "T_XIFENFEI1" 86275 rows imported
IMP-00017: following statement failed with ORACLE error 1740:
"CREATE TABLE "T_XIFENFEI2" ("SQL_TEXT" VARCHAR2(1000), "SQL_FULLTEXT" CLOB,"
" "SQL_ID" VARCHAR2(13), "SHARABLE_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 encountered
ORA-01740: missing double quote in identifier
IMP-00008: unrecognized statement in the export file:
IMP-00008: unrecognized statement in the export file:
导入报IMP-00003 ORA-01740 IMP-00008,由于dmp文件被dd破坏(而且破坏位置是T_XIFENFEI2所在之处),因此imp导入到T_XIFENFEI2之时,抛出大量异常,imp终止
6.检查Oracle数据库Imp导入表情况
SQL> conn xifenfeinew/"www.xifenfei.com"
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1 TABLE
SQL> select count(*) from t_xifenfei1;
COUNT(*)
----------
86275
和预期相符,表t_xifenfei1导入进去,但是t_xifenfei2由于坏块原因未导入,由于t_xifenfei3在t_xifenfei2之后,因此也未导入
7.使用CPFL程序抽取正常dmp文件
CPFL>getdmp '/data/temp/t_xifenfei.dmp' skip table 'T_XIFENFEI2' >/data/temp/t_xifenfeinew.dmp
重新导入dmp文件
[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew >file=/data/temp/t_xifenfeinew.dmp log=/data/temp/imp_t_xifenfeinew.log Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:41:04 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in ZHS16GBK 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 imported Import terminated successfully no warnings.
导入了t_xifenfei1，t_xifenfei3，果然t_xifenfei2被跳过
8.验证导入数据
[oracle@web103 ~]$ sqlplus xifenfeinew/"www.xifenfei.com"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 22:41:32 2015
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, OLAP, Data Mining and Real Application Testing options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1 TABLE
T_XIFENFEI3 TABLE
SQL> select count(*) from t_xifenfei1;
COUNT(*)
----------
86275
SQL> select count(*) from t_xifenfei3;
COUNT(*)
----------
2800
SQL>
通过验证数据证明，通过CPFL完美跳过了坏块所在表,实现后续数据完美恢复
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle exp dmp文件存在坏块并损坏 使用CPFL跳过坏块并成功导入恢复
原文唯一网址：http://www.oracleplus.net/arch/oracle-20160524-223.html