Oracle Data Pump使用范例及部分注意事项（expdp/impdp）

最近系统要“缩容”，原因我不想多说，非常麻烦的一件事情，因为要把数据提出来压缩、存放。

和操作系统无关，主要系Oracle的数据。

Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

另外，系统有进行了6个节点的RAC集群，每个表都按照月份或者日期进行了分区。

 

一、前置工作：

1. 在系统中建立生成dmp文件（导出的数据文件）的目录（一般情况下都需要root权限，这个自由发挥了^_^）：

root@bidb04# mkdir -p /data/oracle_backup
root@bidb04# chown oracle:oinstall /data/oracle_backup

另外提个醒，无论你导出导入的时候用的是什么用户和密码登陆，数据库都是用（linux系统中的）oracle用户导出文件的，所以这个目录一定要让（linux系统中的）oracle用户有写权限。

 

2. 在oracle中定义生成dmp文件（导出的数据文件）的目录：

使用sqlplus / as sysdba进入sqlplus：

sql> grant create any directory to scott;         --这一步也可以不用。
sql> create or replace directory oracle_backup as '/data/oracle_backup';        --有了上面那一步，目录也可以使用scott用户建立，当然直接就这么建立就完了。
sql> grant read, write on directory oracle_backup to scott;                --赋予目录访问权限。
sql> create or replace directory DATA_PUMP_DIR as '/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log/';        --这个貌似我是没有这么建立的，系统自己有了。
sql> grant read, write on directory DATA_PUMP_DIR to scott;                --同上备注。
sql> grant EXP_FULL_DATABASE to scott;         --导出权限
sql> grant IMP_FULL_DATABASE to scott;          --导入权限

 

 

二、Data Pump导出（expdp）:

expdp scott/**********@bidb4 TABLES=KR.TR_DATA_APP_2A3R_D:P20130101 DIRECTORY=ORACLE_BACKUP DUMPFILE=KR.TR_DATA_APP_2A3R_D0101%u.dmp LOGFILE=DATA_PUMP_DIR:KR.TR_DATA_APP_2A3R_D0101.log CONTENT=data_only CLUSTER=N PARALLEL=8 FILESIZE=2G ENCRYPTION=data_only ENCRYPTION_PASSWORD=password

备注：

#1.   scott/**********@bidb4   （红色部分）用户名密码和SID，这个就和sqlplus登陆的一致了。
#2.   TABLES          （绿色部分）表名(表名称:分区)
#3.   DIRECTORY （蓝色部分）要生成导出文件的目录名--在oracle dba_directories中定义（定义方法见前文）
#4.   DUMPFILE     （黄色部分）生成的导出文件名%u参数表示对个文件的后缀，与parallel、filesize一起使用
#5.   LOGFILE        （紫色部分）日志记录文件名称（log目录:log名称，其中log目录在oracle dba_directories中定义）（定义方法见前文）
#6.   CONTENT     （橙色部分）要包含的内容
#7.   CLUSTER      （灰色部分）是否使用集群，默认是Y，这里需要N（可以看看后记）
#8.   PARALLEL     （灰色部分）并行处理
#9.   FILESIZE        （灰色部分）单个文件最大大小
#10. ENCRYPTION （灰色部分）加密选项1，指定加密内容（附录中有描述参数可选内容）
#11. ENCRYPTION_PASSWORD  （灰色部分）加密选项2，指定加密密钥

 

三、Data Pump导入（impdp）:

impdp scott/**********@bidb4  TABLES=KR.TR_DATA_APP_2A3R_D DIRECTORY=ORACLE_BACKUP DUMPFILE=KR.TR_DATA_APP_2A3R_D0101%u.dmp LOGFILE=DATA_PUMP_DIR:IMP_KR.TR_DATA_APP_2A3R_D0101.log CONTENT=data_only CLUSTER=N PARALLEL=8 FILESIZE=2G ENCRYPTION_PASSWORD=password

 

备注：

基本和expdp的参数配置一致，有区别主要在第2个和第10个，另外，导出的日志名最好稍微修改一下，不然就覆盖掉了。

#2.   TABLES          （绿色部分）表名（只要表名就好了，不用加上分区，因为导入的数据里面已经包含了分区信息）
#10. ENCRYPTION （不再需要了）

 

 

后记：

1. 这个有一个很容易被忽略的参数：CLUSTER，这个表示使用集群导出/导入，由于系统是6机器做成的集群，导出的时候会默认使用集群导出，如果不指定N，则无论如何都报错。这里给诸位参考一下，如果系统没有做集群的话，这个参数就忽略吧。

2. 导出了dmp文件之后，本来想压缩一下传输到别的机器上去存放的，发现压缩前整整2GB的文件，zip -9 压缩之后变成2.1GB，不知道神马情况，所以果断放弃压缩的念头。

3. 如果有错误信息，贴出来看看能不能帮大家解决。

4. 上面的expdp语句是用在linux层面的，写个shell就能批量解决了：

用法：compress.sh /home/oracle/tmp/table_list.conf    #最好是跟绝对路径了

#!/bin/sh
#shell name : compress.sh
#conf文件格式：<1表优先级>|<2表所属用户>|<3表名>|<4分区名>
if [ -z $1 ] ; then
	   echo -e "usage:$0 /path/conf ERROR! \033[7;5m Warning \033[0m"
	   exit 1
fi

#定义参数
_log_name=`echo $1|awk -F'/' '{print $NF}'`
shell_log="/home/oracle/tmp/$_log_name.log"
tmp_ftp_log="/home/oracle/tmp/tmp_ftp.log"
exp_dir="/data/oracle_backup"
_str_pwd="/work2/oracle_backup/"    #远程机器的存放目录
_str_lpwd=$exp_dir         #lpwd这个命令大家都懂的。
ftp_ip="192.168.1.1"       #远程机器的ip
USER="sam"                 #远程主机ftp用户名
PASSWORD='password\#123'   #ftp密码，如果有特殊字符，则需要如实例般转义。

#程序开始
echo ---`date`--- > $shell_log
echo ---------------BEGIN-------------- >> $shell_log

while read line  
do
	#获取并格式化参数
	user_name=`echo $line|awk -F'|' '{print $2}'`
	table_name=`echo $line|awk -F'|' '{print $3}'`
	list_name=`echo $line|awk -F'|' '{print $4}'`
	
	tables_name_format="${user_name}.${table_name}:${list_name}"
	file_name_format="${user_name}.${table_name}.${list_name}"
	dmp_log="/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log/${file_name_format}.log"
	
	expdp_exc="expdp scott/scott@bidb4 tables=${tables_name_format} DIRECTORY=ORACLE_BACKUP DUMPFILE=${file_name_format}%u.dmp LOGFILE=DATA_PUMP_DIR:${file_name_format}.log content=data_only cluster=N PARALLEL=8 filesize=2G encryption=data_only encryption_password=password"
	list_file_exc="ls -l $exp_dir"
	find_file_exc="ls $exp_dir"

	echo **ExpdpBegin`date`**
	echo **ExpdpBegin`date`** >> $shell_log
	echo $expdp_exc
	echo $expdp_exc >> $shell_log
	`$expdp_exc`
	echo Log file is: $dmp_log
	echo Log file is: $dmp_log >> $shell_log
	echo **ExpdpEnd`date`**
	echo **ExpdpEnd`date`** >> $shell_log
	sleep 2
	
	#检查是否成功
	pa_count=`cat ${dmp_log}|grep Job|grep successfully|wc -l`
	if [ $pa_count -eq 0 ] ; then
	   echo -e "chk_expdp ${file_name_format} ERROR \033[7;5m Warning \033[0m"
	   echo "chk_expdp ${file_name_format} ERROR" >> $shell_log
	   exit 1
	else
	   echo "chk_expdp ${file_name_format} SUCC"
	   echo "chk_expdp ${file_name_format} SUCC" >> $shell_log
	fi
	
	#压缩导出文件           #实测，压缩后文件增大，所以，果断放弃压缩。
	#echo **CompressBegin`date`**
	#echo **CompressBegin`date`** >> $shell_log
	#$list_file_exc|grep .dmp|grep ${file_name_format} >> $shell_log
	#_file_list=`$find_file_exc|grep .dmp|grep ${file_name_format}`
	#for files in $_file_list
	#do
	#	echo "gzip -9 $exp_dir/$files"
	#	echo "gzip -9 $exp_dir/$files" >> $shell_log
	#	`gzip -9 $exp_dir/$files` >> $shell_log
	#done
	#$list_file_exc|grep .dmp.gz|grep ${file_name_format} >> $shell_log
	#echo **CompressEnd`date`** >> $shell_log
	#sleep 2
	
	#ftp上传
	echo **FtpputBegin`date`**
	echo **FtpputBegin`date`** >> $shell_log
	#_file_list=`$find_file_exc|grep .dmp.gz|grep ${file_name_format}`      ##如果有压缩，就用这一行，没有压缩用下面那两行
	$list_file_exc|grep .dmp|grep ${file_name_format} >> $shell_log
	_file_list=`$find_file_exc|grep .dmp|grep ${file_name_format}`
	for files in $_file_list
	do
		echo $files
		echo "ftp put file: $_str_lpwd/$files"
ftp -v -n ${ftp_ip}<<! > $tmp_ftp_log      ##切记啊，这里一定要顶行写，每行开头不能有任何空格，不然这个shell怎么都报错。
user ${USER} ${PASSWORD}
prompt
cd $_str_pwd
lcd $_str_lpwd
mput $files
bye
!
		cat $tmp_ftp_log >> $shell_log
		pa_count=`cat ${tmp_ftp_log}|grep "receive OK"|wc -l`
		if [ $pa_count -eq 0 ] ; then
		   echo -e "chk_ftpput ${file_name_format} ERROR \033[7;5m Warning \033[0m"
		   echo "chk_ftpput ${file_name_format} ERROR" >> $shell_log
		   exit 1
		else
		   echo "chk_ftpput ${file_name_format} SUCC"
		   echo "chk_ftpput ${file_name_format} SUCC" >> $shell_log
		fi
		echo "rm $_str_lpwd/$files" >> $shell_log
		`rm $_str_lpwd/$files`
	done
	echo **FtpputEnd`date`** >> $shell_log
	
	echo ---------END ONE TABLE---------- >> $shell_log
	echo "" >> $shell_log
	
done < $1
echo ---------------END-------------- >> $shell_log

 

 

附：

1. ENCRYPTION 有效关键字值为: ALL, DATA_ONLY, METADATA_ONLY,ENCRYPTED_COLUMNS_ONLY 或 NONE。

2. 参考资料：

http://space.itpub.net/519536/viewspace-709931

http://www.cnblogs.com/lanzi/archive/2011/01/06/1927731.html