RMAN 脚本定时备份数据库

原创

用户8006012

发布于 2022-06-28 16:50:03

1.1K0

发布于 2022-06-28 16:50:03

文章被收录于专栏：DATABASE

Oracle RMAN 在生产环境使用一般都是热备的方式，冷备需要关掉数据库才可以进行，生产环境一般是没有停机时间给你做备份的，所以冷备不太适合生产备份。热备的方式需要打开归档模式！

1、打开数据库归档模式

如果数据库已打开归档模式，执行 archive log list 可以看到以下结果：

[oracle@orcl:/home/oracle]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2022 16:43:13

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                29-JUN-2022 16:43:15
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@orcl:/home/oracle]$ startup
bash: startup: command not found...
[oracle@orcl:/home/oracle]$ clear
[oracle@orcl:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2022 16:43:41

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                29-JUN-2022 16:43:15
Uptime                    0 days 0 hr. 0 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@orcl:/home/oracle]$ sas

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 29 16:43:45 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             402653200 bytes
Database Buffers          822083584 bytes
Redo Buffers                8970240 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archivelog
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL>

如果未打开归档模式，一般结果显示如下：

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             disabled
Archive destination            
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL>

打开数据库归档模式需要重启数据库，完整步骤如下：

-- 设置归档日志路径
alter system set log_archive_dest_1='LOCATION=/archivelog';
-- 关闭数据库，重启至mount模式
shutdown immediate
startup mount
-- 开启归档模式
alter database archivelog;
-- 打开数据库
alter database open;
-- 检查归档模式是否打开
archive log list

如上图，数据库打开归档模式之后，接下来就可以开始部署 RMAN 脚本进行在线备份了。

由于打开归档模式后会源源不断的产生归档日志，所以建议部署一个定期删除归档的任务：

## oracle 用户下执行
SCRIPTSDIR=/home/oracle/scripts
mkdir -p ${SCRIPTSDIR}
{
  echo '#!/bin/bash'
  echo 'source ~/.bash_profile'
  echo 'deltime=`date +"20%y%m%d%H%M%S"`'
  echo "rman target / nocatalog msglog ${SCRIPTSDIR}/del_arch_\${deltime}.log<<EOF"
  echo 'crosscheck archivelog all;'
  echo "delete noprompt archivelog until time 'sysdate-7';"
  echo "delete noprompt force archivelog until time 'SYSDATE-10';"
  echo 'EOF'
} >>${SCRIPTSDIR}/del_arch.sh

chmod +x ${SCRIPTSDIR}/del_arch.sh

## root 用户下执行
SCRIPTSDIR=/home/oracle/scripts
{
  echo "#00 02 * * * ${SCRIPTSDIR}/del_arch.sh"
} >>/var/spool/cron/oracle

2、本地备份脚本

该脚本为增量备份脚本，分为 0 级和 1 级，周日 0 级全备，周一至周六 1 级备份。

首先需要在本地磁盘创建一个备份目录，需要有足够的磁盘空间：

## root 用户下执行
mkdir /backup
chown -R oracle:oinstall /backup
chmod -R 775 /backup

以下脚本直接复制粘贴即可使用，只需注意修改对应的备份路径即可！

周日为 0 级备份脚本：

#!/bin/bash
source ~/.bash_profile
backtime=`date +"20%y%m%d%H%M%S"`
rman target / log=/backup/level0_backup_${backtime}.log<<EOF
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all;
sql"alter system archive log current";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup incremental level 0 database include current controlfile format '/backup/backlv0_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT format '/backup/arch_%d_%T_%t_%s_%p';
release channel c1;
release channel c2;
}
EOF

周一至周六为 1 级备份脚本内容：

#!/bin/bash
source ~/.bash_profile
backtime=`date +"20%y%m%d%H%M%S"`
rman target / log=/backup/level1_backup_${backtime}.log<<EOF
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all;
sql"alter system archive log current";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup incremental level 1 database include current controlfile format '/backup/backlv1_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT format '/backup/arch_%d_%T_%t_%s_%p';
release channel c1;
release channel c2;
}
EOF

脚本创建完之后，记得给脚本赋予可执行权限：

cd /home/oracle/scripts
chmod +x dbbackup_lv0.sh
chmod +x dbbackup_lv1.sh
ls -lrth


sh /home/oracle/scripts/dbbackup_lv0.sh &
sh /home/oracle/scripts/dbbackup_lv1.sh &

3、定时任务

Oracle RMAN 本地备份脚本一般是与定时任务（crontab）配合使用，选择合适的时间段进行备份很重要，建议尽量挑选业务空闲或者负载较低的时间段进行备份。假设凌晨是业务空闲时段，部署定时任务：

## 在 root 用户下执行
su - root
echo "00 00 * * 0 /home/oracle/scripts/dbbackup_lv0.sh" >> /var/spool/cron/oracle
echo "00 00 * * 1,2,3,4,5,6 /home/oracle/scripts/dbbackup_lv1.sh" >> /var/spool/cron/oracle

也可以连接到 RMAN 查看备份详细情况：

rman target /
list backup;

也可以连接到数据库查询视图：

set line222
set pagesize1000
col status for a10
col input_type for a20
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10 
col TIME_TAKEN_DISPLAY for a10
select input_type,
       status,
       to_char(start_time,
               'yyyy-mm-dd hh24:mi:ss'),
       to_char(end_time,
               'yyyy-mm-dd hh24:mi:ss'),
       input_bytes_display,
       output_bytes_display,
       time_taken_display,
       COMPRESSION_RATIO
  from v$rman_backup_job_details
 order by 3 desc;

2、验证备份集

RMAN 也提供了备份集的验证功能，通过简单的几行命令即可验证备份是否可用：

restore validate database;
restore validate spfile;
restore validate controlfile;

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

sql

linux

oracle

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度