前言

• 使用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<

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<

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<

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%';