Oracle RMAN 口令加密测试

Posted on by hackdl

一、 测试背景

业务希望对一些不由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

发起备份

./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;

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

恢复数据

① 不使用密码

报错，符合预期

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

② 使用错误密码

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> set decryption identified by 'W6666666';executing command: SET decryptionRMAN> restore database;Starting restore at 08-NOV-22using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00003 to /data/prd/oracle/database/oradata/orcl/undotbs01.dbfchannel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fh1caked_1_1_20221108.bkpchannel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_fh1caked_1_1_20221108.bkpchannel 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_1423channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00004 to /data/prd/oracle/database/oradata/ORCL/datafile/o1_mf_users_kpkw71x8_.dbfchannel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fi1caked_1_1_20221108.bkpchannel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_fi1caked_1_1_20221108.bkpchannel 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_1423channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to /data/prd/oracle/database/oradata/orcl/sysaux01.dbfchannel ORA_DISK_1: reading from backup piece /data/rmanbackup/Tue/db_orcl_level_1_fg1caked_1_1_20221108.bkpchannel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_fg1caked_1_1_20221108.bkpchannel 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_1423channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /data/prd/oracle/database/oradata/orcl/system01.dbfchannel ORA_DISK_1: reading from backup piece /data服务器托管网/rmanbackup/Tue/db_orcl_level_1_ff1caked_1_1_20221108.bkpchannel ORA_DISK_1: errors found reading piece handle=/data/rmanbackup/Tue/db_orcl_level_1_ff1caked_1_1_20221108.bkpchannel 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_1423channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:45Finished restore at 08-NOV-22RMAN> recover database;Starting recover at 08-NOV-22using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 08-NOV-22RMAN> alter database open;database openedRMAN> 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 > ${RMAN_LOG_FILE}
#finish
exit

参考

SET

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

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

相关推荐: 快手三面全过了，却因为背调时leader手机号造假，导致offer作废了！

这是一个悲伤的故事： 快手本地三面全过了，但因为背调时leader手机号造假，导致offer作废了。 楼主感叹：大家背调填写信息时，一定要慎重再慎重，不要重复他的悲剧！ 网友愤慨，照这么说，那和领导不和的人岂不是进不去了？如果不幸遇到垃圾领导，难道整个职业生涯…

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: