Encryption of the Database

Encryption of the Database

sqlplus / as sysdba

Step 1: Set the master encryption key from SQL*Plus:
$ sqlplus / as sysdba;
SQL>select * from V$encryption_wallet;
SQL>alter system set encryption key identified by <"strong password">;

Step 2: Bounce database
SQL>shutdown normal;
SQL> exit;

Step 3: Startup the database normally, ensuring that the wallet is open: sqlplus "/ as sysdba"
SQL>startup mount;

SQL>alter system set encryption wallet open identified by <"strong password">;
System altered.
SQL>alter database open;
System altered.
SQL>exit;

Step 4: Identify all the temporary and undo tablespaces in the database:
SQL>select tablespace_name from dba_tablespaces where contents='TEMPORARY' and STATUS='ONLINE';
SQL>select tablespace_name from dba_tablespaces where contents='UNDO' and STATUS='ONLINE';

Step 5: Create a script called tbsp_offline.sql script to bring tablespaces other than system, sysaux, temp and undo offline:
$ sqlplus / as sysdba;
SQL>set heading off
SQL>spool tbsp_offline.sql
SQL>select 'alter tablespace '||tablespace_name|| ' offline;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','UNDOTS1');
SQL>exit

Step 6: Bring all the specified tablespaces offline by connecting to SQL*Plus as sysdba, and executing the script tbsp_offline.sql.
$ sqlplus / as sysdba
SQL>@tbsp_offline.sql

Step 7: Create a script called datafiles_encrypt.sql containing the commands to encrypt your datafiles, except system, sysaux, temp and undo.
$ sqlplus / as sysdba
SQL>set heading off
SQL>set linesize 150
SQL>spool datafiles_encrypt.sql
SQL>select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','UNDOTS1');
SQL>exit

Step 8: Encrypt your datafiles by running the datafiles_encrypt.sql encryption script from SQL*Plus as sysdba:
$ sqlplus / as sysdba
SQL>@datafiles_encrypt.sql

Note: If there are Large number of datafiles, you can parallelize their encryption by creating sub-scripts and running the sub-scripts from parallel SQL session.

Step 9: Take the offline tablespaces online.
Edit the tbsp_online.sql file, change the word offline to online, then save the file.
Check the status of tablespace encryption by connecting to SQL*Plus / as sysdba and running the query shown:
$ sqlplus / as sysdba
SQL>select tablespace_name, encrypted from dba_tablespaces;

Step 10: To make the wallet auto login, run the following command:
$ orapki wallet create -wallet "" -auto_login

About Vast Edge - Simplified Cloud Solutions

Founded in the year 2004, Vast Edge is a leading IT Consulting Company and Global Service provider of Business Intelligence, big data analytics, cloud ERP, IoT platform, enterprise backup, and disaster recovery, Blockchain, Cassandra, AI/ML, and Integration solutions. We perform Application modernization, follow continuous change management, and implement advanced practices while working on the key areas of enhancing methodologies of project development.

Get a Free Assessment from our cloud experts.