Oracle RMAN 口令加密测试

一、测试背景

业务希望对一些不由DBA运维的数据库配置备份设置加密，密码仅由业务同事保存，必须输入密码才能进行数据恢复。

二、 RMAN加密方法

rman对备份提供3种常见的加密方法：

仅口令加密
基于wallet的加密
混合加密（口令加密+wallet加密）

考虑业务同事需要保存密码，而基于wallet的加密配置较复杂、DBA又难以接触到相关服务器，仅口令加密方式较为合适。

设置方法

SET ENCRYPTION IDENTIFIED BY '密码' ONLY;

注意事项：

口令务必要记得，否则后续无法会恢复数据
由于set命令在单个rman会话中生效，不能作为全局设置，因此需要在rman备份脚本中加入加密设置（When used outside a RUN block, attributes changed by SET remain in effect until you exit the RMAN client.）
如果不加引号，密码只能设置为字符串，不能设置为纯数字，例如 SET ENCRYPTION IDENTIFIED BY 6666666 ONLY;

如果不加引号，密码中的字母均会被Oracle转为大写，因此以下3个密码实际是一样的，都被转为 SOMEPWD


IDENTIFIED BY somepwd
IDENTIFIED BY Somepwd
IDENTIFIED BY sOmEpWd

三、口令加密测试

1. 构造测试数据

SQL> create table test(a varchar2(30));
SQL> insert into test values('encrypted');

SQL> select * from test;
A
------------------------------
encrypted

2. 备份测试

在备份脚本中加入口令设置，其余脚本设置项按实际修改即可（完整脚本在最后）

...
$ORACLE_HOME/bin/rman target ${MY_CONNECT} msglog ${RMAN_LOG_FILE} append << EOF
SET ENCRYPTION ON ALGORITHM 'AES128' IDENTIFIED BY 'W6666666' ONLY;
RUN {
...

发起备份

./rmanbackup.sh

查看备份集

[oracle@T01 Tue]$ pwd
/data/rmanbackup/Tue
[oracle@T01 Tue]$ ll -h
total 291M
-rw-r----- 1 oracle oinstall 1.5M Nov  8 14:24 arch_orcl_fk1cakhe_1_1_20221108.bkp
-rw-r----- 1 oracle oinstall 9.9M Nov  8 14:24 c-1647672351-20221108-02
-rw-r----- 1 oracle oinstall 9.9M Nov  8 14:24 c-1647672351-20221108-03
-rw-r----- 1 oracle oinstall 1.2M Nov  8 14:24 ControlFile_orcl_fl1cakhh_1_1_20221108.bkp
-rw-r----- 1 oracle oinstall 183M Nov  8 14:24 db_orcl_level_1_ff1caked_1_1_20221108.bkp
-rw-r----- 1 oracle oinstall  74M Nov  8 14:23 db_orcl_level_1_fg1caked_1_1_20221108.bkp
-rw-r----- 1 oracle oinstall 1.2M Nov  8 14:23 db_orcl_level_1_fh1caked_1_1_20221108.bkp
-rw-r----- 1 oracle oinstall 1.1M Nov  8 14:23 db_orcl_level_1_fi1caked_1_1_20221108.bkp
-rw-r----- 1 oracle oinstall 9.8M Nov  8 14:24 snapcf_orcl.f

3. 模拟宕机

停库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

移走数据文件

[oracle@T01 datafile]$ mv o1_mf_users_kpkw71x8_.dbf o1_mf_users_kpkw71x8_.dbf.bak
[oracle@T01 datafile]$ pwd
/data/prd/oracle/database/oradata/ORCL/datafile

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area 3023110144 bytes
Fixed Size                  2232192 bytes
Variable Size             637534336 bytes
Database Buffers         2365587456 bytes
Redo Buffers               17756160 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4:
'/data/prd/oracle/database/oradata/ORCL/datafile/o1_mf_users_kpkw71x8_.dbf'

SQL> select status from v$instance;

STATUS
------------
MOUNTED

4. 恢复测试

注册恢复目录

# 这里要mv一下，否则catalog 会认不到文件
[oracle@T01 rmanbackup]$ mv Tue/ Tue1108

RMAN> catalog start with '/data/rmanbackup/Tue1108';

using target database control file instead of recovery catalog
searching for all files that match the pattern /data/rmanbackup/Tue1108
List of Files Unknown to the Database
=====================================
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_ff1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_fg1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_fh1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_fi1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/snapcf_orcl.f
File Name: /data/rmanbackup/Tue1108/c-1647672351-20221108-02
File Name: /data/rmanbackup/Tue1108/arch_orcl_fk1cakhe_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/ControlFile_orcl_fl1cakhh_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/c-1647672351-20221108-03

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_ff1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_fg1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_fh1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/db_orcl_level_1_fi1caked_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/snapcf_orcl.f
File Name: /data/rmanbackup/Tue1108/c-1647672351-20221108-02
File Name: /data/rmanbackup/Tue1108/arch_orcl_fk1cakhe_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/ControlFile_orcl_fl1cakhh_1_1_20221108.bkp
File Name: /data/rmanbackup/Tue1108/c-1647672351-20221108-03

恢复数据

① 不使用密码

报错，符合预期

RMAN> restore database;

Starting restore at 08-NOV-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data/prd/oracle/database/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fh1caked_1_1_20221108.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/08/2022 14:27:45
ORA-19870: error while restoring backup piece /data/rmanbackup/Tue1108/db_orcl_level_1_fh1caked_1_1_20221108.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

② 使用错误密码

RMAN> set decryption identified by 'oracle';   

executing command: SET decryption
using target database control file instead of recovery catalog

RMAN> restore database;

Starting restore at 08-NOV-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data/prd/oracle/database/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fh1caked_1_1_20221108.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/08/2022 14:31:10
ORA-19870: error while restoring backup piece /data/rmanbackup/Tue1108/db_orcl_level_1_fh1caked_1_1_20221108.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

③ 使用正确密码

RMAN> set decryption identified by 'W6666666';
executing command: SET decryption

RMAN> restore database;

Starting restore at 08-NOV-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data/prd/oracle/database/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fh1caked_1_1_20221108.bkp
channel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_fh1caked_1_1_20221108.bkp
channel ORA_DISK_1: failover to piece handle=/data/rmanbackup/Tue1108/db_orcl_level_1_fh1caked_1_1_20221108.bkp tag=LEVEL_1_DB_2022_1108_1423
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /data/prd/oracle/database/oradata/ORCL/datafile/o1_mf_users_kpkw71x8_.dbf
channel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fi1caked_1_1_20221108.bkp
channel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_fi1caked_1_1_20221108.bkp
channel ORA_DISK_1: failover to piece handle=/data/rmanbackup/Tue1108/db_orcl_level_1_fi1caked_1_1_20221108.bkp tag=LEVEL_1_DB_2022_1108_1423
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /data/prd/oracle/database/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fg1caked_1_1_20221108.bkp
channel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_fg1caked_1_1_20221108.bkp
channel ORA_DISK_1: failover to piece handle=/data/rmanbackup/Tue1108/db_orcl_level_1_fg1caked_1_1_20221108.bkp tag=LEVEL_1_DB_2022_1108_1423
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/prd/oracle/database/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_ff1caked_1_1_20221108.bkp
channel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_ff1caked_1_1_20221108.bkp
channel ORA_DISK_1: failover to piece handle=/data/rmanbackup/Tue1108/db_orcl_level_1_ff1caked_1_1_20221108.bkp tag=LEVEL_1_DB_2022_1108_1423
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 08-NOV-22

RMAN> recover database;

Starting recover at 08-NOV-22
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-NOV-22

RMAN> alter database open;
database opened

RMAN> 
Recovery Manager complete.

⑤ 验证数据

SQL> select * from test;

A
------------------------------
encrypted

四、备份脚本

默认口令加密那行是注释的，需要用取消掉即可


export ORACLE_SID=mydb
export MY_CONNECT='/'
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export MY_BACKUP_DIR=/rmanbackup
export MY_OBSOLETE_DAYS=30
export MY_KEEP_DAYS=8
# 限制备份速度
#export MY_RATE=100M
###############################################
export MY_DATE=`date +%Y_%m%d_%H%M`
export HOST_NAME=`hostname`
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
if [ ! -d ${MY_BACKUP_DIR} ]
        then
        mkdir -p ${MY_BACKUP_DIR}
fi
os=`uname`
echo $os
export TEST_FILE=${MY_BACKUP_DIR}/test.file
echo 'test' > ${TEST_FILE}
# 日期转为英文格式
export MY_WEEK=`env LANG=en_US.UTF-8 date | awk '{print $1}'`
case ${MY_WEEK} in
        Sat)
        export BACKUP_TYPE="INCREMENTAL LEVEL=2 CUMULATIVE "
                ;;
        Sun)
        export BACKUP_TYPE="INCREMENTAL LEVEL=0"
                ;;
        Mon)
        export BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
                ;;
        Tue)
        export BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
                ;;
        Wed)
        export BACKUP_TYPE="INCREMENTAL LEVEL=2 CUMULATIVE"
                ;;
        Thu)
        export BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
                ;;
        Fri)
        export BACKUP_TYPE="INCREMENTAL LEVEL=1 CUMULATIVE"
                ;;
        *)
        echo 'RMAN-MY_WEEK IS ERROR' > /dev/null
        exit
                ;;
esac
export MY_LEVEL=`echo ${BACKUP_TYPE} |  awk -F"=" '{print $2}'`
export MY_LEVEL_NUMBER=`echo ${MY_LEVEL} |awk '{print $1}'`
export RMAN_LOG_FILE=${MY_BACKUP_DIR}/rman_log/rman_${MY_DATE}_level_${MY_LEVEL_NUMBER}.log
if [ ! -d ${MY_BACKUP_DIR}/rman_log ]
        then
        mkdir -p ${MY_BACKUP_DIR}/rman_log
fi
if [ ! -d ${MY_BACKUP_DIR}/${MY_WEEK} ]
        then
        mkdir -p ${MY_BACKUP_DIR}/${MY_WEEK}
fi
 
 
# Starting
echo 'I Started' > ${RMAN_LOG_FILE}
chmod 666 ${RMAN_LOG_FILE}
echo ==== started on `date` ==== >> ${RMAN_LOG_FILE}
echo ==== '                '==== >> ${RMAN_LOG_FILE}
echo ==== BACKUP_TYPE IS ${BACKUP_TYPE} ==== >> ${RMAN_LOG_FILE}
env >> ${RMAN_LOG_FILE}
$ORACLE_HOME/bin/rman target ${MY_CONNECT} msglog ${RMAN_LOG_FILE} append << EOF
# 口令加密
# SET ENCRYPTION ON ALGORITHM 'AES128' IDENTIFIED BY 'W6666666' ONLY;
RUN {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${MY_OBSOLETE_DAYS} DAYS ;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${MY_BACKUP_DIR}/${MY_WEEK}/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${MY_BACKUP_DIR}/${MY_WEEK}/snapcf_${ORACLE_SID}.f';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '${MY_BACKUP_DIR}/${MY_WEEK}/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
# 限制备份速度
#ALLOCATE CHANNEL ch00 DEVICE TYPE DISK RATE=${MY_RATE};
#ALLOCATE CHANNEL ch01 DEVICE TYPE DISK RATE=${MY_RATE};
#ALLOCATE CHANNEL ch02 DEVICE TYPE DISK RATE=${MY_RATE};
#ALLOCATE CHANNEL ch03 DEVICE TYPE DISK RATE=${MY_RATE};
ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch02 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch03 DEVICE TYPE DISK;
        BACKUP AS COMPRESSED BACKUPSET
                $BACKUP_TYPE
                #SKIP INACCESSIBLE
                TAG level_${MY_LEVEL_NUMBER}_db_${MY_DATE}
                FILESPERSET 10
                FORMAT '${MY_BACKUP_DIR}/${MY_WEEK}/db_${ORACLE_SID}_level_${MY_LEVEL_NUMBER}_%U_%T'
        DATABASE
        ;
        SQL 'ALTER SYSTEM CHECKPOINT';
        SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
#ALLOCATE CHANNEL ch00 DEVICE TYPE DISK RATE=${MY_RATE};
ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
        BACKUP
                TAG arch_${MY_DATE}
                FORMAT '${MY_BACKUP_DIR}/${MY_WEEK}/arch_${ORACLE_SID}_%U_%T'
        ARCHIVELOG  ALL;
        BACKUP
                TAG controlfile_${MY_DATE}
                FORMAT '${MY_BACKUP_DIR}/${MY_WEEK}/ControlFile_${ORACLE_SID}_%U_%T'
        CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${MY_OBSOLETE_DAYS} DAYS ;
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-${MY_KEEP_DAYS}';
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-8';
LIST BACKUP SUMMARY;
LIST BACKUP TAG 'level_${MY_LEVEL_NUMBER}_db_${MY_DATE}';
LIST BACKUP TAG 'arch_${MY_DATE}';
LIST BACKUP TAG 'controlfile_${MY_DATE}';
EOF
echo ==== $LOGMSG stop on `date` ==== >> ${RMAN_LOG_FILE}
#finish
exit

参考

SET

Configuring the RMAN Environment: Advanced Topics - 11g Release 2 (11.2)

相关阅读:
LeetCode算法动态规划—剑指 Offer 10- II. 青蛙跳台阶问题
 1089 Insert or Merge
我想进阿里：通宵达旦三个月，学了这些技术点（附Java思维导图）
信息服务上线渗透检测网络安全检查报告和解决方案
 《Mybatis 手撸专栏》第7章：SQL执行器的定义和实现
 Java：使用 Graphics2D 类来绘制图像
 猿创征文｜手把手玩转docker，从入门到精通
 虚拟路由冗余协议_VRRP
Maven3种打包方式之一maven-assembly-plugin的使用
 mysql服务器数据同步
原文地址：https://blog.csdn.net/Hehuyi_In/article/details/127752006