备份Oracle数据库业务用户数据之shell脚本

Posted by Harid2013 - Jun - 16 留个言

在管理生产、测试系统的过程中,我们肯定会遇到经常需要迁移数据库的情况。迁移数据库有许多种方式,其中数据泵expdp/impdp的方式应该是使用最频繁的,本文即基于该种数据泵方式,实现对数据库中的业务用户按用户导出的目的。

脚本需要获取数据库实例sys用户的密码以及各个业务用户的用户密码。为了在新环境中导入完成之后能够改尽可能少的东西就让数据库、应用可以使用,我在备份的脚本中将获取到的各个业务用户的用户密码作为文件保存下来了(passwd.out),这样在新环境中导入成功之后就无需更改应用中连接串的数据库用户密码。

另外,脚本还会在指定的备份目录下面创建tablespace.out、user_quota.out、user_tablespace.out三个文件,主要是保存现有数据库中一些用户与表空间的关系信息,以保证在新环境导入数据库的时候能够在自动创建好数据库后自动创建相关的表空间和业务用户,做到让我们尽可能少做事情。我们到时需要做的仅是将备份目录下面的所有备份文件拷贝至新环境的某个目录中。

下面是具体的脚本内容:

#!/bin/bash
####
##
## Author: http://www.timepoet.com
## Date: 2013-05-17
####

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:$PATH
export PATH

### Check user id ###
if [ `id -u` -ne `cat /etc/passwd | grep "oracle" | cut -d ':' -f3` ];then
	echo "You must execute this script as oracle account. Exit now."
	exit 1
fi
### End ###

function get_char(){
	echo ""
	echo "Press any key to continue, CTRL+C to quit..."
	SAVEDSTTY=`stty -g`
	stty -echo
	stty cbreak
	dd if=/dev/tty bs=1 count=1 2> /dev/null
	stty -raw
	stty echo
	stty $SAVEDSTTY
}

### Read the Oracle SID ###
read -p "Pls input the SID name which you wanna backup: " SID
if [ "X${SID}" == "X" ];then
	echo "You must give a database name. Exit now."
	exit 1
fi
### End ###

### Read a path for storing dump files. ###
read -p "Pls input the backup path [Default: ${ORACLE_BASE}/oradata/${SID}/Backup]: " BackDir
if [ "X$BackDir" == "X" ];then
	while [ ! -d "${ORACLE_BASE}/oradata/${SID}/Backup" ]
	do
		echo "Create backup directory..."
		mkdir -p ${ORACLE_BASE}/oradata/${SID}/Backup
		if [ `echo $?` -gt 0 ];then
			clear
			echo "Create backup directory ${BackDir} failed. Pls create it first."
			get_char
		fi
	done
	BackDir="${ORACLE_BASE}/oradata/${SID}/Backup"
else
	while [ ! -d $BackDir ]
	do
		mkdir -p $BackDir
		if [ `echo $?` -gt 0 ];then
			clear
			echo "Create backup directory ${BackDir} failed. Pls create it first."
			get_char
		fi
	done
fi
echo "Backup directory is $BackDir"
### End ###

### Read sys password ###
read -p "Enter sys's password: " sysPasswd
if [ "X${sysPasswd}" == "X" ];then
	echo "You must give the sys's password. Exit now."
	exit 1
else
	sqlplus sys/${sysPasswd}@${SID} as sysdba >>EOF
	exit;
	exit;
	exit;
	exit;
EOF
	while [ `echo $?` -gt 0 ]
	do
		echo ""
		echo "The password of sys account is wrong."
		read -p "Enter sys's password: " sysPasswd
		sqlplus sys/${sysPasswd}@${SID} as sysdba < ${workplace}/user.out
rm -rf ${workplace}/user_t.out
sed -i '/^$/d' ${workplace}/user.out
userNum=`cat ${workplace}/user.out | wc -l`
count=0
if [ -e ${BackDir}/passwd.out ];then
	rm -rf ${BackDir}/passwd.out
	while [ `echo $?` -gt 0 ];then
	do
		echo "Pls check whether oracle account has permission of this directory."
		get_char
		rm -rf ${BackDir}/passwd.out
	done
fi
for username in `cat ${workplace}/user.out`
do
	count=`expr ${count} + 1`
	user=`echo ${username} | cut -d ' ' -f1`
	read -p "Pls input ${user}'s password: " uPasswd
	sqlplus ${user}/${uPasswd}@${SID} < ${workplace}/passwd${count}
	echo "${user}/${uPasswd}" >> ${BackDir}/passwd.out
	sqlplus sys/${sysPasswd}@${SID} as sysdba <> ${workplace}/bkresult.out
	else
		echo "${user}'s backup succeed." >> ${workplace}/bkresult.out
	fi
	rm -f ${workplace}/passwd${count}
done

rm -f ${workplace}/user.out
### End ###

### Get user && tablespace info ###
rm -rf ${BackDir}/tablespace.out ${BackDir}/user_tablespace.out ${BackDir}/user_quota.out
sqlplus sys/${sysPasswd}@${SID} as sysdba < ${BackDir}/tablespace.temp
sed -i '/^$/d' ${BackDir}/tablespace.temp
cat ${BackDir}/user_quota.out | grep -v "select" | grep -v "USERNAME" | grep -v "-" | grep -v "spool" > ${BackDir}/user_quota.temp
sed -i '/^$/d' ${BackDir}/user_quota.temp
cat ${BackDir}/user_tablespace.out | grep -v "select" | grep -v "USERNAME" | grep -v "-" | grep -v "spool" > ${BackDir}/user_tablespace.temp
sed -i '/^$/d' ${BackDir}/user_tablespace.temp
rm -rf ${BackDir}/tablespace.out ${BackDir}/user_tablespace.out ${BackDir}/user_quota.out
mv ${BackDir}/tablespace.temp ${BackDir}/tablespace.out
mv ${BackDir}/user_quota.temp ${BackDir}/user_quota.out
mv ${BackDir}/user_tablespace.temp ${BackDir}/user_tablespace.out
### End ###

### Result ###
clear
echo ""
echo "----------------RESULT--------------"
echo "Success:"
cat ${workplace}/bkresult.out | grep "succeed"
echo ""
echo "Fail: "
cat ${workplace}/bkresult.out | grep "wrong"
### End ###

exit 0

   声明:本文采用 BY-NC-SA 协议进行授权 | 星期九
   原创文章转载请注明:转自《备份Oracle数据库业务用户数据之shell脚本

No comments yet.
41 + 6 =  (required)
comment_ad

 NOTICE1: You should type some Chinese word (like “你好”) in your comment to pass the spam-check, thanks for your patience!

 NOTICE2: 请申请gravatar头像(http://en.gravatar.com),木有头像的会显示为“小怪物”头像,将难以通过审核!

分享按钮