===========================================
In Oracle 12cR2, to encrypt tablespace using the Offline method:
===========================================
Note: This script is tested in TEST Database.
In a production database executes at your own risk. The author (Hayat Mohammad Khan) claims no responsibility.
--Create tablespace with no encryption enabled
SQL> create tablespace TESTnoENC datafile 'D:\DB_HOME\ORADATA\ORCL\TESTnoENC101.DBF' size 10M;
Tablespace created.
SQL> alter tablespace TESTnoENC add datafile 'D:\DB_HOME\ORADATA\ORCL\TESTnoENC102.DBF' size 10M;
Tablespace altered.
select file#,name from v$datafile
File# Name
================================================
1 D:\DB_HOME\ORADATA\ORCL\SYSTEM01.DBF
2 D:\DB_HOME\ORADATA\ORCL\TESTNOENC102.DBF
3 D:\DB_HOME\ORADATA\ORCL\SYSAUX01.DBF
4 D:\DB_HOME\ORADATA\ORCL\UNDOTBS01.DBF
5 D:\DB_HOME\ORADATA\ORCL\TESTNOENC101.DBF
7 D:\DB_HOME\ORADATA\ORCL\USERS01.DBF
-- To enable Encryption, take the tablespace into offline mode.
SQL> ALTER TABLESPACE TESTnoENC OFFLINE;
Tablespace altered.
SQL> ALTER DATABASE DATAFILE 'D:\DB_HOME\ORADATA\ORCL\TESTnoENC102.DBF' ENCRYPT;
Database altered.
SQL> alter tablespace TESTnoENC online;
select file#,tablespace_name,encrypted from v$datafile_header where file# in (2,5);
F# TBS-Name Encrypted
==============================
2 TESTNOENC NO
5 TESTNOENC YES
SQL> ALTER DATABASE DATAFILE 'D:\DB_HOME\ORADATA\ORCL\TESTnoENC102.DBF' ENCRYPT;
ALTER DATABASE DATAFILE 'D:\DB_HOME\ORADATA\ORCL\TESTnoENC102.DBF' ENCRYPT
*
ERROR at line 1:
ORA-28440: cannot offline encrypt or decrypt data file 2 - file is in use or
recovery
ORA-01110: data file 2: 'D:\DB_HOME\ORADATA\ORCL\TESTNOENC102.DBF'
---try with Higher encryption algorithm
SQL> ALTER TABLESPACE TESTnoENC ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
ALTER TABLESPACE TESTnoENC ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
*
ERROR at line 1:
ORA-28431: cannot encrypt an already encrypted data file TESTNOENC
---offine with default previously used algorithm
SQL> ALTER TABLESPACE TESTnoENC OFFLINE;
Tablespace altered.
SQL> ALTER DATABASE DATAFILE 'D:\DB_HOME\ORADATA\ORCL\TESTnoENC102.DBF' ENCRYPT;
Database altered.
SQL> show parameter encr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string CLOUD_ONLY
The default value of this parameter is “CLOUD_ONLY.” According to the Oracle documentation,
there are three possible values: “CLOUD_ONLY / ALWAYS / DDL.” If we change the value to “ALWAYS”
then the tablespace will be encrypted with AES128 even if there is no “ENCRYPTION” clause specified when creating the tablespace.
-----Default Encryption Algorithm is : AES128
SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS, STATUS FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;
NAME ENCRYPT STATUS
------------------------------ ------- ----------
TESTNOENC AES128 NORMAL
#####################################################
Moral of the story:
#####################################################
If you want to use different algorithm, during OFFLINE clause modify it:
--create tablespace with no encryption enabled.
create tablespace TEST_ALGO_AES256 datafile 'D:\DB_HOME\ORADATA\ORCL\TEST_ALGO_AES256_01.DBF' size 10M;
--Two step process:
SQL> alter tablespace TEST_ALGO_AES256 offline;
Tablespace altered.
SQL> ALTER TABLESPACE TEST_ALGO_AES256 ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
Tablespace altered.
-- Make it online once encryption algorithm is enabled
SQL> ALTER TABLESPACE TEST_ALGO_AES256 ONLINE;
Tablespace altered.
SQL> SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS, STATUS FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;
NAME ENCRYPT STATUS
------------------------------ ------- ----------
TESTNOENC AES128 NORMAL
TEST_ALGO_AES256 AES256 NORMAL
File#8, belongs to TEST_ALGO_AES256 tablespace
select file_id, file_name,tablespace_name from dba_data_files where file_id=8;
8 D:\DB_HOME\ORADATA\ORCL\TEST_ALGO_AES256_01.DBF TEST_ALGO_AES256
No comments:
Post a Comment