标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,671)
- DB2 (22)
- MySQL (73)
- Oracle (1,533)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (560)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
标签归档:exp dmp恢复
exp dmp文件损坏(坏块/corruption)恢复—跳过dmp坏块
在有些情况下,大家都知道通过dul可以恢复损坏的dmp文件的表的数据,但是该方法有很多问题,特别是对很多数据类型的支持不够完美,比如lob,long raw类型等,而且还有可能恢复出来数据大量丢失,本人通过对dmp结构的分析,使用使用一些特殊的技巧方法,可以实现对于损坏的dmp文件,通过跳过异常坏块所在表,继续恢复后续表,从而最大程度减少损坏
创建测试表
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
导出来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.
获取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"
使用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
尝试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终止
检查导入表情况
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之后,因此也未导入
使用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被跳过
验证导入数据
[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完美跳过了坏块所在表,实现后续数据完美恢复