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 🙂
ORACLE-LEARN 