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

前言

 https://blog.csdn.net/m0_50546016/article/details/118863062?spm=1001.2014.3001.5501

• 使用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%';

　　

本次分享到此结束啦~

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

​