How to Encrypt Oracle Database (10g/11g/12c/18c/19c)
Including RAC and Dataguard Technology
NOTE:
ORCL database is a cluster database running on
instances orcldb1 and orcldb2.
Also, the DR (Disaster Recovery) is running on two instances
orclstby1 and orclstby2.
1.
PREREQUISITES
---ORCL Prod TNS
ORCLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST
= mycluster-scan) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldb)
)
)
---ORCL DR TNS
ORCLSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = mystandby-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLSTBY)
)
)
=================================================================================
1.1. Check if DR
is in Sync (Only in case of Dataguard configuration)
SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a.
last_app_timestamp, b.last_seq-a.applied_seq
ARC_DIFF
FROM (SELECT thread#,
MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM
gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM
gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;
Note: ARC_DIFF school be near 0.
1.2. Check whether
a tablespace has been encrypted
SQL>select tablespace_name,encrypted from dba_tablespaces;
Note: encrypted column should be:
-
NO if a tablespace is not encrypted
-
YES else
1.3. Set this on
.bash_profile file
$export
ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
$export ORACLE_SID=orcldb
$export DB_NAME=orcldb
In case of RAC environment, Set it on all the nodes.
Instances should be orcldb1, orcldb2…etc. Also set it on DR site in case of
Dataguard configuration.
1.4. Checking for
the Owner, tablespace name and size for segment to be encrypted
select owner,segment_name, tablespace_name, sum(bytes)/1024 size_kb from
dba_segments where segment_name in
('SEGMENT1'
,'SEGMENT2',
,…
,…
,'SEGEMENTN'
group by owner,segment_name,tablespace_name order by 2;
2.
WALLET CREATION
2.1. Modify the
SQLNET.ORA file on each node and DR to specify the wallet location
2.1.1.
Create the wallet location
$cd /u01/app/oracle/admin/orcldb
$mkdir wallet
At the end of the sqlnet.ora file, add code similar to
the following:
$cd /u01/app/oracle/product/12.2.0/db_1/network/admin
$vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE) (METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/$DB_NAME/wallet/)))
You may also For DR sqlnet.ora location
2.1.2.
Create the wallet
Connect to the database or one of the cluster node (in
case of RAC)
$ sqlplus / as sysdba
SQL> alter system set
encryption key identified by "Welcome1";
System altered.
SQL> select * from
v$encryption_wallet;
2.1.3.
Make the Wallet Auto-login
·
Browse to
/u01/app/oracle/admin/orcldb/wallet/.
$ orapki wallet create -wallet /u01/app/oracle/admin/orcldb/wallet/
-auto_login_local
·
SCP the files under /u01/app/oracle/admin/orcldb/wallet/
to all the nodes and Standby
$scp /u01/app/oracle/admin/orcldb/wallet/* oracle@hostname:/u01/app/oracle/admin/orcldb/wallet/
$scp /u01/app/oracle/admin/orcldb/wallet/* oracle@hostname:/u01/app/oracle/admin/orclstby/wallet/
·
Test if the Wallet is opened
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
--------- -------------------- ------------
file /u01/app/oracle/admin/$DB_NAME/wallet/ OPEN
If the Wallet is closed, open it by running the below
command:
SQL>alter system set encryption wallet open identified by
"Welcome1";
3.
CREATE A NEW ENCRYPTED TABLESPACES
First check to ensure that space is on the disk group
[grid@server]$
asmcmd lsdg
State
Type Rebal Sector
Logical_Sector Block AU
Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks
Voting_files Name
MOUNTED
EXTERN N 512 512 4096
4194304 5722044 2351448 0 2351448 0 N
DATA/
MOUNTED
EXTERN N 512 512 4096
4194304 2097144 1955020 0 1955020 0 N
FRA/
MOUNTED
EXTERN N 512 512
4096 4194304 15258784
3323340 0 3323340 0 N
ORCL_DG/
MOUNTED
EXTERN N 512 512 4096
4194304 190732 154836 0 154836 0 N
MGMT_DG/
MOUNTED
NORMAL N 512 512 4096
4194304 61428 60364 20476 19944 0 Y
OCR_DG/
Create
tablespace for data:
SQL>create bigfile tablespace ORCL_TAB_TBSP_TDE datafile '+ORCL_DG' size 10G autoextend on next 1G maxsize
unlimited encryption using 'AES256' default storage (ENCRYPT);
Create
tablespace for the indexes:
SQL>create tablespace ORCL_IDX_TBSP_TDE datafile '+ORCL_DG' size 1G
autoextend on next 1G maxsize unlimited encryption using 'AES256' default
storage (ENCRYPT);
Check
whether a tablespace has been encrypted
SQL>select tablespace_name,encrypted from dba_tablespaces;
4.
SCRIPT TO MOVE TABLES AND REBUILD THE INDEXES
4.1.1.
Create the directory TDE_PUMP_DIR:
SQL>CREATE OR REPLACE DIRECTORY TDE_PUMP_DIR AS
'/u01/app/oracle/admin/orcldb/dpdump';
4.1.2.
Create gen_tab_rebuild procedure
Ensures to make the necessary changes before executing
the scripts
CREATE OR REPLACE procedure SYS.gen_tab_rebuild (
tname in Varchar2, towner in varchar2)
AS
cursor sub_part is
select
TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME
from DBA_TAB_SUBPARTITIONS
where table_name=upper(tname)
and table_owner=upper(towner);
cursor tb_part is
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME
from DBA_TAB_PARTITIONS
where table_name=upper(tname)
and table_owner=upper(towner);
sb_num number;
tb_num number;
tb_number number ;
tb_name Varchar2(50);
tb_owner Varchar2(50);
fhandle utl_file.file_type;
begin
fhandle:= utl_file.fopen('TDE_PUMP_DIR','Table_'||towner||'_'||tname||'.sql','w',32767);
for tb_prt in tb_part
loop
dbms_output.put_line ('alter table
'||tb_prt.table_owner||'.'||tb_prt.table_name||' move partition ' ||tb_prt.PARTITION_NAME ||'
tablespace ORCL_TAB_TBSP_TDE parallel 8;');
utl_file.put_line(fhandle,'alter table
'||tb_prt.table_owner||'.'||tb_prt.table_name||' move partition ' ||tb_prt.PARTITION_NAME ||'
tablespace ORCL_TAB_TBSP_TDE parallel 8;');
tb_num:=tb_part%ROWCOUNT ;
end loop;
for sb_prt in sub_part
loop
dbms_output.put_line ('alter table
'||sb_prt.table_owner||'.'||sb_prt.table_name||' move subpartition '
||sb_prt.SUBPARTITION_NAME ||' tablespace ORCL_TAB_TBSP_TDE parallel 8;');
utl_file.put_line(fhandle ,'alter table
'||sb_prt.table_owner||'.'||sb_prt.table_name||' move subpartition '
||sb_prt.SUBPARTITION_NAME ||' tablespace ORCL_TAB_TBSP_TDE parallel 8;');
end loop;
select table_name , owner into tb_name,tb_owner from
dba_tables where table_name=upper(tname) and owner=upper(towner);
if (sb_num is null
and tb_num is null )
then
dbms_output.put_line ('alter table
'||tb_owner||'.'||tb_name||' move tablespace ORCL_TAB_TBSP_TDE parallel 16;');
utl_file.put_line(fhandle,'alter table
'||tb_owner||'.'||tb_name||' move tablespace ORCL_TAB_TBSP_TDE parallel 16;');
else
dbms_output.put_line ('alter table
'||tb_owner||'.'||tb_name||' modify default attributes tablespace ORCL_TAB_TBSP_TDE;');
utl_file.put_line(fhandle,'alter table
'||tb_owner||'.'||tb_name||' modify default attributes tablespace ORCL_TAB_TBSP_TDE;');
end if;
utl_file.fclose(fhandle);
dbms_output.put_line (tb_num ||'~~~~~~~~~~~'||sb_num);
end ;
/
4.1.3.
Create gen_ind_rebuild procedures
Ensure to make the necessary changes before executing
the scripts
CREATE OR REPLACE procedure SYS.gen_ind_rebuild (
tname in Varchar2, towner in varchar2)
AS
cursor indxes is
select
INDEX_NAME,TABLE_NAME,INDEX_TYPE,PARTITIONED,OWNER
from dba_indexes
where TABLE_NAME=tname
and PARTITIONED='NO'
and owner=towner
and index_type not in ('LOB');
cursor ind_sub_part is
select
INDEX_NAME,PARTITION_NAME,INDEX_OWNER,SUBPARTITION_NAME
from dba_ind_subpartitions
where INDEX_NAME in (
select distinct INDEX_NAME
from DBA_INDEXES
where table_name=tname
and table_owner=towner
and PARTITIONED='YES');
cursor ind_part is
select INDEX_NAME,PARTITION_NAME,INDEX_OWNER,SUBPARTITION_COUNT
from dba_ind_partitions
where INDEX_NAME in (
select distinct INDEX_NAME
from DBA_INDEXES
where table_name=tname
and table_owner=towner
and PARTITIONED='YES'
and SUBPARTITION_COUNT=0)
and INDEX_OWNER=towner;
cursor ind_attrs is
select distinct INDEX_NAME,INDEX_OWNER
from dba_ind_partitions
where INDEX_NAME in (
select distinct INDEX_NAME
from DBA_INDEXES
where table_name=tname
and table_owner=towner
and PARTITIONED='YES')
and INDEX_OWNER=towner;
sb_num number;
tb_num number;
tb_number number ;
tb_name Varchar2(50);
tb_owner Varchar2(50);
fhandle utl_file.file_type;
begin
fhandle:=
utl_file.fopen('TDE_PUMP_DIR','Table_'||towner||'_'||tname||'.sql','a',32767);
for indx in indxes
loop
dbms_output.put_line ('alter index
'||indx.OWNER||'.'||indx.INDEX_NAME||'
Rebuild tablespace ORCL_IDX_TBSP_TDE
parallel 16;');
utl_file.put_line(fhandle,'alter index
'||indx.OWNER||'.'||indx.INDEX_NAME||'
Rebuild tablespace ORCL_IDX_TBSP_TDE
parallel 16;');
utl_file.put_line(fhandle,'alter index
'||indx.OWNER||'.'||indx.INDEX_NAME||'
NOPARALLEL;');
end loop;
for ind_prti in ind_part
loop
--dbms_output.put_line ('alter table
'||tb_prt.table_owner||'.'||tb_prt.table_name||' move partiton ' ||tb_prt.PARTITION_NAME ||'
tablespace ENCRYPTED_DATA parallel 8;');
dbms_output.put_line ('alter index
'||ind_prti.INDEX_OWNER||'.'||ind_prti.INDEX_NAME||' move partition ' ||ind_prti.PARTITION_NAME
||' tablespace ORCL_IDX_TBSP_TDE parallel 8;');
utl_file.put_line(fhandle,'alter index
'||ind_prti.INDEX_OWNER||'.'||ind_prti.INDEX_NAME||' move partition ' ||ind_prti.PARTITION_NAME
||' tablespace ORCL_IDX_TBSP_TDE parallel 8;');
end loop;
for id_sb_prt in ind_sub_part
loop
dbms_output.put_line ('alter index '||id_sb_prt.INDEX_OWNER||'.'||id_sb_prt.INDEX_NAME||' move subpartition '
||id_sb_prt.SUBPARTITION_NAME ||' tablespace ORCL_IDX_TBSP_TDE parallel 8;');
utl_file.put_line(fhandle ,'alter index
'||id_sb_prt.INDEX_OWNER||'.'||id_sb_prt.INDEX_NAME||' move subpartition ' ||id_sb_prt.SUBPARTITION_NAME
||' tablespace ORCL_IDX_TBSP_TDE parallel 8;');
end loop;
for id_attr in ind_attrs
loop
utl_file.put_line(fhandle,'alter index
'||id_attr.index_owner||'.'||id_attr.index_name||' modify default attributes
tablespace ORCL_IDX_TBSP_TDE;');
end loop;
utl_file.fclose(fhandle);
end ;
/
4.1.4.
Generate scripts
After the 2 procedures created, run the below PL/SQL
script to generate the scripts to move tables, partitions, subpartitions et
rebuild the index.
declare
cursor mvtable is
select table_name,owner
from dba_tables
where table_name in
('TABLE_1, TABLE_2,…')
and owner='XXXXX';
Begin
for tab in mvtable
loop
gen_tab_rebuild(tab.table_name,tab.owner);
gen_ind_rebuild(tab.table_name,tab.owner);
end loop;
end ;
/
Note:
1-
Generate scripts for different owners
2-
Scripts would be generated under the TDE_PUMP_DIR
database Directory
5.
PREPARE AND RUN THE SCRIPTS TO MOVE TABLES AND REBUILD
THE INDEXES
5.1.1.
Bounce the database
SQL> shutdown immediate;
SQL> startup;
5.1.2.
Stop the MRP (In case of Dataguard)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
5.1.3.
Create shell script
$cd /u01/app/oracle/admin/orcldb/dpdump
$vi moveAll.sh
Content of moveAll.sh:
sqlplus / as sysdba <
set echo on ;
set timing on;
spool move.log
@Table_ORCL_1.sql
@Table_ORCL_2.sql
@Table_ORCL_3.sql
@Table_ORCL_...sql
spool off;
EOF
chmod 777 moveAll.sh
In case of LOB Objects:
Generate the commands to move specific Lobs if needed
select 'Alter table ORCL_MTNC.'||TABLE_NAME||' MOVE
LOB('||COLUMN_NAME||') STORE AS (TABLESPACE ORCL_TAB_TBSP_TDE) parallel 16;'
from dba_lobs
where table_name in
(‘TABLE_1’,’TABLE_2’,….)
and owner='XXXX'
and tablespace_name not like '%TDE';
6.
POST IMPLEMENTION
6.1.1.
Check for unusable indexes
select 'alter index ‘||owner||’.’'||index_name||' Rebuild
tablespace ORCL_IDX_TBSP_TDE parallel 8;'
from dba_indexes
where TABLESPACE_NAME like '%TDE'
and status = 'UNUSABLE';
6.1.2.
Start MRP and check DB is sync
---From DR
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
*****PLEASE
ENSURE TO TAKE BACKUP OF THE KEYS
/u01/app/oracle/admin/orcldb/wallet/
