Manage an encrypted tablespace

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Manage an encrypted tablespace with 11g commands.
[2] Manage Key Store with Multitenent
2.1 Configure Key Store in CDB.
2.2 Configure Key Store in PDB.
2.3 Test TDE Table & TDE TABLESPACE
2.4 Manage Key Store in CDB and PDB
2.5 Unplug/Plugin PDBs with TDE
2.6 Auto-Login Keystores
[3] Manage Key Store in Single instance dataguard environment
3.1 Configure the wallet directory
3.1 Keystore Operations like Create,Open,Close
3.1 Configure the Master Key for Keystore.
3.1 Test the Encrption of Encrption Tablespace
3.1 Create Auto-login keystores.
3.1 Change the password of keystore.

[1]Manage an encrypted tablespace with 11g commands.

1.1 About Encrypted Tablespaces

-> Encrypted tablespaces primarily protect your data from unauthorized access 
by means other than through the database.

-> For example, when encrypted tablespaces are written to backup media for 
travel from one Oracle database to another or for travel to an off-site 
facility for storage, they remain encrypted.
-> Also, encrypted tablespaces protect data from users who try to circumvent 
the security features of the database and access database files directly 
through the operating system file system.

-> Tablespace encryption is completely transparent to your applications, 
so no application modification is necessary.
-> Tablespace encryption does not address all security issues. 
-> for example, provide access control from within the database. Any user 
who is granted privileges on objects stored in an encrypted tablespace can 
access those objects without providing any kind of additional password or key.
-> When you encrypt a tablespace, all tablespace blocks are encrypted. 
All segment types are supported for encryption, 
including tables, clusters, indexes, LOBs (BASICFILE and SECUREFILE), 
table and index partitions, and so on.

-> Tablespace encryption uses the Transparent Data Encryption feature of 
Oracle Database, which requires that you create a keystore to store the 
master encryption key for the database.

-> The keystore must be open before you can create the encrypted tablespace 
and before you can store or retrieve encrypted data.

-> When you open the keystore, it is available to all session, and it remains
open until you explicitly close it or until the database is shut down.

Transparent Data Encryption supports industry-standard encryption algorithms, 
including the following Advanced Encryption Standard (AES) and 
Triple Data Encryption Standard (3DES) algorithms:

AES256
AES192
AES128
3DES168

-> The encryption key length is implied by the algorithm name.
For example, the AES128 algorithm uses 128-bit keys

-> You specify the algorithm to use when you create the tablespace, and
   different tablespaces can use different algorithms. 
-> Although longer key lengths theoretically provide greater security, 
   there is a trade-off in CPU overhead. 
-> If you do not specify the algorithm in your CREATE TABLESPACE statement,
   AES128 is the default. 
-> There is no disk space overhead for encrypting a tablespace.

Restrictions :->
1. You cannot encrypt an existing tablespace with an ALTER TABLESPACE statement. 
However, you can use Data Pump or SQL statements such as CREATE TABLE AS SELECT or 
ALTER TABLE MOVE to move existing table data into an encrypted tablespace.

2. Encrypted tablespaces are subject to restrictions when transporting to 
   another database.

3. When recovering a database with encrypted tablespaces (for example after
   a SHUTDOWN ABORT or a catastrophic error that brings down the database instance), 
   you must open the keystore after database mount and before database open, 
   so the recovery process can decrypt data blocks and redo.

1.2 Prerequisite to create an Encrypted tablespace

This is general procedure to create an encrypted tablespace. 
1. First, create the wallet ( file) where the master encryption key is stored.

Reference: Database Net Services Reference -> 5 Parameters for the sqlnet.ora File 
-> sqlnet.ora Profile Parameters -> WALLET_LOCATION

#Create the path where the wallet store
mkdir -p $ORACLE_HOME/wallets/oemdb

# Edit the sqlnet.ora file
vi $ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION= 
(SOURCE=
(METHOD=file)
(METHOD_DATA= 
(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/wallets/oemdb)))

2. The DB has to have the COMPATIBLE parameter > = 11.1
- We validate that we fulfill this requirement
SHOW PARAMETER COMPATIBLE

3. Create the Wallet in 11g
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle_4U" ;

4. Restart the database to open the WALLET
- Whenever you reboot the end, we must reopen the wallet
SHUTDOWN IMMEDIATE 
STARTUP MOUNT
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle_4U" ;
ALTER DATABASE OPEN;

Note: To Close WALLET from CLI

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY " oracle_4U " ;

1.3 Create and View Encrypted Tablespaces in 11g

13.2.5.2 Creating Encrypted Tablespaces
You can create encrypted tablespaces to protect your data from unauthorized 
access.

To encrypt a tablespace, you must open the database with the COMPATIBLE 
initialization parameter set to 11.1.0 or higher. 
Any user who can create a tablespace can create an encrypted tablespace.

The following statement creates an encrypted tablespace with the default
encryption algorithm:

CREATE TABLESPACE securespace
DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT); 

The following statement creates the same tablespace with the AES256 algorithm:

CREATE TABLESPACE securespace
DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

13.2.5.3 Viewing Information About Encrypted Tablespaces

VIEWS:
V$ENCRYPTION_WALLET
-> Consulted the state WALLET using query select status,wrl_parameter from 
v$encryption_wallet

V$ENCRYPTED_TABLESPACE 
-> lists all currently encrypted tablespaces
DBA_TABLESPACES | USER_TABLESPACES 
-> views include a column named ENCRYPTED. This column contains YES for 
encrypted tablespaces.
select tablespace_name,encrypted from dba_tablespaces;

The following query displays the name and encryption algorithm of encrypted 
tablespaces:

SELECT t.name, e.encryptionalg algorithm 
FROM v$tablespace t, v$encrypted_tablespaces e
WHERE t.ts# = e.ts#;

Sample Queries.
SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;
SELECT STATUS,WRL_PARAMETER FROM V$ENCRYPTION_WALLET;
SELECT * FROM DBA_ENCRYPTED_COLUMNS;
SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;
SELECT * FROM V$ENCRYPTED_TABLESPACES;

1.4 Auto login wallet

problem or drawback we've seen over the last year is that every time 
we open the restart WALLET again. But we can create an AUTO LOGIN WALLET 
to fix it. We have two ways to do it .

The first method is to use orakpi command to create an auto login wallet.
The second way to configure an auto login wallet is with the graphical utility owm.


[2] Manage Key Store with Multitenent
2.1 Configure Key Store in CDB.
2.2 Configure Key Store in PDB.
2.3 Test TDE Table & TDE TABLESPACE
2.4 Manage Key Store in CDB and PDB
2.5 Unplug/Plugin PDBs with TDE
2.6 Auto-Login Keystores

Oracle database 12c introduced a new way to manage keystores, encryption keys 
and secrets using the ADMINISTER KEY MANAGEMENT command. 
This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET 
ENCRYPTION WALLET commands for key and wallet administration from previous 
releases. The terminology in the documentation freely mixes the terms wallet 
and keystore, but the intention seems to be to move to the term keystore, in 
line with the Java terminology.

The multitenant architecture complicates key management somewhat, as the 
root container needs an open keystore with an active master encryption key.

The CDBs keystore is used to store encryption keys for all the associated PDBs,
but they each need their own master encryption key. 
The master encryption key for the PDB must be exported before an unplug 
operation, so it can be imported after a subsequent plugin operation.


[1] Configure Key Store in CDB.

$ mkdir -p $ORACLE_HOME/wallets/cdb2
$ cd $ORACLE_HOME/wallets/cdb2
$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2
ls
cd $ORACLE_HOME/network/admin
-bash-4.1$ vi sqlnet.ora
-bash-4.1$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2)))

Note: Do not add quotation mark for directory location.

-bash-4.1$ sql

SQL> show parameter compati

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
plsql_v2_compatibility boolean FALSE
SQL>
SQL> ADMINISTER KEY MANAGEMENT 
CREATE KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2' IDENTIFIED BY oracle_4U;

keystore altered.

SQL> quit
-bash-4.1$ cd /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2
-bash-4.1$ ll
total 4
-rw-r--r-- 1 oracle oinstall 2408 Sep 4 09:44 ewallet.p12
-bash-4.1$
-bash-4.1$ sql

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED 
     BY oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN_NO_MASTER_KEY /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
CLOSED /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY oracle_4U 
WITH BACKUP USING 'elite123';

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/

SQL> quit

-bash-4.1$ ll /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/
total 8
-rw-r--r-- 1 oracle oinstall 2408 Sep 4 09:55 ewallet_2019090404252964_elite123.p12
-rw-r--r-- 1 oracle oinstall 3848 Sep 4 09:55 ewallet.p12
-bash-4.1$
-bash-4.1$ sql

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> alter session set container=mypdb;

Session altered.


SQL> select status,wrl_parameter,con_id from v$encryption_wallet;

STATUS WRL_PARAMETER CON_ID
--------------------------------------------------------------------------------
CLOSED /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/ 0

SQL> SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

no rows selected

SQL> alter session set container=cdb$root;

Session altered.

SQL> SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;SQL>

CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 AbDiT9nz5U9svzjGD7UJdSsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


[2] Configure Key Store in PDB.


SQL> alter session set container=mypdb;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
----------------------------------------------------------------------------------------------------
OPEN_NO_MASTER_KEY /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY mypdb123;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY mypdb123
*
ERROR at line 1:
ORA-46627: keystore password mismatch


SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle_4U;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle_4U
*
ERROR at line 1:
ORA-46631: keystore needs to be backed up


SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle_4U WITH BACKUP;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
----------------------------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/

SQL> SELECT con_id, key_id FROM v$encryption_keys;

CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 AU5v1WczKE9ev9tge+b8CNEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> select status,wrl_parameter,con_id from v$encryption_wallet;

STATUS WRL_PARAMETER CON_ID
--------------------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/ 0


SQL> alter session set container=cdb$root;

Session altered.

SQL> select con_id,key_id from v$encryption_keys;

CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 AU5v1WczKE9ev9tge+b8CNEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
0 AbDiT9nz5U9svzjGD7UJdSsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


[3] Test TDE Table & TDE TABLESPACE


SQL> alter session set container=mypdb;

Session altered.

SQL> connect PM/PM@//racnode2/mypdb
Connected.
SQL>
SQL> create table tde_test
2 (id number(10),
3 data varchar2(50) ENCRYPT
4 );

Table created.

SQL> INSERT INTO tde_test values ( 1, 'This is a secret!');

1 row created.

SQL> commit;

Commit complete.

SQL> connect sys/oracle_4U@racnode2/mypdb as sysdba
Connected.
SQL>
SQL> CREATE TABLESPACE encrypted_tbs
2 datafile '/u03/app/oracle/oradata/cdb2/encrypted_tbs01.dbf' size 10M
3 autoextend on encryption using 'AES256'
4 DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> ALTER USER PM QUOTA UNLIMITED ON encrypted_tbs;

User altered.

SQL> connect PM/PM@//racnode2/mypdb
Connected.
SQL>
SQL> create table tde_tbs_test
2 ( id number(10),
3 data varchar2(50)
4 ) tablespace encrypted_tbs;

Table created.

SQL> insert into tde_tbs_test values (1,'This is also a secret!');

1 row created.

SQL> commit;

Commit complete.

SQL> connect sys/oracle_4U@racnode2/mypdb as sysdba
Connected.
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
----------------------------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


[4] Manage Key Store in CDB and PDB


SQL> shutdown
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL>
SQL> select * from PM.tde_test;
select * from PM.tde_test
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> select * from PM.tde_tbs_test;
select * from PM.tde_tbs_test
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select * from PM.tde_test;

ID DATA
---------- --------------------------------------------------
1 This is a secret!

SQL> select * from PM.tde_tbs_test;

ID DATA
---------- --------------------------------------------------
1 This is also a secret!

SQL>


#-> If the CDB is restarted, the keystore must be opened in both the CDB and the PDBs.

SQL> CONN / AS SYSDBA
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 830472192 bytes
Fixed Size 2929840 bytes
Variable Size 566233936 bytes
Database Buffers 255852544 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.


SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS
------------------------------
WRL_PARAMETER
----------------------------------------------------------------------------------------------------
CLOSED
/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


SQL> select con_id,key_id from v$encryption_keys;

no rows selected

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U CONTAINER=ALL;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS
------------------------------
WRL_PARAMETER
----------------------------------------------------------------------------------------------------
OPEN
/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/


SQL> select con_id,key_id from v$encryption_keys;

CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 AbDiT9nz5U9svzjGD7UJdSsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
0 AU5v1WczKE9ev9tge+b8CNEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> connect PM/PM@racnode2/mypdb
Connected.
SQL>
SQL> select * from tde_test;

ID DATA
---------- --------------------------------------------------
1 This is a secret!

SQL> select * from tde_tbs_test;

ID DATA
---------- --------------------------------------------------
1 This is also a secret!

SQL>

[5] Unplug/Plugin PDBs with TDE

This section describes the process of unplugging mypdb from the CDB2 instance 
and plugging into the CDB1 instance 
on the same machine with a new name of PDB2.


-bash-4.1$ export ORACLE_SID=cdb2
-bash-4.1$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 4 11:36:09 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 830472192 bytes
Fixed Size 2929840 bytes
Variable Size 566233936 bytes
Database Buffers 255852544 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL>
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U 
CONTAINER=ALL;

keystore altered.

SQL> alter session set container=mypdb;

Session altered.


SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS 
WITH SECRET "MySecretMyPDB" TO '/tmp/exportmypdb.p12' IDENTIFIED BY oracle_4U;

keystore altered.

SQL> shutdown
Pluggable Database closed.
SQL>
SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB MOUNTED
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL>
SQL> alter pluggable database mypdb unplug into '/tmp/mypdb.xml';

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB MOUNTED
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL> drop pluggable database mypdb keep datafiles;

Pluggable database dropped.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL>
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
exit


-bash-4.1$ sql


SQL> r
1 BEGIN
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/tmp/mypdb.xml') then
3 dbms_output.put_line('compatible');
4 else
5 dbms_output.put_line('not compatible');
6 END IF;
7* END;
compatible

PL/SQL procedure successfully completed.

SQL> select * from pdb_plug_in_violations;


SQL> set pagesize 0
SQL> /
04-SEP-19 12.05.50.846665 PM
MYPDB
Parameter WARNING
0 1
CDB parameter memory_target mismatch: Previous 792M Current 0
PENDING
Please check the parameter in the current CDB

04-SEP-19 12.05.50.848507 PM
MYPDB
Parameter WARNING
0 2
CDB parameter _catalog_foreign_restore mismatch: Previous FALSE Current TRUE
PENDING
Please check the parameter in the current CDB


SQL> CREATE PLUGGABLE DATABASE PDB2 USING '/tmp/mypdb.xml' NOCOPY;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN;

Warning: PDB altered with errors.

SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE YES
SQL>
SQL>

If CDB1 doesn't already have a keystore at the root level, you will need 
to create it.

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.


$ cd $ORACLE_HOME/network/admin
$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2)))


-bash-4.1$ vi sqlnet.ora
$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/product/12.1.0.2/db_1/wallets/$ORACLE_SID)))
-bash-4.1$
-bash-4.1$ cd /u01/app/oracle/product/12.1.0.2/db_1/wallets
-bash-4.1$ ls
cdb2
-bash-4.1$ mkdir cdb1
-bash-4.1$
-bash-4.1$ export ORACLE_SID=cdb1
-bash-4.1$ sql

SQL> ADMINISTER KEY MANAGEMENT 
CREATE KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb1' IDENTIFIED BY Oracle_4U;

keystore altered.


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN_NO_MASTER_KEY /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb1/

SQL> alter session set container=pdb2;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN_NO_MASTER_KEY /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb1/


SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "MySecretMyPDB" FROM '/tmp/exportmypdb.p12' IDENTIFIED BY oracle_4U WITH BACKUP;
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "MySecretMyPDB" FROM '/tmp/exportmypdb.p12' IDENTIFIED BY oracle_4U WITH BACKUP
*
ERROR at line 1:
ORA-46627: keystore password mismatch


SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS 
WITH SECRET "MySecretMyPDB" FROM '/tmp/exportmypdb.p12' IDENTIFIED BY Oracle_4U WITH BACKUP;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb1/

SQL> SHUTDOWN;
Pluggable Database closed.
SQL> STARTUP;
Pluggable Database opened.
SQL>
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Oracle_4U;

keystore altered.

SQL> connect PM/PM@racnode2/pdb2
Connected.
SQL>
SQL> SELECT * FROM tde_test;

ID DATA
---------- --------------------------------------------------
1 This is a secret!

SQL> SELECT * FROM tde_tbs_test;

ID DATA
---------- --------------------------------------------------
1 This is also a secret!

SQL> quit
-bash-4.1$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/wallets
-bash-4.1$ ls
cdb1 cdb2
-bash-4.1$ ls -ltr cdb1/
total 8
-rw-r--r-- 1 oracle oinstall 2408 Sep 4 12:22 ewallet_2019090406525335.p12
-rw-r--r-- 1 oracle oinstall 3888 Sep 4 12:22 ewallet.p12
-bash-4.1$
-bash-4.1$ ls -ltr cdb2/
total 16
-rw-r--r-- 1 oracle oinstall 2408 Sep 4 09:55 ewallet_2019090404252964_elite123.p12
-rw-r--r-- 1 oracle oinstall 3848 Sep 4 10:09 ewallet_2019090404391794.p12
-rw-r--r-- 1 oracle oinstall 6264 Sep 4 10:09 ewallet.p12


[6] Auto-Login Keystores
Creation of an auto-login keystore means you no longer need to explicitly 
open the keystore after a restart. 
The first reference to a key causes the keystore to be opened automatically, 
as shown below.


-bash-4.1$ sql

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM 
KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb1' IDENTIFIED BY Oracle_4U;

keystore altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL>
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> alter session set container=pdb2;

Session altered.

SQL> select * from pm.tde_test;

ID DATA
---------- --------------------------------------------------
1 This is a secret!

SQL> select * from pm.tde_tbs_test;

ID DATA
---------- --------------------------------------------------
1 This is also a secret!

SQL> alter session set container=cdb$root;

Session altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb1/


SQL> select con_id,key_id from v$encryption_keys;

CON_ID KEY_ID
------------------------------------------------------------------------------
0 AQDnRCqCmk9Fv4JQL4UPhJ0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
0 AU5v1WczKE9ev9tge+b8CNEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


SQL> quit


[3] Manage Key Store in Single instance dataguard environment 
3.1 Configure the wallet directory 
3.1 Keystore Operations like Create,Open,Close 
3.1 Configure the Master Key for Keystore.
3.1 Test the Encrption of Encrption Tablespace
3.1 Create Auto-login keystores.
3.1 Change the password of keystore.

#Configure the wallet directory

-bash-4.1$ mkdir -p /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb

-bash-4.1$ cd $ORACLE_HOME/network/admin
-bash-4.1$ ls
listener.ora samples shrept.lst tnsnames.ora
-bash-4.1$ vi sqlnet.ora
-bash-4.1$ pwd
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/network/admin
-bash-4.1$ vi sqlnet.ora
-bash-4.1$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb)))

#Keystore Operations like Create,Open,Close

-bash-4.1$ sql

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb' IDENTIFIED BY oracle_4U;

keystore altered.

SQL> quit
-bash-4.1$ ll /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb
total 4
-rw-r--r-- 1 oracle oracle 2400 Sep 2 17:21 ewallet.p12

-bash-4.1$ sql

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
CLOSED /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN_NO_MASTER_KEY /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
CLOSED /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

#Configure the Master Key for Keystore.

SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY oracle_4U WITH BACKUP USING 'orcl12102cdb';

keystore altered.

SQL> SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;

KEYSTORE_TYPE
-----------------
SOFTWARE KEYSTORE

SQL> SELECT WRL_PARAMETER FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER
--------------------------------------------------------------------------------
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS
------------------------------
OPEN


#Test the Encrption of Encrption Tablespace

SQL> create tablespace tbs_encrypted
datafile '/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/tbs_encrypted.dbf' 
size 10M autoextend on
encryption using 'AES192'
DEFAULT STORAGE (ENCRYPT)

Tablespace created.

SQL> quit


Standby 
=======

WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/tbs_encrypted.dbf
Successfully added datafile 16 to media recovery
Datafile #16: '/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/tbs_encrypted.dbf'
kcrf_decrypt_redokey: wallet is not opened..(err 28365)
MRP0: Background Media Recovery terminated with error 28365
Mon Sep 02 17:45:13 2019
Errors in file /refresh/home/app/12.1.0.2/oracle/diag/rdbms/orcl12102cdb_stby/orcl12102cdb_stby/trace/orcl12102cdb_stby_pr00_6876.trc:
ORA-28365: wallet is not open
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4737732
Mon Sep 02 17:45:13 2019
Errors in file /refresh/home/app/12.1.0.2/oracle/diag/rdbms/orcl12102cdb_stby/orcl12102cdb_stby/trace/orcl12102cdb_stby_pr00_6876.trc:
ORA-28365: wallet is not open
Mon Sep 02 17:45:13 2019
MRP0: Background Media Recovery process shutdown (orcl12102cdb_stby)

From Primary
============

-bash-4.1$ cd /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/
-bash-4.1$ ll
total 8
-rw-r--r-- 1 oracle oracle 2400 Sep 2 17:32 ewallet_2019090217325862_orcl12102cdb.p12
-rw-r--r-- 1 oracle oracle 3848 Sep 2 17:32 ewallet.p12
-bash-4.1$ scp ewallet* oracle@celvpvm13323.us.oracle.com:/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/
oracle@celvpvm13323.us.oracle.com's password:
ewallet_2019090217325862_orcl12102cdb.p12 100% 2400 2.3KB/s 00:00
ewallet.p12 100% 3848 3.8KB/s 00:00
-bash-4.1$
-bash-4.1$ cd /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/network/admin
-bash-4.1$ ll
total 20
-rw-r--r-- 1 oracle oinstall 350 Aug 15 10:39 listener.ora
drwxr-xr-x 2 oracle oracle 4096 Aug 23 2017 samples
-rw-r--r-- 1 oracle oracle 373 Oct 31 2013 shrept.lst
-rw-r--r-- 1 oracle oinstall 169 Sep 2 17:18 sqlnet.ora
-rw-rw-r-- 1 oracle oracle 3169 Aug 15 10:22 tnsnames.ora
-bash-4.1$ scp sqlnet.ora oracle@celvpvm13323.us.oracle.com:/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/network/admin
oracle@celvpvm13323.us.oracle.com's password:
sqlnet.ora 100% 169 0.2KB/s 00:00

From Standby
============

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
TBS_ENCRYPTED YES

6 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS_ENCRYPTED ONLINE


From Primary
============
SQL> create tablespace nonencrypted_tbs
2 datafile '/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/nonencrypted_tbs.dbf' size 10M autoextend on
3 ;

Tablespace created.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS
------------------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/


SQL> create table tblenc(username varchar2(25),password varchar2(25))tablespace encrypted_tbs;
create table tblenc(username varchar2(25),password varchar2(25))tablespace encrypted_tbs
*
ERROR at line 1:
ORA-00959: tablespace 'ENCRYPTED_TBS' does not exist


SQL> select tablespace_name from v$tablespace;
select tablespace_name from v$tablespace
*
ERROR at line 1:
ORA-00904: "TABLESPACE_NAME": invalid identifier


SQL> desc v$tablespace
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
CON_ID NUMBER


SQL> create table tblenc(username varchar2(25),password varchar2(25))tablespace TBS_ENCRYPTED;

Table created.

SQL> c/enc/nonenc
1* create table tblnonenc(username varchar2(25),password varchar2(25))tablespace TBS_ENCRYPTED
SQL> c/TBS_ENCRYPTED/nonencrypted_tbs
1* create table tblnonenc(username varchar2(25),password varchar2(25))tablespace nonencrypted_tbs
SQL> r
1* create table tblnonenc(username varchar2(25),password varchar2(25))tablespace nonencrypted_tbs

Table created.

SQL> insert into tblenc('hitesh','hit123');
insert into tblenc('hitesh','hit123')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into tblenc values ('hitesh','hit123');

1 row created.

SQL> c/enc/nonenc
1* insert into tblnonenc values ('hitesh','hit123')
SQL> r
1* insert into tblnonenc values ('hitesh','hit123')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> c/enc/nonenc
1* select * from tblnonenc
SQL> r
1* select * from tblnonenc

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> alter system flush buffer_cache;

System altered.

SQL> quit

cd /refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/
-bash-4.1$ strings tbs_encrypted.dbf | grep hit*
-bash-4.1$ strings nonencrypted_tbs.dbf | grep hit*
hitesh
hit123

From Standby
============

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> c/enc/nonenc
1* select * from tblnonenc
SQL> r
1* select * from tblnonenc

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> quit
-bash-4.1$ cd /refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/
-bash-4.1$ ls
control01.ctl data_D-ORCL1210_I-2368395079_TS-SYSTEM_FNO-1_29u9eris PDB1 pdbseed system01.dbf temp01.dbf users01.dbf
control02.ctl nonencrypted_tbs.dbf pdb2 sysaux01.dbf tbs_encrypted.dbf undotbs01.dbf
-bash-4.1$
-bash-4.1$ strings tbs_encrypted.dbf | grep hit*
-bash-4.1$ strings nonencrypted_tbs.dbf | grep hit*
hitesh
hit123

From Primary
============
SQL> startup force
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

SQL> select * from tblenc;
select * from tblenc
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

From Standby
============

SQL> shut abort
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

SQL> select * from tblenc;
select * from tblenc
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> select * from tblnonenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123


#Create Auto-login keystores.

From Primary
============

SQL>
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN 
KEYSTORE FROM KEYSTORE '/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb' IDENTIFIED BY oracle_4U;

keystore altered.

SQL> select encryption_type from v$encryption_keys;
select encryption_type from v$encryption_keys
*
ERROR at line 1:
ORA-00904: "ENCRYPTION_TYPE": invalid identifier


SQL> select keystore_type from v$encryption_keys;

KEYSTORE_TYPE
-----------------
SOFTWARE KEYSTORE

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS
------------------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/


SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
-bash-4.1$ ll /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/
total 12
-rw-rw-r-- 1 oracle oracle 3893 Sep 3 05:54 cwallet.sso
-rw-r--r-- 1 oracle oracle 2400 Sep 2 17:49 ewallet_2019090217325862_orcl12102cdb.p12
-rw-r--r-- 1 oracle oracle 3848 Sep 2 17:49 ewallet.p12

After you run this statement, the cwallet.sso file appears in the keystore location. 
The ewallet.p12 file is the password-based wallet.

Note:
Do not remove the PKCS#12 wallet (ewallet.p12 file) after you create the 
auto login keystore (.sso file). 
You must have the PKCS#12 wallet to regenerate or rekey the TDE master 
encryption key in the future.


-bash-4.1$
-bash-4.1$ sql

SQL> startup force
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> quit

$cd /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/
$ls
cwallet.sso ewallet_2019090217325862_orcl12102cdb.p12 ewallet.p12

$scp cwallet.sso oracle@celvpvm13281.us.oracle.com:/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/
oracle@celvpvm13281.us.oracle.com's password:

From Standby
============

SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
SQL>
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile 
disconnect from session;

Database altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL>


#Change the password of keystore.

From Primary
============

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY
2 oracle_4U SET elite_4U;
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY
*
ERROR at line 1:
ORA-28417: password-based keystore is not open


SQL> !oerr ora 28417
28417, 0000, "password-based keystore is not open"
// *Cause: Password-based keystore was not opened.
// *Action: Close the auto login keystore, if required, and open a
// password-based keystore.


Reference
TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login 
wallet is present (Doc ID 1944507.1)

cd /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb/
-bash-4.1$ ls
cwallet.sso ewallet_2019090217325862_orcl12102cdb.p12 ewallet.p12
-bash-4.1$ rm cwallet.sso


-bash-4.1$ sql


SQL> administer key management set keystore close;

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

SQL> administer key management set keystore open identified by oracle_4U;

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY
2 oracle_4U SET elite_4U WITH BACKUP USING 'elite_4U';

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by elite_4U;

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

SQL> select * from tblenc;
select * from tblenc
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY elite_4U;

keystore altered.

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

-bash-4.1$ ll
total 16
-rw-r--r-- 1 oracle oracle 2400 Sep 2 17:32 ewallet_2019090217325862_orcl12102cdb.p12
-rw-r--r-- 1 oracle oracle 3848 Sep 3 07:59 ewallet_2019090307595685_elite_4U.p12
-rw-r--r-- 1 oracle oracle 4784 Sep 3 07:59 ewallet.p12

SQL> startup force
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

SQL> select * from tblenc;
select * from tblenc
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM 
KEYSTORE '/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb' 
IDENTIFIED BY elite_4U;

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

-bash-4.1$ ll
total 24
-rw-r--r-- 1 oracle oracle 4829 Sep 3 08:06 cwallet.sso
-rw-r--r-- 1 oracle oracle 2400 Sep 2 17:32 ewallet_2019090217325862_orcl12102cdb.p12
-rw-r--r-- 1 oracle oracle 3848 Sep 3 07:59 ewallet_2019090307595685_elite_4U.p12
-rw-r--r-- 1 oracle oracle 4784 Sep 3 07:59 ewallet.p12


From Standby
============

-bash-4.1$ rm -rf *
-bash-4.1$ pwd
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/wallets/orcl12102cdb

Note:
Tranfer wallet directory from primary to standby.

-bash-4.1$ ll
total 24
-rw-r--r-- 1 oracle oracle 4829 Sep 3 08:20 cwallet.sso
-rw-r--r-- 1 oracle oracle 2400 Sep 3 08:21 ewallet_2019090217325862_orcl12102cdb.p12
-rw-r--r-- 1 oracle oracle 3848 Sep 3 08:21 ewallet_2019090307595685_elite_4U.p12
-rw-r--r-- 1 oracle oracle 4784 Sep 3 08:21 ewallet.p12
-bash-4.1$
-bash-4.1$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 3 08:21:24 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> select * from tblenc;

USERNAME PASSWORD
------------------------- -------------------------
hitesh hit123

SQL> quit

=====================
Path to Documentation:
=====================
Database Administrator’s Guide -> 13 Managing Tablespaces
-> 13.2.5 Encrypted Tablespaces

Oracle Database Administrator’s Guide -> 20 Managing Tables 
-> 20.2.13 Consider Encrypting Columns That Contain Sensitive Data

Database 2 Day + Security Guide
-> 4 Encrypting Data with Oracle Transparent Data Encryption

Database Advanced Security Guide
4 Managing the Keystore and the TDE Master Encryption Key
--> Managing the TDE Master Encryption Key
-> Exporting and Importing the TDE Master Encryption Key

Thank you for visiting this blog 🙂