Oracle Transparent Data Encryption (TDE) in Tablespace for Oracle Database 12c

Transparent Data Encryption enables you to encrypt data. Typically, you encrypt sensitive data, such as credit card numbers or Social Security numbers. Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.

After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen.

This example provide a step by step guide on how to implement TDE in tablespace level.

     1.       Create a key-store directory that

$mkdir /u01/app/oracle/product/12.1.0.2/db_1/owm/tdewallet/$ORACLE_UNQNAME

       

      2.       Change directory of TNS files.

$mkdir /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

      3.       Copy the files to the above location

$cp /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

$cp /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

$cp /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

      4.       Change the environment variable of the database .bash_profile and add

export TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

      5.       Add the below entry top sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/product/11.2.0.4/db_1/owm/tdewallet/$ORACLE_UNQNAME

      6.       With user sys as sysdba create the keys-tore

SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/owm/tdewallet/$ORACLE_UNQNAME/' IDENTIFIED BY mypassword;

      7.       Check Wallet Status

SQL>SELECT * FROM v$encryption_wallet;

                  7.1   To manually open wallet

SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypassword;

                  7.2   To manually close wallet

SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY mypassword;

      8.       Enable Wallet with Backup

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY mypassword WITH BACKUP;

      9.       Check if Wallet is backup

SQL>SELECT con_id, key_id FROM v$encryption_keys;

      10.   Create auto login for the wallet

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/owm/tdewallet/$ORACLE_UNQNAME/ ' IDENTIFIED BY password;

      11.   Create Encrypted Tablespace

SQL>CREATE TABLESPACE <Tablespace Name> DATAFILE

  '<Datafile location and name>' SIZE 100M AUTOEXTEND ON NEXT 144M MAXSIZE UNLIMITED

LOGGING

ENCRYPTION USING 'AES256'

DEFAULT STORAGE (ENCRYPT)

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

      12.   Check Tablespace status

SQL>SELECT tablespace_name, encrypted FROM dba_tablespaces;

Oracle Database High Availability Service For Data Guard

The Maximum Availability Architecture (MAA) is Oracle's best practices for near to zero best failover. It is based on proven Oracle high availability technologies and recommendations. The goal of the MAA is to remove the complexity in designing the optimal high availability architecture by providing configuration recommendations and tuning tips to get the most out of your architecture and Oracle features. This document explain step by step on how to create High Availability Service on grid control service. 

This instructions assumes that you already have:

• Grid Control Setup
• Oracle RAC Database Installation
• Oracle Data Guard

STEPS

      1.       Connect to Primary Database

      2.       Run the below command

$ srvctl add service –d <DATABASE> -s <SERVICE_NAME> -r <DB_INSTANCE1>,<DB_INSTANCE2> -l PRIMARY –q FALSE –e NONE –m NODE –w 0 –z 0

        3.       Configure the services on Primary Database

$ srvctl config service –s <SERVICE_NAME> –d <DATABASE>

        4.       Start the service on Primary Database

$ srvctl start service –d <DATABASE> -s <SERVICE_NAME>

        5.       Load the environment of grid_asm and check that the service is STABLE and running OK

$ crsctl stat res –t

i.e

ora.xxxxxx.xxxxxx_ha_jdbc.svc

   1    ONLINE  ONLINE       xxxxxxxxxxx       STABLE

   2    ONLINE  ONLINE       xxxxxxxxxxx       STABLE

        6.       Connect to Primary Database as SYSDBA and run the below command only in one node

>SQL

BEGIN

DBMS_SERVICE.CREATE_SERVICE (service_name =>'<SERVICE_NAME>',network_name =>'<SERVICE_NAME>',aq_ha_notifications => FALSE, failover_method =>'BASIC',failover_type =>'NONE',failover_retries => 0,failover_delay => 0);

END;

/

        7.       Create the trigger to Primary Database as SYSDBA

>SQL

create or replace trigger START_HA_JDBC after startup on database

DECLARE

role varchar(30);

BEGIN

select database_role into role from v$database;

if role='PRIMARY' then

dbms_service.start_service('<SERVICE_NAME>');

end if;

END;

/

         8.       Connect to DR database and run the below command only in one node

$ srvctl add service –d <DATABASE> -s <SERVICE_NAME> -r <DB_INSTANCE1>,<DB_INSTANCE2> -l PRIMARY –q FALSE –e NONE –m NODE –w 0 –z 0

         9.       On DR database configure the service name

$ srvctl config service –s <SERVICE_NAME> –d <DATABASE>

         10.   Connect to DR Server and load environment grid_asm and check the service, the service should be OFFILINE

$ crsctl stat res –t

i.e

ora.xxxxxx.xxxxxxx_ha_jdbc.svc

      1        OFFLINE OFFLINE           STABLE

      2        OFFLINE OFFLINE           STABLE

       

         11.   TNS name must be change as follow

ALIAS_NAME =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-scan)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = <SERVICE_NAME>)

      (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))

    )

 )

Configure Oracle Report Server to Display correctly Greek fonts in PDF and HTML format

Upload the arial font in ttf format and pfa format

arialbi.ttf

arialbd.ttf

ariali.ttf

arial.ttf

[oraclefrm@srvas1 AFM]$ od -a ariali | grep

Usage: grep [OPTION]... PATTERN [FILE]...
Try `grep --help' for more information.
[oraclefrm@srvas1 AFM]$ od -a arial|grep cr
[oraclefrm@srvas1 AFM]$ od -a arialbi|grep cr
[oraclefrm@srvas1 AFM]$ od -a arialbd|grep cr
[oraclefrm@srvas1 AFM]$ od -a ariali|grep cr
[oraclefrm@srvas1 AFM]$

Rename arial to :

mv arial   ArialMT
mv arialbd Arial-BoldMT
mv ariali  Arial-ItalicMT
mv arialbi Arial-BoldItalicMT

Add In screenprinter.ppd:

*Font ArialMT: Standard "(001.004)" Standard ROM
*Font Arial-BoldMT: Standard "(001.004)" Standard ROM
*Font Arial-ItalicMT: Standard "(001.004)" Standard ROM
*Font Arial-BoldItalicMT: Standard "(001.004)" Standard ROM

[oraclefrm@srvas1 bin]$ vi reports.sh
Add the application reports’ repository
REPORTS_PATH=$ORACLE_HOME/reports/rdf:$ORACLE_HOME/reports/fonts
:$ORACLE_HOME/reports/templates:$ORACLE_HOME/reports/samples/demo
:$ORACLE_HOME/reports/integ:$ORACLE_HOME/reports/printers:${REPORTS_PATH};
export REPORTS_PATH

Add the end add:

NLS_LANG=AMERICAN_AMERICA.EL8ISO8859P7; export NLS_LANG

Copy the Arial fonts under AFM into $ORACLE_HOME/reports/fonts
Transfer binary the *.ttf file into $ORACLE_HOME/reports/fonts

Comment out the Arial at the beginning of the file uifont.ali and
add the following lines in uifont.ali under [ PDF:Subset ]section
[PDF:Subset ]
Arial..Italic.Bold.. = "arialbi.ttf"
Arial...Bold.. = "arialbd.ttf"
Arial..Italic... = "ariali.ttf"
Arial..... = "arial.ttf"