自动备份和恢复数据库脚本及实现

说明：

主机IP：192.168.0.131

备机IP：192.168.0.132

 

1、  在主机（131）上创建备份脚本目录及脚本

以下为131上的操作：

[oracle@db_master ~]$ pwd

/home/oracle

[oracle@db_master ~]$ mkdir dbbakscript

[oracle@db_master ~]$ chmod 777 dbbakscript /

[oracle@db_master dbbakscript]$ vi backup_level0.sh                     #0级增量备份

 

#!/bin/sh

source /home/oracle/.bash_profile        #引入oracle环境变量

cd $ORACLE_HOME/bin

rman target  / nocatalog msglog=/home/oracle/dbbakscript/log/rman_bk_level0_`date '+%Y%m%d%H%M%S'`.log <<EOF        

run{

REPORT OBSOLETE;                    #查看过期备份

DELETE force NOPROMPT OBSOLETE;     #删除早期的所有备份

backup incremental level 0 cumulative tag 'db0' database;    #0级增量备份

sql 'alter system archive log current';    #切换当前日志并且自动归档

}

list backup;

crosscheck backup;  

delete noprompt expired backup;  #删除被标记为expired的文件

delete noprompt obsolete; 

exit;

EOF

echo "--------------------backup level0 end--------------------";date

 

[oracle@db_master dbbakscript]$ chmod 755 backup_level0.sh   //赋权

 

[oracle@db_ master dbbakscript]$ vi backup_level1.sh   #1级增量备份

 

#!/bin/sh

source /home/oracle/.bash_profile

cd $ORACLE_HOME/bin

rman target  / nocatalog msglog=/home/oracle/backupscript/log/rman_bk_level1_`date '+%Y%m%d%H%M%S'`.log <<EOF

run{

REPORT OBSOLETE;

DELETE force NOPROMPT OBSOLETE;

backup incremental level 1 cumulative tag 'db1' database;      #1级增量备份

sql 'alter system archive log current';

}

list backup;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

exit;

EOF

echo "--------------------backup level1 end--------------------";date

 

[oracle@db_master dbbakscript]$ chmod 755 backup_level1.sh   //赋权

 

2、编写同步脚本：

[root@db_ master oracle]# vi db_rsync.sh

 

#!/bin/sh

source /home/oracle/.bash_profile     

export ORACLE_BASE=/home/oracle

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db4  

src=/home/oracle/oracle/flash_recovery_area/ORCL/       //主机oracle需同步备份目录

des=oracle@192.168.0.132:/home/oracle/backupdir       //备机oracle接收备份目录 

rsync=/home/oracle/bin/rsync                       

rsync -avz --delete --progress ${src} ${des} &&            //将主机备份目录文件同步到备机

echo "${src} was rsynced"

echo "-----------------------------------------------------"

#done

 

3、设置备份定时任务：

如：

周日0点做0级增量备份

周1至周5每天0点做1级增量备份

每天早上4点将主机备份目录文件同步至备机相应目录中

[oracle@db_master ]$ crontab -l

0 0 * * 0 /home/oracle/dbbakscript/backup_level0.sh > /dev/null 2>&1   

0 0 * * 1-5 /home/oracle/dbbakscript/backup_level1.sh > /dev/null 2>&1

0 4 * * * /home/oracle/db_rsync.sh > /dev/null 2>&1

 

4、在备机（132）上编写自动恢复脚本：

[root@db_ bakup oracle]# vi db_restore.sh

#!/bin/sh

source /home/oracle/.bash_profile

cd $ORACLE_HOME/bin

sqlplus -S /nolog <<EOF

set heading off feedback off pagesize 0 verify off echo off;

conn / as sysdba;

shutdown abort;

exit;

EOF

 

rm -rf /home/oracle/oracle/flash_recovery_area/orcl/autobackup/

rm -rf /home/oracle/oracle/flash_recovery_area/orcl/archivelog/

cp -r /home/oracle/backupdir/autobackup /home/oracle/oracle/flash_recovery_area/orcl/

cp -r /home/oracle/backupdir/archivelog /home/oracle/oracle/flash_recovery_area/orcl/

 

cd $ORACLE_HOME/bin                 

sqlplus -S /nolog <<EOF

set heading off feedback off pagesize 0 verify off echo off;

conn / as sysdba;

startup nomount;

exit;

EOF

      

rman target  / nocatalog <<EOF

restore controlfile to '/home/oracle/oracle/oradata/orcl/control01.ctl' from '/home/oracle/oracle/flash_recovery_area/orcl/autobackup/2010_09_26/o1_mf_s_730686784_69w923pm_.bkp';      # “红色部分”改成你需要恢复的备份集文件

exit;

EOF

 

echo "control file duplicate process..........."

cp  /home/oracle/oracle/oradata/orcl/control01.ctl                                                                 

/home/oracle/oracle/oradata/orcl/control02.ctl

cp /home/oracle/oracle/oradata/orcl/control01.ctl

/home/oracle/oracle/oradata/orcl/control03.ctl     //生成新的控制文件

 

sqlplus -S /nolog <<EOF

set heading off feedback off pagesize 0 verify off echo off;

conn / as sysdba;

alter database mount;

exit;

EOF

 

rman target  / nocatalog <<EOF

restore database;

sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

recover database until time '2010-09-27 06:00:00';    //基于时间点的不完全恢复

exit;

EOF

 

sqlplus -S /nolog <<EOF

set heading off feedback off pagesize 0 verify off echo off;

conn / as sysdba;

alter database open resetlogs;

shutdown immediate;

startup upgrade;

@$ORACLE_HOME/rdbms/admin/utlirp.sql;

shutdown immediate;

startup;

exit;

EOF

 

[oracle@db_backup oracle]$ chmod 755 db_restore.sh   //赋权

 

 

5、恢复过程中所遇到的问题及解决

（1）

SQL>  startup

ORACLE instance started.

Total System Global Area 1241513984 bytes

Fixed Size                  1219136 bytes

Variable Size             318768576 bytes

Database Buffers          905969664 bytes

Redo Buffers               15556608 bytes

Database mounted.

ORA-16038: log 3 sequence# 1 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread 1:

'/home/oracle/oracle/oradata/orcl/redo03.log'

 

解决：

2009-05-31 15:42[oracle@S10 ~]$sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Sun May 31 15:28:06 2009

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 159383552 bytes

Fixed Size                  1279072 bytes

Variable Size              79694752 bytes

Database Buffers           75497472 bytes

Redo Buffers                2912256 bytes

Database mounted.

ORA-16038: log 3 sequence# 34 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread 1: '/home/oracle/oracle/oradata/orcl/redo03.log'

SQL> shutdown

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 159383552 bytes

Fixed Size                  1279072 bytes

Variable Size              79694752 bytes

Database Buffers           75497472 bytes

Redo Buffers                2912256 bytes

Database mounted.

SQL> select group#,sequence# from v$log;

 

    GROUP# SEQUENCE#

---------- ----------

         1         35

         3         34

         2         36

 

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database open;

Database altered.

 

（2）

 

出错日志oradim.log 发现如下报警信息：

 

 

ORA-16038: log 3 sequence# 472 cannot be archived
ORA-19809: limit exceeded for recovery files

 

ORA-00312: online log 3 thread 1: '/home/oracle/oracle/oradata/orcl/redo03.log'

 

 

解决方法：

 

 

1、增大闪回恢复区 db_recovery_dest_size 的值： 

ALTER SYSTEM SET db_recovery_file_dest_size=4g scope=both;    

 

2、将归档设置到其他目录

alter system set log_archive_dest = 其他