分类目录归档：Oracle

深入分析一次ORA-00314错误

发表于 2014 年 5 月 17 日 由 惜分飞

运行在win平台上的oracle 10G数据库,因为主机蓝屏,使用pe拷贝出来相关数据库文件,redo文件,控制文件,在恢复数据库过中遇到ORA-00314错误,而无法继续下去

恢复报错

SQL> recover database
ORA-00283: 恢复会话因错误而取消
ORA-00314: 日志 3 (用于线程 1) 要求的序号 1366 与 1363 不匹配
ORA-00312: 联机日志 3 线程 1:
'DC:\ORADATA\INTERLIB\REDO03.LOG'

00314, 00000, "log %s of thread %s, expected sequence# %s doesn't match %s"
// *Cause:  The online log is corrupted or is an old version.
// *Action: Find and install correct version of log or reset logs.

这里提示可以大概看出来,数据库进行恢复的时候,需要sequence 为1366的日志,但是与现在的1363不匹配。通过上面的解释我们很可能知道是redo文件异常或者弄错了redo文件

分析Oracle Database Recovery Check结果
控制文件scn相关信息,从这里可以看到数据库的控制文件scn为47714860,checkpoint scn为47711411
r1
控制文件中关于数据库文件是scn为47711411,而且stop_scn为null
r2
数据文件头scn是47711411,而且fuzzy为yes
r3
redo的相关信息,这里我们可以看出当前redo的sequence为1366,first_scn为47711411,redo的写入顺序是redo01->redo02->redo03->redo01
r4
通过这里整体分析,我们可以知道,数据库为非正常关闭,恢复应该从redo03(sequence 1366)开始进行恢复,但是为什么出现ORA-00314呢？通过dump redo header继续分析

dump redo header 分析
这个里面我们可以看到redo01的sequence 为16进制554等于10进制的1364,scn的范围为:0x000002d6e4ab-0x000002d7455b
redo1

这个里面我们可以看到redo02的sequence 为16进制555等于10进制的1365,scn的范围为:0x000002d7455b-0x000002d804b3
redo2

这个属于异常redo,注意第一个seq: 0×00000556等于十进制的1366(也就是表示该redo的sequence为1366),”Seq# 0000001363,SCN 0x000002d5e1c6-0x000002d6e4ab”表示在redo的文件头有部分信息记录的为sequence为1633,另外这里表示该文件最大scn为0x000002d6e4ab,和redo01的最小scn(0x000002d6e4ab-0x000002d7455b)/redo01的”Prev scn: 0×0000.02d5e1c6″与现在看到的redo03中的最小scn匹配.
redo3

通过这里可以明白,在主机蓝屏的时候由于某种异常,导致redo03中的部分信息修改为了sequence为1366,但是部分信息依然保留它上次的sequence为1363的信息,导致数据库在重新恢复的时候无法正常成功.

处理方法
该故障是由于current redo异常导致,根据经验(具体参考:ORACLE REDO各种异常恢复),一般使用隐含参数屏蔽前滚,然后强制拉库,绝大部分情况能够拉库成功,如果人品不好可能需要使用其他隐含参数甚至bbed等方式处理

发表在 Oracle备份恢复 | 标签为 , , | 一条评论

执行计划改变导致数据库负载过高

发表于 2014 年 5 月 13 日 由 惜分飞

核心生产库突然负载飙升,我的压力来了,通过分析是一条sql的执行计划改变引起该故障,最终通过sql profile固定执行计划解决该问题
数据库主机负载
这里明显表现系统load 偏高，而且还在上升中；top的进程中，占用cpu都计划100%

top - 16:25:39 up 123 days,  1:42,  4 users,  load average: 46.19, 45.08, 43.93
Tasks: 1469 total,  28 running, 1439 sleeping,   0 stopped,   2 zombie
Cpu(s): 45.9%us,  1.1%sy,  0.0%ni, 47.1%id,  5.2%wa,  0.1%hi,  0.6%si,  0.0%st
Mem:  264253752k total, 262605260k used,  1648492k free,   413408k buffers
Swap: 33554424k total,   458684k used, 33095740k free, 67110504k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                    
 2622 oracle    18   0  150g  34m  28m R 100.0  0.0  11:58.21 oracleq9db1 (LOCAL=NO)    
15881 oracle    21   0  150g  35m  28m R 100.0  0.0  72:14.20 oracleq9db1 (LOCAL=NO)    
17214 oracle    19   0  150g  38m  32m R 100.0  0.0  20:47.44 oracleq9db1 (LOCAL=NO)    
17705 oracle    21   0  150g  34m  28m R 100.0  0.0  27:33.00 oracleq9db1 (LOCAL=NO)    
 6110 oracle    19   0  150g  34m  28m R 99.8  0.0  12:33.34 oracleq9db1 (LOCAL=NO)     
 6876 oracle    19   0  150g  34m  27m R 99.5  0.0  12:43.90 oracleq9db1 (LOCAL=NO)     
17205 oracle    23   0  150g  34m  27m R 99.5  0.0  21:37.18 oracleq9db1 (LOCAL=NO)     
24629 oracle    20   0  150g  35m  29m R 99.5  0.0  28:10.62 oracleq9db1 (LOCAL=NO)     
26959 oracle    19   0  150g  34m  27m R 99.5  0.0  47:17.87 oracleq9db1 (LOCAL=NO)     
 7655 oracle    18   0  150g  30m  25m R 98.5  0.0   2:28.45 oracleq9db1 (LOCAL=NO)     
16377 oracle    18   0  150g  34m  28m R 98.5  0.0  36:07.11 oracleq9db1 (LOCAL=NO)     
24637 oracle    18   0  150g  37m  30m R 98.2  0.0  26:39.15 oracleq9db1 (LOCAL=NO)     
 6106 oracle    21   0  150g  40m  33m R 97.2  0.0  11:37.75 oracleq9db1 (LOCAL=NO)     
28785 oracle    18   0  150g  34m  28m R 96.9  0.0  24:03.29 oracleq9db1 (LOCAL=NO)     
24278 oracle    17   0  150g  31m  26m S 96.5  0.0   3:15.51 oracleq9db1 (LOCAL=NO)     
24283 oracle    17   0  150g  33m  28m S 96.5  0.0   6:25.26 oracleq9db1 (LOCAL=NO)     
 7098 oracle    18   0  150g  32m  27m R 94.6  0.0   2:20.22 oracleq9db1 (LOCAL=NO)     
 6874 oracle    17   0  150g  34m  28m R 87.0  0.0  12:02.92 oracleq9db1 (LOCAL=NO)     
18206 oracle    16   0  150g  34m  27m R 86.1  0.0  16:11.28 oracleq9db1 (LOCAL=NO)     
 7096 oracle    17   0  150g  29m  24m R 85.4  0.0   3:01.72 oracleq9db1 (LOCAL=NO)

数据库等待事件

       SID EVENT
---------- ----------------------------------------------------------------
       183 gc cr request
       185 latch: cache buffers chains
       239 db file sequential read
       292 gc cr request
       406 gc cr request
       410 db file sequential read
       463 gc current request
       572 gc buffer busy acquire
       575 gc buffer busy acquire
       577 latch: cache buffers chains
       629 db file sequential read
       747 gc cr request
       919 latch: cache buffers chains
       974 gc cr request
      1033 log file sync
      1141 db file parallel write
      1153 gc cr request
      1199 db file sequential read
      1378 db file sequential read
      1495 gc cr request
      1540 db file parallel write
      1547 gc buffer busy acquire
      1662 gc cr request
      1715 gc buffer busy acquire
      1770 SQL*Net message to client
      1830 latch: cache buffers chains
      1884 gc cr request
      2113 db file sequential read
      2173 db file sequential read
      2229 rdbms ipc reply
      2292 db file sequential read
      2341 db file sequential read
      2348 gc cr request
      2460 gc cr request
      2632 gc cr request
      2684 gc cr request
      2687 db file sequential read
      2749 db file sequential read
      2913 gc cr request
      2967 db file sequential read
      3038 gc cr request
      3087 SQL*Net message to client
      3089 gc cr request
      3194 db file sequential read
      3195 db file sequential read
      3309 latch: cache buffers chains
      3371 gc cr request
      3485 gc cr request
      3535 gc cr request

可以这里有很多gc cr request等待和cache buffers chains等待，第一反应就是很可能系统某条sql执行计划不正确导致逻辑读剧增.

分析awr报告
故障时候的逻辑读 top sql可以看出来cdwjdd67x27mh的sql逻辑读异常大
awr1
分析正常时间点awr报告中cdwjdd67x27mh逻辑读情况
awr2
通过对比可以发现两个awr中，cdwjdd67x27mh 语句执行次数差不多，但是单次逻辑读从800多突变为34000多，增加了40多倍

cdwjdd67x27mh 语句分析
执行计划

17:36:08 sys@Q9DB>select * from table(dbms_xplan.display_awr('cdwjdd67x27mh'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cdwjdd67x27mh
--------------------
select * from ( select          *                               from                            GE_TAOBAO_BILL           o 
                 WHERE 1=1                       and
o.CREATED_TIME >= :1            and o.CREATED_TIME < :2                    
                                and o.REC_SITE_ID = :3                                
             and o.STATUS_ID = :4             
             and o.SERVICE_TYPE = :5 ) where rownum
<= 36000

Plan hash value: 647855111

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |     5 (100)|          |        |
|   1 |  COUNT STOPKEY                       |                           |       |       |            |          |        |
|   2 |   FILTER                             |                           |       |       |            |          |        |
|   3 |    PARTITION RANGE ITERATOR          |                           |     1 |   455 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| GE_TAOBAO_BILL            |     1 |   455 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                | IDX_TAOBAO_BILL_CR_ISDP_S |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------------

SQL_ID cdwjdd67x27mh
--------------------
select * from ( select          *                               from                            GE_TAOBAO_BILL           o 
                 WHERE 1=1                       and
o.CREATED_TIME >= :1            and o.CREATED_TIME < :2                    
                                and o.REC_SITE_ID = :3                                
             and o.STATUS_ID = :4   
             and o.SERVICE_TYPE = :5 ) where rownum
<= 36000

Plan hash value: 2979024279

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                          |       |       |    13 (100)|          |       ||
|   1 |  COUNT STOPKEY                       |                          |       |       |            |          |       ||
|   2 |   FILTER                             |                          |       |       |            |          |       ||
|   3 |    PARTITION RANGE ITERATOR          |                          |     1 |   455 |    13   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| GE_TAOBAO_BILL           |     1 |   455 |    13   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                | IDX_TAOBAO_BILL_CR_REC_S |     4 |       |     9   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------

这里可以发现cdwjdd67x27mh在数据库中有两个执行计划

通过awr数据，看执行计划的变化情况

10:50:29 sys@Q9DB>select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
10:50:30   2  from dba_hist_sqlstat a, dba_hist_snapshot b 
10:50:30   3  where sql_id ='cdwjdd67x27mh' 
10:50:30   4  and a.snap_id = b.snap_id 
10:50:30   5  order by instance_number,begin_interval_time  desc;

INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
--------------- ---------- ------------- --------------- ----------------------------------------
              1      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.753 PM
              1      20719 cdwjdd67x27mh       647855111 12-MAY-14 05.00.12.753 PM
              1      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.702 PM
              1      20719 cdwjdd67x27mh       647855111 12-MAY-14 05.00.12.702 PM
              1      20718 cdwjdd67x27mh       647855111 12-MAY-14 04.00.24.197 PM
              1      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.197 PM
              1      20718 cdwjdd67x27mh       647855111 12-MAY-14 04.00.24.172 PM
              1      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.172 PM
              1      20717 cdwjdd67x27mh       647855111 12-MAY-14 03.11.22.251 PM
              1      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.251 PM
              1      20717 cdwjdd67x27mh       647855111 12-MAY-14 03.11.22.188 PM
              1      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.188 PM
              ………………
              1      20696 cdwjdd67x27mh      2979024279 11-MAY-14 07.00.07.142 PM
              1      20696 cdwjdd67x27mh      2979024279 11-MAY-14 07.00.07.105 PM
              1      20695 cdwjdd67x27mh      2979024279 11-MAY-14 06.00.12.771 PM
              1      20695 cdwjdd67x27mh      2979024279 11-MAY-14 06.00.12.707 PM
              1      20694 cdwjdd67x27mh      2979024279 11-MAY-14 05.00.48.249 PM
              1      20694 cdwjdd67x27mh      2979024279 11-MAY-14 05.00.48.170 PM
              1      20693 cdwjdd67x27mh      2979024279 11-MAY-14 04.00.37.841 PM
              …………
              2      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.753 PM
              2      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.702 PM
              2      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.197 PM
              2      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.172 PM
              2      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.251 PM
              2      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.188 PM

这里可以清晰看到，执行计划在1节点中发生震荡(两个执行计划都有选择)，仔细看两个执行计划，会发现就是查询的时候所使用的index不同而已,继续分析两个index

IDX_TAOBAO_BILL_CR_ISDP_S      CREATED_TIME                            1
IDX_TAOBAO_BILL_CR_ISDP_S      IS_DISPART                              2
IDX_TAOBAO_BILL_CR_ISDP_S      STATUS_ID                               3

IDX_TAOBAO_BILL_CR_REC_S       REC_SITE_ID                             1
IDX_TAOBAO_BILL_CR_REC_S       CREATED_TIME                            2
IDX_TAOBAO_BILL_CR_REC_S       STATUS_ID                               3

分析数据分布情况(其他列省略)

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL
------------------------------ ------------------------------ ------------ ---------
GE_TAOBAO_BILL                 CREATED_TIME                      287080448 05-MAY-14
GE_TAOBAO_BILL                 REC_SITE_ID                           13176 05-MAY-14
GE_TAOBAO_BILL                 IS_DISPART                                2 05-MAY-14
GE_TAOBAO_BILL                 STATUS_ID                                 7 05-MAY-14

这里可以看到数据库在正常的时候使用IDX_TAOBAO_BILL_CR_REC_S没有问题，但是在某些情况下选择使用IDX_TAOBAO_BILL_CR_ISDP_S这个就有问题,导致逻辑读过高,其实对于该sql语句，是因为IDX_TAOBAO_BILL_CR_REC_S 索引不合理导致，如果创建CREATED_TIME,REC_SITE_ID,STATUS_ID，就不会因为传输的值范围不同而使用IDX_TAOBAO_BILL_CR_ISDP_S的情况。针对该情况，因为表非常大，短时间内无法修改index，只能考虑使用 sql profile固定执行计划

sql profile固定计划

SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh

Parameter 1:
SQL_ID (required)



PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2979024279        .011
      647855111       5.164

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2979024279

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "cdwjdd67x27mh"
PLAN_HASH_VALUE: "2979024279"


Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
on TARGET system in order to create a custom SQL Profile
with plan 2979024279 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select * from ( select
  7                  *
  8  
  9                  from
 10  
 11                  GE_TAOBAO_BILL
 12  
 13                  o
 14  
 15                   WHERE 1=1
 16  
 17  
 18  
 19  
 20  
 21  
 22  
 23  
 24  
 25  
 26  
 27                                  and o.CREATED_TIME >= :1
 28  
 29  
 30                                  and o.CREATED_TIME < :2
 31  
 32  
 33                                  and o.REC_SITE_ID = :3
 34  
 35  
 36  
 37  
 38  
 39  
 40  
 41  
 42  
 43  
 44  
 45  
 46  
 47  
 48  
 49                                  and o.STATUS_ID = :4
 50  
 51  
 52  
 53  
 54  
 55  
 56  
 57  
 58  
 59  
 60  
 61  
 62  
 63  
 64                                  and o.SERVICE_TYPE = :5 ) where rownum <= 36000
 65  ]';
 66  h := SYS.SQLPROF_ATTR(
 67  q'[BEGIN_OUTLINE_DATA]',
 68  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 69  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
 70  q'[DB_VERSION('11.2.0.3')]',
 71  q'[FIRST_ROWS]',
 72  q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',
 73  q'[MERGE(@"SEL$2")]',
 74  q'[OUTLINE(@"SEL$1")]',
 75  q'[OUTLINE(@"SEL$2")]',
 76  q'[INDEX_RS_ASC(@"SEL$F5BB74E1" "O"@"SEL$2" ("GE_TAOBAO_BILL"."REC_SITE_ID" "GE_TAOBAO_BILL"."CREATED_TIME" "GE_TAOBAO_BILL"."STATUS_ID"))]',
 77  q'[END_OUTLINE_DATA]');
 78  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 79  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 80  sql_text    => sql_txt,
 81  profile     => h,
 82  name        => 'coe_cdwjdd67x27mh_2979024279',
 83  description => 'coe cdwjdd67x27mh 2979024279 '||:signature||'',
 84  category    => 'DEFAULT',
 85  validate    => TRUE,
 86  replace     => TRUE,
 87  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 88  END;
 89  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
 18414135509058398362


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_cdwjdd67x27mh_2979024279 completed

固定执行计划后，系统负载恢复正常
load恢复正常，单个进程占用cpu 也正常

top - 18:25:29 up 123 days,  4:25,  3 users,  load average: 17.59, 16.72, 16.10
Tasks: 1559 total,   6 running, 1551 sleeping,   0 stopped,   2 zombie
Cpu(s):  6.2%us,  1.2%sy,  0.0%ni, 84.3%id,  7.4%wa,  0.1%hi,  0.9%si,  0.0%st
Mem:  264253752k total, 262395300k used,  1858452k free,   305928k buffers
Swap: 33554424k total,   467420k used, 33087004k free, 66811412k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                  
 3010 oracle    15   0  150g  26m  21m S 23.6  0.0   0:08.60 oracleq9db2 (LOCAL=NO)   
26936 oracle    15   0  150g  33m  28m S 22.9  0.0   0:12.53 oracleq9db2 (LOCAL=NO)   
 9691 oracle    15   0  150g  34m  28m D 21.0  0.0   0:48.73 oracleq9db2 (LOCAL=NO)   
 9694 oracle    15   0  150g  34m  28m S 17.4  0.0   0:45.74 oracleq9db2 (LOCAL=NO)   
14366 oracle    15   0  150g  33m  28m R 17.4  0.0   0:25.11 oracleq9db2 (LOCAL=NO)   
 2471 oracle    -2   0  150g 180m  37m S 16.7  0.1  10795:55 ora_lms3_q9db2           
 2463 oracle    -2   0  150g 180m  37m S 15.7  0.1  10648:38 ora_lms1_q9db2           
 2459 oracle    -2   0  150g 180m  37m S 14.8  0.1  10726:42 ora_lms0_q9db2           
 2467 oracle    -2   0  150g 180m  36m S 14.8  0.1  10980:33 ora_lms2_q9db2           
13324 oracle    15   0  150g  31m  26m S 14.1  0.0   0:22.75 oracleq9db2 (LOCAL=NO)   
16740 oracle    15   0  150g  34m  28m R 13.4  0.0   0:43.85 oracleq9db2 (LOCAL=NO)   
 1908 oracle    15   0  150g  27m  22m S 11.8  0.0   0:03.95 oracleq9db2 (LOCAL=NO)   
 9689 oracle    15   0  150g  33m  27m S 11.8  0.0   0:46.01 oracleq9db2 (LOCAL=NO)   
19410 oracle    15   0  150g  31m  26m S 11.8  0.0   0:43.18 oracleq9db2 (LOCAL=NO)   
14102 oracle    15   0  150g  31m  25m S 11.5  0.0   2:17.23 oracleq9db2 (LOCAL=NO)   
 1914 oracle    15   0  150g  29m  24m S 11.1  0.0   0:04.77 oracleq9db2 (LOCAL=NO)   
31106 oracle    15   0  150g  34m  28m S  9.8  0.0   1:24.85 oracleq9db2 (LOCAL=NO)   
31139 oracle    15   0  150g  30m  24m S  9.8  0.0   1:21.75 oracleq9db2 (LOCAL=NO)   
 2498 oracle    15   0  150g  42m  35m S  7.9  0.0   3838:43 ora_lgwr_q9db2           
28108 oracle    15   0  150g  36m  29m S  7.9  0.0   0:18.19 oracleq9db2 (LOCAL=NO)   
 2392 oracle    15   0  150g  35m  17m S  7.5  0.0   5304:57 ora_lmd0_q9db2
发表在 Oracle性能优化 | 标签为 , | 评论关闭

exp dmp文件损坏恢复

发表于 2014 年 4 月 29 日 由 惜分飞

在有些时候,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

发表在 非常规恢复 | 标签为 , , | 2 条评论