Lets make some quick tutorial about how to set TDE in a Multitenant 12.1c system
We are using today the system in 12.1 called cdb121 (I guess that a good clue about the system configuration already…)
DB_NAME INSTANCE_NAME CDB HOST_NAME STARTUP DATABASE_ROLE OPEN_MODE STATUS
--------- -------------------- --- ------------------------------ ---------------------------------------- ---------------- -------------------- ------------
CDB121 cdb1211 YES rac5-node1.raclab.local 02-MAR-2019 17:39:48 PRIMARY READ WRITE OPEN
CDB121 cdb1212 YES rac5-node2.raclab.local 02-MAR-2019 17:39:52 PRIMARY READ WRITE OPEN
INST_ID CON_ID NAME OPEN_MODE OPEN_TIME STATUS
---------- ---------- -------------------- ---------- ---------------------------------------- ---------
1 2 PDB$SEED READ ONLY 02-MAR-19 05.40.16.122 PM +00:00 NORMAL
2 2 PDB$SEED READ ONLY 02-MAR-19 05.40.15.788 PM +00:00 NORMAL
1 3 PDB1 READ WRITE 02-MAR-19 05.41.18.784 PM +00:00 NORMAL
2 3 PDB1 READ WRITE 02-MAR-19 05.41.34.222 PM +00:00 NORMAL
1 4 PDB2 READ WRITE 02-MAR-19 05.42.35.452 PM +00:00 NORMAL
2 4 PDB2 READ WRITE 02-MAR-19 05.42.46.843 PM +00:00 NORMAL
6 rows selected.Create Keystore
We are starting the encryption from scratch, so lets confirm we have not keys set already:
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/cdb121/wallet NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>
sys@cdb1211>alter session set container=PDB1;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/cdb121/wallet NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>alter session set container=PDB2;
Session altered.
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/cdb121/wallet NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>Ok, we are ready.
We are creating the keystore into +ASM. On this case, we are using diskgroup DATA_DB, so lets create a directory where the keystore will reside
[oracle@rac5-node1 ~]$ asmcmd -p
ASMCMD [+] > cd data_db
ASMCMD [+data_db] > cd CDB121
ASMCMD [+data_db/CDB121] > ls
8320CDF38E440B67E055000000000001/
832104B5BE3055F0E055000000000001/
8321098DB16D5B60E055000000000001/
CONTROLFILE/
DATAFILE/
FD9AC20F64D244D7E043B6A9E80A2F2F/
ONLINELOG/
PARAMETERFILE/
PASSWORD/
TEMPFILE/
ASMCMD [+data_db/CDB121] > mkdir WALLET
ASMCMD [+data_db/CDB121] > ls
8320CDF38E440B67E055000000000001/
832104B5BE3055F0E055000000000001/
8321098DB16D5B60E055000000000001/
CONTROLFILE/
DATAFILE/
FD9AC20F64D244D7E043B6A9E80A2F2F/
ONLINELOG/
PARAMETERFILE/
PASSWORD/
TEMPFILE/
WALLET/
ASMCMD [+data_db/CDB121] >Lets now create the keystore
sys@cdb1211>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA_DB/CDB121/WALLET' IDENTIFIED BY "Welcome1";
keystore altered.
sys@cdb1211>As soon as we execute the command, we can see a file created in the ASM location
ASMCMD [+data_db/CDB121/WALLET] > ls
ewallet.p12
ASMCMD [+data_db/CDB121/WALLET] >See how we still dont see it though…
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- -------------------------------------- -------------- ------------- --------- --------- ----------
FILE /u01/app/oracle/admin/cdb121/wallet NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>We have to add the wallet to the file "$ORACLE_HOME/network/admin/sqlnet.ora" of each node
Some advice from Tim Hall about this:
https://oracle-base.com/articles/12c/multitenant-transparent-data-encryption-tde-12cr1
Keystores should not be shared between CDBs, so if multiple CDBs are
run from the same ORACLE_HOME you must do one of the following to keep
them separate.Use the default keystore location, so each CDB database has its own keystore.
Specify the location using the $ORACLE_SID.ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)))Have a separate "sqlnet.ora" for each database, making sure the TNS_ADMIN variable is set correctly.
Make sure is added in both nodes
[oracle@rac5-node1 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
## COMMON WALLET LOCATION
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA_DB/CDB121/WALLET)
)
)
[oracle@rac5-node1 ~]$
[oracle@rac5-node2 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
## COMMON WALLET LOCATION
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA_DB/CDB121/WALLET)
)
)
[oracle@rac5-node2 ~]$Get into the system again and check if it is there now
sys@cdb1211> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0
sys@cdb1212> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0Lets open it.
Note we only execute the command from 1 of the instances, but the change will affect both of them
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1;
keystore altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
sys@cdb1211>
sys@cdb1212>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
sys@cdb1212>What about our PDBs?
well, we didnt specified 'container=all' during the open statement so they are closed
sys@cdb1211>alter session set container=PDB1;
Session altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>alter session set container=PDB2;
Session altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>Lets try again, but remember to close it first (or you will get the error below) and then open it in all containers
sys@cdb1211>alter session set container=CDB$ROOT;
Session altered.
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1 CONTAINER=ALL;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1 CONTAINER=ALL
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY Welcome1;
keystore altered.
sys@cdb1211>
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1 CONTAINER=ALL;
keystore altered.
sys@cdb1211>
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
sys@cdb1211>alter session set container=PDB1;
Session altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
sys@cdb1211>alter session set container=PDB2;
Session altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
sys@cdb1211>Create a Key
We got the Keystore created and open in all containers.. but we dont have a key yet.. Lets create one "with backup".
More info regarding "WITH BACKUP" clause:
https://docs.oracle.com/database/121/SQLRF/statements_1003.htm#BGEIBFFBNotes on the WITH BACKUP Clause Many ADMINISTER KEY MANAGEMENT
operations include the WITH BACKUP clause. This clause applies only to
password-based software keystores. It indicates that the keystore must
be backed up before the operation is performed. Therefore, you must
either specify the WITH BACKUP clause when performing the operation,
or issue the ADMINISTER KEY MANAGEMENT backup_clause statement
immediately before performing the operation.When you specify the WITH BACKUP clause, Oracle Database creates a
backup file with a name of the form ewallet_timestamp.p12, where
timestamp is the file creation timestamp in UTC format. The backup
file is created in the same directory as the keystore you are backing
up.The optional USING 'backup_identifier' clause lets you specify a
backup identifier, which is added to the backup file name. For
example, if you specify a backup identifier of 'Backup1', then Oracle
Database creates a backup file with a name of the form
ewallet_timestamp_Backup1.p12.
Remember to use 'CONTAINER=ALL' clause so we add it to all PDBs
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome1 WITH BACKUP CONTAINER=ALL;
keystore altered.
sys@cdb1211>
select * from V$ENCRYPTION_WALLET ;sys@cdb1211>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1211>
sys@cdb1212>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0Note how the system has generated a new file (backup) in ASM location and we can also see the "keys" in the Database
ASMCMD [+data_db/cdb121/wallet] > ls
ewallet.p12
ewallet_2019030313202649.p12
ASMCMD [+data_db/cdb121/wallet] >
# We can see the 3 'Keys' from CDB$ROOT:
sys@cdb1212>SELECT con_id, key_id FROM v$encryption_keys;
CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 AYSFd/NUeE86v46qA6o2K8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
0 Ad527VZdj09xv2B5Z79HNjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
0 ASs87Jao10+Ov9B9YsOGdvUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
sys@cdb1212>
sys@cdb1212>alter session set container=PDB1;
Session altered.
sys@cdb1212>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1212>SELECT con_id, key_id FROM v$encryption_keys;
CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 Ad527VZdj09xv2B5Z79HNjYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
sys@cdb1212>
sys@cdb1212>alter session set container=PDB2;
Session altered.
sys@cdb1212>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1212>SELECT con_id, key_id, status FROM v$encryption_keys;
CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 AYSFd/NUeE86v46qA6o2K8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Ok, we have the Keystore and the key already set, lets create Encrypted column.
We will create a new user 'P42' in PDB2 which will be the one creating a 'test' table with encrypted column
sys@cdb1211>ALTER SESSION SET CONTAINER = pdb2;
Session altered.
sys@cdb1211>CREATE TABLESPACE P42;
Tablespace created.
sys@cdb1211>alter tablespace P42 online;
Tablespace altered.
sys@cdb1211>CREATE USER P42
IDENTIFIED BY Welcome1
DEFAULT TABLESPACE P42
TEMPORARY TABLESPACE TEMP
QUOTA 200M on P42; 2 3 4 5
User created.
sys@cdb1211>GRANT CONNECT TO P42;
Grant succeeded.
sys@cdb1211>grant create session, create procedure,create table to P42;
Grant succeeded.
sys@cdb1211> CONN P42/Welcome1@pdb2
pdb2>CREATE TABLE test (
id NUMBER(10),
data VARCHAR2(50) ENCRYPT
); 2 3 4
Table created.
pdb2>INSERT INTO test VALUES (1, 'This is a secret!');
1 row created.
pdb2>COMMIT;
Commit complete.
pdb2>
# Check the data from the table:
pdb2>select * from p42.test;
ID DATA
---------- --------------------------------------------------
1 This is a secret!
pdb2>
# Check from the Container root all Encrypted columns in all PDBs:
sys@cdb1212>select d.NAME, e.table_name, e.column_name, e.encryption_alg
from cdb_encrypted_columns e, v$pdbs d
where d.CON_ID = e.CON_ID;
NAME TABLE_NAME COLUMN_NAM ENCRYPTION_ALG
------------------------------ ---------- ---------- --------------------------------------------------
PDB2 TEST DATA AES 192 bits key
sys@cdb1212>Lets close the Keystore and confirm we can not access to the data
sys@cdb1211>ALTER SESSION SET CONTAINER =PDB2;
Session altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1211>select * from p42.test;
ID DATA
---------- --------------------------------------------------
1 This is a secret!
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY Welcome1;
keystore altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>
sys@cdb1211>select * from p42.test;
select * from p42.test
*
ERROR at line 1:
ORA-28365: wallet is not openWhat if we restart the system? Well, on this case, we can have some issues, since we will need to re-open the keystore manually:
[oracle@rac5-node1 ~]$ srvctl stop database -d cdb121 ; srvctl start database -d cdb121
[oracle@rac5-node1 ~]$ sqlplus / as sysdba
DB_NAME INSTANCE_NAME CDB HOST_NAME STARTUP DATABASE_ROLE OPEN_MODE STATUS
--------- -------------------- --- ------------------------------ ---------------------------------------- ---------------- -------------------- ------------
CDB121 cdb1211 YES rac5-node1.raclab.local 03-MAR-2019 15:03:12 PRIMARY READ WRITE OPEN
CDB121 cdb1212 YES rac5-node2.raclab.local 03-MAR-2019 15:03:13 PRIMARY READ WRITE OPEN
sys@cdb1211>sys@cdb1211>sys@cdb1211> 2 3 4
INST_ID CON_ID NAME OPEN_MODE OPEN_TIME STATUS
---------- ---------- -------------------- ---------- ---------------------------------------- ---------
1 2 PDB$SEED READ ONLY 03-MAR-19 03.03.36.698 PM +00:00 NORMAL
2 2 PDB$SEED READ ONLY 03-MAR-19 03.03.37.895 PM +00:00 NORMAL
1 3 PDB1 READ WRITE 03-MAR-19 03.04.47.321 PM +00:00 NORMAL
2 3 PDB1 READ WRITE 03-MAR-19 03.04.46.919 PM +00:00 NORMAL
1 4 PDB2 READ WRITE 03-MAR-19 03.04.40.101 PM +00:00 NORMAL
2 4 PDB2 READ WRITE 03-MAR-19 03.04.39.694 PM +00:00 NORMAL
6 rows selected.
sys@cdb1211>
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 0
sys@cdb1211>
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1 CONTAINER=ALL;
keystore altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET ;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1211>Encrypted Backup with RMAN with Autologin Keystore
Lets add Encryption in the RMAN configuration
[oracle@rac5-node1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 3 15:12:40 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB121 (DBID=3962082852)
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name CDB121 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_cdb1211.f'; # default
RMAN>Let's Backup the Database (Spoiler alert: Will fail)
RMAN> backup database;
Starting backup at 03-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 instance=cdb1211 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=00001 name=+DATA_DB/CDB121/DATAFILE/system.345.1001870545
input datafile file number=00003 name=+DATA_DB/CDB121/DATAFILE/sysaux.271.1001870465
[.....]
input datafile file number=00005 name=+DATA_DB/CDB121/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.315.1001870713
channel ORA_DISK_1: starting piece 1 at 03-MAR-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/03/2019 15:27:04
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open
RMAN>The wallet is open, but RMAN is not able to use it
To avoid this issue and also to make sure the Wallet is open by default when system is restarted, lets enable AUTOLOGIN to our Keystore:
More information on AUTO_LOGIN KEYSTORE:
https://docs.oracle.com/database/121/SQLRF/statements_1003.htm#SQLRF55976CREATE [ LOCAL ] AUTO_LOGIN KEYSTORE Specify this clause to create an
auto-login software keystore. An auto-login software keystore is
created from an existing password-based software keystore. The
auto-login keystore has a system-generated password. It is stored in a
PKCS#12-based file named cwallet.sso in the same directory as the
password-based software keystore.By default, Oracle creates an auto-login keystore, which can be opened from computers other than the computer on which the keystore
resides. If you specify the LOCAL keyword, then Oracle Database
creates a local auto-login keystore, which can be opened only from the
computer on which the keystore resides.For keystore_location, specify the full path name of the directory in which the existing password-based software keystore resides. The
password-based software keystore can be open or closed.For keystore_password, specify the password for the existing password-based software keystore.
sys@cdb1211>set lines 500
col WRL_PARAMETER for a30
select * from V$ENCRYPTION_WALLET ;sys@cdb1211>sys@cdb1211>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1211>ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA_DB/CDB121/WALLET/' IDENTIFIED BY "Welcome1";
keystore altered.
sys@cdb1211>
# We can see the autologin file is created in the wallet location:
ASMCMD [+data_db/CDB121/wallet] > ls
cwallet.sso
ewallet.p12
ewallet_2019030313202649.p12
ASMCMD [+data_db/CDB121/wallet] >In order to enable the Keystore Autologin, we just need for the system to reload the keystore so it reads the "Autogin file – cwallet.sso"
sys@cdb1211>select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
sys@cdb1211>ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA_DB/CDB121/WALLET/' IDENTIFIED BY "Welcome1";
keystore altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN PASSWORD SINGLE NO 0
# To reload the Keystore, we just try to close it. That should be enough
sys@cdb1211>ADMINISTER KEY MANAGEMENT SET KEYSTORE Close IDENTIFIED BY Welcome1 CONTAINER=ALL;
keystore altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN AUTOLOGIN SINGLE NO 0
sys@cdb1211>ALTER SESSION SET CONTAINER = pdb2;
Session altered.
sys@cdb1211>select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN AUTOLOGIN SINGLE NO 0
sys@cdb1211>
# Same in second instance
sys@cdb1212>select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
ASM +DATA_DB/CDB121/WALLET/ OPEN AUTOLOGIN SINGLE NO 0
sys@cdb1212>Lets try the Backup now and confirm RMAN can encrypt it since the wallet is set in Autologin
[oracle@rac5-node1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 3 15:47:21 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB121 (DBID=3962082852)
RMAN> backup database;
Starting backup at 03-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 instance=cdb1211 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=00001 name=+DATA_DB/CDB121/DATAFILE/system.345.1001870545
input datafile file number=00003 name=+DATA_DB/CDB121/DATAFILE/sysaux.271.1001870465
input datafile file number=00004 name=+DATA_DB/CDB121/DATAFILE/undotbs1.344.1001870641
input datafile file number=00008 name=+DATA_DB/CDB121/DATAFILE/undotbs2.256.1001871283
input datafile file number=00006 name=+DATA_DB/CDB121/DATAFILE/users.346.1001870641
channel ORA_DISK_1: starting piece 1 at 03-MAR-19
channel ORA_DISK_1: finished piece 1 at 03-MAR-19
piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/09trh40u_1_1 tag=TAG20190303T154741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00013 name=+DATA_DB/CDB121/8321098DB16D5B60E055000000000001/DATAFILE/sysaux.269.1001871733
input datafile file number=00012 name=+DATA_DB/CDB121/8321098DB16D5B60E055000000000001/DATAFILE/system.263.1001871733
input datafile file number=00015 name=+DATA_DB/CDB121/8321098DB16D5B60E055000000000001/DATAFILE/p42.267.1001943869
input datafile file number=00014 name=+DATA_DB/CDB121/8321098DB16D5B60E055000000000001/DATAFILE/users.264.1001871775
channel ORA_DISK_1: starting piece 1 at 03-MAR-19
channel ORA_DISK_1: finished piece 1 at 03-MAR-19
piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/0atrh41n_1_1 tag=TAG20190303T154741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA_DB/CDB121/832104B5BE3055F0E055000000000001/DATAFILE/sysaux.258.1001871649
input datafile file number=00009 name=+DATA_DB/CDB121/832104B5BE3055F0E055000000000001/DATAFILE/system.257.1001871649
input datafile file number=00011 name=+DATA_DB/CDB121/832104B5BE3055F0E055000000000001/DATAFILE/users.270.1001871705
channel ORA_DISK_1: starting piece 1 at 03-MAR-19
channel ORA_DISK_1: finished piece 1 at 03-MAR-19
piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/0btrh426_1_1 tag=TAG20190303T154741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA_DB/CDB121/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.311.1001870713
input datafile file number=00005 name=+DATA_DB/CDB121/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.315.1001870713
channel ORA_DISK_1: starting piece 1 at 03-MAR-19
channel ORA_DISK_1: finished piece 1 at 03-MAR-19
piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/0ctrh42m_1_1 tag=TAG20190303T154741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 03-MAR-19
Starting Control File and SPFILE Autobackup at 03-MAR-19
piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-3962082852-20190303-00 comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-19
RMAN>This is just a quick start of what we can do with TDE.
We didn’t create Encrypted Tablespaces, or convert regular one to Encrypted . What about doing a Database duplicate process? and PDB transport?…
Like after every tutorial, we can find more questions that when we started.. and that is why more guides will be created for those topics "soon"
Comments