Why and How to implement TDE in 11g and 19c ?

Why ?

As clarified by Oracle ,“Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces” . Below are a quick note using oracle document for both versions 19c or lower versions (11g or 12c).

How ?

1- Set the Software Keystore Location in the sqlnet.ora File . For 11g or 12.1 or 12.2c .

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=

   (METHOD=FILE)

    (METHOD_DATA=

     (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))

For  19c, it is recommended to use spfile parameters as below :

ALTER SYSTEM SET WALLET_ROOT = '$ORACLE_BASE/ADMIN/ORCL/WALLET' SCOPE = SPFILE SID = '*';

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE = BOTH SID = '*';

2- Create the Software Keystore:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;

You may create an auto login to avoid re-entering the wallet password every time:

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;

3- Open the Software Keystore ## In case autologin not configured:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;

4- Set the Software TDE Master Encryption Key:

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'key_backup';

5- Encrypt Your Data:

CREATE TABLESPACE encrypt_ts

  DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M

  ENCRYPTION USING 'AES256'

  DEFAULT STORAGE (ENCRYPT);

6- Convert exist Tablespace, for prior 12.2c it is offline only , for 12.2 and later version , both offline and online can be used:

## offline

ALTER TABLESPACE users OFFLINE NORMAL;

ALTER TABLESPACE users ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;

ALTER TABLESPACE users ONLINE;

## online

ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf');

 

Why and How to fix Red hat Linux library issue?

Why ?

For any reason you may discover an issue with library like the below issue , where you will not be able even to access the server with emergency mode/target , use the below to fix it, for example , I got the below error.

/usr/sbin/sulogin: error while loading shared libraries: libcrypt.so.1: cannot open shared object file: permission denied

 

How ?

1- First it needs to boot the system into the rescue mode using DVD/CD ISO on the installation media:

How to boot Red Hat Enterprise Linux to Rescue Mode for Data Collection (sosreport, vmcore, etc.)

https://access.redhat.com/articles/3405661

2- Check the rpm package that is behind this library as below:

# rpm -qf /usr/lib64/libcrypt.so.1

libxcrypt-4.1.1-4.el8.x86_64

3- re-install the package using the below and reboot the system to disk boot again:

yum reinstall libxcrypt

Why and How to migrate and upgrade from 12c to 19c using RMAN incremental backup?

 Why ?

Migrate is move your database from machine to different machine and upgrade is moving your data from old to new version , it is may you need to move and upgrade in same time , below I am listing simple steps to achieve this goal by using RMAN incremental backup to minimize the downtime needed in case the RMAN backup is the only suitable solution in your case.

How ?

Below are steps to be follow in order to achive the goal , I am assuming the below:

Source DB 12c : orcl , running in archive log mode , OS is Linux

Destination DB 19c : orcl , software only is installed , OS is Linux

1- check the archive log mode in source DB, if it is disable then backup will be only offline backup ( cold backup) :

Sql> archive log list ;

2- Take backup level 0 which is equivalent to full backup for both database and archive logs:

rman> backup incremental level 0 database format '/u01/backup/db_%U' plus archivelog format '/u01/backup/arch_%U';

3- On target database start create a password file as below:

ORAPWD file=PWD<ORACLE_SID>.ora password=<password>

4- take a pfile from spfile of source and edited as needed and startup the target DB using this pfile:

Sql> create pfile=’location/orclinit.ora’ from spfile;

 

-- copy the file to the target and edit it with the needed, make sure the create any missed directory specially the adum path.

 

-- startup the target DB to nomount:

Sql> startup nmount pfile=location\orclinit.ora;

 

5- On source database take backup of control file:

rman> backup current controlfile format '/u01/backup/cf_%U';

6- On target database restore backup of control file and start database on mount stage:

rman> restore controlfile from '/u01/backup/<filename> ;

rman> alter database mount ;

 

7- On target database correct any path need to be correct if it is differ from source , you may check this using the below:

sql> report schema ;

sql> select * from v$logfile ;

sql> alter database rename file '<sourcepath>/redo01.log' to '<distpath>/redo01.log';

sql> alter database rename file '<sourcepath>/redo02.log' to '<distpath>/redo02.log';

sql> alter database rename file '<sourcepath>/redo03.log' to '<distpath>/redo03.log';

8- On target database restore backup incremental backup level 0:

rman>

run

{

allocate channel c1 device type disk;

set newname for database to '/u02/oradata/%U';

set newname for tempfile '/u01/oradata/orcl/temp01.dbf' to '/u02/oradata/temp01.dbf' ;

restore database ;

switch datafile all;

switch tempfile all;

release channel c1;

}

 

8- On source database take backup incremental level 1:

rman> backup incremental level 1 database format '/u01/backup/db_%U' plus archivelog format '/u01/backup/arch_%U';

8- On target database recover backup incremental level 1 and level 0 after catalog the new level1 in the control file, check the last sequence and add 1 then:

rman> catalog start with '/u01/backup/';

rman> list backup ;

run

{

allocate channel c1 device type disk;

set until sequence (maxnumber+1);

recover database ;

release channel c1;

}

9- Now you can start database in upgrade stage and reset the logs , then perform the upgrade(you may use autoupgrade.jar) :

rman> alter databas open resetlogs upgrade ;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

Why and How to use PowerShell script in CMD Windows?

Why ?

If you intended to use a PowerShell functions and code to retrieve certain output or do some work using PowerShell scripting instead of CMD and accordingly you will complete your work in CMD batch scripting , in this case I am sharing a simple code to achieve this.

How ?

Below is a sample code script running as .bat , CMD batch scripting that will call PowerShell script to retrieve yesterday date and store it in CMD batch variable and continue work with it in CMD , retrieving yesterday date is very difficult in CMD however it is so easy on PowerShell thus I use it as below:

1- Code below, please save it in .bat and run it using CMD:

for /f "delims=" %%a in ('powershell get-date((get-date^).addDays(-1^)^) -uformat "%%Y%%m%%d"') do set date=%%a

for /f "delims=" %%a in ('powershell get-date((get-date^).addDays(-1^)^) -uformat "%%Y%%b%%d"') do set date2=%%a

  

echo %date%

echo %date2%

 

::echo %datetime%

set year=%date:~0,4%

set mm=%date:~4,2%

set day=%date:~6,2%

set mmm=%date2:~4,3%

 

echo checking files for date %year%/%mm%/%day% %time%

echo checking files for date %year%/%mmm%/%day% %time%

pause  

Why and How to delete and compact files Windows?

Why ?

To use a CMD code (.bat) that delete files older than x days and/or use a compact windows feature ( compact) use the below code as a sample to achieve this goal.

How ?

Below is a Windows CMD code that will cleanup/delete files and compact other files based on age, in my below code example I will delete files older than 180 days and compact(compress) files older than 2 days in specific path with certain file pattern naming:

1- Code below:

:: Delete All Archives older than 6 months (1 weeks for client logs)

 

echo %DATE% %TIME%

 

forfiles /p "E:\Avanza\Logs\Svc" /s /m PayHub_*.log /c "cmd /c Del @path" /d -180

forfiles /p "E:\Avanza\Logs\Svc" /s /m PayHub_*.log /c "cmd /c if @isdir==FALSE Compact /c @path" /d -2

Why and How to use winrar to zip and delete (Archive) files Windows?

 

Why ?

If you got a case where you have to compress and cleanup files on windows using winrar to get the most compression , also you may delete the compressed file , this can be consider of archiving of logs , the files in my case containing the dates in the file name and I will use this as a condition while doing this archiving.

How ?

Below is a Windows CMD code that will get the system date and archive files before last week ,:

1- Code below:

::@ECHO OFF

 

:: Keep variables local

SETLOCAL

  

set yyyy=

 

set $tok=1-3

for /f "tokens=1 delims=.:/-, " %%u in ('date /t') do set $d1=%%u

if "%$d1:~0,1%" GTR "9" set $tok=2-4

for /f "tokens=%$tok% delims=.:/-, " %%u in ('date /t') do (

for /f "skip=1 tokens=2-4 delims=/-,()." %%x in ('echo.^|date') do (

set %%x=%%u

set %%y=%%v

set %%z=%%w

set $d1=

set $tok=))

 

if "%yyyy%"=="" set yyyy=%yy%

if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% - 100

 

set CurDate=%mm%/%dd%/%yyyy%

set dayCnt=%7

 

if "%dayCnt%"=="" set dayCnt=7

 

REM Substract your days here

set /A dd=1%dd% - 100 - %dayCnt%

set /A mm=1%mm% - 100

 

:CHKDAY

if /I %dd% GTR 0 goto DONE

set /A mm=%mm% - 1

if /I %mm% GTR 0 goto ADJUSTDAY

set /A mm=12

set /A yyyy=%yyyy% - 1

 

:ADJUSTDAY

if %mm%==1 goto SET31

if %mm%==2 goto LEAPCHK

if %mm%==3 goto SET31

if %mm%==4 goto SET30

if %mm%==5 goto SET31

if %mm%==6 goto SET30

if %mm%==7 goto SET31

if %mm%==8 goto SET31

if %mm%==9 goto SET30

if %mm%==10 goto SET31

if %mm%==11 goto SET30

REM ** Month 12 falls through

 

:SET31

set /A dd=31 + %dd%

goto CHKDAY

 

:SET30

set /A dd=30 + %dd%

goto CHKDAY

 

:LEAPCHK

set /A tt=%yyyy% %% 4

if not %tt%==0 goto SET28

set /A tt=%yyyy% %% 100

if not %tt%==0 goto SET29

set /A tt=%yyyy% %% 400

if %tt%==0 goto SET29

 

:SET28

set /A dd=28 + %dd%

goto CHKDAY

 

:SET29

set /A dd=29 + %dd%

goto CHKDAY

 

:DONE

if /I %mm% LSS 10 set mm=0%mm%

if /I %dd% LSS 10 set dd=0%dd%

 

REM Set yesterday variable variables

::set IISDT=%yyyy:~2,2%%mm%%dd%

::set AWSDT=%yyyy%-%mm%-%dd%

 

set YYYYMMDD=%yyyy%%mm%%dd%

set MMDD=%mm%%dd%

echo %MMDD%

echo %YYYYMMDD%

echo %DATE% %TIME%

 

 

C:

CD "C:\Program Files\WinRAR"

 

taskkill /F /IM WinRAR.exe /T

 

WinRAR.exe a -r -m5 -dh -df -ep E:{path_to_file}\rarfile1_%YYYYMMDD% "E:{path_to_files}\*%YYYYMMDD%*.log"

taskkill /F /IM WinRAR.exe /T

echo %DATE% %TIME%

 

echo completed

echo %DATE% %TIME%