oracle:exp/imp和expdp/impdp数据备份实例

最近在做oracle的数据备份任务，查了一下，有冷备份、热备份，综合考虑了一下，觉得exp/imp和expdp/impdp比较适合，一个是数据量也不算太大（估计最多也就上百万条），二实时要求低。

exp/imp和expdp/impdp原理不一样，exp/imp导出导入是通过sql语句导出插入，效率慢；
而expdp/impdp是oracle10g以后新增的特性，采用自带的API接口，大大提高效率（约2-5倍）。

一、 exp/imp实例：

exp username/pwd@orcl file=/home/test_ora_backup.dmp log=/home/test_ora_backup.log owner=username

imp username/pwd@orcl file=/home/test_ora_backup.dmp fromuser=username touser=username ignore=y

ignore=y：表示如果当前表存在就不创建表结构直接导入数据

二、 expdp/impdp实例：
1.back.sh

sqlplus SYSTEM/ORACLE @./back.sql
BACK_DIR=`(sqlplus -s 'username/pwd@orcl'<<EOF
  set heading off
  SELECT directory_path FROM dba_directories where directory_name='BACKUP_PCMP';
EOF
)`
if [ -f ${BACK_DIR}/test_ora_backup.dmp ] ; then
  rm -rf ${BACK_DIR}/test_ora_backup.dmp
fi
expdp username/pwd@orcl dumpfile=test_ora_backup.dmp directory=BACKUP_PCMP
echo "Backup database data finished."

2.back.sql

--Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR
BEGIN execute immediate 'DROP DIRECTORY BACKUP_PCMP'; 
	EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE DIRECTORY BACKUP_PCMP AS '/home/oracle/back';
GRANT read, write ON DIRECTORY BACKUP_PCMP TO PCRFBEIJING;
commit;
exit;

3.restore.sh

impdp username/pwd dumpfile=test_ora_backup.dmp directory=BACKUP_PCMP table_exists_action=APPEND

但是实际impdp操作的时候，去报错：

ORA-31693: Table data object "username"."AREA" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (username.PK_AREA) violated

到官网查了一下，是oracle的hug（4383811），在 10.2.0.3才解决。本系统为10.2.0.1。

具体exp/imp，expdp/impdp参数选项可输入

expdp -help

参看.

也有很多网友收集了可查看：
exp/imp
http://dbajun.iteye.com/blog/215706
expdp/impdp
http://hi.baidu.com/edeed/blog/item/9799a3ccc4a29f1401e928a0.html
http://space.itpub.net/35489/viewspace-614126