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跳过坏块并成功导入恢复
关键词：
379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
