标签云
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,678)
- DB2 (22)
- MySQL (73)
- Oracle (1,540)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- 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)
-
最近发表
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- 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故障再现
标签归档:dmp损坏
expdp dmp被加密破坏恢复
有朋友oracle数据库dmp备份被加密,后缀为:.DMP.voyager,通过分析发现文件加密2M左右
这里可以看出来dmp文件为expdp方式导出(expdp本质上xml方式存储,exp使用直接二进制方式存储),通过工具分析可恢复表情况.
通过工具对该dmp文件进行分析
CPFL> OPEN F:\BaiduNetdisk\KINGDEE85GH_2020-03-17.DMP.voyager TABLE_NAME START_POS DATA_BYTE -------------------------------------------------- --------------- --------------- KINGDEE85GH.T_WFD_PROCESSDEF 116300288 648396035 KINGDEE85GH.T_DYN_DYNAMICCONFIGURE 864710656 181453794 KINGDEE85GH.T_RPTS_STORAGEFILEDATA 1078767616 21548951 KINGDEE85GH.T_BOT_RULESEGMENT 1100324864 10372516 KINGDEE85GH.T_LOG_APP 1110712320 12603573 KINGDEE85GH.T_PM_PERMITEM 1123336192 7282412 KINGDEE85GH.T_PM_USERORGPERM 1130635264 6692320 KINGDEE85GH.T_DYN_APPSOLUTION 1137336320 801697 KINGDEE85GH.T_PM_MAINMENUITEM 1138155520 3573943 KINGDEE85GH.T_PM_PERMUIGROUP 1141751808 2159245 KINGDEE85GH.T_SYS_ENTITYREF 1143922688 4183869 KINGDEE85GH.T_PM_ROLEPERM 1148116992 2758960 KINGDEE85GH.T_BAS_SYSMENUITEM 1150885888 3304627 KINGDEE85GH.T_JP_PAGE 1154211840 3019174 ………… KINGDEE85GH.T_XT_CHECKTIME 1212776448 41 KINGDEE85GH.T_XT_SYNCHTIME 1212784640 41 SYSTEM.SYS_EXPORT_SCHEMA_02 1212792832 215423380 -------------------------------------------------- --------------- --------------- Scanned Find 895 segments.
通过这个基本上可以确定丢失了100多M数据,其他数据理论上可以恢复.
创建用户
SQL> create user KINGDEE85GHidentified by oracle; User created. SQL> grant dba to KINGDEE85GH; Grant succeeded.
unexpdp数据(自动创建表和导入数据)
CPFL> unexpdp table KINGDEE85GH.T_WFD_PROCESSDEF unexpdp table: KINGDEE85GH.T_WFD_PROCESSDEF storage(START_POSITION:116300288 DATA_BYTE:748396035) 824 rows unexpdp
如果你有oracle expdp dmp被加密或者破坏,无法正常导入数据库,可以联系我们对其进行恢复处理:提供(ORACLE数据库恢复技术支持):
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
如果你的oracle dmp是exp方式导出,也可以联系我们对其进行处理,参见:
exp dmp文件损坏恢复
oracle 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完美跳过了坏块所在表,实现后续数据完美恢复
exp dmp文件损坏恢复
在有些时候,exp的dmp文件因为某种原因损坏(比如磁盘异常,exp过程损坏等),导致imp导入无法继续,下面的处理方法(直接读取dmp文件)来对dmp文件进行抢救性恢复,最大程度减少数据丢失损失
创建exp dmp文件并使用dd破坏
SQL> create table t_xifenfei as select * from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 90915 [oracle@localhost ~]$ exp chf/xifenfei@pdb1 file=/tmp/t_xifenfei.dmp tables=t_xifenfei Export: Release 12.1.0.2.0 on Sun Apr 27 21:39:26 2014 Copyright (c) 1982, 2014, 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 Analytics and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8MSWIN1252 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table T_XIFENFEI 90915 rows exported EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. [oracle@localhost ~]$ od -x /tmp/t_xifenfei.dmp |head -10 0000000 0003 4501 5058 524f 3a54 3156 2e32 3130 0000020 302e 0a30 4344 4648 520a 4154 4c42 5345 0000040 380a 3931 0a32 0a30 3237 300a 000a 0001 0000060 07b2 00d0 0001 0000 0000 0000 0000 0013 0000100 2020 2020 2020 2020 2020 2020 2020 2020 * 0000140 2020 2020 2020 2020 7553 206e 7041 2072 0000160 3732 3220 3a31 3933 323a 2036 3032 3431 0000200 742f 706d 742f 785f 6669 6e65 6566 2e69 0000220 6d64 0070 0000 0000 0000 0000 0000 0000 --strings命令看dmp文件 [oracle@localhost ~]$ strings /tmp/t_xifenfei.dmp |head -50 EXPORT:V12.01.00 DCHF RTABLES 8192 Tue Apr 29 0:39:49 2014/tmp/t_xifenfei.dmp #G#G #G#G +08:00 BYTE UNUSED INTERPRETED DISABLE:ALL METRICST TABLE "T_XIFENFEI" CREATE TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 13631488 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS INSERT 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, :10, :11, :12, :13, :14, :15, :16, :17, :18) PUBLIC V$DATAGUARD_CONFIG SYNONYM 2014-04-22:17:51:05 VALID METADATA LINK V_$DATAGUARD_STATS VIEW 2014-04-22:17:51:05 --破坏exp dmp文件 [oracle@localhost ~]$ dd if=/dev/zero of=/tmp/t_xifenfei.dmp bs=1024 count=1 conv=notrunc 1+0 records in 1+0 records out 1024 bytes (1.0 kB) copied, 6.0291e-05 seconds, 17.0 MB/s [oracle@localhost ~]$ od -x /tmp/t_xifenfei.dmp |head -10 0000000 0000 0000 0000 0000 0000 0000 0000 0000 * 0020000 0064 0000 6000 2401 050f 0c0b 0c03 050c 0020020 0504 060d 0709 0508 0505 0505 050f 0505 0020040 0505 050a 0505 0505 0504 0706 0808 4723 0020060 4723 1108 0823 4111 47b0 8300 b200 d007 0020100 0003 0000 0000 0000 0000 0000 0000 0000 0020120 0000 0000 0000 0000 0000 0000 0000 0000 0020140 0000 0000 0000 0064 0000 6000 2401 050f 0020160 0c0b 0c03 050c 0504 060d 0709 0508 0505 --损坏后的dmp文件使用strings命令看 [oracle@localhost ~]$ strings /tmp/t_xifenfei.dmp |head -50 #G#G #G#G +08:00 BYTE UNUSED INTERPRETED DISABLE:ALL METRICST TABLE "T_XIFENFEI" CREATE TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 13631488 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS INSERT 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, :10, :11, :12, :13, :14, :15, :16, :17, :18) PUBLIC V$DATAGUARD_CONFIG SYNONYM 2014-04-22:17:51:05 VALID METADATA LINK V_$DATAGUARD_STATS --imp 导入dmp文件失败 [oracle@localhost ~]$ imp chf/xifenfei@pdb1 file=/tmp/t_xifenfei.dmp full=y Import: Release 12.1.0.2.0 - on Sun Apr 27 22:02:40 2014 Copyright (c) 1982, 2014, 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 Analytics and Real Application Testing options IMP-00037: Character set marker unknown IMP-00000: Import terminated unsuccessfully
这里通过分析可以知道,exp dmp文件虽然损坏了一点,但是通过strings命令看,相关记录依然存在,因此可以通过工具去读exp dmp文件,然后分析得出相关数据
恢复损坏exp dmp文件数据
CPFL> SEARCH TABLE T_XIFENFEI FROM EXPFILE /tmp/t_xifenfei.dmp 8461: TABLE "T_XIFENFEI" 8480: CREATE TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS 9145: INSERT 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, :10, :11, :12, :13, :14, :15, :16, :17, :18) Conventional export 9644: start of table data 12331252: TABLE "T_XIFENFEI" 12331349: ENDTABLE CPFL> UNLOAD TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) expfile /tmp/t_xifenfei.dmp from 8480 until 12331349 --因为exp dmp文件损坏记录 CPFL: Error: column 1 length 21059 exceeds max bind size 128 0000000000 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 failed 8480: row 1 failed row conversion failure, retrying from offset 8481 CPFL: Error: Zero (illegal) length column number 2 ………… CPFL: Error: Zero (illegal) length column number 1 9644: succesful conversion 1164 bytes skipped due to conversion problems 131877: row 1000 ok 253310: row 2000 ok ………… 12200617: row 90000 ok Unloaded 90915 rows, end of table marker at 12322835 [oracle@localhost CPFL]$ ls -ltr T_XIFENFEI.* -rw-r--r-- 1 oracle oinstall 17230747 Apr 27 22:12 T_XIFENFEI.dat -rw-r--r-- 1 oracle oinstall 1489 Apr 27 22:17 T_XIFENFEI.ctl
导入数据并对比
SQL> create table t_xifenfei_exp as select * from t_xifenfei where 1=0; Table created. [oracle@localhost CPFL]$ more T_XIFENFEI.ctl load data CHARACTERSET UTF8 infile 'T_XIFENFEI.dat' insert into table "T_XIFENFEI_EXP" ---修改为T_XIFENFEI_EXP表 fields terminated by whitespace ( "OWNER" CHAR(128) enclosed by X'7C' ,"OBJECT_NAME" CHAR(128) enclosed by X'7C' ,"SUBOBJECT_NAME" CHAR(29) enclosed by X'7C' ,"OBJECT_ID" CHAR(5) enclosed by X'7C' ,"DATA_OBJECT_ID" CHAR(5) enclosed by X'7C' ,"OBJECT_TYPE" CHAR(20) 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" enclosed by X'7C' ,"TIMESTAMP" CHAR(19) enclosed by X'7C' ,"STATUS" CHAR(5) enclosed 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(13) enclosed by X'7C' ,"EDITIONABLE" CHAR(1) enclosed by X'7C' ,"ORACLE_MAINTAINED" CHAR(1) enclosed by X'7C' ,"UNEXP_STATUS" FILLER CHAR(3) enclosed by X'7C' ) [oracle@localhost CPFL]$ sqlldr chf/xifenfei@pdb1 control=T_XIFENFEI.ctl SQL*Loader: Release 12.1.0.1.0 on Sun Apr 27 22:17:54 2014 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 64 Commit point reached - logical record count 128 Commit point reached - logical record count 192 ………… Commit point reached - logical record count 90887 Commit point reached - logical record count 90915 Table "T_XIFENFEI_EXP": 90915 Rows successfully loaded. Check the log file: T_XIFENFEI.log for more information about the load. [oracle@localhost CPFL]$ sqlplus chf/xifenfei@pdb1 SQL*Plus: Release 12.1.0.2.0 Beta on Sun Apr 27 22:18:08 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Apr 27 2014 22:17:54 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select count(*) from t_xifenfei_exp; COUNT(*) ---------- 90915 SQL> select * from t_xifenfei 2 minus 3 select * from t_xifenfei_exp; no rows selected
通过这里可以看出来,在exp dmp文件有部分损坏的情况下,还是可以通过直接读取dmp文件的方式恢复全部或者部分exp dmp文件中内容(具体恢复量取决于dmp文件损坏程度)
如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们(ORACLE数据库恢复技术支持),将为您提供专业数据库技术支持:
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com