Thursday 27 September 2018

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;


Tuesday 25 September 2018

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))
    )
 )

Tuesday 27 September 2011

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"