一、 测试背景
业务希望对一些不由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
参考
Configuring the RMAN Environment: Advanced Topics - 11g Release 2 (11.2)