Friday 19 April 2013

ORA-31536: / ORA-28350: cannot encrypt the specified column recorded in CDC synchronized change table:- Solution

ORA-28350: cannot encrypt the specified column recorded in CDC synchronized change table



ORA-31536: cannot support encrypted column  in the source table


Solution:- You can use Encrypted tablespace with encryption algoritham for the source table


create tablespace encrypt_data_tbl  datafile '/app/oracle/oradata/orcl/encrypt_data01.dbf' size 100m ENCRYPTION USING 'AES256'  DEFAULT STORAGE (ENCRYPT);


ALTER TABLE EMP_MASTER MOVE TABLESPACE ENCRYPT_DATA_TBL;


EXEC DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE (....);


Wednesday 17 April 2013

Transparent Data Encryption in Oracle 11g

 

   Transparent Data Encryption in Oracle 11g R&D

Tablespace Encryption Setup

1. Make sure the ORACLE_BASE variable has been set. If it has not, set it by issuing

$ export ORACLE_BASE=/app/oracle



2. Change to the ORACLE_BASE directory and then to the admin subdirectory for that instance. In my case, the instance is named prolin1, so I issue

$ cd $ORACLE_BASE/admin/ora11g



3. Create a directory called “wallet” to hold the wallet:

$ mkdir wallet

$ sqlplus / as sysdba
SQL> alter system set encryption key authenticated by "ASHOK";
 
The password is case-sensitive.
 
After the database is opened, the wallet remains open until either the wallet is explicitly closed or the database is shut down, at which time the wallet automatically closes. You can reopen this wallet after the database is restarted, by using

SQL> alter system set wallet open identified by "ASHOK";
 
 
 
Now that the wallet is set up, we can create the encrypted tablespace.
1.     The following code sets up an encrypted tablespace named  encrypt_data_tbl
2.     create tablespace encrypt_data_tbl  datafile '/app/oracle/oradata/inc11g/encrypt_data01.dbf' size 100m ENCRYPTION USING 'AES256'  DEFAULT STORAGE (ENCRYPT);
Also we can resize and make it auto extent

ALTER DATABASE DATAFILE '/app/oracle/oradata/inc11g/encrypt_data01.dbf'
RESIZE 200M;

ALTER DATABASE DATAFILE '/app/oracle/oradata/inc11g/encrypt_data01.dbf'
AUTOEXTEND ON;

Note the special encryption using 'AES128’ clause, which indicates that the AES algorithm is to be used with a 128-bit key. You can also use the values AES192 and AES256 (in place of AES128, the default value) to use 192- and 256-bit keys, respectively.
3.     Once the tablespace is created, we can create objects in it. For instance, the following code creates a table called ALL_LOOKENCRYPT
SQL> alter user ashok quota unlimited on encrypt_data_tbl;

User altered.

CREATE TABLE ashok.ALL_LOOKENCRYPT
(
   ALL_LOOKUPS_PKEY   NUMBER NOT NULL,
   TYPE               VARCHAR2 (50 BYTE) NOT NULL,
   TYPE_DESCRIPTION   VARCHAR2 (100 BYTE),
   CODE               VARCHAR2 (100 BYTE) NOT NULL,
   CODE_DESC          VARCHAR2 (250 BYTE),
   VALUE              VARCHAR2 (100 BYTE) NOT NULL,
   VALUE_DESC         VARCHAR2 (250 BYTE)
)
TABLESPACE encrypt_data_tbl;

SQL>insert into ashok.ALL_LOOKENCRYPT select ALL_LOOKUPS_PKEY,TYPE,TYPE_DESCRIPTION,CODE,CODE_DESC,VALUE,VALUE_DESC from ashokan.all_lookups where ALL_LOOKUPS_PKEY<=10;
 
 
To confirm encryption,

 
 
This will not show any output, because the MARRIED value has been stored in an encrypted tablespace. Searching for a cleartext value found nothing, as expected. (Note that if we do a string search in a file of an unencrypted tablespace, a successful search will return the cleartext from the file.) see below
Next, let’s create two identical tables— ALL_LOOKENCRYPT and ALL_LOOKNONENCRYPT —in tablespaces normal_ts and encrypt_data_tbl (the encrypted tablespace we created earlier), respectively, and populate the tables in the same way.
 
create tablespace nonencrypt_data_tbl  datafile '/app/oracle/oradata/inc11g/nonencrypt_data01.dbf' size 100m autoextend on next 1M;
 
CREATE TABLE ashok.ALL_LOOKNONENCRYPT
(
   ALL_LOOKUPS_PKEY   NUMBER NOT NULL,
   TYPE               VARCHAR2 (50 BYTE) NOT NULL,
   TYPE_DESCRIPTION   VARCHAR2 (100 BYTE),
   CODE               VARCHAR2 (100 BYTE) NOT NULL,
   CODE_DESC          VARCHAR2 (250 BYTE),
   VALUE              VARCHAR2 (100 BYTE) NOT NULL,
   VALUE_DESC         VARCHAR2 (250 BYTE)
)
TABLESPACE nonencrypt_data_tbl;

insert into ashok.ALL_LOOKNONENCRYPT select ALL_LOOKUPS_PKEY,TYPE,TYPE_DESCRIPTION,CODE,CODE_DESC,VALUE,VALUE_DESC from ashok.ALL_LOOKENCRYPT where ALL_LOOKUPS_PKEY<=10;

SET DEFINE OFF;
Insert into ashok.ALL_LOOKNONENCRYPT
   (ALL_LOOKUPS_PKEY, TYPE, CODE, VALUE)
 Values
   (12, 'MARITAL_STATUS', 'MS1',
    'MARRIED');
COMMIT;
strings nonencrypt_data01.dbf | grep MARRIED
see below




Tablespace Encryption and Performance

An issue with any encryption method is the negative impact on performance.
      For example, searches for patterns in the values in an encrypted column may or may not be able to use indexes. That is exactly where TDE tablespace encryption excels—it allows data to be in cleartext in the buffer cache, where all the searching occurs.
To accurately ascertain the performance impact of encryption in the tablespace, we can do a small test.

Try with encrypted table
set autot on explain stat
set timing on
SELECT
    ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
   CODE, CODE_DESC, VALUE,
   VALUE_DESC
FROM ASHOK.ALL_LOOKENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/

6 rows selected.

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 2677629693

--------------------------------------------------------------------------------
-----

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time
    |

--------------------------------------------------------------------------------
-----

|   0 | SELECT STATEMENT  |                 |     62700 |     3   (0)| 00:00
:01 |

|*  1TABLE ACCESS FULL| ALL_LOOKENCRYPT |     62700 |     3   (0)| 00:00
:01 |

--------------------------------------------------------------------------------
-----


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TYPE"='EMPLOYMENT_STATUS')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>


Try with NON encrypted table
set autot on explain stat
set timing on
SELECT
    ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
   CODE, CODE_DESC, VALUE,
   VALUE_DESC
FROM ASHOK.ALL_LOOKNONENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/


6 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2487804367

--------------------------------------------------------------------------------
--------

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Ti
me     |

--------------------------------------------------------------------------------
--------

|   0 | SELECT STATEMENT  |                    |     62700 |     3   (0)| 00
:00:01 |

|*  1TABLE ACCESS FULL| ALL_LOOKNONENCRYPT |     62700 |     3   (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TYPE"='EMPLOYMENT_STATUS')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>
The execution time is not approximately the same for the query in both the unencrypted and encrypted tablespaces, with about the same number of blocks fetched in both cases.
Finally, let’s create an index on each of the tables on the TYPE column. 

Create index all_look_enc_idx
on ALL_LOOKENCRYPT (TYPE)
tablespace encrypt_data_tbl
/
create index all_look_nonenc_idx
on ALL_LOOKNONENCRYPT (TYPE)
tablespace nonencrypt_data_tbl
/
begin
   dbms_stats.gather_table_stats (
        ownname => 'ASHOK',
        tabname => 'ALL_LOOKENCRYPT',
        estimate_percent => 100,
        cascade => true
    );
end;
/
begin
   dbms_stats.gather_table_stats (
        ownname => 'ASHOK',
        tabname => 'ALL_LOOKNONENCRYPT',
        estimate_percent => 100,
        cascade => true
    );
end;
/
ALTER SYSTEM FLUSH BUFFER_CACHE;

Index scan on table in encrypted tablespace

set autot on explain stat
set timing on
SELECT
    ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
   CODE, CODE_DESC, VALUE,
   VALUE_DESC
FROM ASHOK.ALL_LOOKENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/

6 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2666554740

--------------------------------------------------------------------------------
----------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%
CPU)| Time     |

--------------------------------------------------------------------------------
----------------

|   0 | SELECT STATEMENT            |                  |     6 |   228 |     3
 (0)| 00:00:01 |

|   1TABLE ACCESS BY INDEX ROWID| ALL_LOOKENCRYPT  |     6 |   228 |     3
 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | ALL_LOOK_ENC_IDX |     6 |       |     1
 (0)| 00:00:01 |

--------------------------------------------------------------------------------
----------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TYPE"='EMPLOYMENT_STATUS')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1118  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>
Index scan on table in unencrypted tablespace
set autot on explain stat
set timing on
SELECT
    ALL_LOOKUPS_PKEY, TYPE, TYPE_DESCRIPTION,
   CODE, CODE_DESC, VALUE,
   VALUE_DESC
FROM ASHOK.ALL_LOOKNONENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/

6 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 592077950

--------------------------------------------------------------------------------
-------------------

| Id  | Operation                   | Name                | Rows  | Bytes | Cost
 (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------

|   0 | SELECT STATEMENT            |                     |     6 |   228 |
3   (0)| 00:00:01 |

|   1TABLE ACCESS BY INDEX ROWID| ALL_LOOKNONENCRYPT  |     6 |   228 |
3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | ALL_LOOK_NONENC_IDX |     6 |       |
1   (0)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TYPE"='EMPLOYMENT_STATUS')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1118  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>
The execution time is approximately the same for the query in both the unencrypted and encrypted tablespaces, with about the same number of blocks fetched in both cases. This shows that there is no significant performance penalty for querying tables in encrypted tablespaces.



Comparison of Tablespace and Column-Level Encryption


create table ALL_LOOKENCRYPT_FIELD
nologging as
select * from ALL_LOOKNONENCRYPT;

alter table ALL_LOOKENCRYPT_FIELD
modify TYPE encrypt using 'AES256'
no salt;

CREATE INDEX ASHOK.ALL_LOOK_ENC_IDX1 ON ASHOK.ALL_LOOKENCRYPT_FIELD
(TYPE)
LOGGING
TABLESPACE ENCRYPT_DATA_TBL;

SELECT DISTINCT TABLESPACE_NAME, owner,table_name
  FROM all_tables
 WHERE owner='ASHOK' and table_name='ALL_LOOKENCRYPT_FIELD';

 SELECT DISTINCT TABLESPACE_NAME, TABLE_OWNER
  FROM all_indexes
 WHERE owner='ASHOK' and table_name='ALL_LOOKENCRYPT_FIELD';

Query comparison of column-level and tablespace encryption

set autot on explain stat
set timing on
SELECT
    count(1)
FROM ASHOK.ALL_LOOKENCRYPT_FIELD
Where
TYPE = 'EMPLOYMENT_STATUS'
/

  COUNT(1)
----------
         6

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 3909791586

--------------------------------------------------------------------------------
-------

| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------

|   0 | SELECT STATEMENT  |                   |     1 |    61 |     1   (0)| 00:
00:01 |

|   1SORT AGGREGATE   |                   |     1 |    61 |            |
      |

|*  2 |   INDEX RANGE SCAN| ALL_LOOK_ENC_IDX1 |     6 |   366 |     1   (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TYPE"='EMPLOYMENT_STATUS')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
set autot on explain stat
set timing on
SELECT
    count(1)
FROM ASHOK.ALL_LOOKNONENCRYPT
Where
TYPE = 'EMPLOYMENT_STATUS'
/
  COUNT(1)
----------
         6

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2984913500

--------------------------------------------------------------------------------
---------

| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT  |                     |     1 |    17 |     1   (0)| 0
0:00:01 |

|   1SORT AGGREGATE   |                     |     1 |    17 |            |
        |

|*  2 |   INDEX RANGE SCAN| ALL_LOOK_NONENC_IDX |     6 |   102 |     1   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TYPE"='EMPLOYMENT_STATUS')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
Consider the difference.

which resulted in a reduction of consistent gets from 7 to a mere 1  -- less than 6 percent of the original value. This means that the query against the encrypted tablespace table generated just 6 percent of the logical I/O of the query on the table with the encrypted column.

A second major difference is in column restrictions. Certain datatypes can’t be encrypted with TDE column encryption, columns under TDE can’t be used for foreign keys, only B-tree indexes can be created against the columns under TDE—and these are just some of the limitations of TDE column encryption. However, there is no restriction on these objects in an encrypted tablespace.
TDE column encryption has additional space requirements. Typically, encrypted values are larger than unencrypted values, causing a table with column encryption to be larger overall. To check for the exact increase, we can use the show_space procedure to show the space inside the tables.

Conclusion

In general, encryption solves a security issue But degraded performance.