Quick guide to set up Transparent Data Encryption for a tablespace

If not already done, create a folder for the wallet:

# mkdir -p  /u01/oracle/admin/proddb01/wallet

Step 1: configure the software keystore location in the sqlnet.ora file

Edit your $TNS_ADMIN/sqlnet.ora:

SQLNET.WALLET_OVERRIDE = TRUE
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/oracle/admin/proddb01/wallet)
    )
  )
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_DES_CBC_SHA)

Step 2: create the software keystore

There are three different types of software keystores.

You can create a) password-based software keystores, b) auto-login software keystores, and c) local auto-login software keystores.
In this guide, I am setting up password-based software keystore.
Password-based software keystores are protected by using a password that you create.

You must open this type of keystore before the keys can be retrieved or used.


Verify wallet location:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        NOT_AVAILABLE                  UNKNOWN

Note that wallet type = UNKNOWN , status=NOT_AVALABLE
Run "ADMINISTER KEY MANAGEMENT" to create the keystore:

SYS@proddb01 SQL> administer key management create keystore '/u01/oracle/admin/proddb01/wallet' identified by "secretpassword";

Keystore altered.

Check the view v$encryption_Wallet now, and you can see that it exists, but the wallet type is still UNKNOWN:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        CLOSED                         UNKNOWN

Step 3: Open the Software Keystore

Depending on the type of keystore you create, you must manually open the keystore before you can use it.
You must manually open a password-based software keystore before any TDE master encryption keys can be created or accessed in the keystore.

After you open a keystore, it remains open until you manually close it.
Each time you restart a database instance, you must manually open the password keystore to reenable encryption and decryption operations.

Open the keystore:

SYS@proddb01 SQL> administer key management set keystore open identified by "secretpassword";
 
Keystore altered.

Check the view v$encryption_wallet again:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;
 

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        OPEN_NO_MASTER_KEY             PASSWORD

Note that if the keystore is open but you have not created a TDE master encryption key yet, the STATUS column of the V$ENCRYPTION_WALLET view reminds you with a status of OPEN_NO_MASTER_KEY.

Step 4: Set the Software TDE Master Encryption Key


The TDE master encryption key is stored in the keystore.
This key protects the TDE table keys and tablespace encryption keys.
By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates.
Password-based software keystores must be open before you can set the TDE master encryption key.

Create a master key:

SYS@proddb01 SQL> administer key management set key identified by "****" with backup using 'initial_key_backup';

Keystore altered.

Check the view v$encryption_wallet again:

SYS@proddb01 SQL>  select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        OPEN                           PASSWORD

Step 5: Encrypt Your Data

After you complete the software keystore configuration, you can begin to encrypt data.
You can encrypt data in individual table columns or in entire tablespaces, like I do below:

create bigfile tablespace SECRET_DATA_TBS datafile '/u02/oradata/proddb01/secret_data_01.dbf'  size 256M autoextend on next 128M maxsize unlimited
encryption using 'AES128'
default storage(encrypt);

Check status of the tablespaces:

SYS@proddb01 SQL> SELECT t.name, e.encryptionalg algorithm
FROM  v$tablespace t, v$encrypted_tablespaces e
WHERE t.ts# = e.ts#;
 
NAME                           ALGORIT
------------------------------ -------
SECRET_DATA_TBS                AES128

When the database is restarted, the wallet will be in status CLOSED.

To access your data, the wallet must be opened first:

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

You may want to add a startup trigger that opens the wallet every time the database starts. See this post for how I solved this.

Sources:

1. configure the software keystore location in the sqlnet.ora file
2. log in to the database instance to create and open the keystore
3. Open the Software keystore
4. set the TDE master encryption key
5. begin to encrypt data

Find column semantics

When migrating from single-byte code to multi-byte code character sets, one way to move your data without data loss is to change the semantics used. In other words, VARCHAR2 and CHAR type columns can be redefined to use character semantics instead of the default byte semantics.

A schema-by-schema overview of the tables and column involved can be extracted by using the query below:

select owner "owner",
                CASE 
                    WHEN char_used = 'B' then 'BYTE'
                    WHEN char_used = 'C' then 'CHAR'
                END "semantics"
,count(*) "antall"
from dba_tab_columns
where owner in ('SCOTT','RYAN')
and data_Type in ('CHAR','VARCHAR2')
group by OWNER, ROLLUP(char_used);

owner	semantics	antall
SCOTT	BYTE	62
SCOTT	CHAR	610
SCOTT		672
RYAN	BYTE	486
RYAN		486

In this example, the SCOTT schema contains columns of both semantics types.
The schema RYAN contains only the default, BYTE.

Use the dbms_flashback to set your session back in time

A convenient way to set your session back in time, is to execute the dbms_flashback.enable_at_time procedure.
This way you can work with your data as they were at a previous point-in-time, given that your UNDO settings support it.

Oracle states

"The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, perform normal queries as if you were at that earlier time, and then return to the present. Because you can use the DBMS_FLASHBACK package to perform queries on past data without special clauses such as AS OF or VERSIONS BETWEEN, you can reuse existing PL/SQL code to query the database at earlier times."



A simple test of the procedure follows.

First, the user would need execute privileges on the package in which enable_at_time belongs, the dbms_flashback package:

connect sys/passwd as sysdba
grant execute on dbms_flashback to scott;

SQL>connect scott/passwd 
SQL>select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

SQL>

Make a change:

SQL> insert into album (album_id, album_title,artist_id) values(200,'Whiplash Smile', 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
       200 Whiplash Smile                                             10
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

To see the content of the table album as it was 5 minutes ago:

SQL> execute dbms_flashback.enable_at_time(sysdate-5/1440);

PL/SQL procedure successfully completed.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

Disable the flashback setting:

SQL> execute dbms_flashback.disable();

PL/SQL procedure successfully completed.

If you want to use flashback queries against other schemas' tables, you need object privileges to do so:

grant flashback on scott.album to jim;

Alternatively, you can grant jim the system privilege flashback any table:

connect / as sysdba
grant flashback any table to jim;

Sources: Oracle Documentation This document from Oracle Support

Missing trailing slash in listener.ora caused ORA-27101 when attempting to connect

Not too long ago, I got an error when connecting to my database using TOAD:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory

However, when connecting to the same database with sqlplus from the command line from a remote client, I could connect without errors.


Doc ID 1296982.1 "DVCA receives ORA-01034, ORA-27101" pointed me in the right direction:


"The value of ORACLE_HOME (or ORACLE_SID) passed to the DVCA utility does not match the value of ORACLE_HOME (or ORACLE_SID) that was in effect when the instance was started.

The shared memory segment key for an Oracle instance uses a hashed value based on the contents of ORACLE_HOME and ORACLE_SID. So, if one or the other of these values does not match what was used to start the instance, the resulting hash will not match, and one will encounter "ORA-27101: shared memory realm does not exist" when trying to connect to the instance.

This can commonly be caused by the presence (or lack thereof) of trailing slashes in the string for ORACLE_HOME, as passed to the -oh parameter of DVCA."

I changed my listener.ora file to read as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204 )
      (SID_NAME = mydb)
    )
  )

to

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204/) <-- Note the trailing slash character at the end of the ORACLE_HOME path
      (SID_NAME = mydb)
    )
  )

Reload the listener with lsnrctl reload, and the listener once again accepted connections from TOAD.

How to list partitions and their sizes

Using an inline view with a correlated subquery:

set lines 200
col partition_name format a40
SELECT P.PARTITION_NAME, (  SELECT ROUND(SUM(BYTES)/1024/1024/1024)
                            FROM DBA_SEGMENTS S
                            WHERE S.PARTITION_NAME = P.PARTITION_NAME
                            AND SEGMENT_NAME='&&TABLE_NAME') "size GB"
FROM DBA_TAB_PARTITIONS P
WHERE P.TABLE_NAME = '&&TABLE_NAME'
ORDER BY P.PARTITION_POSITION ASC;

Example output:

sqlplus / as sysdba @get_size.sql
Enter value for table_name: ARCHIVED_DOCUMENTS


PARTITION_NAME                      size GB
------------------------------   ----------
DOKARCHIVE1                           2.875
DOKARCHIVE2                               3
DOKARCHIVE3                               3
DOKARCHIVE4                               3
DOKARCHIVE5                               3
DOKARCHIVE6                          2.8125
DOKARCHIVE7                            2.75

Or, limit the output to specific partitions:

select s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='SALES_ARCHIVE'
and   s.owner='SH'
and s.partition_name in ( 'P_2015_01','P_2016_01','P_2017_01')
group by s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	GB
P_2015_01	SALES_2015	ENABLED	22077874	2,3
P_2016_01	SALES_2016	ENABLED	394694450	42,3
P_2017_01	SALES_2017	ENABLED	481708328	48,9

If your partitions involve lob columns, make sure you fetch the sizes of the lob partitions, too.

How to list table sizes and their LOB segment sizes

Sometimes a quick sum of the used bytes in DBA_SEGMENTS is not enough to find the total size of a table.
If you have Large Object type columns defined in the table, the total table size can potentially be far greater than what you'd think.

Let's look at an example.

I have two tables in two differen schemas, with the same name. The tables are identical, and they both contain a LOB column:

SQL> desc livedocs.documents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(19)
 VERSION                                            NUMBER(19)
 DOC_ID                                             NOT NULL NUMBER(19)
 TYPE                                               NOT NULL VARCHAR2(255 CHAR)
 NUM_PAGES                                          NUMBER(15)
 CREATED                                            TIMESTAMP(6)
 LAST_CHANGED                                       TIMESTAMP(6)
 CONTENTS                                           NOT NULL BLOB

Check the tables' sizes:

SELECT OWNER,SEGMENT_NAME,SUM(BYTES)/1024/1024/1024 "table size (GB)" 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('DOCUMENTS') 
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
GROUP BY OWNER,SEGMENT_NAME;

Result:

OWNER	SEGMENT_NAME	table size (GB)
LIVEDOCS	DOCUMENTS	2,5
ARCHIVEDDOCS	DOCUMENTS	1,875

Let's find the name of the LOB segments and their corresponding indexes:
(The LOB indexes are internal data structures only. See this post.)

SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME 
FROM DBA_LOBS 
WHERE TABLE_NAME='DOCUMENTS' 
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS');

OWNER	TABLE_NAME	SEGMENT_NAME	COLUMN_NAME	INDEX_NAME
ARCHIVEDDOCS	DOCUMENTS	ARCHIVED_DOCS_LOB	CONTENT	SYS_IL0000123181C00008$$
LIVEDOCS	DOCUMENTS	LIVE_DOCS_LOB	CONTENT	SYS_IL0000194213C00009$$

How much space have been allocated to these LOB segments?

SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) "LOB size (GB)" 
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN 
    (
    SELECT SEGMENT_NAME 
    FROM DBA_LOBS WHERE TABLE_NAME='DOCUMENTS'
    AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
    )
GROUP BY OWNER,SEGMENT_NAME 
; 

OWNER	SEGMENT_NAME	LOB size (GB)
LIVEDOCS	LIVE_DOCS_LOB	1256
ARCHIVEDDOCS	ARCHIVED_DOCS_LOB	3642

Putting it together, you can list both the table and its dependent LOB segments like this:

SELECT S.OWNER "Owner",NVL(S.SEGMENT_NAME, 'TABLE TOTAL SIZE') "Segment name",ROUND(SUM(S.BYTES)/1024/1024/1024,1) "Segment size (GB)"
FROM  DBA_SEGMENTS S 
WHERE S.SEGMENT_NAME IN ('DOCUMENTS')
AND   S.OWNER IN ('LIVEDOCS','ARCHIVEDOCS')
OR    S.SEGMENT_NAME IN (
                    (
                        SELECT L.SEGMENT_NAME FROM DBA_LOBS L 
                        WHERE L.TABLE_NAME = 'DOCUMENTS' 
                        AND L.OWNER = S.OWNER 
                        AND L.OWNER in ('LIVEDOCS','ARCHIVEDOCS')
                    )
)
GROUP BY S.OWNER,ROLLUP(S.SEGMENT_NAME)
ORDER BY 1,2,3; 

The resulting listing is:

Owner	Segment name	Segment size (GB)
ARCHIVEDDOCS	ARCHIVED_DOCS_LOB	3642,4
ARCHIVEDDOCS	DOCUMENTS	1,9
ARCHIVEDDOCS	TABLE TOTAL SIZE	3644,3
LIVEDOCS	DOCUMENTS	2,5
LIVEDOCS	LIVE_DOCS_LOB	1255,9
LIVEDOCS	TABLE TOTAL SIZE	1258,4

How to add minutes, seconds, hours or days to a datetime value

Simply add the desired interval to your timestamp value:

select current_timestamp,
       current_timestamp + interval '3' minute "current_timestamp+3min" 
from dual;


CURRENT_TIMESTAMP                                 current_timestamp+3min
----------------------------------------          ---------------------------------------------------------------------------
24.08.2016 19.44.32,808781 +2:00                  24.08.2016 19.47.32,808781000 +2:00

There are numerous possibilities to add very granular time intervals to an existing datetime value.

Check the examples of Interval literals in the official documentation.