Oracle Database TDE Enabling Automation on a huge database can be made faster by parallelized tasks. The below automation will help to achieve it on Linux.
The logic used here is
- build the sql for altering the datafile encrypt
- find the total number of CPU
- split the workload - datafile/cpu
- submit all the jobs in the background
- monitor the job completion
- validate if all datafiles are encrypted
#Beginning of the Script
echo "TDE - PreStage - `date` - Started"
echo "TDE - PreStage - `date` -- Building SQL for Encrypting Tablespace - Started"
rm tablespace_tde_pre.sql tablespace_tde_exec.sql tablespace_tde_job_*
echo "set pagesize 1000" > tablespace_tde_pre.sql
echo "set lines 200" >> tablespace_tde_pre.sql
echo "set define off" >> tablespace_tde_pre.sql
echo "set term off" >> tablespace_tde_pre.sql
echo "set head off" >> tablespace_tde_pre.sql
echo "set feedback off" >> tablespace_tde_pre.sql
echo "spool tablespace_tde_exec.sql" >> tablespace_tde_pre.sql
echo "select 'alter database datafile '||chr(39)||df.name||chr(39)||' encrypt;' from v\$tablespace ts, v\$datafile df where ts.ts#=df.ts# and (ts.name not in ('SYSTEM','SYSAUX') and ts.name not in (select value from gv\$parameter where name='undo_tablespace'));" >> tablespace_tde_pre.sql
echo "spool off" >> tablespace_tde_pre.sql
echo "exit" >> tablespace_tde_pre.sql
sqlplus -s / as sysdba @tablespace_tde_pre.sql
sed -i '/^$/d' tablespace_tde_exec.sql
echo "TDE - PreStage - `date` -- Building SQL for Encrypting Tablespace - Completed"
echo "TDE - PreStage - `date` -- Building Parallel SQL for Encrypting Tablespace - Started"
cpuCount=`nproc`
echo "TDE - PreStage - `date` -- CPU Count is $cpuCount"
dbfileCount=`cat tablespace_tde_exec.sql|wc -l`
echo "TDE - PreStage - `date` -- DB File Count is $dbfileCount"
if [ $dbfileCount -lt $cpuCount ]
then
parallelJobCount=1
else
parallelJobCount=`expr $dbfileCount / $cpuCount`
fi
echo "TDE - PreStage - `date` -- Number of Datafiles in a Job is $parallelJobCount"
split -l $parallelJobCount tablespace_tde_exec.sql tablespace_tde_job_
echo "TDE - PreStage - `date` -- Created $cpuCount Jobs"
echo "TDE - PreStage - `date` -- Building Parallel SQL for Encrypting Tablespace - Completed"
echo "TDE - Execution - `date` -- Executing Parallel SQL for Encrypting Tablespace - Started"
echo "sqlplus / as sysdba << EOF" > tablespace_tde_jobs.template
echo "set echo on " >> tablespace_tde_jobs.template
for i in `ls tablespace_tde_job_*`
do
cp tablespace_tde_jobs.template tablespace_tde_jobs.t
cat $i >> tablespace_tde_jobs.t
echo "EOF" >> tablespace_tde_jobs.t
cp tablespace_tde_jobs.t $i
nohup sh $i > $i.log 2>&1 &
echo "TDE - Execution - `date` -- Job $i Submitted - Log $i.log"
done
echo "TDE - Execution - `date` -- Executing Parallel SQL for Encrypting Tablespace - Completed"
echo "TDE - Execution - `date` -- Monitoring Parallel SQL for Encrypting Tablespace - Started"
runningTDEJobs=`ps -ef|grep -i tablespace_tde_job|grep -v grep|wc -l`
while [ $runningTDEJobs -gt 0 ]
do
echo "TDE - Execution - `date` -- Running TDE Jobs"
ps -ef|grep -i tablespace_tde_job|grep -v grep
sleep 30
runningTDEJobs=`ps -ef|grep -i tablespace_tde_job|grep -v grep|wc -l`
done
echo "TDE - Execution - `date` -- Monitoring Parallel SQL for Encrypting Tablespace - Completed"
echo "TDE - PostStage - `date` -- Verification of Tablespace Encryption - Started"
sqlplus / as sysdba << EOF
alter database open;
select name from v\$tablespace where ts# not in (select ts# from v\$encrypted_tablespaces);
EOF
echo "Verify if all the tablespaces with the exception of TEMP/UNDO/SYSTEM/SYSAUX is encrypted"
echo "TDE - PostStage - `date` -- Verification of Tablespace Encryption - Completed"
#End of the Script
Customize it to your need. 😵
