使用 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 点击(此处)折叠或打开 #!/bin/bash ################################################################### # Usage: # backup_rman_v1.1.sh [all|repeat|sync] # all: list all backup file. # repeat: repeat level-1 increment backup. # sync: sync backup file to target database. ################################################################### export ORACLE_HOME=/opt/oracle11g/u01 export ORACLE_SID=primary export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/lib export PATH=$PATH:${ORACLE_HOME}/bin export NLS_LANG="American_america.zhs16gbk" export ORACLE_OWNER=oracle export backup_dir=/home/oracle/backup/backup export log=/home/oracle/backup/log rsync_dir=$backup_dir #sync dir controlfile_dir=$backup_dir/controlfile username=oracle #target OS ,oracle user password=go2north #target oracle user password target_host=172.16.5.27 today_backup=`date +'%Y-%m-%d'` mail_to_admin="[e-mail]" ########set display color######### white=$(echo -e "\e[39;40m") green=$(echo -e "\e[36;40m") red=$(echo -e "\e[31;40m") purple=$(echo -e "\e[35;40m") yellow=$(echo -e "\e[33;40m") blue=$(echo -e "\e[34;40m") ########color set end ############# data backup status. # 0: backup failed. # 2: default # 9: success backup_status=2#database status check ,If it's not turn on,the value is 0,or else 1 ora_stat=`ps -ef | grep -i 'ora_smon_*' |grep -v grep| wc -l`#database mode check,If it's archive mode,that value is 1,or else 0; arch=`ps -ef | grep -i 'ora_arc_*' | grep -v grep | wc -l`function open_database() { if [ "$ora_stat" = 0 ]; then cat << EOF | $ORACLE_HOME/bin/sqlplus '/as sysdba' shutdown immediate; startup; quit; EOF backup_status=2 if [ "$?" = 1 ]; then echo "database unable strtup!" backup_status=0 exit 1 fi fi } function open_archive_mode() { if [ "$arch" = 0 ]; then #if arch=1,nothing,because it was already on archive mode echo "****************open archive mode*************" cat < 0 ]; then #if arch=1,nothing,because initialization mode is archive mode echo "********* return initialization database mode**********" cat <&1;cat << EOF | $ORACLE_HOME/bin/rman target / |tee $log/rman_increment_db_`date +'%y%m%d%H%M'`.log configure maxsetsize to 20g; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to '$controlfile_dir/%F'; run { allocate channel c01 type disk; backup incremental level 1 database format '$backup_dir/increment_db_%d_%s_%t_%p' tag="increment_db_`date +'%y%m%d%H%M'`"; release channel c01; } configure controlfile autobackup off; crosscheck backup of database; crosscheck archivelog all; delete noprompt obsolete ; delete noprompt expired backup; delete noprompt backup completed before 'sysdate-30'; delete noprompt archivelog until time 'sysdate-14'; EOF if [ "$?" = 0 ];then ????echo "*******************level-1 backup completed!************************" ????????backup_status=9 else ????echo "*****************level-1 backup databae failed,please contact oracle dba*******" ????backup_status=0 fi return $backup_status return_initaliztion_mode } function level_0_backup_database() { open_database open_archive_mode echo "************* Do level-0 backup ****************" cat < /dev/null # test network link # if [ $? != 0 ] ; then ????echo "sync host:$red $target_host $white link failed!,please check network." ????exit 1 fi if [ -f /usr/bin/rsync ]; then #check resync command # ???cat << EOF > sync #!/usr/bin/expect spawn /usr/bin/rsync -avzu $rsync_dir/ $username@$target_host:$rsync_dir expect "password:" send "$password\n"; send "quit\n"; interact EOF echo "********copy backup files to target database********" ??if [ -f sync -a -f /usr/bin/expect ]; then ????chmod +x sync ????./sync ????rm -rf ./sync #list sync files backup_file=`ls -ltR --full-time $backup_dir/ | egrep -i "increment_|c-" | grep -i $today_backup | awk '{print $6 " " substr($7,1,8) " " $9}'` ????echo "sync files:" ????echo "$blue" ????j=0 ????for i in $backup_file ????do ????????((j++)) ????????a[$j]=$i ????????if [ $j = 3 ]; then ????????????echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} ${a[$j]}" ?????????????j=0 ????????fi ????done ????echo "$white" ????echo " transtion has succeed.please check the backup files on target database." ????exit 0 ???else ??????echo "command expect not found, please install Tcl/expect" ??????exit 1 ???fi else ????echo "command rsync not found,please install!" ????exit 1 fi } if [ -f $log/autobak_`date +'%Y%m%d'`.log ]; then ????rm -rf $log/autobak_`date +'%Y%m%d'`.log fi ( level_0_backup_status=`find $backup_dir/ -name 'full_db_*'| grep -i full_db |grep -v grep | wc -l` 2>&1 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 if [ $level_0_backup_status = 0 -a $backup_status = 2 ]; then ????level_0_backup_database ????backup_status=$? fi if [ $level_1_backup_status = 0 -a $backup_status = 2 ]; then ????increment_backup_level_1 ????backup_status=$? fi # ############Today's database backup information########## # check today's backup status # check_backup=`ls -l --full-time $backup_dir/ | egrep -i "increment_db_|full_db_" | awk '{print $6}' | grep -i $today_backup | wc -l` # check today's controlfile backup information # control_file=`ls -lt --full-time $controlfile_dir/ | grep -i "c-*" | grep -i $today_backup | awk '{print $6 " " substr($7,1,8) " " $9}'` # check today's increment backup information # 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}'` 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}'` if [ "$1" = "all" ] ; then ????backup_file_info=`ls -lt --full-time $backup_dir/ | egrep -i "increment_db_|full_db" | awk '{print $6 " " substr($7,1,8) " " $9}'` ????control_file=`ls -lt --full-time $controlfile_dir/ | grep -i "c-*"| awk '{print $6 " " substr($7,1,8) " " $9}'` fi ????# print today's backup information including controlfile and log information # if [ $check_backup -ge 0 ]; then????if [ "$1" = "repeat" ] ; then ????????repeat_increment_backup $1 ????else ????????????????echo " ############Today's database backup information########## " ???????????if [ "$1" = "all" ]; then ????????????????????????today_backup=`ls -l --full-time $backup_dir/ | grep -i full_db_* | awk '{print $6}'` ????????????????????????echo "List date $purple ${today_backup[0]} $white level-0 backup database after file information" ???????????else ????????????????echo "Date $purple $today_backup $white database backup is completed." ????????????????fi ????????????echo "backup file directory: $backup_dir" ????????????echo "backup file information: $green" ????????echo "" ????????j=0 ????????????for i in $backup_file_info ????????do ????????????((j++)) ????????????a[$j]=$i ????????????if [ $j = 3 ]; then ????????????????echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} $backup_dir/${a[$j]}" ?????????????????j=0 ????????????fi ????????done ????????????echo "$white" ????????????????echo "Controlfile information:$yellow" ????????echo "" ????????j=0 ????????for p in $control_file;do ????????????((j++)) ????????????a[$j]=$p ????????????if [ $j = 3 ] ; then ????????????????????echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} $controlfile_dir/${a[$j]}" ????????????????????j=0 ????????????fi ??????????done ????????echo "$white" ????????????????echo "log information:$blue" ????????echo "" ????????j=0 ????????for p in $log_file_info;do ????????????((j++)) ????????????a[$j]=$p ????????????if [ $j = 3 ] ; then ????????????????????echo "${a[`expr $j - 2`]} ${a[`expr $j - 1`]} $log/${a[$j]}" ????????????????????j=0 ????????????fi ??????????done ????????echo "$white" ????????????echo "If you want increment backup database again,please use \"repeat\" parameter" ????????????????echo " ############Today database backup information the end ########## " ????????fi fi # end print backup information # # copy backup file # if [ "$1" = "sync" ] ; then ????backup_status=9 fi if [ "$backup_status" = 9 ]; then ????sync else ?????echo "Today's Backup file is synced. please check whether it's in the target database." ?????echo "If you want to sync again,please use \"sync\" parameter." ?????exit 0 fi echo "If you want to view all backup information,Please use \"all\" parameter." ) 2> $log/autobak_`date +'%Y%m%d'`.log #mail -s "`date +'%Y%m%d'`database backup information" $mail_to_admin<$log/autobak_`date +'%Y%m%d'`.log 恢复脚本:rman_restore_v1.1.sh#!/bin/shexport ORACLE_HOME=/opt/oracle11g/u01export ORACLE_SID=primaryexport LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/libexport PATH=$PATH:${ORACLE_HOME}/binexport NLS_LANG="American_america.zhs16gbk"export ORACLE_OWNER=oracleexport backup_dir=/home/oracle/backup/backupexport log=/home/oracle/backup/logrsync_dir=$backup_dir #sync dircontrolfile_dir=$backup_dir/controlfileusername=oracle #target OS ,oracle user password=go2north #target oracle user password target_host=172.16.5.27today_backup=`date +'%Y-%m-%d'`today=`date +'%Y%m%d'`white=$(echo -e "\e[39;40m")green=$(echo -e "\e[36;40m")red=$(echo -e "\e[31;40m")blue=$(echo -e "\e[33;40m")backup_status=2 #data backup status ,0: backup faild,1: 1 level increment backup,2 : 0 level backup# Begin change restore variablerestore_status=truelast_restore_file=increment_db_ORCL_76_663449691_1last_restore_date=(2008-08-22 19:36)last_recover_time="2008-08-28 15:12:08"last_restore_time=2last_restore_num=3# End change restore variable#sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/t=[0-9]\+/t='$((t+1))'/' $0#sed -i 's/t=[0-9]\+/t='$((t+1))'/' $0restore_file=`ls -lt --full-time $backup_dir/ | grep -i 'increment_db_*' | awk '{print $9}'|head -1`controlfile=`ls -lt --full-time $controlfile_dir | grep -i 'c-*' | awk '{print $9}'| head -1`recover_time=`ls -lt --full-time $backup_dir/ | grep -i 'increment_db_*' | awk '{print substr($7,1,5)}'| head -1`recover_date=(`ls -lt --full-time $backup_dir/ | grep -i 'increment_db_*' | awk '{print $6 " " substr($7,1,5)}' | head -1`)recover_times=`date +'%Y-%m-%d %H:%M:%S'`function update_backup_info(){????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=true/' $0????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_file=.*/last_restore_file='"${restore_file}"'/' $0????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time='"\"${recover_times}\""'/' $0????????????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_date=.*/last_restore_date='"(${recover_date[*]})"/ $0????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_time=.*/last_restore_time='$((last_restore_time+1))'/' $0????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_num=.*/last_restore_num='$((last_restore_num+1))'/' $0}function restore_database() {????echo "************* recover database start ****************"????????cat << EOF | $ORACLE_HOME/bin/sqlplus '/as sysdba'????????shutdown immediate;????????startup nomount;EOF????????cat << EOF | $ORACLE_HOME/bin/rman target / |tee $log/rman_restore_db_`date +'%y%m%d%H%M'`.log????????run {????????????allocate channel c01 type disk;????????????allocate channel c02 type disk;????????????allocate channel c03 type disk;????????????restore controlfile from "$controlfile_dir/$controlfile"; ????????????alter database mount;????????????recover database;????????????release channel c01;????????????release channel c02;????????????release channel c03;????????}????????alter database open resetlogs;EOF????????if [ "$?" = 0 ];then ????????????echo "*******************restore be completed!************************"????????????backup_status=9????????????update_backup_info????????????exit 0????????else????????????echo "******************restore database failed,please contact oracle dba*******"????????????????????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time='"\"${recover_times}\""'/' $0????????????sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=false/' $0????????fi????????????return "$backup_status"}#delete backup file on the weekend again#file_info=`ls -l $backup_dir/ | grep -i 'increment_backup_*.gz'|wc -l`#if [ $file_count -gt 7 ]#then# del_files=`ls -lR $backup_dir| egrep -i "increment_db_|full_db_"|grep \`date -d "7 days ago" +'%Y-%m-%d'\`|awk "{print $9}"`# rm -f $del_files;# if [ $? = 0 ]# then# echo "removing $del_files"# fi#else# echo "No last file"##fi###########################################if [ "$last_restore_file" != "$restore_file" -o "${last_restore_date[1]}" != "$recover_time" ]; then restore_database????fiif [ "$1" = "repeat" ] ; then???????????????restore_databasefi if [ "$restore_status" = "true" -o "${last_restore_date[0]}" = ${recover_date[0]} ]; then echo "Today's sync already completed!"echo "Last restore file: $backup_dir/$last_restore_file"echo "Last restore time: $last_restore_time"echo "The number of times the database be restored today: $last_restore_num"echo "The total number of times the database have ever been restore: $last_recover_time"sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time='"\"${recover_times}\""'/' $0elsesed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_time=.*/last_restore_time=0/' $0sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=false/' $0sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time='"\"${recover_times}\""'/' $0fi ?