All entries for Friday 27 January 2012

January 27, 2012

Oracle TDE

First we check there is no wallet in the database.
oracle@warwick$ mkdir /app/oracle/admin/hsun/tde_wallet

oracle@warwick$ sqlplus / as sysdba
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

We now create the wallet using “orapki”. Then create a local auto-login wallet. Note the wallet has no master key in it. The status of wallet is “OPEN_NO_MASTER_KEY”


oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet

oracle@warwick$ ls -l /app/oracle/admin/hsun/tde_wallet/
total 1
-rw-------   1 oracle   oinstall    3512 Jan 27 12:47 ewallet.p12

oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet  -auto_login_local
oracle@warwick$ ls -l /app/oracle/admin/hsun/tde_wallet
total 17
-rw-------   1 oracle   oinstall    3589 Jan 27 12:50 cwallet.sso
-rw-------   1 oracle   oinstall    3512 Jan 27 12:47 ewallet.p12

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>CREATE TABLESPACE securespace
DATAFILE '/app/oracle/oradata/hsun/secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

ERROR at line 1:
ORA-28374: typed master key not found in wallet

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN_NO_MASTER_KEY

SYS@hsun SQL>alter system switch logfile;
SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

SYS@hsun SQL>alter system set encryption wallet open identified by "password";

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN_NO_MASTER_KEY

SYS@hsun SQL>alter system set encryption wallet  close identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

We remove this wallet and create a new wallet using “alter system” . The wallet will be created and open.

oracle@warwick$ rm  /app/oracle/admin/hsun/tde_wallet/ewallet.p12 

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>alter system set encryption wallet open identified by "password";
alter system set encryption wallet open identified by "password" 
*
ERROR at line 1:
ORA-28367: wallet does not exist

SYS@hsun SQL>alter system set encryption key identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>alter system set encryption wallet  close identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

Create a new local auto-login wallet. It open automatically when db open. You cannot close it, even the “close” wallet command run successfully without warnings.

oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet  -auto_login_local

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>alter system set encryption wallet close;

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

We can delete this local wallet and revert to use first wallet.

oracle@warwick$ rm /app/oracle/admin/hsun/tde_wallet/cwallet.sso 

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

SYS@hsun SQL>alter system set encryption wallet open identified by "password";

System altered.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>CREATE TABLESPACE securespace
DATAFILE '/app/oracle/oradata/hsun/secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SYS@hsun SQL>
SYS@hsun SQL>create table  person (id number, name varchar2(20))  tablespace securespace;

Table created.

SYS@hsun SQL>insert into person values (1, 'hongfeng');

1 row created.

SYS@hsun SQL>commit;

Commit complete.

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup

SYS@hsun SQL>select * from person;
select * from person
              *
ERROR at line 1:
ORA-28365: wallet is not open

SYS@hsun SQL>alter system set encryption wallet open identified by "password";

System altered.

SYS@hsun SQL>select * from person;

        ID NAME
---------- --------------------
         1 hongfeng

SYS@hsun SQL>exit

We can re-create a local wallet. We don’t need to use the first wallet now. Local wallet will take over from it.


oracle@warwick$ orapki wallet create -wallet /app/oracle/admin/hsun/tde_wallet -auto_login_local

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup
SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
----------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
OPEN

SYS@hsun SQL>select * from person;

        ID NAME
---------- --------------------
         1 hongfeng

We can change the password of first wallet. The “orapki” only require you to input password once. So be careful you know what you input. Or put the password in the command line.


oracle@warwick$ orapki wallet change_pwd -wallet /app/oracle/admin/hsun/tde_wallet 
Enter wallet password:       llll

New password:
Enter wallet password:          88

oracle@warwick$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 27 13:41:30 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Real Application Testing options

SYS@hsun SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@hsun SQL>startup

SYS@hsun SQL>select * from person;

        ID NAME
---------- --------------------
         1 hongfeng


We can move the wallet to different location.
  
oracle@warwick$ mv /app/oracle/admin/hsun/tde_wallet/
cwallet.sso  ewallet.p12  
oracle@warwick$ mv /app/oracle/admin/hsun/tde_wallet/* /app/oracle/admin/hsun/tde_backup
oracle@warwick$ ls -l /app/oracle/admin/hsun/tde_backup
total 8
-rw-------   1 oracle   oinstall    1387 Jan 27 13:41 cwallet.sso
-rw-------   1 oracle   oinstall    1310 Jan 27 13:41 ewallet.p12
oracle@warwick$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 27 14:30:47 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Real Application Testing options

SYS@hsun SQL>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS@hsun SQL>startup
ORACLE instance started.

SYS@hsun SQL>select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
-------------------------------------------------
STATUS
------------------
file
/app/oracle/admin/hsun/tde_wallet/
CLOSED

SYS@hsun SQL>select * from hsun.person;
select * from hsun.person
                   *
ERROR at line 1:
ORA-28365: wallet is not open

SYS@hsun SQL>exit

oracle@warwick$ cp  /app/oracle/admin/hsun/tde_backup/
cwallet.sso  ewallet.p12  
oracle@warwick$ cp  /app/oracle/admin/hsun/tde_backup/cwallet.sso  /app/oracle/admin/hsun/tde_wallet/

oracle@warwick$ sqlplus / as sysdba

SYS@hsun SQL>alter system set encryption wallet open;
                                      *
ERROR at line 1:
ORA-28356: invalid open wallet syntax

SYS@hsun SQL>shutdown immediate;
SYS@hsun SQL>startup

SYS@hsun SQL>select * from hsun.person;

        ID NAME
---------- ----------------------------------------
         1 peter

Oracle securefile use TDE master key as well.

HSUN@hsun SQL>CREATE TABLE securefile_tab (
  id         NUMBER,
  clob_data  CLOB
)   2    3    4  
  5  tablespace users
  6  LOB(clob_data) STORE AS SECUREFILE securefile_lob(
 encrypt
 compress
);  7    8    9  

Table created.

HSUN@hsun SQL>select segment_name , tablespace_name from user_segments;   

SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
PERSON                                                                            SECURESPACE

HSUN@hsun SQL>INSERT INTO securefile_tab VALUES (1, 'ONE');
INSERT INTO securefile_tab VALUES (2, 'TWO');
COMMIT;

1 row created.

HSUN@hsun SQL>
1 row created.

HSUN@hsun SQL>
Commit complete.

HSUN@hsun SQL>select segment_name , tablespace_name from user_segments;   

SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
PERSON                                                                            SECURESPACE
SECUREFILE_TAB                                                                    USERS
SYS_IL0000074563C00002$$                                                          USERS
SECUREFILE_LOB                                                                    USERS

Auto login Wallet
Oracle warned that:
If an auto login wallet is being used, you must not use the ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “password” command.

In my test, no harm if you run that command.

SYS@hsun SQL>ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";

ERROR at line 1:
ORA-28354: wallet already open

BUG 11727498 Recreating Wallet
If dropping the wallet after creating TDE encrypted data (using column or tablespace encryption), the wallet can’t be recreated.

This issue has been investigated in:
BUG 11727498SECOND WALLET CREATED AFTER DELETE ORIGINAL DOES NOT HAVE TS MASTER KEY
The bug is fixed starting with release 11.2.0.3.
Whenever recreating the wallet, make sure to do a full log switch rotation and a checkpoint after the wallet has been closed.


Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXII