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.iyunv.com/blog/215706
expdp/impdp
http://hi.baidu.com/edeed/blog/item/9799a3ccc4a29f1401e928a0.html
http://space.itpub.net/35489/viewspace-614126