使用 RMAN 同步数据库

最新推荐文章于 2022-07-14 23:08:56 发布

vict_

最新推荐文章于 2022-07-14 23:08:56 发布

阅读量921

点赞数

文章标签： oracle 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: {<RET>=suggested | filename | AUTO | CANCEL}

Cancel <需要手工输入后回车>

Media recovery cancelled.

最后执行resetlogs关键字启动数据库。

SQL> alter database open resetlogs;

Database altered.

备份及同步脚本:
rman_backup_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