免费做网站推广的软件,网站设计作业平台,定西网站建设公司排名照片,建设个人网站刷使用 RMAN 同步数据库使用 RMAN 同步数据库一#xff0e;概述二 操作步骤(一)#xff0e;把生产库置为归档模式(二).启动rman做数据库0级备份(三)#xff0e;修改生产库数据库到未归档(四)#xff0e;拷贝备份集到测试库(五).在测试库上的操作一#xff0e;概述 因项目组遇… 使用 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 [oracleprimary orcl]$ Sqlplus ‘/as sysdba’2 Sqlshutdown immediate;3 Sqlstartup mount;4 Sqlalter database archivelog;5 Sqlalter database open;6 Sqlquit(二).启动rman做数据库0级备份 1. [oracleprimary orcl]$ rman target /2. RMANCONFIGURE CONTROLFILE AUTOBACKUP ON;3. RMAN backup incremental level 0 database format full_db_%d_%s_%t_%p;--第一次同步操作时用该语句。RMANquit;日常同步备份时使用1级备份4. [oracleprimary orcl]$ rman target /5. RMANCONFIGURE CONTROLFILE AUTOBACKUP ON;6. RMAN backup incremental level 1 database format increment _db_%d_%s_%t_%p;7. RMANquit;(三)修改生产库数据库到未归档. 1. [oracleprimary orcl]$ Sqlplus ‘/as sysdba’2. Sqlalter database noarchivelog;3. Sqlalter database open;4. Sqlquit;(四)拷贝备份集到测试库 拷贝备份集文件到测试库并和生产库相同位置,可以使用sftp或ftp等工具完成。(五).在测试库上的操作 1. [oracleprimary orcl]$ Sqlplus ‘/as sysdba’2. Sqlstartup nomount;3. Sqlquit;4. [oracleprimary 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. RMANalter database open resetlogs;注意所有的增量或差异备份都是用recover database来恢复的如果丢失增量备份需用下面方法启动数据库将可能导致丢失大量数据:启动sqlplus使用下面语句清除丢失的增量备份.Sqlrecover database using backup controlfile until cancel;ORA-00279: ü 1839635 ( 08/18/2008 23:25:21 ú) 1 ±èORA-00289: ¨é: /archive/1_74_662640938.dbfORA-00280: ü 1839635 ( 1) ò #74Specify 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_SIDprimary export LD_LIBRARY_PATH${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/lib export PATH$PATH:${ORACLE_HOME}/bin export NLS_LANGAmerican_america.zhs16gbk export ORACLE_OWNERoracle export backup_dir/home/oracle/backup/backup export log/home/oracle/backup/log rsync_dir$backup_dir #sync dir controlfile_dir$backup_dir/controlfile usernameoracle #target OS ,oracle user passwordgo2north #target oracle user password target_host172.16.5.27 today_backupdate %Y-%m-%d mail_to_adminzhaorupeng126.com ########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_status2#database status check ,If its not turn on,the value is 0,or else 1 ora_statps -ef | grep -i ora_smon_* |grep -v grep| wc -l#database mode check,If its archive mode,that value is 1,or else 0; archps -ef | grep -i ora_arc_* | grep -v grep | wc -lfunction open_database() { if [ $ora_stat 0 ]; then cat EOF | $ORACLE_HOME/bin/sqlplus /as sysdba shutdown immediate; startup; quit; EOF backup_status2 if [ $? 1 ]; then echo database unable strtup! backup_status0 exit 1 fi fi } function open_archive_mode() { if [ $arch 0 ]; then #if arch1,nothing,because it was already on archive mode echo ****************open archive mode************* cat EOF | $ORACLE_HOME/bin/sqlplus /as sysdba shutdown immediate; startup mount; alter database archivelog; alter database open; quit; EOF fi } function return_initaliztion_mode() { if [ $arch 0 -a $backup_status 0 ]; then #if arch1,nothing,because initialization mode is archive mode echo ********* return initialization database mode********** cat EOF | $ORACLE_HOME/bin/sqlplus /as sysdba shutdown immediate; startup mount; alter database noarchivelog; alter database open; quit; EOF fi if [ $? 0 ]; then echo return initalization database successfully. fi echo ************return initialization database mode ********* ; } function increment_backup_level_1() # incremental level-1 backup { open_database open_archive_mode echo ******** date %Y%m%d Do level-1 increment backup....********************* 21;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 tagincrement_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_status9 else echo *****************level-1 backup databae failed,please contact oracle dba******* backup_status0 fi return $backup_status return_initaliztion_mode } function level_0_backup_database() { open_database open_archive_mode echo ************* Do level-0 backup **************** cat EOF | $ORACLE_HOME/bin/rman target / |tee $log/rman_full_db_date %y%m%d%H%M.log configure retention policy to redundancy 30; configure maxsetsize to 20g; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to $controlfile_dir/%F; 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-5; run { allocate channel c1 type disk; backup incremental level 0 database format $backup_dir/full_db_%d_%s_%t_%p tagfull_db_date %y%m%d%H%M; release channel c1 ; } configure controlfile autobackup off; quit; EOF if [ $? 0 ];then echo *******************level-0 backup completed!************************ backup_status9 else echo ******************level-0 backup databae failed,please contact oracle dba******* backup_status0 fi return $backup_status return_initaliztion_mode } function repeat_increment_backup() { if [ $# 0 ]; then exit 0 else if [ $1 repeat ]; then echo ************do database increment backup again************** increment_backup_level_1 $ORACLE_HOME $log $backup_dir echo ************repeat increment backup completed!************** else echo command error,please use parameter repeat exit 0 fi fi }# sync target database backup files # function sync() { ping $target_host -c 1 /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_filels -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 j0 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]} j0 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_statusfind $backup_dir/ -name full_db_*| grep -i full_db |grep -v grep | wc -l 21 level_1_backup_statusls -l --full-time $backup_dir/ |grep -i increment_db_*| grep -i $today_backup|grep -v grep | wc -l 21 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 # ############Todays database backup information########## # check todays backup status # check_backupls -l --full-time $backup_dir/ | egrep -i increment_db_|full_db_ | awk {print $6} | grep -i $today_backup | wc -l # check todays controlfile backup information # control_filels -lt --full-time $controlfile_dir/ | grep -i c-* | grep -i $today_backup | awk {print $6 substr($7,1,8) $9} # check todays increment backup information # backup_file_infols -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_infols -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_infols -lt --full-time $backup_dir/ | egrep -i increment_db_|full_db | awk {print $6 substr($7,1,8) $9} control_filels -lt --full-time $controlfile_dir/ | grep -i c-*| awk {print $6 substr($7,1,8) $9} fi # print todays backup information including controlfile and log information # if [ $check_backup -ge 0 ]; then if [ $1 repeat ] ; then repeat_increment_backup $1 else echo ############Todays database backup information########## if [ $1 all ]; then today_backupls -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 j0 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]} j0 fi done echo $white echo Controlfile information:$yellow echo j0 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]} j0 fi done echo $white echo log information:$blue echo j0 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]} j0 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_status9 fi if [ $backup_status 9 ]; then sync else echo Todays Backup file is synced. please check whether its 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%ddatabase 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_SIDprimaryexport LD_LIBRARY_PATH${ORACLE_HOME}/lib:${ORACLE_HOME}/ctx/libexport PATH$PATH:${ORACLE_HOME}/binexport NLS_LANGAmerican_america.zhs16gbkexport ORACLE_OWNERoracleexport backup_dir/home/oracle/backup/backupexport log/home/oracle/backup/logrsync_dir$backup_dir #sync dircontrolfile_dir$backup_dir/controlfileusernameoracle #target OS ,oracle user passwordgo2north #target oracle user password target_host172.16.5.27today_backupdate %Y-%m-%dtodaydate %Y%m%dwhite$(echo -e \e[39;40m)green$(echo -e \e[36;40m)red$(echo -e \e[31;40m)blue$(echo -e \e[33;40m)backup_status2 #data backup status ,0: backup faild,1: 1 level increment backup,2 : 0 level backup# Begin change restore variablerestore_statustruelast_restore_fileincrement_db_ORCL_76_663449691_1last_restore_date(2008-08-22 19:36)last_recover_time2008-08-28 15:12:08last_restore_time2last_restore_num3# End change restore variable#sed -i /^# Begin change restore variable/,/^# End change restore variable/s/t[0-9]\/t$((t1))/ $0#sed -i s/t[0-9]\/t$((t1))/ $0restore_filels -lt --full-time $backup_dir/ | grep -i increment_db_* | awk {print $9}|head -1controlfilels -lt --full-time $controlfile_dir | grep -i c-* | awk {print $9}| head -1recover_timels -lt --full-time $backup_dir/ | grep -i increment_db_* | awk {print substr($7,1,5)}| head -1recover_date(ls -lt --full-time $backup_dir/ | grep -i increment_db_* | awk {print $6 substr($7,1,5)} | head -1)recover_timesdate %Y-%m-%d %H:%M:%Sfunction update_backup_info(){ sed -i /^# Begin change restore variable/,/^# End change restore variable/s/restore_status.*/restore_statustrue/ $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_time1))/ $0 sed -i /^# Begin change restore variable/,/^# End change restore variable/s/last_restore_num.*/last_restore_num$((last_restore_num1))/ $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_status9 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_statusfalse/ $0 fi return $backup_status}#delete backup file on the weekend again#file_infols -l $backup_dir/ | grep -i increment_backup_*.gz|wc -l#if [ $file_count -gt 7 ]#then# del_filesls -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 Todays sync already completed!echo Last restore file: $backup_dir/$last_restore_fileecho Last restore time: $last_restore_timeecho The number of times the database be restored today: $last_restore_numecho The total number of times the database have ever been restore: $last_recover_timesed -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_time0/ $0sed -i /^# Begin change restore variable/,/^# End change restore variable/s/restore_status.*/restore_statusfalse/ $0sed -i /^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time.*/last_recover_time\${recover_times}\/ $0fi 转载于:https://blog.51cto.com/xin521long/1832844
