Oracle Database wallet creation

For Implementing TDE you need to install wallet

For RAC it is recommended to use shared location for wallet

First put the wallet location in sqlnet.ora

### TDE env. ###
ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
   (METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet)
   )
  )

After that make sure folder is present

mkdir -p /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/

Login to db

sqlplus / as sysdba

For Creating

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "xxxxxxx";


Once created Wallet will be open

If you need to open wallet manually. This should be done after restarting db or else you will get error when viewing encrypted tables saying wallet not open

For Opening

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "xxxxxxxx";

For Closing

alter system set encryption wallet close identified by "xxxxxxxx";

Once you create wallet you will see a file named ewallet.p12 in the wallet folder.

Make sure you set permission to 600 for the file.

If RAC and not using shared location copy that file to same location on node2.

Your 2nd instance should pick it up.

you can check wallet status using 

select * from gv$encryption_wallet;

TIP: Some time gv$ shows one of the instance as closed. Then use 

select * from v$encryption_wallet;

Once it shows open then use the gv$ view again. I think its a bug in 11.2.0.4

Note: Also make sure you set up unq_name for database.

For RAC you can check using

srvctl config database -d db_name

Its a good practice to manually export the unq_name using the following before creating or opening the wallet.

FOR RAC set environments using following

srvctl setenv database -d prod -T "ORACLE_UNQNAME=prod"

 export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`

Now configuring Auto login wallet

Use ORAPKI utility

orapki wallet create -wallet /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/ -auto_login

It will prompt for wallet password and your auto login wallet will be created. You will see a new file in the wallet location cwallet.sso

Make sure you change permissions for that file too

Deleting wallet ( To be continued)

Basically you need to decrypt everything before you remove wallet

For reference see: http://www.toadworld.com/platforms/oracle/w/wiki/11195.oracle-database-tde-in-rac-non-shared-location-auto-login-considerations

Refrence:

sqlplus startup works but srvctl fails: ORA-03113/ORA-28365: wallet is not open (Doc ID 2112118.1) Managing TDE wallets in a RAC environment (Doc ID 567287.1) PRCR-1079 CRS-5017 "ORA-28365: wallet is not open" When Starting Database Using srvctl as Wrong Environment Variable is Set in OCR (Doc ID 1567201.1)

To create a new entry in wallet.. Before doing this add a tns entry.. in the example below tns entry is thicon_sys and username is sys pasword is Password#1..
Location is wallet location.. we can get it form sqlnet.ora

mkstore -wrl /opt/app/oracle/product/11.2.0/11.2.0.4/dbs -createCredential thicon_sys sys Password#1


This lists the credentials stored in wallet


mkstore -wrl /opt/app/oracle/product/11.2.0/11.2.0.4/dbs -listCredential

Make sure to export unqname before creating wallet for RAC

 export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`

The following may be/ may not be related 

To create a wallet in database here are the sample steps

Sample sqlnet.ora file


### TDE env. ###
ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
   (METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet)
   )
  )

#11gR1 and 11gR2 Target Database or Repository Create Several TNS-12599 Errors in Alert.log File (Doc ID 1150874.1)
SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168)

#TNS-12599: TNS:cryptographic Checksum Mismatch in alert.log after enabling of encryption on the server side (Doc ID 1927120.1)
DIAG_ADR_ENABLED=OFF

After this

Create the db folder in the location

cd /u01/app/oracle/admin/

mkdir -p sdwh/wallet

Login to sdwh database as sysdba

sqlplus / as sysdba

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "xxxxxxxxxxx";

If you dont create folder as above mentioned similar to sqlnet.ora then  you will get the following error

ERROR at line 1:

ORA-28368: cannot auto-create wallet

After creating the password the wallet will be open and ready to be used.

You can close the wallet by using following command

alter system set encryption wallet close identified by "xxxxxxxxxx"

Once wallet is closed you wont be able to query the tables that are encrypted.

If you do a db shutdown which has wallet encrrypted columns or tablespace you should open the wallet after restart of database

Here is the command

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

******************************************************************************************

Wallet addition info to prevent unauthorized access

Creating a Wallet

Use the mkstore command on an empty directory as follows:
mkdir -p /oracle/admin/DBNAME/wallet
mkstore -wrl /oracle/admin/DBNAME/wallet -create

You will be prompted for a password to secure the wallet. Make sure it is something secure, and record the password in your central password store.

Next, add the following lines to your sqlnet.ora configuration file.
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION=(
  SOURCE=(METHOD=FILE)
  (METHOD_DATA=(DIRECTORY=/oracle/admin/DBNAME/wallet))
)

Note: There are implications for both Clusterware and OS authentication when using wallet_override:, so please see the section "Known Issues / Gotchas" at the end of the article)

Adding a username and password to the wallet

Before adding the username and password, we create an alias in the tnsnames.ora file that will be used whenever we want to log in using the stored credentials. Only one password may be stored in the wallet per TNS alias: In our example below, we have created an alias called "DBFS":
# Connectiong string for DBFS Oracle Wallet
DBFS =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=`hostname`-vip)(PORT=1528))
(CONNECT_DATA=(SID=PMLOC1_1))
)

Now to add a username and password to an existing wallet, use the mkstore command with the -createCredential option as follows:
mkstore -wrl <wallet_location> -createCredential <TNS_alias> <username> <password>

Example:
mkstore -wrl /oracle/admin/DBNAME/wallet -createCredential DBFS dbfs_admin p@ssw0rd

Testing the Wallet

That's it, your wallet is created and you've stored a username and password inside it. Now all you need to do is test it using the TNS alias you setup (DBFS in our example):
sqlplus /@DBFS

Administering the Wallet

Listing credentials stored in the wallet:
mkstore -wrl <wallet_location> -listCredential

Modifying credentials stored in the wallet:
mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

Deleting credentials stored in the wallet:
mkstore -wrl <wallet_location> -deleteCredential <db_alias>

Deleting the whole wallet:
rm -rf <wallet_location>