使用 RMAN 同步数据库

2． Sql>shutdown immediate;

3． Sql>startup mount;

4． Sql>alter database archivelog;

5． Sql>alter database open;

6． Sql>quit

1. [oracle@primary orcl]$ rman target /

2. RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

3. RMAN> backup incremental level 0 database format ‘full_db_%d_%s_%t_%p‘;

--第一次同步操作时用该语句。

RMAN>quit;

日常同步备份时使用1级备份

4. [oracle@primary orcl]$ rman target /

5. RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

6. RMAN> backup incremental level 1 database format ‘increment _db_%d_%s_%t_%p‘;

7. RMAN>quit;

1. [oracle@primary orcl]$ Sqlplus ‘/as sysdba’

2. Sql>alter database noarchivelog;

3. Sql>alter database open;

4. Sql>quit;

拷贝备份集文件到测试库，并和生产库相同位置,可以使用sftp或ftp等工具完成。

1. [oracle@primary orcl]$ Sqlplus ‘/as sysdba’

2. Sql>startup nomount;

3. Sql>quit;

4. [oracle@primary orcl]$ rman target /

5. RMAN> restore controlfile from/opt/oracle11g/u01/dbs/c-1190421672-20080819-01‘;

--该处可修改为实际备份集。

6. RMAN> alter database mount;

7. RMAN> restore database;

8. RMAN> recover database;

9. RMAN>alter database open resetlogs;

注意，所有的增量或差异备份都是用recover database来恢复的，如果丢失增量备份，需用下面方法启动数据库，将可能导致丢失大量数据:

启动sqlplus，使用下面语句清除丢失的增量备份.

Sql>recover database using backup controlfile until cancel;

ORA-00279: ü 1839635 ( 08/18/2008 23:25:21 ú) 1 ±è

ORA-00289: ¨é: /archive/1_74_662640938.dbf

ORA-00280: ü 1839635 ( 1) ò #74

Specify log: {=suggested | filename | AUTO | CANCEL}

Cancel <需要手工输入后回车>

Media recovery cancelled.

最后执行resetlogs关键字启动数据库。

SQL> alter database open resetlogs;

Database altered.

备份及同步脚本:
 rman_backup_v1.1.sh

点击(此处)折叠或打开

1. #!/bin/bash 
2. ################################################################### 
3. # Usage: 
4. # backup_rman_v1.1.sh [all|repeat|sync] 
5. # all: list all backup file. 
6. # repeat: repeat level-1 increment backup. 
7. # sync: sync backup file to target database. 
8. ################################################################### 
9. export ORACLE_HOME=/opt/oracle11g/u01 
10. export ORACLE_SID=primary 
11. export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/lib 
12. export PATH=$PATH:${ORACLE_HOME}/bin 
13. export NLS_LANG="American_america.zhs16gbk" 
14. export ORACLE_OWNER=oracle 
15. export backup_dir=/home/oracle/backup/backup 
16. export log=/home/oracle/backup/log 
17. rsync_dir=$backup_dir #sync dir 
18. controlfile_dir=$backup_dir/controlfile 
19. username=oracle #target OS ,oracle user 
20. password=go2north #target oracle user password 
21. target_host=172.16.5.27 
22. today_backup=`date +‘%Y-%m-%d‘` 
23. mail_to_admin="zhaorupeng@126.com" 
24. ########set display color######### 
25. white=$(echo -e "\e[39;40m") 
26. green=$(echo -e "\e[36;40m") 
27. red=$(echo -e "\e[31;40m") 
28. purple=$(echo -e "\e[35;40m") 
29. yellow=$(echo -e "\e[33;40m") 
30. blue=$(echo -e "\e[34;40m") 
31. ########color set end ############
32. # data backup status. 
33. # 0: backup failed. 
34. # 2: default 
35. # 9: success 
36. backup_status=2
37. #database status check ,If it‘s not turn on,the value is 0,or else 1 
38. ora_stat=`ps -ef | grep -i ‘ora_smon_*‘ |grep -v grep| wc -l`
39. #database mode check,If it‘s archive mode,that value is 1,or else 0; 
40. arch=`ps -ef | grep -i ‘ora_arc_*‘ | grep -v grep | wc -l`
41. function open_database() 
42. { 
43. if [ "$ora_stat" = 0 ]; then 
44. cat << EOF | $ORACLE_HOME/bin/sqlplus ‘/as sysdba‘ 
45. shutdown immediate; 
46. startup; 
47. quit; 
48. EOF 
49. backup_status=2 
50. if [ "$?" = 1 ]; then 
51. echo "database unable strtup!" 
52. backup_status=0 
53. exit 1 
54. fi 
55. fi 
56. } 
57. function open_archive_mode() 
58. { 
59. if [ "$arch" = 0 ]; then #if arch=1,nothing,because it was already on archive mode 
60. echo "****************open archive mode*************" 
61. cat << EOF | $ORACLE_HOME/bin/sqlplus ‘/as sysdba‘ 
62. shutdown immediate; 
63. startup mount; 
64. alter database archivelog; 
65. alter database open; 
66. quit; 
67. EOF 
68. fi 
69. } 
70. function return_initaliztion_mode() 
71. { 
72. if [ "$arch" = 0 -a "$backup_status" > 0 ]; then 
73. #if arch=1,nothing,because initialization mode is archive mode 
74. echo "********* return initialization database mode**********" 
75. cat << EOF | $ORACLE_HOME/bin/sqlplus ‘/as sysdba‘ 
76. shutdown immediate; 
77. startup mount; 
78. alter database noarchivelog; 
79. alter database open; 
80. quit; 
81. EOF 
82. fi 
83. if [ "$?" = 0 ]; then 
84.     echo "return initalization database successfully." 
85. fi 
86. echo "************return initialization database mode *********" ; 
87. } 
88. function increment_backup_level_1() # incremental level-1 backup 
89. { 
90. open_database 
91. open_archive_mode 
92. echo "******** `date +‘%Y%m%d‘` Do level-1 increment backup....*********************" 2>&1;
93. cat << EOF | $ORACLE_HOME/bin/rman target / |tee $log/rman_increment_db_`date +‘%y%m%d%H%M‘`.log 
94. configure maxsetsize to 20g; 
95. configure controlfile autobackup on; 
96. configure controlfile autobackup format for device type disk to ‘$controlfile_dir/%F‘; 
97. run { 
98. allocate channel c01 type disk; 
99. backup incremental level 1 database format ‘$backup_dir/increment_db_%d_%s_%t_%p‘ tag="increment_db_`date +‘%y%m%d%H%M‘`"; 
100. release channel c01; 
101. } 
102. configure controlfile autobackup off; 
103. crosscheck backup of database; 
104. crosscheck archivelog all; 
105. delete noprompt obsolete ; 
106. delete noprompt expired backup; 
107. delete noprompt backup completed before ‘sysdate-30‘; 
108. delete noprompt archivelog until time ‘sysdate-14‘; 
109. EOF 
110. if [ "$?" = 0 ];then 
111.     echo "*******************level-1 backup completed!************************" 
112.         backup_status=9 
113. else 
114.     echo "*****************level-1 backup databae failed,please contact oracle dba*******" 
115.     backup_status=0 
116. fi 
117. return $backup_status 
118. return_initaliztion_mode 
119. } 
120. function level_0_backup_database() 
121. { 
122. open_database 
123. open_archive_mode 
124. echo "************* Do level-0 backup ****************" 
125. cat << EOF | $ORACLE_HOME/bin/rman target / |tee $log/rman_full_db_`date +‘%y%m%d%H%M‘`.log 
126. configure retention policy to redundancy 30; 
127. configure maxsetsize to 20g; 
128. configure controlfile autobackup on; 
129. configure controlfile autobackup format for device type disk to ‘$controlfile_dir/%F‘; 
130. crosscheck backup of database; 
131. crosscheck archivelog all; 
132. delete noprompt obsolete ; 
133. delete noprompt expired backup; 
134. delete noprompt backup completed before ‘sysdate-30‘; 
135. delete noprompt archivelog until time ‘sysdate-5‘; 
136. run { 
137. allocate channel c1 type disk; 
138. backup incremental level 0 database format ‘$backup_dir/full_db_%d_%s_%t_%p‘ tag="full_db_`date +‘%y%m%d%H%M‘`"; 
139. release channel c1 ; 
140. } 
141. configure controlfile autobackup off; 
142. quit; 
143. EOF 
144. if [ "$?" = 0 ];then 
145.     echo "*******************level-0 backup completed!************************" 
146.         backup_status=9 
147. else 
148.     echo "******************level-0 backup databae failed,please contact oracle dba*******"
149.     backup_status=0 
150. fi 
151. return $backup_status 
152. return_initaliztion_mode 
153. } 
154. function repeat_increment_backup() 
155. { 
156.     if [ "$#" = 0 ]; then 
157.         exit 0 
158.     else
159.           if [ "$1" = "repeat" ]; then 
160.             echo "************do database increment backup again**************" 
161.             increment_backup_level_1 $ORACLE_HOME $log $backup_dir 
162.             echo "************repeat increment backup completed!**************" 
163.           else 
164.             echo "command error,please use parameter ‘repeat‘" 
165.             exit 0 
166.           fi 
167.     fi 
168. }
169. # sync target database backup files # 
170. function sync() 
171. { 
172. ping $target_host -c 1 > /dev/null # test network link # 
173. if [ $? != 0 ] ; then 
174.     echo "sync host:$red $target_host $white link failed!,please check network." 
175.     exit 1 
176. fi 
177. if [ -f /usr/bin/rsync ]; then 
178. #check resync command # 
179.    cat << EOF > sync 
180. #!/usr/bin/expect 
181. spawn /usr/bin/rsync -avzu $rsync_dir/ $username@$target_host:$rsync_dir 
182. expect "password:" 
183. send "$password\n"; 
184. send "quit\n"; 
185. interact 
186. EOF 
187. echo "********copy backup files to target database********" 
188.   if [ -f sync -a -f /usr/bin/expect ]; then 
189.     chmod +x sync 
190.     ./sync 
191.     rm -rf ./sync 
192. #list sync files 
193. backup_file=`ls -ltR --full-time $backup_dir/ | egrep -i "increment_|c-" | grep -i $today_backup | awk ‘{print $6 " " substr($7,1,8) " " $9}‘` 
194.     echo "sync files:" 
195.     echo "$blue" 
196.     j=0 
197.     for i in $backup_file 
198.     do 
199.         ((j++)) 
200.         a[$j]=$i 
201.         if [ $j = 3 ]; then 
202.             echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} ${a[$j]}" 
203.              j=0 
204.         fi 
205.     done 
206.     echo "$white" 
207.     echo " transtion has succeed.please check the backup files on target database." 
208.     exit 0 
209.    else 
210.       echo "command expect not found, please install Tcl/expect" 
211.       exit 1 
212.    fi 
213. else 
214.     echo "command rsync not found,please install!" 
215.     exit 1 
216. fi 
217. } 
218. if [ -f $log/autobak_`date +‘%Y%m%d‘`.log ]; then 
219.     rm -rf $log/autobak_`date +‘%Y%m%d‘`.log 
220. fi 
221. ( 
222. level_0_backup_status=`find $backup_dir/ -name ‘full_db_*‘| grep -i full_db |grep -v grep | wc -l` 2>&1 
223. level_1_backup_status=`ls -l --full-time $backup_dir/ |grep -i ‘increment_db_*‘| grep -i $today_backup|grep -v grep | wc -l` 2>&1 
224. if [ $level_0_backup_status = 0 -a $backup_status = 2 ]; then 
225.     level_0_backup_database 
226.     backup_status=$? 
227. fi 
228. if [ $level_1_backup_status = 0 -a $backup_status = 2 ]; then 
229.     increment_backup_level_1 
230.     backup_status=$? 
231. fi 
232. # ############Today‘s database backup information########## 
233. # check today‘s backup status # 
234. check_backup=`ls -l --full-time $backup_dir/ | egrep -i "increment_db_|full_db_" | awk ‘{print $6}‘ | grep -i $today_backup | wc -l` 
235. # check today‘s controlfile backup information # 
236. control_file=`ls -lt --full-time $controlfile_dir/ | grep -i "c-*" | grep -i $today_backup | awk ‘{print $6 " " substr($7,1,8) " " $9}‘` 
237. # check today‘s increment backup information # 
238. backup_file_info=`ls -lt --full-time $backup_dir/ | egrep -i "increment_db_|full_db_" | grep -i $today_backup | awk ‘{print $6 " " substr($7,1,8) " " $9}‘` 
239. log_file_info=`ls -lt --full-time $log/ | egrep -i "increment_db_|full_db_" | grep -i $today_backup | awk ‘{print $6 " " substr($7,1,8) " " $9}‘` 
240. if [ "$1" = "all" ] ; then 
241.     backup_file_info=`ls -lt --full-time $backup_dir/ | egrep -i "increment_db_|full_db" | awk ‘{print $6 " " substr($7,1,8) " " $9}‘` 
242.     control_file=`ls -lt --full-time $controlfile_dir/ | grep -i "c-*"| awk ‘{print $6 " " substr($7,1,8) " " $9}‘` 
243. fi 
244.     
245. # print today‘s backup information including controlfile and log information # 
246. if [ $check_backup -ge 0 ]; then
247.     if [ "$1" = "repeat" ] ; then 
248.         repeat_increment_backup $1 
249.     else 
250.                 echo " ############Today‘s database backup information########## " 
251.            if [ "$1" = "all" ]; then 
252.     
253.                     today_backup=`ls -l --full-time $backup_dir/ | grep -i full_db_* | awk ‘{print $6}‘` 
254.             
255.             echo "List date $purple ${today_backup[0]} $white level-0 backup database after file information" 
256.            else 
257.                 echo "Date $purple $today_backup $white database backup is completed." 
258.                 fi 
259.             echo "backup file directory: $backup_dir" 
260.             echo "backup file information: $green" 
261.         echo "" 
262.         j=0 
263.             for i in $backup_file_info 
264.         do 
265.             ((j++)) 
266.             a[$j]=$i 
267.             if [ $j = 3 ]; then 
268.                 echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} $backup_dir/${a[$j]}" 
269.                  j=0 
270.             fi 
271.         done 
272.             echo "$white" 
273.                 echo "Controlfile information:$yellow" 
274.         echo "" 
275.         j=0 
276.         for p in $control_file;do 
277.             ((j++)) 
278.             a[$j]=$p 
279.             if [ $j = 3 ] ; then 
280.                     echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} $controlfile_dir/${a[$j]}" 
281.                     j=0 
282.             fi 
283.           done 
284.         echo "$white" 
285.                 echo "log information:$blue" 
286.         echo "" 
287.         j=0 
288.         for p in $log_file_info;do 
289.             ((j++)) 
290.             a[$j]=$p 
291.             if [ $j = 3 ] ; then 
292.                     echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} $log/${a[$j]}" 
293.                     j=0 
294.             fi 
295.           done 
296.         echo "$white" 
297.             echo "If you want increment backup database again,please use \"repeat\" parameter" 
298.                 echo " ############Today database backup information the end ########## " 
299.         fi 
300. fi 
301. # end print backup information # 
302. # copy backup file # 
303. if [ "$1" = "sync" ] ; then 
304.     backup_status=9 
305. fi 
306. if [ "$backup_status" = 9 ]; then 
307.     sync 
308. else 
309.      echo "Today‘s Backup file is synced. please check whether it‘s in the target database." 
310.      echo "If you want to sync again,please use \"sync\" parameter." 
311.      exit 0 
312. fi 
313. echo "If you want to view all backup information,Please use \"all\" parameter." 
314. ) 2> $log/autobak_`date +‘%Y%m%d‘`.log 
315. #mail -s "`date +‘%Y%m%d‘`database backup information" $mail_to_admin<$log/autobak_`date +‘%Y%m%d‘`.log

1. #!/bin/sh
2. export ORACLE_HOME=/opt/oracle11g/u01
3. export ORACLE_SID=primary
4. export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/lib
5. export PATH=$PATH:${ORACLE_HOME}/bin
6. export NLS_LANG="American_america.zhs16gbk"
7. export ORACLE_OWNER=oracle
8. export backup_dir=/home/oracle/backup/backup
9. export log=/home/oracle/backup/log
10. rsync_dir=$backup_dir #sync dir
11. controlfile_dir=$backup_dir/controlfile
12. username=oracle #target OS ,oracle user 
13. password=go2north #target oracle user password 
14. target_host=172.16.5.27
15. today_backup=`date +‘%Y-%m-%d‘`
16. today=`date +‘%Y%m%d‘`
17. white=$(echo -e "\e[39;40m")
18. green=$(echo -e "\e[36;40m")
19. red=$(echo -e "\e[31;40m")
20. blue=$(echo -e "\e[33;40m")
21. backup_status=2 #data backup status ,0: backup faild,1: 1 level increment backup,2 : 0 level backup
22. # Begin change restore variable
23. restore_status=true
24. last_restore_file=increment_db_ORCL_76_663449691_1
25. last_restore_date=(2008-08-22 19:36)
26. last_recover_time="2008-08-28 15:12:08"
27. last_restore_time=2
28. last_restore_num=3
29. # End change restore variable
30. #sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/t=[0-9]\+/t=‘$((t+1))‘/‘ $0
31. #sed -i ‘s/t=[0-9]\+/t=‘$((t+1))‘/‘ $0
32. restore_file=`ls -lt --full-time $backup_dir/ | grep -i ‘increment_db_*‘ | awk ‘{print $9}‘|head -1`
33. controlfile=`ls -lt --full-time $controlfile_dir | grep -i ‘c-*‘ | awk ‘{print $9}‘| head -1`
34. recover_time=`ls -lt --full-time $backup_dir/ | grep -i ‘increment_db_*‘ | awk ‘{print substr($7,1,5)}‘| head -1`
35. recover_date=(`ls -lt --full-time $backup_dir/ | grep -i ‘increment_db_*‘ | awk ‘{print $6 " " substr($7,1,5)}‘ | head -1`)
36. recover_times=`date +‘%Y-%m-%d %H:%M:%S‘`
37. function update_backup_info()
38. {
39.     sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=true/‘ $0
40.         sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_file=.*/last_restore_file=‘"${restore_file}"‘/‘ $0
41.         sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time=‘"\"${recover_times}\""‘/‘ $0
42.                 sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_date=.*/last_restore_date=‘"(${recover_date[*]})"/ $0
43.         sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_time=.*/last_restore_time=‘$((last_restore_time+1))‘/‘ $0
44.         sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_num=.*/last_restore_num=‘$((last_restore_num+1))‘/‘ $0
45. }
46. function restore_database() 
47. {
48.     echo "************* recover database start ****************"
49.         cat << EOF | $ORACLE_HOME/bin/sqlplus ‘/as sysdba‘
50.         shutdown immediate;
51.         startup nomount;
52. EOF
53.         cat << EOF | $ORACLE_HOME/bin/rman target / |tee $log/rman_restore_db_`date +‘%y%m%d%H%M‘`.log
54.         run {
55.             allocate channel c01 type disk;
56.             allocate channel c02 type disk;
57.             allocate channel c03 type disk;
58.             restore controlfile from "$controlfile_dir/$controlfile"; 
59.             alter database mount;
60.             recover database;
61.             release channel c01;
62.             release channel c02;
63.             release channel c03;
64.         }
65.         alter database open resetlogs;
66. EOF
67.         if [ "$?" = 0 ];then 
68.             echo "*******************restore be completed!************************"
69.             backup_status=9
70.             update_backup_info
71.             exit 0
72.         else
73.             echo "******************restore database failed,please contact oracle dba*******"
74.                         sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time=‘"\"${recover_times}\""‘/‘ $0
75.             sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=false/‘ $0
76.         fi
77.             return "$backup_status"
78. }
79. #delete backup file on the weekend again
80. #file_info=`ls -l $backup_dir/ | grep -i ‘increment_backup_*.gz‘|wc -l`
81. #if [ $file_count -gt 7 ]
82. #then
83. # del_files=`ls -lR $backup_dir| egrep -i "increment_db_|full_db_"|grep \`date -d "7 days ago" +‘%Y-%m-%d‘\`|awk "{print $9}"`
84. # rm -f $del_files;
85. # if [ $? = 0 ]
86. # then
87. # echo "removing $del_files"
88. # fi
89. #else
90. # echo "No last file"
91. #
92. #fi
93. ###########################################
94. if [ "$last_restore_file" != "$restore_file" -o "${last_restore_date[1]}" != "$recover_time" ]; then 
95. restore_database    
96. fi
97. if [ "$1" = "repeat" ] ; then
98.                restore_database
99. fi 
100. if [ "$restore_status" = "true" -o "${last_restore_date[0]}" = ${recover_date[0]} ]; then 
102. echo "Today‘s sync already completed!"
103. echo "Last restore file: $backup_dir/$last_restore_file"
104. echo "Last restore time: $last_restore_time"
105. echo "The number of times the database be restored today: $last_restore_num"
106. echo "The total number of times the database have ever been restore: $last_recover_time"
107. sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time=‘"\"${recover_times}\""‘/‘ $0
108. else
109. sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_time=.*/last_restore_time=0/‘ $0
110. sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=false/‘ $0
111. sed -i ‘/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time=‘"\"${recover_times}\""‘/‘ $0
112. fi

使用 RMAN 同步数据库

标签：