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

Posted by Harid六月 - 16 - 2013 Leave comments

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

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

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

下面是具体的脚本内容:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
#!/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 <<EOF
		exit;
		exit;
		exit;
		exit;
EOF
	done
fi
### End ###
 
### Get non-system account ###
workplace=`pwd`
rm -rf ${workplace}/user_t.out
while [ `echo $?` -gt 0 ]
do
	echo "Pls check whether oracle account has permission of this directory."
	get_char
	rm -rf ${workplace}/user_t.out
done
 
sqlplus sys/${sysPasswd}@${SID} as sysdba <<EOF
col username format A20;
set pagesize 40;
spool ${workplace}/user_t.out;
select username from dba_users where default_tablespace not in ('USERS','SYSAUX','SYSTEM');
spool off;
create or replace directory datapump as '`echo $BackDir`';
exit;
EOF
### End ###
 
### Get non-system account's password ###
cat ${workplace}/user_t.out | grep -v "select username" | grep -v "USERNAME" | grep -v "selected" | grep -v "spool off" | grep -v "-" > ${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} <<EOF
	exit;
	exit;
	exit;
	exit;
EOF
	while [ `echo $?` -gt 0 ]
	do
		read -p "Error password. Input ${user}'s password: " uPasswd
		sqlplus ${user}/${uPasswd}@${SID} <<EOF
		exit;
		exit;
		exit;
		exit;
EOF
	done
	echo $uPasswd > ${workplace}/passwd${count}
	echo "${user}/${uPasswd}" >> ${BackDir}/passwd.out
	sqlplus sys/${sysPasswd}@${SID} as sysdba <<EOF
	grant read,write on directory datapump to ${user};
	exit;
EOF
done
### End ###
 
### Start Backup ###
clear
echo ""
echo "Start expdp program..."
sleep 3s
count=0
rm -rf ${workplace}/bkresult.out
for username in `cat ${workplace}/user.out`
do
	count=`expr ${count} + 1`
	user=`echo ${username} | cut -d ' ' -f1`
	uPasswd =`cat ${workplace}/passwd${count} | cut -d ' ' -f1`
	expdp ${user}/${uPasswd}@${SID} directory=datapump dumpfile=${user}.dmp logfile=expdp_${user}.log
	if [ `echo $?` -gt 0 ];then
		echo "An error occurred."
		echo "${user}'s backup has occurred some wrong." >> ${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 <<EOF
col USERNAME format A15;
col DEFAULT_TABLESPACE format A30;
set pagesize 40;
spool ${BackDir}/user_tablespace.out;
select USERNAME,DEFAULT_TABLESPACE from dba_users where default_tablespace not in ('USERS','SYSAUX','SYSTEM');
spool off;
col TABLESPACE_NAME format A19;
col FILE_NAME format A60;
set pagesize 40;
spool ${BackDir}/tablespace.out;
select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME not in ('SYSTEM','SYSAUX','USERS') and TABLESPACE_NAME not like 'UNDOTBS%';
spool off;
col TABLESPACE_NAME format A19;
col USERNAME format A15;
set pagesize 40;
spool ${BackDir}/user_quota.out;
select TABLESPACE_NAME,USERNAME from dba_ts_quotas where TABLESPACE_NAME not in ('SYSTEM','SYSAUX','USERS') and TABLESPACE_NAME not like 'UNDO%';
spool off;
exit;
EOF
 
cat ${BackDir}/tablespace.out | grep -v "select" | grep -v "FILE_NAME" | grep -v "-" | grep -v "spool" > ${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脚本


分享按钮