Analysis of slow expdp backup and expdp backup

Analysis of slow expdp backup and expdp backup

-- Export Problem Analysis
-- Two time Statement Analysis: This statement exports only 4,059,292 of the Data. After 10 minutes, the data does not continue to be exported.
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39396 13-jul14 18:30:32 558 1.7
End Snap: 39407 13-jul14 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-jul14 18:30:32 558 1.7
End Snap: 39398 13-jul14 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-jul14 18:30:32 558 1.7
End Snap: 39417 13-jul14 22:00:18 559 1.7
Elapsed: 209.77 (mins)
DB Time: 27.15 (mins)

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

 

-- The statement has not been executed.
# Plan Hash Value Total Elapsed Time (MS) Executions 1st Capture Snap ID Last Capture Snap ID
1 2193842017 7,218 0 39397 39397

 

-- Export resource usage in case of problems
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 cballs 0 0.00

Rows 4,059,292 --------------------------- in fact, I have obtained two awrsqlrpt reports for analysis. There are no data exported after.
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

 

-- Resource usage during export
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39421 13-jul14 22:40:22 557 1.7
End Snap: 39422 13-jul14 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 cballs 1 1.00 0.05

Rows 10,555,042 10,555,042.00 --- How to export a total of multiple data records (which can be exported normally)
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

-- Analyze the preceding statements:
SQL Id SQL Text
Bcjy9a9sp1uw9 SELECT/* + NESTED_TABLE_GET_REFS + */"KDTA_HIS". "H_ACK_DIVIDEND". * FROM "KDTA_HIS". "H_ACK_DIVIDEND"

-- Analysis: if an export accident occurs, stop or wait, or LMON causes latch.
-- Session wait event analysis:

SQL> select substrb (session_id,) | '.' | substrb (session_serial ),
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
13 where (SAMPLE_TIME> =
14 to_date ('2017-07-13 01:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and
15 SAMPLE_TIME <=
16 to_date ('2017-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 session Analysis
SQL> select substrb (session_id,) | '.' | substrb (session_serial ),
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

9 where (SAMPLE_TIME> =
10 to_date ('2017-07-13 18:30:00 ', 'yyyy-mm-dd hh24: mi: ss') and
11 SAMPLE_TIME <=
12 to_date ('2017-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 cannot analyze the current data
-- The awr report is analyzed and no latch problems are found.

-- The next time you encounter this problem, perform the operation in a timely manner.
-- 1 log on to the database
Sqlplus/nolog
Conn/as sysdba
Oradebug setmypid
Oradebug dump systemstate 10
Oradebug tracefile_name

 

-- 2. Execute the command again.
1. Obtain the process id.
Ps-ef | grep expdp
Id
2 10046 Analysis

SQL> select to_char (a. last_analyzed, 'yyyymmdd hh24mis') from dba_tables a where table_name = 'H _ ACK_DIVIDEND ';

TO_CHAR (A. LAST _
---------------
20140713 181130

SQL>

 

An exception is aborted or lmon causes a pending suspension. The root cause cannot be analyzed!

 

 

 

Oracle impdp/expdp Backup Recovery Problems

Parameter exclude:
Exclude = object_type [: name_clause] [,...]
For example: impdp user/pass DIRECTORY = dmpdir DUMPFILE = user. dmp exclude = PROCEDURE: "in ('p1', 'p2')", TABLE_STATISTICS

How to make the file name automatically contain date information during Oracle expdp data backup?

Run the doscommand.

1. Adjust the date format on the machine to a fixed format, for example, 2012-09-01
Ensure the date format of the doscommand

2. "% date :~ 0, 4% "indicates taking the four-digit year from the date (for the above format)
"% Date :~ 5, 2% "two-digit month
"% Date :~ 8, 2% "2-digit day

3. example with exp:
Exp user/pwd @ orcl file = "e: \ orcl % date :~ 0, 4% % date :~ 5, 2% % date :~ 8, 2%. dmp"