使用 RMAN 同步資料庫

rongshiyuan發表於2012-07-02
資料庫
使用 RMAN 同步資料庫 (2012-06-12 16:47) 一鍵轉載
分類： Oracle 日常管理


使用 RMAN 同步資料庫

使用 RMAN 同步資料庫

一．概述

二 操作步驟

(一)．把生產庫置為歸檔模式

(二).啟動rman做資料庫0級備份

(三)．修改生產庫資料庫到未歸檔

(四)．拷貝備份集到測試庫

(五).在測試庫上的操作

一．概述

因專案組遇到要2臺資料庫同步的問題，決定使用rman來實現此功能，生產庫執行在非歸檔模式，所以操作起來相對麻煩些，生產庫最多允許丟失半天資料，晚上可以重啟資料，這樣就為我們使用rman創造了必要條件，先來理清一下操作步驟:

1. 首先shutdown生產庫(node1) ,並修改成歸檔模式。

2. 使用rman做一個0級備份集，並設定控制檔案為自動備份。

3. 修改生產庫為非歸檔模式，並置為open狀態。

4. 到測試庫上面，修改資料庫為歸檔模式，因沒有效能要求，不需要再改回非歸檔，方便恢復操作。

5. 拷貝備份集到測試庫(node2)，備份集的存放位置和生產庫保持一致。

6. 啟動測試庫到nomount狀態。

7. 啟動rman，第一步先恢復controlfile.裡面包含了重要的備份資訊。

8. 更改資料庫到mount狀態.

9. 恢復資料庫 restore database,注意,此步驟只會使用最近的一次0級備份，而不會應用增量或差異備份。

10. Recover database,此步驟會首先應用所有的差異或增量備份，然後恢復歸檔日誌，當然在恢復歸檔時會出現錯誤，在此可以忽略掉。

11. 使用resetlogs關鍵字開啟資料庫，資料庫同步完成。

以後每天按上面操作寫成指令碼自動執行即可，需要注意的地方是把0級備份改為1級或2級即可,這樣就可以每半月做一次0級備份，每天晚上做一次1級或2級備份，然後恢復到測試庫，以此來實現2資料庫的同步.

二 操作步驟 (一)．把生產庫置為歸檔模式.

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

2． Sql>shutdown immediate;

3． Sql>startup mount;

4． Sql>alter database archivelog;

5． Sql>alter database open;

6． Sql>quit

(二).啟動rman做資料庫0級備份

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

  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

  253. today_backup=`ls -l --full-time $backup_dir/ | grep -i full_db_* | awk '{print $6}'`

  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
恢復指令碼:
rman_restore_v1.1.sh

  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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-734311/，如需轉載，請註明出處，否則將追究法律責任。

相關文章