Database Encryption in Oracle 19c

 

Steps to Enable Encryption in Database

 

Step#1: Set Wallet location in sqlnet.ora file

## Add to $ORACLE_HOME/network/admin/sqlnet.ora file

ENCRYPTION_WALLET_LOCATION =

              (SOURCE = (METHOD = FILE)

              (METHOD_DATA =

              (DIRECTORY =/u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet)

              )

)


Step#2: Create the wallet directory. In RAC, wallet directory should be shared location. 

mkdir -p /u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet

chmod 700 /u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet

 

Step#3: Create Password Based Keystore

Sql> administer key management create keystore '/u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet' identified by <wallet_password>;

 

Step#4: Open the Keystore

Sql> administer key management set keystore open identified by <wallet_password>;

 

Step#5: Create the Master Encryption Key

Sql> administer key management set key identified by <wallet_password> with backup using 'oracle123';

 

Step#6: Create AutoLogin wallet  

Sql> administer key management create auto_login keystore from keystore  '/u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet' identified by <wallet_password>;

Note: cwallet.sso – the autologin file created in the wallet directory.

 

Step#7: Verify wallet status. It should be open and auto login for all instances.  

Sql> select inst_id, wrl_type, wrl_parameter, status, wallet_type, wallet_order from gv$encryption_wallet;

 

#####  Tablespace Encryption #####  

 

## Encrypt the tablespace online

Sql> alter tablespace tbs1 encryption online using 'AES256 ' encrypt;


## Create a new encrypted tablesapce

Sql> create tablespace tbs1 datafile '+DATA1 ' size 10G encryption using 'AES256' default storage (encrypt);

 

## Verify Tablespace Encryption

Sql> select t.name as tablespace_name, e.encryptionalg, e.status from v$encrypted_tablespace e join v$tablespace t on e.ts# = t.ts# order by t.name;

 

## Set Default Encryption for new tablespace

Sql> alter system set encrypt_new_tablespaces=always scope=both instances= '* ';

 


 #####  Disable Encryption From Database #####  

 

## Find the Current Encryption Status

Step#1: Current Status of TDE Encryption

Sql> select * from v$encryption_wallet;

 

Step#2: Current Status of Encrypted Tablespace

Sql> select t.name as tablespace_name, e.encryptionalg, e.status from v$encrypted_tablespace e join v$tablespace t on e.ts# = t.ts# order by t.name;

 

Step#3: Current Status of Encrypted Columns

Sql> select owner, table_name, column_name, encryption_alg, salt from dba_encrypted_columns order by owner, table_name;

 

Step#4: Encryption key details

Sql> select key_id, keystore_type, creator, key_use, creation_time from v$encryption_keys;

 

## Steps to Disable Encryption 


Step#1: Disable Column level Encryption

Sql> alter table owner.table_name modify (col_name decrypt);

Sql> select * from dba_encrypted_columsn where owner=<schema_name>;

 

Step#2: Decrypt encrypted tablespace

Sql> alter tablespace tbs1 encryption online decrypt; OR

 

# Move tablespace data to encrypted tablespace and drop encrypted tablespace 

## Create unencrypted tablespace

Sql> Create tablespace tbs2 datafile '+DATA1 ' size 10G;

 

## Move objects to encrypted tablespace

Sql> Alter table owner.table_name move tablespace tbs2;

 

## Rebuild Index

Sql> alter index owner.index_name rebuild online;

 

## Drop encrypted tablespace

Sql> drop tablespace tbs1 including contents and datafiles;

 


Step#3: Close the TDE wallet and delete or rename auto login wallet file

Sql> administer key management set keystore close identified by <wallet_password> ;

 # cd /u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet

# mv cwallet.sso cwallet.sso_bak

 

Step#4: Comment/delete Wallet location lines in sqlnet.ora file

#ENCRYPTION_WALLET_LOCATION =

#            (SOURCE = (METHOD = FILE)

#            (METHOD_DATA =

#            (DIRECTORY =/u01/app/oracle/product/19.26.0.0/db_1/network/admin/wallet)

#            )

#)

 

Step#5: Check status of TDE Encryption. It should be closed

Sql> select * from v$encryption_wallet;

 

Comments

Post a Comment

Popular posts from this blog

 Exadata  Oracle Exadata Smart Flash Cache * Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log  * CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE * ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) * ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) Smart Flash Cache Mode: 1. Write Through Flash Cache Mode (Default):  Write on Disk -> Copy to Flash Cache  * excellent for absorbing repeated random reads  * commonly found in OLTP applications. * dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode" * Steps to enable Write-Through Mode CellCLI> ALTER FLASHCACHE ALL FLUSH CellCLI> DROP FLASHCACHE CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV CellCLI> ALTER CELL flashCacheMode=WriteThrough CellCLI> ALTER CELL STARTUP SERVICES CELLSRV CellCLI> CREATE FLASHCACHE ALL 2. Write Back Flash Cache Mode:  Write on Flash -> Copy to Disk  * best ...
Read more

Database Network : Configuration Parameters

LSNRCTL> command [listener_name] lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script Listener.ora file parameters: ·          PASSWORDS_listener_name  #an unencrypted password ·          LSNRCTL> change_password #to establish an encrypted password ·          LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora ·          LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands ·          Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subseque...
Read more

Undo Segments : Drop Problematic Undo Segments

ERROR about to recover undo segment 98 status:6 inst:0 mark undo segment 98 as available status:6 ret:1  about to recover undo segment 46 status:6 inst:0 mark undo segment 46 as available status:6 ret:1   SQL> select * from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID ---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- 46 46 2313064 RECOVERING 505 24992423 77 5586 0 0 0 10001000684B2300 0000000000000000 1 0 98 25 1352150 RECOVERING 0 226231 78 5586 0 0 0 30001900D6A11400 0000000000000000 1 0   SQL> SELECT segment_name, tablespace_name  FROM dba_rollback_segs  WHERE segment_id IN (98, 46); SEGMENT_NAME               TABLESPACE_NAME ------------------------------ ----------------------------...
Read more