【Oracle数据库】RMAN备份恢复脚本分享,附SQL实时查看进度

1,454 阅读1分钟

这是我参与8月更文挑战的第28天,活动详情查看:8月更文挑战

前言

  • 使用rman进行备份恢复时,通过客户端执行记录无法直观看出进度如何,可以通过SQL进行查询。

一、RMAN备份

  • 以下命令,直接复制执行即可。

1 配置备份路径和计划任务

  • 备份路径设置
SCRIPTSDIR=/home/oracle/scripts
BACKUPDIR=/backup
mkdir -p $BACKUPDIR $SCRIPTSDIR
  • 写入计划任务
cat <<EOF>>/var/spool/cron/oracle
30 00 * * 0 ${SCRIPTSDIR}/dblevel0_backup.sh
30 00 * * 1-6 ${SCRIPTSDIR}/dbleve1_backup.sh
EOF

2 全备脚本

{
	echo '#!/bin/sh'
	echo 'source ~/.bash_profile'
	echo 'backtime=`date +"20%y%m%d%H%M%S"`'
	echo "rman target / log=${BACKUPDIR}/full_backup_\${backtime}.log<<EOF"
	echo 'run {'
	echo 'allocate channel c1 device type disk;'
	echo 'allocate channel c2 device type disk;'
	echo 'crosscheck backup;'
	echo 'crosscheck archivelog all; '
	echo 'sql"alter system switch logfile";'
	echo 'delete noprompt expired backup;'
	echo 'delete noprompt obsolete device type disk;'
	echo "backup database include current controlfile format '${BACKUPDIR}/backfull_%d_%T_%t_%s_%p';"
	echo 'backup archivelog all DELETE INPUT format '${BACKUPDIR}/archivelog_%d_%T_%t_%s_%p';'
	echo 'release channel c1;'
	echo 'release channel c2;'
	echo '}'
	echo 'EOF'
} >>${SCRIPTSDIR}/dbbackup_full.sh

注意:全备脚本和增量0级备份等同。

3 增量备份脚本

  • 每周日00:30 做0级增量备份脚本
{
	echo '#!/bin/sh'
	echo 'source ~/.bash_profile'
	echo 'backtime=`date +"20%y%m%d%H%M%S"`'
	echo "rman target / log=${BACKUPDIR}/level0_backup_\${backtime}.log<<EOF"
	echo 'run {'
	echo 'allocate channel c1 device type disk;'
	echo 'allocate channel c2 device type disk;'
	echo 'crosscheck backup;'
	echo 'crosscheck archivelog all; '
	echo 'sql"alter system switch logfile";'
	echo 'delete noprompt expired backup;'
	echo 'delete noprompt obsolete device type disk;'
	echo "backup incremental level 0 database include current controlfile format '${BACKUPDIR}/backlv0_%d_%T_%t_%s_%p';"
	echo 'backup archivelog all DELETE INPUT format '${BACKUPDIR}/archivelog_%d_%T_%t_%s_%p';'
	echo 'release channel c1;'
	echo 'release channel c2;'
	echo '}'
	echo 'EOF'
} >>${SCRIPTSDIR}/dbbackup_lv0.sh
  • 每周一至周六00:30 做1级增量备份脚本
{
	echo '#!/bin/sh'
	echo 'source ~/.bash_profile'
	echo 'backtime=`date +"20%y%m%d%H%M%S"`'
	echo "rman target / log=${BACKUPDIR}/level1_backup_\${backtime}.log<<EOF"
	echo 'run {'
	echo 'allocate channel c1 device type disk;'
	echo 'allocate channel c2 device type disk;'
	echo 'crosscheck backup;'
	echo 'crosscheck archivelog all; '
	echo 'sql"alter system switch logfile";'
	echo 'delete noprompt expired backup;'
	echo 'delete noprompt obsolete device type disk;'
	echo "backup incremental level 1 database include current controlfile format '${BACKUPDIR}/backlv1_%d_%T_%t_%s_%p';"
	echo 'backup archivelog all DELETE INPUT format '${BACKUPDIR}/archivelog_%d_%T_%t_%s_%p';'
	echo 'release channel c1;'
	echo 'release channel c2;'
	echo '}'
	echo 'EOF'
} >>${SCRIPTSDIR}/dbbackup_lv1.sh

4 查看rman备份进度sql

    SELECT sid,
           serial#,
           CONTEXT,
           sofar,
           totalwork,
           round(sofar / totalwork * 100,
                 2) "%_COMPLETE"
      FROM gv$session_longops
     WHERE opname LIKE 'RMAN%'
       AND opname NOT LIKE '%aggregate%'
       AND totalwork != 0
       AND sofar <> totalwork;

二、RMAN恢复

1 恢复脚本

  • 数据库开启到nomount
sqlplus / as sysdba
startup nomount
  • rman恢复控制文件,开启数据库到mount
rman target /
restore controlfile from '/backup/control.bak';
alter database mount;
  • rman 恢复数据库
rman target /
restore database;
recover database;
  • 打开数据库到resetlogs
alter database open resetlogs;

2 查看rman恢复进度sql

SELECT sid,
       serial#,
       CONTEXT,
       sofar,
       totalwork,
       round(sofar / totalwork * 100,
             2) "% Complete"
  FROM v$session_longops
 WHERE opname LIKE 'RMAN:%'
   AND opname NOT LIKE 'RMAN: aggregate%';

本次分享到此结束啦~

如果觉得文章对你有帮助,点赞、收藏、关注、评论,一键四连支持,你的支持就是我创作最大的动力。

​ 技术交流可以 关注公众号:Lucifer三思而后行