ACFS

Leave a comment Posted on ACFS, Oracle, TDE

TDE SETUP in a rac environment:

Summary:

This document will share the steps to implement TDE (transparent Database encryption) in the database in a Cluster.  Required Steps that are needed to implement it:

  • have the requirement to have an ACFS file system in place with proper setup there (see below).
  • Have the requirement to have an adapted sqlnet.ora in a centralized location.

Details:

  1. Setup will require preparations. In our case, the first database was MYDB. Steps below will be identical for each database in scope. (Of course you need to change the database name according to the database in your scope).

Mysrv[3-4]dr is holding the MYDB database.

  • Create a Diskgroup in normal Redundancy and call it TDE_KEYS.
  • Prepare the acfs created mountpoint by creating a TDE_VOL.
  • Mount the TDE_VOL as /app/oracle/admin/WALLET
  • On the acfs mount (/app/oracle/admin/WALLET
  • cd /app/oracle/admin/WALLET

mkdir MYDB,

ln -sf MYDB MYDB <1,2>

This acfs filesystem is shared between the clusternode.

With the mkdir you wil create an entry for  the database you are about to encrypt , with the 2 links you will point both instances to the database directory , to keep all in a central place.

  • Make sure you have a centralized Sqlnet.ora and adapt this one. As you can see the ORACLE_SID ( the instance name offers a flexible input in this way):

ENCRYPTION_WALLET_LOCATION =

  (SOURCE =(METHOD = FILE)(METHOD_DATA =

    (DIRECTORY = /app/oracle/admin/WALLET/$ORACLE_SID/ )

                          )

  )

  • In the database first check:

SQL> select * from v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

NOT_AVAILABLE                                    UNKNOWN                    SINGLE    UNDEFINED

                 0

  • With the below command you will create the keystore  and you will give the keystore a password.

SQL> administer key management create keystore ‘/app/oracle/admin/WALLET/MYDB/’ identified by “mypwd19!”;

## Once that command is given Sqlplus  will report:

keystore altered.

## TIP Be sure the correct sqlnet is read (links?)

  • run below command to open your keystore:

SQL> administer key management set keystore open identified by “mypwd19!”;

## this will report in sqlplus:

keystore altered.

  1. Now is a good time to Check the status of your wallet:

SQL> select * from v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

OPEN_NO_MASTER_KEY                    PASSWORD                    SINGLE    UNDEFINED

                 0

  1.  Check  your encryption keys.

SQL> select key_id,activation_time from v$encryption_keys;

## sql will 1st time report:

no rows selected

## On Os you should already see the wallet similar to below:

SQL> host

oracle@Mysrv3dr:/app/oracle/admin/WALLET/MYDB []# ls -lisa

77 4 -rw——-. 1 oracle dba 2555 Sep 13 05:51 ewallet.p12

  1. In Sqlplus  now it is time to create the key:

SQL> administer key management create key identified by “mypwd19!” with backup;

  1. When you check your encryption keys again now:

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID

——————————————————————————

ACTIVATION_TIME

—————————————————————————

NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

  1. In sqlplus give below command:

SQL> administer key management use key ‘ NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‘ identified by “mypwd19!” with backup;

## sqlplus will report:

keystore altered.

  1. Check again your encryption keys again to get something similar to below:

SQL> select key_id,activation_time from v$encryption_keys;

## now you will see an activation_time Too:

KEY_ID

——————————————————————————

ACTIVATION_TIME

—————————————————————————

NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

13-SEP-19 06.01.49.995963 AM +02:00

  1. As a test create a small  tablspace:

SQL> create tablespace ENC datafile ‘+MYDB_DATA’ size 10M encryption using ‘AES256’ default storage(encrypt);

## Check this with:

select tablespace_name,encrypted from dba_tablespaces order by 1;

## Sqlplus should report something like this:

SQL> select tablespace_name,encrypted from dba_tablespaces order by 1;

TABLESPACE_NAME                ENC

—————————— —

ENC                            YES

Now you are all set to create all required application tablespaces with these extra parameters  encryption using ‘AES256’ default storage(encrypt).

Automatically open the wallet

Note:   Basically a wallet needs to be open before you can access the database. This however can and should be altered: When opening your database this should automatically open the wallet:

  1. In sqlplus check:

SQL> select * from v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

OPEN                                           PASSWORD                    SINGLE    NO

                 0

  • Give  this command:

SQL> administer key management create auto_login keystore from keystore ‘/app/oracle/admin/WALLET/MYDB/’ identified by “mypwd19!”;

## sqlplus will report:

keystore altered.

## On OS you will see :

oracle@Mysrv3dr:/app/oracle/admin/WALLET/MYDB [MYDB1]# ls -lisa

total 48

74  4 drwxr-xr-x. 2 oracle dba 4096 Sep 13 06:37 .

 2  4 drwxrwxr-x. 5 oracle dba 4096 Sep 13 05:44 ..

82 12 -rw——-. 1 oracle dba 5304 Sep 13 06:37 cwallet.sso

78  4 -rw——-. 1 oracle dba 2555 Sep 13 05:55 ewallet_2019091303553484.p12

80  4 -rw——-. 1 oracle dba 3803 Sep 13 05:57 ewallet_2019091303572233.p12

81  8 -rw——-. 1 oracle dba 5067 Sep 13 06:01 ewallet_2019091304014993.p12

77 12 -rw——-. 1 oracle dba 5259 Sep 13 06:01 ewallet.p12

  • And in Sqlplus  too you  will see  a change.

SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

OPEN                                           AUTOLOGIN                  SINGLE    NO

                 0

Appendix 1 Create an acfs file system:

Idea was to setup acfs via asmca. This however did not work in first implementation due to error because ora.proxy_advm is offline on some nodes.

It should be:

crsctl stat res ora.proxy_advm -t

——————————————————————————–

Name           Target  State        Server                   State details      

——————————————————————————–

Local Resources

——————————————————————————–

ora.proxy_advm

               ONLINE  ONLINE       Mysrv3dr                 STABLE

               ONLINE  ONLINE       Mysrv4dr                 STABLE

If the status is offline check if the acfsmodule are loaded: lsmod | grep acfs

oracleacfs           4840664  2

oracleoks             663240  2 oracleacfs,oracleadvm

if they are not loaded on the local node: log in as root and perform

$GRID_HOME/bin/csrctl stop crs

$GRID_HOME/bin/acfstool install

$GRID_HOME/bin/crsctl start crs

Modules should be loaded now. Volume can be created in asmca (high protection, TDE_VOL1, 1GB)

The volume will show up under /dev/asm (/dev/asm/tde_vol1-301 in this case).

Mkdir  -p /app/oracle/admin/wallet

Cd /app/oracle

Chown –R oracle:dba admin

/sbin/mkfs -t acfs /dev/asm/tde_vol1-302

Register in clusterware:

/sbin/acfsutil registry -a /dev/asm/tde_vol1-302 /app/oracle/admin/WALLET

7 Comments Posted on 12.2 Grid Infra, ACFS, CLSRSC-400, Oracle, Red Hat 74, restart

Installing 12.2 GI on Red hat Linux 74

Introduction.

From one of the Oracle new features books ( I believe it was 11G ) it was stated that the only constant is change. And with recent experience when installing 12.2 Grid Infrastructure as a fine example I could not agree more. But as always if everything with regard to Oracle would be easy…

For a project new installations need to be done. In this specific case that means that I will work with Red Hat Linux 74 together with Oracle 12.2 both  Grid Infrastructure and 12.2 Oracle Rdbms including latest PSU (at the moment January 2018). As mentioned in my other post setup for Grid Infrastructure has changes (no more runInstaller there , and unzipping the Oracle provided Zip in the destination which will hold your software ( so no more staging area).

For one of the platforms it was mandatory to setup 12.2 GI ( and database ) on a single server which is also referred to by Oracle as Oracle restart.

Details.

Now this is where the challenging – even scary part comes in. Once  the installation was on its way , at app 80% of  the progress the famous second window pops up where you are asked to start a second session , become root and run the script root.sh.  Well this is what happened on the way to that  theater:

CRS-4664: Node mysrvr successfully pinned.

2018/01/30 09:19:28 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.service’

2018/01/30 09:21:03 CLSRSC-400: A system reboot is required to continue installing.

The command ‘/app/grid/product/12.2.0.1/grid/perl/bin/perl -I/app/grid/product/12.2.0.1/grid/perl/lib -I/app/grid/product/12.2.0.1/grid/crs/install /app/grid/product/12.2.0.1/grid/crs/install/roothas.pl ‘ execution failed

I have looked at the web and it was almost a  relieve that more bloggers already came across this phenomena.   As always when reading the scenarios that others have followed to solve the issue , it will always be the question does this apply to your specific situation. It was interesting to identify that this issue is caused by acfs drivers when the kernel used in Red Hat 74 ( and also in 73  ) is higher then an expected (default) one thus causing  the root.sh to fail. And as a spoiler alert the suggested reboot , did not help in my specific case.  Consulted with The Oracle and this came back:

==============================
>  ACFS-9154: Loading ‘oracleoks.ko’ driver.
>  modprobe: ERROR: could not insert ‘oracleoks’: Unknown symbol in module, or unknown parameter (see dmesg)
>  ACFS-9109: oracleoks.ko driver failed to load.
>  ACFS-9178: Return code = USM_FAIL
>  ACFS-9177: Return from ‘ld usm drvs’
>  ACFS-9428: Failed to load ADVM/ACFS drivers. A system reboot is recommended.
==============================

Work Around – Solution:

Either apply a oneoff patch:

25078431 – is for 7.3.
26247490 – is for 7.4.

or

Even better but also a new approach: Apply  January  2018 PSU before installation.

Both Scenarios could be interesting but given the fact that i need the psu 2018 as a baseline anyhow this is how  the scenario worked:

1 Installation did not complete, so I did not perform a normal de-install.

oracle@mysrvr:/app/grid/product/12.2.0.1 []# cd grid
oracle@mysrvr:/app/grid/product/12.2.0.1/grid []# rm -rf *

2 Unzipped to /app/grid/product/12.2.0.1/grid.

unzip /app/grid/product/12.2.0.1/linuxx64_12201_grid_home.zip -d /app/grid/product/12.2.0.1/grid

3 Latest version of Opatch needed , make sure you download and have this in place.

unzip /app/grid/product/12.2.0.1/p6880880_122010_Linux-x86-64.zip -d /app/grid/product/12.2.0.1/grid

4 this is January 2018 PSU for GI p27100009_122010_Linux-x86-64.zip.

Unzip /app/grid/product/12.2.0.1/p27100009_122010_Linux-x86-64.zip -d /app/grid/product/12.2.0.1/patch

5 Check for correct Opatch.

oracle@mysrvr:/app/grid/product/12.2.0.1/grid []# cd OPatch
oracle@mysrvr:/app/grid/product/12.2.0.1/grid/OPatch []# opatch version
OPatch Version: 12.2.0.1.11
OPatch succeeded.

6 Time to run this command , it will first patch the software tree then start the setup:

cd ..
./gridSetup.sh -applyPSU /app/grid/product/12.2.0.1/patch/27100009
After some minutes this showed : Successfully applied the patch.
The log can be found at: /app/oraInventory/logs/GridSetupActions2018-01-30_02-34-09PM/installerPatchActions_2018-01-30_02-34-09PM.log
right before the gridSetup started.

Then the grid installer started ….

7 And in a popup screen was asked to have root the run :

time to run root.sh
mysrvr:root:/app/grid/product/12.2.0.1/grid $ ./root.sh
This time  a much more positive output came in:

Performing root user operation.
 
The following environment variables are set as:
 ORACLE_OWNER= oracle
 ORACLE_HOME= /app/grid/product/12.2.0.1/grid
 
Enter the full pathname of the local bin directory: [/usr/local/bin]:
 
The following environment variables are set as:
 ORACLE_OWNER= oracle
 ORACLE_HOME= /app/grid/product/12.2.0.1/grid
 
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
 
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /app/grid/product/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
 /app/oracle/crsdata/mysrvr/crsconfig/roothas_2018-01-30_03-03-44PM.log
 
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'dba'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node mysrvr successfully pinned.
2018/01/30 15:04:19 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'mysrvr'
CRS-2673: Attempting to stop 'ora.evmd' on 'mysrvr'
CRS-2677: Stop of 'ora.evmd' on 'mysrvr' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'mysrvr' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
 
mysrvr 2018/01/30 15:06:13 /app/grid/product/12.2.0.1/grid/cdata/mysrvr/backup_20180130_150613.olr 2960767134
2018/01/30 15:06:14 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

And once again a happy me.

Dedication: would like to dedicate this Post to the colleagues from Oracle Acs support for  their great and swift help. Always a pleasure to work together.

As always, happy reading and till we meet again,

Mathijs