[转载]使用 RMAN 同步数据库

http://blog.chinaunix.net/uid-78699-id-3240613.html
使用 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: {<RET>=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="zhaorupeng@126.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_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 << 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 arch=1,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....*********************" 2>&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 << 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' tag="full_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_status=9
    else
        echo "******************level-0 backup databae failed,please contact oracle dba*******"
        backup_status=0
    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_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/sh
    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'`
    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 variable
    restore_status=true
    last_restore_file=increment_db_ORCL_76_663449691_1
    last_restore_date=(2008-08-22 19:36)
    last_recover_time="2008-08-28 15:12:08"
    last_restore_time=2
    last_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))'/' $0
    restore_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    
    fi
    if [ "$1" = "repeat" ] ; then
                   restore_database
    fi
    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}\""'/' $0
    else
    sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_restore_time=.*/last_restore_time=0/' $0
    sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/restore_status=.*/restore_status=false/' $0
    sed -i '/^# Begin change restore variable/,/^# End change restore variable/s/last_recover_time=.*/last_recover_time='"\"${recover_times}\""'/' $0
    fi