oracle12c 使用软件keystore完成数据库透明加密备份

郑全

1.创建keystore目录
   keystore目录按以下顺序去查找：
   
   sqlnet.ora的encryption_wallet_location参数
   sqlnet.ora的wallet_location参数
   $ORACLE_BASE/admin/db_unique_name/wallet
   $ORACLE_HOME/admin/db_unique_name/wallet
   如果要配置 sqlnet.ora，具体如下：
    vim sqlnet.ora
   ENCRYPTION_WALLET_LOCATION =
      (SOURCE =
         (METHOD = FILE)
         (METHOD_DATA =
              (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)
         )
       )
   我们这里直接使用$ORACLE_BASE/admin/db_unique_name/wallet,这个位置，可以在
   v$encryption_wallet 中查找
   SQL> select wrl_parameter FROM v$encryption_wallet;
   WRL_PARAMETER
   --------------------------------------------------------------------------------
   /u01/app/oracle/admin/orcl/wallet
   手工建立该目录即可
   mkdir -p /u01/app/oracle/admin/orcl/wallet
2.使用syskm/sysdba 登陆 数据库
  sql>conn / as syskm;
  sql>show user
  SQL> conn / as syskm
  Connected.
  SQL> show user
  USER is "SYSKM"
  SQL>
3.创建软件keystore文件
  administer key management create keystore
    '/u01/app/oracle/admin/orcl/wallet'
    identified by keystore_password;
   SQL> administer key management create keystore
     2    '/u01/app/oracle/admin/orcl/wallet'           
     3    identified by oracle_4U;
     keystore altered.
     --验证一下keystore文件创建
     SQL> ! ls -ltr /u01/app/oracle/admin/orcl/wallet
     total 4
     -rw-r--r-- 1 oracle asmadmin 2408 Aug 17 11:31 ewallet.p12
     SQL> SELECT wrl_parameter,status FROM v$encryption_wallet;
     WRL_PARAMETER                                                          STATUS
     ------------------------------------------------------         ---------------------------
     /u01/app/oracle/admin/orcl/wallet                                CLOSED
4.打开软件keystore 文件
     administer key management set keystore open
        identified by keystore_password;      --这里的密码为上面创建的密码
    SQL>  administer key management set keystore open
    2     identified by oracle_4U
    3  ;
    keystore altered.

    --验证keystore打开
    SQL> SELECT wrl_parameter,status FROM v$encryption_wallet;
     WRL_PARAMETER                                    STATUS
     --------------------------------------------  ----------------
     /u01/app/oracle/admin/orcl/wallet           OPEN_NO_MASTER_KEY
     
5.创建万能加密 key
    administer key management set key
             identified by keystore_password
             with backup using 'for_12c';
    SQL> administer key management set key
     2     identified by oracle_4U
     3     with backup using 'for_12c';
     keystore altered.
      SQL> SELECT wrl_type,wrl_parameter,status FROM v$encryption_wallet;
      WRL_PARAMETER                                    STATUS
      -------------------------------------------  ------------------------------
      /u01/app/oracle/admin/orcl/wallet          OPEN
   
      看到这个keystore正常打开了。
     
6.备份keystore
   administer key management backup keystore
       identified by keystore_password;
   SQL> administer key management backup keystore
    2     identified by oracle_4U;
    keystore altered.
   --查看文件
    SQL> ! ls -ltr /u01/app/oracle/admin/orcl/wallet
     total 16
    -rw-r--r-- 1 oracle asmadmin 2408 Aug 17 11:37 ewallet_2019081703370681_for_12c.p12
    -rw-r--r-- 1 oracle asmadmin 3848 Aug 17 11:38 ewallet_2019081703382706.p12
    -rw-r--r-- 1 oracle asmadmin 4784 Aug 17 11:38 ewallet.p12
7.备份设置
   RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;
   new RMAN configuration parameters:
   CONFIGURE ENCRYPTION FOR DATABASE ON;
   new RMAN configuration parameters are successfully stored
   
   打开加密
8.备份
   RMAN> backup tablespace users;
Starting backup at 17-AUG-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.259.1016473575
channel ORA_DISK_1: starting piece 1 at 17-AUG-19
channel ORA_DISK_1: finished piece 1 at 17-AUG-19
piece handle=+FRA/ORCL/BACKUPSET/2019_08_17/nnndf0_tag20190817t114521_0.260.1016538321 tag=TAG20190817T114521 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-AUG-19

   可以看到喝平时备份没有什么区别，不用修改备份脚本
9.测试恢复
   --重启数据库
   sql>startup force;
   
   --发现keystore被关闭
   SQL> SELECT wrl_parameter,status FROM v$encryption_wallet;
    WRL_PARAMETER                                   STATUS
    --------------------------------------------  ------------------------------
    /u01/app/oracle/admin/orcl/wallet           CLOSED
    --进行恢复
    RMAN> restore tablespace users;
Starting restore at 17-AUG-19
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 00006 to +DATA/ORCL/DATAFILE/users.259.1016473575
channel ORA_DISK_1: reading from backup piece +FRA/ORCL/BACKUPSET/2019_08_17/nnndf0_tag20190817t114521_0.260.1016538321
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/17/2019 11:52:14
ORA-19870: error while restoring backup piece +FRA/ORCL/BACKUPSET/2019_08_17/nnndf0_tag20190817t114521_0.260.1016538321
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open
      我们发现提示wallet没有打开
     --手工打开keystore
     SQL>  administer key management set keystore open
     2      identified by oracle_4U;
     keystore altered.
     --已经打开
      SQL> SELECT wrl_type,wrl_parameter,status FROM v$encryption_wallet;
      WRL_PARAMETER                                    STATUS
      -------------------------------------------  ------------------------------
      /u01/app/oracle/admin/orcl/wallet          OPEN

     --进行恢复
     RMAN> restore tablespace users;
Starting restore at 17-AUG-19
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 00006 to +DATA/ORCL/DATAFILE/users.259.1016473575
channel ORA_DISK_1: reading from backup piece +FRA/ORCL/BACKUPSET/2019_08_17/nnndf0_tag20190817t114521_0.260.1016538321
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/17/2019 11:52:35
ORA-19870: error while restoring backup piece +FRA/ORCL/BACKUPSET/2019_08_17/nnndf0_tag20190817t114521_0.260.1016538321
ORA-19573: cannot obtain exclusive enqueue for datafile 6
RMAN>

     --可以看到，目前可以恢复了。