What is the Software keystore used by Oracle Transparent Data Encryption?

The Software keystore is the container that stores the TDE master key.
There is one keystore per database.

From the Oracle Documentation:

"A software keystore is a container that stores the Transparent Data Encryption master encryption key."

By "container", Oracle means either

* a software keystore
* a hardware module security (HSM) keystore
* Oracle Key Vault keystore (separately licenced)

Personally I have only used software keystores, which is the equivalent of keeping the keystore as a file on a file system, or a file on an ASM disk group.


How is the keystore located by by the database?

"the database locates this keystore by checking the keystore location that you define in the sqlnet.ora file."

Example of sqlnet.ora from my own environment:

# WALLET_OVERRIDE is only applicable when using auto login, set to FALSE
SQLNET.WALLET_OVERRIDE = FALSE
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)

Now that the keystore is defined, you can log into the database to create and open the keystore.
Finally, you set the TDE master encryption key.

After that, you can encrypt the data.

See my post Quick guide to set up Transparent Data Encryption for a tablespace for examples on how to set it up.

What is Transparent Data Encryption and when should it be used?

Use TDE to protect data on disk, or "data at rest".
In other words, TDE adds another layer of security to your database setup, but is by no means sufficent to protect your data by itself.

Inside the database, Oracle uses different methods to protect the data against unauthorized access.

For example, you can set up auditing to trace Access to specific tables in the database.
Another example would be redaction, introduced in Oracle 12cR1, which can hide sensitive data from the users.

However, none of these methods would protect data on disk, or "data at rest" as Oracle calls it.

From the documentation:

"TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen."

and

"To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files."

How does Oracle prevent unauthorized decryption?

"To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore."

Source: Oracle Documentation

How to use yum to list installed packages

yum list package_name

For example:

[root@myserver ~]# yum list compat-libcap1
Loaded plugins: product-id, rhnplugin, search-disabled-repos, security
This system is receiving updates from RHN Classic or RHN Satellite.
Installed Packages
compat-libcap1.x86_64                                                   1.10-1                                                   @test-rhel-x86_64-server-6
Available Packages
compat-libcap1.i686                                                     1.10-1    

See also "yum command cheat sheet" from RH for a good overview.

How to clear your bash shell's history

history -c && history -w

How to truncate a partition

ALTER TABLE MYTABLE TRUNCATE PARTITION P_2017_01;

For more details, check the documentation

How to work around ORA-12008 and ORA-01858 during an online redefinition

The following error occured during redefintion:

Move method (cons_use_pk or cons_use_rowid): cons_use_rowid
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 2

The reason:
One of the reasons why I wanted to redefine the table, was to make it partitioned.
One of the column in the interim table was therefore defined as DATE instead of VARCHAR2:

Original table:

CREATE TABLE SCOTT.MYTABLE
(
  SEQ_NUM              NUMBER Generated as Identity ( START WITH 9984 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) CONSTRAINT SYS_C0073650 NOT NULL,
  ENTRY_ID             VARCHAR2(50 BYTE)            NULL,
  ENAME                VARCHAR2(100 BYTE)           NULL,
  CREATED_DT           TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  ACTIVE_YEAR          INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  CONDITION            VARCHAR2(50 BYTE)            NULL,
  FN_ID                VARCHAR2(11 BYTE)            NULL
)
TABLESPACE USERS;

Interim table:

CREATE TABLE SCOTT.MYTABLE_INTERIM
(
  SEQ_NUM              NUMBER,
  ENTRY_ID             VARCHAR2(50 BYTE),
  ENAME                VARCHAR2(100 BYTE),
  CREATED_DT           TIMESTAMP(6),
  REVISION#            INTEGER,
  ACTIVE_YEAR          INTEGER,
  PERIOD               DATE,
  CONDITION            VARCHAR2(50 BYTE),
  FN_ID                VARCHAR2(11 BYTE)
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P_INIT VALUES LESS THAN (TO_DATE('2015-01', 'YYYY-MM'))
)
TABLESPACE USERS
;

The requirement from the Developers was that the partitions should be range partitioned on dates in the format 'YYYY-MM'.

When starting the online redefintion process, I hit the error at the top in this post.

I started to search for the root cause, and executed the following:

select seq_num,TO_DATE(PERIOD,'YYYY-MM') from Scott.mytable
fetch first 5 rows only;

which resulted in

ORA-01858: a non-numeric character was found where a numeric was expected

It turned out that the following SQL would return the data I wanted, in the correct format, defined with the correct data type:

select seq_num,to_date(to_char(TO_DATE(PERIOD),'YYYY-MM'),'YYYY-MM')
from Scott.mytable
fetch first 5 rows only;

This Expression had to go into the redefinition-command, like this:

begin
DBMS_REDEFINITION.start_redef_table(uname=>'EVENT',
orig_table=>'MYTABLE',
int_table=>'MYTABLE_INTERIM',
col_mapping =>'SEQ_NUM SEQ_NUM,ENTRY_ID ENTRY_ID,ENAME ENAME,CREATED_DT CREATED_DT,REVISION# REVISION#,ACTIVE_YEAR ACTIVE_YEAR,to_date(to_char(TO_DATE(PERIOD),''YYYY-MM''),''YYYY-MM'') PERIOD,CONDITION CONDITION,FN_ID FN_ID',
options_flag=>dbms_redefinition.cons_use_pk);
end;
/

After this change, the redefinition executed successfully.

How to create a logon trigger

In this particular example, I create a logon trigger that sets one of many available NLS session parameters, as well as sets an undocumentet parameter that I was recommended to set by Oracle Support services:

CREATE OR REPLACE TRIGGER logon_optimizer 
after logon on database
begin 
   if user in ('SCOTT','JACK','BOB')
   then 
      execute immediate 'alter session set NLS_LANGUAGE="GERMAN"';
      execute immediate 'alter session set "_optimizer_unnest_disjunctive_subq"= FALSE';
   end if; 
end;
/

Test to verify that it Works:

Connect scott/tiger
set lines 200
col parameter format a30
col value format a40
select * from nls_session_parameters;


PARAMETER                      VALUE
------------------------------ -----------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  NORWAY

Now generate an error:

select * from xxx
              *
ERROR at line 1:
ORA-00942: Tabelle oder View nicht vorhanden