AMAZON LINK for 11g New Features for DBA

OCP Oracle Database 11g: New Features for Administrators Exam Guide (Exam 1Z0-050): OCP Oracle Database 11g: New Features for Administrators Exam Guide (Exam 1Z0-XXX) (Osborne ORACLE Press Series)

Oracle 11g or 11gR2 Tablespace Encryption

Oracle 11g (11.2.0.1) Configure Tablespace Encryption using Safenet Hardware Security Module (LUNA SA 4.4.0 HSM)

Note: Step 1 to 4 need repeating on all the nodes in the cluster

Step 5 and 6 don't need repeat for RAC

Example : DATABASE SERVER Hostname is dbserver01
: HSM Device name is hsm01


1. Install HSM Client software on database Server dbserver01:


2. Register Oracle Database server (client) with HSM.

login as root on dbserver01:

su - root
password: *******

[root@dbserver01 bin]# cd /usr/lunasa/bin

-- Fetch server parameter from hsm01 to database server.

[root@dbserver01 bin]# /ctp admin@hsm01.domain.local:server.pem .

--- Verify Cerfiticate
[root@dbserver01 bin]# openssl x509 -in server.pem -noout -text

--- Register Hsm01 as authorised HSM Server.

[root@dbserver01 bin]# ./vtl addServer -n hsm01 -c server.pem

New server hsm01 successfully added to server list.

-- Verify Server added

[root@dbserver01 bin]# ./vtl listServers
Server: hsm01


-- Create HSM Client certificate from the Database Server

[root@dbserver01 bin]# ./vtl createCert -n dbserver01
Private Key created and written to: /usr/lunasa/cert/client/dbserver01Key.pem
Certificate created and written to: /usr/lunasa/cert/client/dbserver01.pem


--- upload client certificate to HSM

[root@dbserver01 bin]# ./ctp /usr/lunasa/cert/client/dbserver01.pem admin@hsm01.domain.local:
admin@hsm01.domain.local's password:
dbserver01.pem 100% 1172 1.1KB/s 00:00


---- Login to HSM Device via SSH or Local console

ssh admin@hsm01.domain.local
password: ***********

----- Register dbserver01 to HSM as client

[hsm01] lunash:>client reg -c dbserver01 -h dbserver01

'client register' successful.

Command Result : 0 (Success)


--- Assing HSM partition to dbserver01(client)

[hsm01] lunash:>client a -p Oracle -c dbserver01

'client assignPartition' successful.

Command Result : 0 (Success)


3. Verify that Oracle Database server can talk to HSM:


[root@gridcluster03 bin]# ./vtl verify

The following Luna SA Slots/Partitions were found:

Slot Serial # Label
==== ======== =====
1 951962001 Oracle

---- You have Successfully Added Database server to HSM as client and Now Oracle Server is ready to use HSM as wallet to Store Tablespace and Column Master keys.



4. Configure Oracle to use HSM.

--- copy the HSM Libarary to Oracle required location:

[root@dbserver01 bin]# mkdir -p /opt/oracle/extapi/64/hsm/safenet/4.4.0/

[root@dbserver01 bin]#cp /usr/lunasa/lib/libshim.so /opt/oracle/extapi/64/hsm/safenet/4.4.0/libshim.so

[root@dbserver01 bin]#chown -R oracle:dba /opt/oracle


--- Login as oracle and update sqlnet.ora

[root@dbserver01 bin]# su - oracle

[oracle@dbserver01 ]$ vi $ORACLE_HOME/network/admin/sqlnet.ora


Add the following line
------------------------------
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = HSM))


5. Enable Tablespace Encryption in database by createing the Master key in HSM wallet.


[oracle@dbserver01 ]$ sqlpus "/ as sysdba"


----- Create wallet in HSM Use this command once only to create the master key. Every time you bounce database you need to open the wallet.

alter system set encryption key identified by "hsmpartition_password";



---- Create tablespace:

CREATE TABLESPACE TDE_TEST01 DATAFILE '+DATA' SIZE 10m ENCRYPTION using 'AES256' DEFAULT STORAGE (ENCRYPT);




6. Every Time you open start database open the wallet by following command.

alter system set encryption wallet open identified by "hsmpartition_password";


Note: Step 1 to 4 need repeating on all the nodes in the cluster

Flashback Database

SQL> alter system set db_recovery_file_dest='+BACKUPDG' scope=both sid='*';

SQL> alter system set db_recovery_file_dest_size=5000M scope=both sid='*';

SQL> alter system set db_flashback_retention_target= 1440 scope=both sid='*';

--- Verify the parameter setup .

COLUMN NAME FORMAT A40
COLUMN VALUE FORMAT A50

SELECT INST_ID,NAME,VALUE FROM gv$PARAMETER WHERE NAME IN ('db_recovery_file_dest','db_recovery_file_dest_size','db_flashback_retention_target')
/

INST_ID NAME VALUE
---------- ---------------------------------------- --------------------------------------------------
1 db_recovery_file_dest +BACKUPDG
1 db_recovery_file_dest_size 5218762752
1 db_flashback_retention_target 1440
2 db_recovery_file_dest +BACKUPDG
2 db_recovery_file_dest_size 5218762752
2 db_flashback_retention_target 1440

6 rows selected.

-- Shutdown database and startup mount;

SQL> shutdown immediate;
SQL> startup mount;
Database mounted.

--- Ensure database is in archive log mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 71
Next log sequence to archive 0
Current log sequence 72

---- Turn the Flashback on;

SQL> alter database flashback on;

-- Verify Flashback in Turned on .

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

SQL> CREATE RESTORE POINT BEFORE_PATCH;

Restore point created.

---- Flashback Related Views :

SELECT OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME ,FLASHBACK_SIZE FROM v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL FLASHBACK_SIZE
-------------------- --------- --------------
859379 10-DEC-09 159416320


SQL> SELECT SCN,NAME ,TIME FROM V$RESTORE_POINT;

SCN NAME TIME
---------- ---------------------------------------- ---------------------------------------------------------------------------
976812 BEFORE_PATCH 11-DEC-09 03.54.59.000000000 PM


COLUMN NAME FORMAT A50
1* SELECT NAME,LOG#,BYTES,FIRST_TIME FROM V$FLASHBACK_DATABASE_LOGFILE
SQL> /

NAME LOG# BYTES FIRST_TIM
-------------------------------------------------- ---------- ---------- ---------
+BACKUPDG/armynet/flashback/log_1.367.705266421 1 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_2.365.705266477 2 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_3.361.705276043 3 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_4.360.705286825 4 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_5.358.705286845 5 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_6.357.705301241 6 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_7.356.705304845 7 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_8.354.705315645 8 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_9.349.705322797 9 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_10.348.705330031 10 15941632 11-DEC-09

10 rows selected.



=======================

---- Flasback to scn:

FLASHBACK [STANDBY] DATABASE TO [BEFORE] SCN ;


---- Flasback to timestamp

FLASHBACK [STANDBY] DATABASE TO [BEFORE] TIMESTAMP ;


---- Flasback to restore_point
FLASHBACK [STANDBY] DATABASE TO [BEFORE] RESTORE POINT ;



Example of Flasback using Restore point (Standby database open read/write and convert back to standby)



CREATE RESTORE POINT before_open;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ALTER DATABASE OPEN;


--- DATABASE OPEN TO READ/WRITE OPERATION.


----TO CONVERT BACK TO STANDBY;


SHUTDOWN IMMEDIATE;

STARTUP MOUNT;
ALTER DATABASE FALSHBACK TO RESTORE POINT BEFORE_OPEN;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ORA-28362: master key not found

ORA-28362: master key not found
Cause:
The required master key required could not be located. This may be casued by the use of an invalid or incorrect wallet.
Action: Check wallet location parameters to see if they specify the correct wallet. Also, verify that an SSO wallet is not being used when an encrypted wallet is intended.

Error Indicate that It doesn't found the current Master key. Effectively Command will create the New master key as the specified location(Either FILE or HSM. Any objects(TAblespace) created with OLD Master key will be unusable.

ORA-28386: "cannot perform a PKI certificate-based Master Key Rekey"
// *Cause: A PKI certificate-based Master Key Rekey was attempted when
// Tablespace Encryption or HSM was in use.
// *Action: Perform a symmetric Master Key Rekey or disable Tablespace
// Encryption for all tablespaces.

11gR2 (11.2.0.1) database don't support REKEY if MASTERKEY is stored in HSM(Hardware Secure module".