大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。

@TOC

前言

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

一、RMAN备份

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

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

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

2 全备脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
	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级增量备份脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
	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级增量备份脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
	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

1
2
3
4
5
6
7
8
9
10
11
12
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
1
2
sqlplus / as sysdba
startup nomount
  • rman恢复控制文件，开启数据库到mount
1
2
3
rman target /
restore controlfile from '/backup/control.bak';
alter database mount;
  • rman 恢复数据库
1
2
3
rman target /
restore database;
recover database;
  • 打开数据库到resetlogs
1
alter database open resetlogs;

2 查看rman恢复进度sql

1
2
3
4
5
6
7
8
9
10
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%';

往期精彩文章

Oracle 一键巡检自动生成 Word 报告
Oracle 一键安装合集
Oracle一键安装脚本的 21 个疑问与解答
Oracle一键巡检脚本的 21 个疑问与解答
全网首发：Oracle 23ai 一键安装脚本（非 RPM）
Oracle 19C 最新 RU 补丁 19.24 ，一键安装！
Oracle Linux 7.9 一键安装 Oracle 19C
RedHat 9.4(aarch64) 一键安装 Oracle 19C
openEuler 22.03 LTS SP4 一键安装 Oracle 19C RAC
RHEL 7.9 一键安装 Oracle 19C 19.23 RAC
Oracle DataGuard GAP 修复手册
优化 Oracle：最佳实践与开发规范
DBA 必备：Linux 软件源配置全攻略
Linux 一键配置时钟同步全攻略

感谢您的阅读，这里是 Lucifer三思而后行，欢迎点赞+关注，我会持续分享数据库知识、运维技巧。
​