expdp備份緩慢問題分析，expdp備份分析

expdp備份緩慢問題分析，expdp備份分析

--匯出問題分析
--兩個時間語句分析，該語句只匯出4,059,292  資料，10分鐘後資料沒有繼續匯出
             Snap Id  Snap Time  Sessions  Cursors/Session
Begin Snap:  39396  13-Jul-14 18:30:32  558  1.7
End Snap:    39407  13-Jul-14 20:20:07  556  1.7
Elapsed:     109.58 (mins)     
DB Time:     16.76 (mins) 

           Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7
End Snap:   39398 13-Jul-14 18:50:37 557 1.7
Elapsed:   20.09 (mins)   
DB Time:   3.51 (mins) 

            Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7
End Snap:   39417 13-Jul-14 22:00:18 559 1.7
Elapsed:    209.77 (mins)    
DB Time:    27.15 (mins) 

            Begin Snap Time Sessions Cursors/Session
            39396 13-Jul-14 18:30:32 558 1.7
End Snap:   39397 13-Jul-14 18:40:34 557 1.7
Elapsed:    10.03 (mins)    
DB Time:    1.64 (mins) 

 

--語句沒執行完畢
# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
1 2193842017            7,218            0               39397 39397

 

--問題時候的匯出資源使用
Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 7,218   0.72
CPU Time (ms) 3,035   0.35
Executions 0   
Buffer Gets 139,102   0.34
Disk Reads 0   0.00
Parse Calls 0   0.00

Rows 4,059,292    -------------------------其實我擷取兩個 awrsqlrpt報告分析，兩個時間，也就是18點50 以後，沒有資料匯出 
User I/O Wait Time (ms) 0   
Cluster Wait Time (ms) 0   
Application Wait Time (ms) 0   
Concurrency Wait Time (ms) 0   
Invalidations 0   
Version Count 2   
Sharable Mem(KB) 44 

 

--22點匯出時候資源使用
 Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39421 13-Jul-14 22:40:22 557 1.7
End Snap: 39422 13-Jul-14 22:50:23 558 1.7
Elapsed:   10.02 (mins)    
DB Time:   1.38 (mins)

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 18,405 18,404.69 22.24
CPU Time (ms) 7,555 7,554.87 10.43
Executions 1   
Buffer Gets 354,583 354,583.00 35.03
Disk Reads 0 0.00 0.00
Parse Calls 1 1.00 0.05

Rows 10,555,042 10,555,042.00    ---一共匯出怎麼多資料（可以正常匯出）
User I/O Wait Time (ms) 0   
Cluster Wait Time (ms) 0   
Application Wait Time (ms) 0   
Concurrency Wait Time (ms) 0   
Invalidations 0   
Version Count 2   
Sharable Mem(KB) 44 

--以上問題給予語句分析：
SQL Id SQL   Text
bcjy9a9sp1uw9 SELECT /*+NESTED_TABLE_GET_REFS+*/ "KDTA_HIS"."H_ACK_DIVIDEND".* FROM "KDTA_HIS"."H_ACK_DIVIDEND"

--分析：匯出發生意外，中止或者等待，或者LMON 引起latch
--會話等待事件分析：

SQL> select substrb(session_id,1,6)||'.'||substrb(session_serial#,1,4),
  2         substrb(event,1,15),
  3         BLOCKING_SESSION,
  4         to_char(SAMPLE_TIME, 'yyyymmdd hh24miss'),
  5         INSTANCE_NUMBER,
  6         sql_id,
  7         (select username
  8            from dba_users b
  9           where b.user_id = a.USER_ID
 10             and rownum = 1) username,
 11         substrb(program,1,10)

 12    from DBA_HIST_ACTIVE_SESS_HISTORY a
 13   where (SAMPLE_TIME >=
 14         to_date('2014-07-13 01:00:00', 'yyyy-mm-dd hh24:mi:ss') and
 15         SAMPLE_TIME <=
 16         to_date('2014-07-14 01:10:00', 'yyyy-mm-dd hh24:mi:ss'))
 17     and a.module like '%exp%'
 18   order by session_id,SAMPLE_TIME;

  SUBSTRB(SES SUBSTRB(EVENT,1 BLOCKING_SESSION TO_CHAR(SAMPLE_ INSTANCE_NUMBER SQL_ID        USERNAME                       SUBSTRB(PR
----------- --------------- ---------------- --------------- --------------- ------------- ------------------------------ ----------

7638.4126                                    20140713 184020               1 88jm6j85t5b8z KDTA_HIS                       exp.exe
7638.4126   latch free                   529 20140713 184151               1               KDTA_HIS                       exp.exe--------
7638.4126                                    20140713 184643               1 g4kubvga4gnxc KDTA_HIS                       exp.exe
7638.4126   SQL*Net more da                  20140713 185145               1 dvjmxz20wf8xz KDTA_HIS                       exp.exe

--529 會話分析
SQL> select substrb(session_id,1,6)||'.'||substrb(session_serial#,1,4),
  2         substrb(event,1,15),
  3         BLOCKING_SESSION,
  4         to_char(SAMPLE_TIME, 'yyyymmdd hh24miss'),
  5         INSTANCE_NUMBER,
  6         sql_id,
  7         substrb(program,1,30)
  8    from DBA_HIST_ACTIVE_SESS_HISTORY a

  9   where (SAMPLE_TIME >=
 10         to_date('2014-07-13 18:30:00', 'yyyy-mm-dd hh24:mi:ss') and
 11         SAMPLE_TIME <=
 12         to_date('2014-07-13 18:45:00', 'yyyy-mm-dd hh24:mi:ss'))
 13     and a.session_id=529
 14   order by session_id,SAMPLE_TIME;

SUBSTRB(SES SUBSTRB(EVENT,1 BLOCKING_SESSION TO_CHAR(SAMPLE_ INSTANCE_NUMBER SQL_ID        SUBSTRB(PROGRAM,1,30)
----------- --------------- ---------------- --------------- --------------- ------------- ------------------------------
529.1                                        20140713 183204               2              oracle@KXYEB02 (LMON)
529.1                                        20140713 183215               2              oracle@KXYEB02 (LMON)

529.1                                        20140713 183609               1              oracle@KXYEB01 (LMON)
529.1                                        20140713 184151               1              oracle@KXYEB01 (LMON)------------
529.1                                        20140713 184218               2              oracle@KXYEB02 (LMON)
529.1                                        20140713 184332               1              oracle@KXYEB01 (LMON)

--dump systemstate 已經無法分析當時資料
--分析awr報告，沒有發現latch 問題

-- 下次遇到這個問題的是及時進行一下操作
--1 登入資料庫
sqlplus /nolog
conn / as sysdba
oradebug setmypid
oradebug dump systemstate 10
oradebug tracefile_name

 

--2 可以的話再執行一次
1  擷取process id
ps -ef|grep expdp
id
2 10046分析

SQL> select to_char(a.last_analyzed,'yyyymmdd hh24miss') from dba_tables a where table_name='H_ACK_DIVIDEND';

TO_CHAR(A.LAST_
---------------
20140713 181130

SQL>

 

異常中止或者lmon引起等待掛起，根源原因分析不出來!

 

 

 

問oracle impdp/expdp恢複備份的問題

參數exclude:
exclude=object_type[:name_clause][,...]
比如：impdp user/pass DIRECTORY=dmpdir DUMPFILE=user.dmp exclude=PROCEDURE:"in('p1', 'p2')",TABLE_STATISTICS
 
Oracle 進行expdp資料備份時怎讓檔案名稱自動包含日期資訊

用dos命令列的吧

1. 把機器上的日期格式調整為固定格式，比如：2012-09-01
保證dos命令的date的格式

2. "%date:~0,4%" 表示取日期中的4位年份（針對上面的格式）
"%date:~5,2%" 取2位月份
"%date:~8,2%" 取2位日

3. 用exp樣本：
exp user/pwd@orcl file="e:\orcl%date:~0,4%%date:~5,2%%date:~8,2%.dmp"