ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用 RMAN同步数据库

使用 RMAN同步数据库

原创 Linux操作系统 作者:amcaesar 时间:2012-06-18 22:49:27 0 删除 编辑



使用 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

×××××××××××××××××××××××××

###################################################################

# 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14509787/viewspace-733228/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-06-18

  • 博文量
    5
  • 访问量
    16879