ORACLE RAC用数据泵导出数据以及卡住问题

rac用数据泵导出数据

注意CLUSTER=N 表示仅允许使用一个数据库实例上的进程执行导出操作。

需要在指定节点创建DIRECTORY

 
create directory mydata as '逻辑目录路径'; 
例如： 
create directory mydata as '/data/oracle/oradata/mydata';

#/bin/sh
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export NLS_LANG=american_america.zhs16gbk
#export DATA_DIR=/oracle/backupqqasda:
now=$(date +%Y%m%d%H)
dmpfile=NC$now.dmp
logtfile=NC$now.log
echo start exp $dmpfile ...
#指定节点expdp dba/****@10.120.11.21:1521/racdb schemas=WBI dumpfile=data_1227_%U.dmp logfile=log_1227.log DIRECTORY=data_dir PARALLEL=4  CLUSTER=N compression=all ACCESS_METHOD=DIRECT_PATH
/u01/app/oracle/product/11.2.0/dbhome_1/bin/expdp expdp dba/****@10.120.11.25:1521/racdb schemas=WBI DUMPFILE=$dmpfile logfile=$logtfile DIRECTORY=data_dir PARALLEL=4  CLUSTER=N compression=all
echo "Delete the file bakup before 1 days..."
find /u01/app/backup  -mtime +0 -name "*.dmp" -type f -exec rm -rf {} \;
find /u01/app/backup  -mtime +0 -name "*.log" -type f -exec rm -rf {} \;
#find /u01/app/backup/ -mtime +1 -name "*.dmp" -exec rm -rf '{}' \;
#find /u01/app/backup/ -mtime +1 -name "*.log" -exec rm -rf '{}' \;
echo "Delete the file bakup successfully."#/bin/sh

 expdp zwdba/157946392aA@10.120.11.21:1521/racdb schemas=WBI dumpfile=data_1227_%U.dmp logfile=log_1227.log DIRECTORY=data_dir PARALLEL=2  CLUSTER=N compression=all ACCESS_METHOD=DIRECT_PATH ESTIMATE=STATISTICS

# ESTIMATE=STATISTICS 

指定估算被导出表所占用磁盘空间分方法.默认值是BLOCKS

EXTIMATE={BLOCKS | STATISTICS}

如果BLOCKS卡住：Estimate in progress using BLOCKS method

方法一 重新统计

exec dbms_stats.gather_schema_stats(OWNNAME=>'WBI',ESTIMATE_PERCENT=>10,DEGREE=>4,cascade=>true);

方法二 用STATISTICS

再卡住：Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

查看是否锁表

select s.EVENT,s.MODULE,s.PROGRAM from gv$session s where s.PROGRAM like '%DW%';

# kill 进程

 SELECT 'alter system kill session '''|| bs.sid||',' ||bs.serial#||''';' ,
bs.INST_ID,        bs.username "Blocking User",          bs.username "DB User",          bs.SID "SID",          bs.serial# "Serial#",          bs.sql_address "address",          bs.sql_hash_value "Sql hash",          bs.program "Blocking App",             
bs.machine "Blocking Machine",           bs.osuser "Blocking OS User",           bs.serial# "Serial#",                                ws.username "Waiting User",          ws.SID "WSID",          ws.program "Waiting App",          ws.machine "Waiting Machine",          ws.osuser "Waiting OS User",          ws.serial# "WSerial#",          wk.TYPE lock_type,          hk.lmode mode_held,          wk.request mode_requested,          TO_CHAR(hk.id1) lock_id1,          TO_CHAR(hk.id2) lock_id2,          hk.BLOCK blocking_others    
FROM gv$lock hk, gv$session bs, gv$lock wk, gv$session ws    
WHERE hk.BLOCK = 1      AND hk.lmode != 0      AND hk.lmode != 1      AND wk.request != 0      AND wk.TYPE(+) = hk.TYPE      AND wk.id1(+) = hk.id1      AND wk.id2(+) = hk.id2  
    AND hk.SID = bs.SID(+)      AND wk.SID = ws.SID(+)      AND (bs.username IS NOT NULL)      AND (bs.username <> 'SYSTEM')      AND (bs.username <> 'SYS')    ORDER BY 1;