There is a new feature that was added to 12.2 of RMAN that allows you to restore tablespaces or a whole database "AS ENCRYPTED" or "AS DECRYPTED".
There is documentation of this feature here for 18c, but for some reason it isn't mentioned as a new feature for 12.2, or documented in 12.2.
Now I am going to demonstrate how to use this.
I have a database (bsg) which is a 12.2 database. I have created an encryption wallet, but none of the datafiles are encrypted.
You can see this from the dba_tablespaces view.
Now I ran a Full backup of my database
There is documentation of this feature here for 18c, but for some reason it isn't mentioned as a new feature for 12.2, or documented in 12.2.
Now I am going to demonstrate how to use this.
I have a database (bsg) which is a 12.2 database. I have created an encryption wallet, but none of the datafiles are encrypted.
You can see this from the dba_tablespaces view.
[oracle@oracle-server BSG]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 15:37:52 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
SQL>
Now I ran a Full backup of my database
[oracle@oracle-server BSG]$ rman
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Apr 8 14:43:50 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: BSG (DBID=589892482)
RMAN> backup incremental level 0 database;
I have a full backup and now I am going to restore the tablespace users as encrypted.
NOTE : I am using "RESTORE TABLESPACE xxx AS ENCRYPTED";
[oracle@oracle-server BSG]$ rman
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Apr 8 14:43:50 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: BSG (DBID=589892482)
RMAN> alter tablespace users offline;
Statement processed
RMAN> restore tablespace users as encrypted;
Starting restore at 04/08/19 14:53: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 00007 to /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/fast_recovery_area/bsg/BSG/backupset/2019_04_08/o1_mf_nnnd0_TAG20190408T144422_gbq5n71p_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/fast_recovery_area/bsg/BSG/backupset/2019_04_08/o1_mf_nnnd0_TAG20190408T144422_gbq5n71p_.bkp tag=TAG20190408T144422
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 04/08/19 14:53:23
RMAN> recover tablespace users;
Starting recover at 04/08/19 14:53:44
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 04/08/19 14:53:44
RMAN> alter tablespace users online;
Statement processed
RMAN> exit
Now that I restored the tablespace and recovered it let's see if it is encrypted in the catalog.
[oracle@oracle-server BSG]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 14:54:13 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS YES
Now to backup the tablespace. Since it is newly encrypted, I need to make sure I perform a new Level 0 incremental backup to capture the encrypted datafiles.
[oracle@oracle-server datafile]$ rman
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Apr 8 16:12:08 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: BSG (DBID=589892482)
RMAN> backup incremental level 0 tablespace users;
Starting backup at 04/08/19 16:12:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
channel ORA_DISK_1: starting piece 1 at 04/08/19 16:12:26
channel ORA_DISK_1: finished piece 1 at 04/08/19 16:12:27
piece handle=/home/oracle/app/oracle/fast_recovery_area/bsg/BSG/backupset/2019_04_08/o1_mf_nnnd0_TAG20190408T161226_gbqbsbmv_.bkp tag=TAG20190408T161226 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04/08/19 16:12:27
Starting Control File and SPFILE Autobackup at 04/08/19 16:12:27
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_DISK_1 channel at 04/08/2019 16:12:28
ORA-00237: snapshot operation disallowed: control file newly created
Now the backup was successful, but the autobackup of the controlfile failed.
There is a MOS note on this (Doc ID 2320446.1)
You cannot backup the controlfile until the database is bounced because there is a data dictionary check pending. Once I bounce the database all is good.
Now now let's try it the other way..
RESTORE TABLESPACE xxx AS DECRYPTED;
[oracle@oracle-server trace]$ rman
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Apr 8 16:29:30 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: BSG (DBID=589892482)
RMAN> alter tablespace users offline;
using target database control file instead of recovery catalog
Statement processed
RMAN> restore tablespace users as decrypted;
Starting restore at 04/08/19 16:29:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 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 00007 to /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/fast_recovery_area/bsg/BSG/backupset/2019_04_08/o1_mf_nnnd0_TAG20190408T162748_gbqcp4vg_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/fast_recovery_area/bsg/BSG/backupset/2019_04_08/o1_mf_nnnd0_TAG20190408T162748_gbqcp4vg_.bkp tag=TAG20190408T162748
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 04/08/19 16:29:45
RMAN> recover tablespace users;
Starting recover at 04/08/19 16:29:54
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 04/08/19 16:29:54
RMAN> alter tablespace users online;
Statement processed
RMAN>
Finally let's double check.
[oracle@oracle-server trace]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 16:31:15 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO