使用 RMAN 同步数据库

使用 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
  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
恢复脚本:
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

posted @ 2017-04-18 14:18  foreverfriends  阅读(565)  评论(0)  编辑  收藏  举报