Oracle 12c Transparent Data Encryption (TDE) on RAC Database with Physical Standby Database : Multiple Databases in Single Oracle Home

Oracle 12c TDE on RAC Database with Physical Standby RAC. The Oracle Database Home has Multiple Instances, due to which we have to use parameter $ORACLE_UNQNAME for ENCRYPTION_WALLET_LOCATION in sqlnet.ora file

In Oracle 12c ALTER SYSTEM and orapki has been deprecated and the correct method is to use ADMINISTER KEY MANAGEMENT commands. Many of the statements from the ALTER SYSTEM SQL statement correspond to the ADMINISTER KEY MANAGEMENT statement.

1. Create folder for wallets, since this is RAC cluster database ,it is better to have a shared filesystem. In our case it is ACFS
mkdir -p /ACFS/wallets/primeprd
mkdir -p /ACFS/wallets/onlnprd
chown -R oracle:oinstall /ACFS/wallets/primeprd
chown -R oracle:oinstall /ACFS/wallets/onlnprd
2. Create sqlnet.ora file with encryption wallet location, do on both nodes with oracle user
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))
3. Set DB Unique Name for the databases and verfiy the encryption wallet location, do on both nodes with oracle user

Also it is better to set the ORACLE_UNQNAME in the user profile

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primeprd -T “ORACLE_UNQNAME=primeprd”
select * from v$encryption_wallet;

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlnprd -T “ORACLE_UNQNAME=onlnprd”
select * from v$encryption_wallet;

4. At Dr side, with Oracle user in DB home directory
mkdir -p /ACFS/wallets/primedr
mkdir -p /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/primedr

Create sqlnet.ora file in DR side with encryption wallet location, do on both nodes with oracle user

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlndr -T “ORACLE_UNQNAME=onlndr”

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primedr -T “ORACLE_UNQNAME=primedr”

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management create keystore ‘/ACFS/wallet/primeprd/’ identified by “Abc1234$#”;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> administer key management create key identified by Abc1234$# with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> select key_id from v$encryption_keys;

KEY_ID
——————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> administer key management use key ‘AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ identified by Abc1234$# with backup;

keystore altered.

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
23-NOV-16 09.53.46.485930 AM +00:00
SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0
— Create Auto Login for the Keystore —

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/ACFS/wallet/primeprd/’ identified by Abc1234$#;

keystore altered.

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:13:30 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN AUTOLOGIN SINGLE NO
0
Copy the Encryption key Files to Standby Site as “Oracle” user

primeprd2::12c–>cd /ACFS/wallet/primeprd/
primeprd2::12c–>scp -r * oracle@exadrdbadm01:/ACFS/wallets/primedr

The authenticity of host ‘exadrdbadm01 (10.5.238.11)’ can’t be established.
RSA key fingerprint is c8:c0:69:80:1c:c8:ea:e3:6c:46:6b:c1:05:86:aa:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘exadrdbadm01,10.5.238.11’ (RSA) to the list of known hosts.
oracle@exadrdbadm01’s password:
cwallet.sso 100% 3893 3.8KB/s 00:00
ewallet_2016112309525312.p12 100% 2408 2.4KB/s 00:00
ewallet_2016112309534645.p12 100% 3656 3.6KB/s 00:00
ewallet.p12 100% 3848 3.8KB/s 00:00

— At DR Side on Both Nodes–
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
—————————— ——————– ——— ——— ———-
FILE
/u03/app/oracle/admin/primedr/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
/home/oracle::12c–>cd /u03/app/oracle/product/12.1.0.2/teststandby/network/admin/
/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /ACFS/wallets/primedr/$ORACLE_UNQNAME/)))

/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:27:22 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallets/primedr/
OPEN AUTOLOGIN SINGLE NO
0
SQL> exit

 

5. Create Encrypted Tablespace
CREATE TABLESPACE encrypt_ts
DATAFILE ‘+DATAC1’ SIZE 250M
ENCRYPTION USING ‘AES256’
DEFAULT STORAGE (ENCRYPT);

Monitor Tablespace Script Oracle 12c 11g 10g

The Below Script will generate output if percentage of tablespace space left free is less tan 20%. You can easily put in a shell script and schedule it on crontab to send alerts on a daily basis.

 

select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) “%_free”,
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;

Shell Script to Monitor Oracle Tablespace and Send Email Alert Only If Threshold Exceeds


export MMSG=/tmp/$$.mail
export ADDR="email address here"
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=appdev1

file=/tmp/${$}_`date +%Y%m%d`

sqlplus -s system/password << .eof > $file
set pages 0
select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) "%_free",
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;
exit;
.eof
#list all datafile below
egrep "SOE|APEX|SYSAUX|SYSTEM|TEMP|UNDOTBS1|USERS|AUDIT_TBS|APP_ENCRYPTED" /tmp/${$}_`date +%Y%m%d` >/tmp/table${ORACLE_SID}.txt
check_stat=`cat /tmp/table${ORACLE_SID}.txt|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
then
echo "tablespace less than 20% for $ORACLE_SID : $oracle_num" > $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE" $ADDR < $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE IN DATABASE $ORACLE_SID" $ADDR < /tmp/table$ORACLE_SID.txt
fi

rm -f $MMSG > /dev/null 2>&1
rm $file

Move datafiles to different location.

From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for this might include: I/O balancing for performance reasons, or to rectify incorrect naming standards etc. In this example we are using non-asm and windows environment. ASM would only use +DATA1 +DATA2 tag etc and for any unix os it will be “/” instead of windows tag 🙂

1. Select the current location of the datafile’s from

SQL>  SELECT NAME FROM V$DATAFILE;

2. Bring the tablespace offline

SQL> ALTER TABLESPACE USERS OFFLINE;

3.  Copy the files using OS command. In windows you can simply do copy paste to the new folder

4. Rename  datafile to new location to put  new information in the control file (Control file holds the structure of the database )

SQL> ALTER DATABASE RENAME DATAFILE ‘E:\APP\PSECC\ORADATA\SWX\SWX01.DBF’  TO  ‘F:\APP\PSECC\ORADATA\SWX\SWX01.DBF’

5. Bring the tablespace online

SQL> ALTER TABLESPACE USERS ONLINE;

Optionally if you want to delete the old files from the OS. Shutdown the database and delete the old files and bring up the database.