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

When wallet is not open in TDE Environment (ORA-28365)

Introduction

For one of the new environments i have setup TDE ( transparent Database Encryption) aiming at encryption on the tablespaces level for a Rac database. As part of the preparations i have created a Diskgroup in ASM with normal redundancy and i have created an ACFS filesystem on top of that which will hold the TDE – Keys.

After restarting the instances ( tailing the alert log of both is an old hab it ). This is what i noticed:

In node of instance 2  I saw in alert that:

2019-09-13T09:36:34.512565+02:00

Errors in file /app/oracle/diag/diag/rdbms/MYDBU10a/MYDBU10A2/trace/MYDBU10A2_j001_354937.trc:

ORA-12012: error on auto execute of job 4

ORA-28365: wallet is not open

ORA-06512: at “SYS.ADMIN_AUDIT”, line 102

## On os level in the mount of the Acfs filesystem ( /app/oracle/admin/WALLET ) I Saw this :

total 84

 2  4 drwxrwxr-x. 5 oracle dba   4096 Sep 13 07:58 .

15  4 drwxr-xr-x. 8 oracle dba   4096 Sep 13 05:42 ..

44  4 -rw-r–r–. 1 oracle dba    100 Sep 13 07:58 afiedt.buf

21 64 drwx——. 2 root   root 65536 Sep 13 05:42 lost+found

 ?  ? d?????????? ? ?      ?        ?            ? MYDBU10A

##Followed below steps :

## as root

mysrvr4dr:root:/root $ /sbin/acfsutil registry

Mount Object:

  Device: /dev/asm/MYDBU10avol-125

  Mount Point: /app/oracle/admin/MYDBU10A

  Disk Group: ACFS

  Volume: MYDBU10AVOL

  Options: none

  Nodes: all

  Accelerator Volumes:

Mount Object:

  Device: /dev/asm/netcfg_vol-125

  Mount Point: /app/oracle/admin/NETCFG/12.2.0.1/db/network/admin

  Disk Group: ACFS

  Volume: NETCFG_VOL

  Options: norootsuid

  Nodes: all

  Accelerator Volumes:

Mount Object:

  Device: /dev/asm/tde_vol-125

  Mount Point: /app/oracle/admin/WALLET

  Disk Group: ACFS

  Volume: TDE_VOL

  Options: none

  Nodes: all

  Accelerator Volumes:

## as root :

mysrvr4dr:root:/root $ umount /app/oracle/admin/WALLET

umount: /app/oracle/admin/WALLET: target is busy.

        (In some cases useful info about processes that use

         the device is found by lsof(8) or fuser(1))

umount.acfs: CLSU-00100: operating system function: OfsWaitProc failed with error data: 32

umount.acfs: CLSU-00101: operating system error message: Broken pipe

umount.acfs: CLSU-00103: error location: OWPR_1

umount.acfs: ACFS-04151: unmount of mount point /app/oracle/admin/WALLET failed

## who is out there :

mysrvr4dr:root:/root $ lsof /app/oracle/admin/WALLET

COMMAND    PID   USER   FD   TYPE    DEVICE SIZE/OFF NODE NAME

ksh      82160 oracle  cwd    DIR 252,64003     4096   74 /app/oracle/admin/WALLET/MYDBU10A (deleted)

ksh      82160 oracle   10r   DIR 252,64003     4096   74 /app/oracle/admin/WALLET/MYDBU10A (deleted)

ksh     240476 oracle  cwd    DIR 252,64003     4096    2 /app/oracle/admin/WALLET

ksh     240476 oracle   11r   DIR 252,64003     4096    2 /app/oracle/admin/WALLET

## kill them all

mysrvr4dr:root:/root $ kill -9 240476

mysrvr4dr:root:/root $ lsof /app/oracle/admin/WALLET

COMMAND   PID   USER   FD   TYPE    DEVICE SIZE/OFF NODE NAME

ksh     82160 oracle  cwd    DIR 252,64003     4096   74 /app/oracle/admin/WALLET/MYDBU10A (deleted)

ksh     82160 oracle   10r   DIR 252,64003     4096   74 /app/oracle/admin/WALLET/MYDBU10A (deleted)

mysrvr4dr:root:/root $ kill -9 82160

mysrvr4dr:root:/root $ lsof /app/oracle/admin/WALLET

## still root then do this:

mysrvr4dr:root:/root $ umount /app/oracle/admin/WALLET

# ah but a mount does not work since acfs mounts are not part of fstab.

mysrvr4dr:root:/root $ mount /app/oracle/admin/WALLET

mount: can’t find /app/oracle/admin/WALLET in /etc/fstab

mysrvr4dr:root:/root $ exit

logout

## as oracle

srvctl start filesystem -device /dev/asm/tde_vol-125 -node mysrvr4dr

oracle@mysrvr4dr:/home/oracle []# cd /app/oracle/admin

oracle@mysrvr4dr:/app/oracle/admin []# ls -lisa

total 32

    15 4 drwxr-xr-x.  8 oracle dba 4096 Sep 13 05:42 .

     2 4 drwxr-xr-x. 15 oracle dba 4096 Sep 12 11:36 ..

786508 4 drwxr-xr-x.  3 oracle dba 4096 Sep  9 09:25 +APX

    16 4 drwxr-xr-x.  3 oracle dba 4096 Sep  3 11:37 +ASM

     2 4 drwxrwxr-x. 19 oracle dba 4096 Sep 13 08:15 MYDBU10A

    21 0 lrwxrwxrwx.  1 oracle dba    8 Sep 12 09:43 MYDBU10A1 -> MYDBU10A

    20 0 lrwxrwxrwx.  1 oracle dba    8 Sep 12 09:43 MYDBU10A2 -> MYDBU10A

393302 4 drwx——.  4 oracle dba 4096 Sep  3 15:47 _mgmtdb

917505 4 drwxrwxr-x.  3 oracle dba 4096 Sep 13 05:21 NETCFG

     2 4 drwxrwxr-x.  5 oracle dba 4096 Sep 13 08:52 WALLET

Works again . all is well when it ends well .

Create password file in 12.2 Oracle on Asm

Introduction.

In current project we are relying on replicated databases  for environments like a Pet. On that cloned environment ( 12.2 Rac database  with ASM)  I received request to add a specific adba group to the user(s) who have sysdba privs in the databases . Below you will find the notes i have created to  get there. In the source environment a password file has been setup in asm. During the cloning the database MYDBP is  renamed to MYDBA with the nid tool.

Creating the Password file as a first step in ASM

orapwd file=+MYDBP_DATA01 password=hugo10! entries=10

ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
ORA-17503: ksfdopn:2 Failed to open file
+MYDBP_DATA01/MYDBA/PASSWORD/pwdMYDBa.312.981043193
ORA-15173: entry ‘MYDBA’ does not exist in directory ‘/’
ORA-06512: at line 4
ORA-06512: at “SYS.X$DBMS_DISKGROUP”, line 665
ORA-06512: at line 2

orapwd file=’+MYDBP_DATA01/MYDBA/orapwdb’ dbuniquename=’MYDBA’

ASMCMD> pwcreate –dbuniquename <dbuniquename> <file_path> <sys-password>

ASMCMD [+MYDBP_DATA01/MYDBP/PASSWORD] > ls
pwdMYDBp.256.975838613

## below error
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
ORA-17503: ksfdopn:2 Failed to open file
+MYDBP_DATA01/MYDBA/PASSWORD/pwdMYDBa.312.981043193
ORA-15173: entry ‘MYDBA’ does not exist in directory ‘/’
ORA-06512: at line 4
ORA-06512: at “SYS.X$DBMS_DISKGROUP”, line 665
ORA-06512: at line 2

## as a Workaround:

oracle@Mysrvrr:/app/oracle/product/12201/db/dbs []# asmcmd
[Option -p will be used ]

## In asmcmd created the subdirectory with the name of the cloned database

ASMCMD [+] > cd +MYDBP_DATA01
ASMCMD [+MYDBP_DATA01] > ls
MYDBP/
ASMCMD [+MYDBP_DATA01] > mkdir MYDBA
ASMCMD [+MYDBP_DATA01] > cd MYDBA
ASMCMD [+MYDBP_DATA01/MYDBA] > ls
ASMCMD [+MYDBP_DATA01/MYDBA] > mkdir PASSWORD
ASMCMD [+MYDBP_DATA01/MYDBA] >

### And tried again

orapwd file='+MYDBP_DATA01/MYDBA/orapwdb' dbuniquename='MYDBA' entries=10
oracle@Mysrvrr:/app/oracle/product/12201/db/dbs []# orapwd file='+MYDBP_DATA01/MYDBA/orapwdb' dbuniquename='MYDBA'
Enter password for SYS:

OPW-00010: Could not create the password file. This resource has a Password File.

## Grrr checked with srvctl config and noticed that this one knows about pwfile too :
Database unique name: MYDBA
Database name:
Oracle home: /app/oracle/product/12201/db
Oracle user: oracle
Spfile:
Password file: +MYDBP_DATA01/MYDBA/PASSWORD/pwdMYDBa.312.981043193
Domain: PROD.NL
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: MYDBP_DATA01,MYDBP_FRA01
Mount point paths:
Services: MYDBA_TAF.prod.nl
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: MYDBA1,MYDBA2
Configured nodes: Mysrvrr,nlp1e6dr
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

## Removed the information about the password file in the cluster.

oracle@Mysrvrr: [MYDBA1]# srvctl modify database -db MYDBA -pwfile ”

##Checked again

oracle@Mysrvrr:/app/oracle/diag/rdbms/local10p/LOCAL10P/trace [MYDBA1]# srvctl config database -d MYDBA
Database unique name: MYDBA
Database name:
Oracle home: /app/oracle/product/12201/db
Oracle user: oracle
Spfile:
Password file:
Domain: PROD.NL
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: MYDBP_DATA01,MYDBP_FRA01
Mount point paths:
Services: MYDBA_TAF.prod.nl
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: MYDBA1,MYDBA2
Configured nodes: Mysrvrr,nlp1e6dr
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

## As they say in german  best way  to get things done is doning things right.

oracle@Mysrvrr:/app/oracle/diag/rdbms/local10p/LOCAL10P/trace [MYDBA1]# orapwd file=’+MYDBP_DATA01/MYDBA/orapwdb’ dbuniquename=’MYDBA’

Enter password for SYS:

## in sqlplus

SQL> grant sysdba to system ;

Grant succeeded.

SQL> select * from V$PWFILE_USERS;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS PASSWORD_PROFILE LAST_LOGIN LOCK_DATE EXPIRY_DATE EXTERNAL_NAME AUTHENTI COM CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----- ----- ----- ----- ----- ----- ------------------------------ -------------------------------------------------------------------------------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD NO 0
SYSTEM TRUE FALSE FALSE FALSE FALSE FALSE OPEN DEFAULT PASSWORD NO 0
AIM_DBA TRUE FALSE FALSE FALSE FALSE FALSE OPEN FUNCTIONAL_USER PASSWORD NO 0

Another small puzzle solved ,  but as always happy me again when being able to help  Colleagues / Customers.

Happy reading,

 

Mathijs

 

Installing 12.3 Goldengate in different Group

Summary

Do yo know the feeling that sometimes it is easier to send a message to yourself ( no message to rudy ) then taking notes? Well this is another fine example of that . For project had to install Oracle Goldengate  in A Cluster and below steps are the ones that were followed to do that. Extra flavor to this assignment due to the fact that the team that will use Goldengate in Bau ( business as usuall)  is not the same team as the team i am part of so in this case it was more or less “thou shall not install as oracle:dba”.

Details:

  • As a starting point  I have created a Diskgroup in ASM , and have setup an ACFS filesystem on it . By doing so  This mount-point ( and also the software) becomes shared / usable on all cluster-nodes.
  • Location of the software: oracle@myStartingHost:/opt/oracle/Odrive/depot/software/oracle/GoldenGate/Linux_64/123012_fbo_ggs_Linux_x64_shiphome.zip

On  the target cluster:  checked current groups. Requested from Linux team that they would add the group adba to the oracle account i am using. (in this scenario it is  required to have adba added to the groups for the Oracle account)

oracle@mysrvr:/app/oracle/GG12/# groups oracle
  • Set newgroups to install  Goldangate tool in an Other teams owned group. in Linux perform this command:
newgrp adba
  • unzip the file
  • In oracle@mysrvr:/app/oracle/GG12/fbo_ggs_Linux_x64_shiphome/Disk1,  this now shows:
oracle@mysrvr:/app/oracle/GG12/fbo_ggs_Linux_x64_shiphome/Disk1 [MIGABP]# ls -lisa
total 24

3276804 4 drwxr-xr-x.  5 oracle adba 4096 Dec  9 15:02 .
3276803 4 drwxr-xr-x.  3 oracle adba 4096 Dec  9 15:02 ..
3276808 4 drwxr-xr-x.  4 oracle adba 4096 Dec  9 15:02 install
3276805 4 drwxrwxr-x.  2 oracle adba 4096 Dec  9 15:02 response
3276807 4 -rwxr-xr-x.  1 oracle adba  918 Dec  9 15:02 runInstaller
3276830 4 drwxr-xr-x. 11 oracle adba 4096 Dec  9 15:02 stage

 

  • ran ./ runInstaller

## Issues i came across since  the port that Goldengate likes to claim was already in use:

netstat -tulpn | grep 7809 as root to kill process at reinstall

 

Setting up Goldengate in this way will make proper entry in the OraInventory.

..Next challenge  but that is for different post ,  to add Goldengate to the  Oracle Grid Infrastructure as a resource so the cluster-ware will monitor and respond to this resource should the  node restart or if a relocation of the resource  would help to lift some burden on  that server .. Stay tuned.

Happy reading ,

Mathijs

 

 

Reconfigure Rac 12.2 Gridinfra network

Summary.

In 12.2 Grid infrastructure Oracle has altered the concept of ASM to flex-ASM as a default. This blog will take a focus on re-configuring the Oracle Rac 12.2 Grid infrastructure network component parts like the interconnect, the public or to change the interface to do-not-use, whenever that applies / is an improvement to the situation at hand. Read carefully in full before performing it on one of your clusters. Baseline for this action will be a document on Mos (How to Modify Private Network Information in Oracle Clusterware (Doc ID 283684.1))

Details:

As with any change, when going through input – processing – output it is important to have a clear picture of the situation as is. So a first and  very mandatory step should be to check with the oifcfg getif command how things are before starting the changes:

When entering the command information with regard to the known network interfaces in the Rac cluster similar to below should be showing:

oracle@mysrvr1dr:/app/oracle/stage/27468969 [+ASM1]# oifcfg getif

bond0  198.19.11.0  global  public
eth0  10.217.210.0  global  cluster_interconnect,asm
eth2  192.168.10.0  global  cluster_interconnect
eth7  192.168.11.0  global  cluster_interconnect

Here bond0 will be used used as public, eth0 at the moment is holding activities for the cluster interconnect and for asm, eth2 and eth7 are dedicated to  the interconnect. Eth0 is defined as admin lan for various activities. In this setup the cluster is unstable, nodes are being evicted. Time to perform steps to stabilize it.

From the Mos note, looking at Case IV. Changing private network interface name, subnet or netmask. For 12c Oracle Clusterware with Flex ASM.

Precaution, taking backup of profile.xml on each node.

Take a backup of profile.xml on all cluster nodes before proceeding, as grid user. In this specific case this is the user that has installed the Grid Infrastructure ( in this scenario that was the  oracle user):
Command:

$ cd $GRID_HOME/gpnp/<hostname>/profiles/peer/
 $ cp -p profile.xml profile.xml.bk
cd /app/grid/product/12201/grid/gpnp/mysrvr1dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr2dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr3dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr4dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr5dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr6dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr7dr/profiles/peer
cp -p profile.xml profile.xml.bk

cd /app/grid/product/12201/grid/gpnp/mysrvr8dr/profiles/peer
cp -p profile.xml profile.xml.bk

Altering the interconnect:

One of the interconnects should be altered to make sure that the ASM listener is able to communicate using that interface to. In this scenario eth2 was used to do so. When doing this take note of the ip since it will be needed to configure a new ASM listener.

oifcfg setif -global eth2/192.168.10.0:cluster_interconnect,asm
oifcfg setif -global eth7/192.168.11.0:cluster_interconnect

Now eth2  shows that it setup for interconnect and asm (only one interconnect should be setup to combine cluster_interconnect+asm).

peer [+ASM1]# oifcfg getif

bond0  198.19.11.0  global  public
eth0  10.217.210.0  global  cluster_interconnect,asm
eth2  192.168.10.0  global  cluster_interconnect,asm
eth7  192.168.11.0  global  cluster_interconnect

With this information checked and in place it is time for setting up new listener for asm since the original ASM listener during the installation used eth0 and that eth0 will be dropped  – removed from cluster configuration in steps below:

Existing listener ASMNET1LSNR  will become new one ASMNET122LSNR.

srvctl add listener -asmlistener -l ASMNET122LSNR -subnet 192.168.10.0
(as mentioned this is the eth2 interface that we are going to use).

As always seeing is believing : use crsctl status resource -t to see details similar to below. The new ASM listener is created as a resource and it is in a status offline offline on all nodes in the cluster at this point and time :

--------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------
ora.ASMNET122LSNR_ASM.lsnr
               OFFLINE OFFLINE      mysrvr1dr                 STABLE
               OFFLINE OFFLINE      mysrvr2dr                 STABLE
               OFFLINE OFFLINE      mysrvr3dr                 STABLE
               OFFLINE OFFLINE      mysrvr4dr                 STABLE
               OFFLINE OFFLINE      mysrvr5dr                 STABLE
               OFFLINE OFFLINE      mysrvr6dr                 STABLE
               OFFLINE OFFLINE      mysrvr7dr                 STABLE
               OFFLINE OFFLINE      mysrvr8dr                 STABLE

In the next step we will remove the old ASM listener, and use a -f option to prevent errors – messages with regard to dependencies.

srvctl update listener -listener ASMNET1LSNR_ASM -asm -remove -force

I have checked again with crsctl status resource -t to make sure the old resource is gone now.

Removing the old ASM listener

In the Mos note there is a little inconsistency because it claims  that as a next step the old ASM listener should be stopped.  I was able to grep for the listener ( ps -ef|grep -i inherit)  and i saw it on OS level on the machine(S). But I am not able to stop that listener  since the cluster resource is already gone and lsnrctl did not work. Solution: What I noticed that when I skipped this step and stopped and started the cluster which is mandatory in this scenario, the listener was gone on all nodes.

Should have given this command, but that is NOT working:
lsnrctl stop ASMNET1LSNR_ASM

Check configuration before restarting GI:

First command:
srvctl config listener -asmlistener

Name: ASMNET122LSNR_ASM
Type: ASM Listener
Owner: oracle
Subnet: 192.168.10.0
Home: <CRS home>
End points: TCP:1527
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

Second Command:
srvctl config asm

ASM home: <CRS home>
Password file: +VOTE/orapwASM
Backup of Password file:
ASM listener: LISTENER
ASM instance count: ALL
Cluster ASM listener: ASMNET122LSNR_ASM

Both results look great so time to move to the next step (restarting the Grid Infra structure on all nodes).

Restarting Grid infrastructure on all Nodes:

For this next step you have to become root (or sudo su – ) to do the next steps. First and importantly make sure that the Grid infra structure is not restarting automatically should a cluster node perform a reboot (disable crs) , then stop the Grid infrastructure software:

As root

/app/grid/product/12201/grid/bin/crsctl disable crs
/app/grid/product/12201/grid/bin/crsctl stop crs
To be done on: mysrvr[1-8]dr

Checking network configuration on all nodes.

mysrvr1dr:root:/root $ ifconfig -a

Starting cluster again:

As root

/app/grid/product/12201/grid/bin/crsctl enable crs
/app/grid/product/12201/grid/bin/crsctl start crs

To be done on: mysrvr[1-8]dr

Final checks:

oifcfg getif

bond0  198.19.11.0  global  public
eth0  10.217.210.0  global  cluster_interconnect,asm
eth2  192.168.10.0  global  cluster_interconnect,asm
eth7  192.168.11.0  global  cluster_interconnect

Time to delete eth0

Since eth0 is admin lan, and after our reconfigure steps, time  to get rid of the eth0 (remove it from the Grid infra structure).

oifcfg delif -global eth0/10.217.210.0 

And a last check again:

oifcfg getif

bond0  198.19.11.0  global  public
eth2  192.168.10.0  global  cluster_interconnect,asm
eth7  192.168.11.0  global  cluster_interconnect

Happy reading, and till we meet again,

Mathijs.

 

 

Some things happened (while installing 12.2 GI in a Rac cluster).

Summary:

One of the fine people in the Oracle community once shared  a thought where he told about repairing a bike once and repeating the plan of approach N times. As a lesson learned from  that scenario it is best to see each bike as a new challenge which deserves a new and fresh approach. In this blog I will describe a number of things i came across when setting up Grid Infra structure 12.2 with January 2018 PSU on a multi-node cluster.

Details – Things to look after:

  • Locating the log files of the installation can make a world of difference. Make sure you understand and find  the location of it and have it tailed during all of install.  In my case in this directory will find subdirs and log file for example: /app/oraInventory/logs/GridSetupActions2018-04-26_09-39-53AM.
  • In the past you always had  one destination to unzip your Software.zip and during installation the runInstaller would ask for an installation location  during setup. With 12.2 ( and in Oracle 18 Grid infra) that is no longer the case. Create the subdirectory where the software is supposed to be installed and unzip your files there as a first step.
  • runInstaller is no more … In order to start the installation process you will have to find this command:./gridSetup.sh
  • When Installing as in my case on Red Hat  Linux 7.4 with a Patched Kernel you might come across  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. You can Solve that by running the gridSetup.sh with parameters which will install the Patch(es) first and then run the commands:./gridSetup.sh -applyPSU /app/grid/product/12201/grid/27100009.  Translated this means that the psu patch needs to be applied first and then the gridSetup can start its setup.
  • Images during setup have changed. In my case I have selected  this one. Which also brought me FLEX-ASM as per default in 12.2.

 

2018-05-06_074150

When installing GI as a standalone cluster in the follow-up screens you are asked to add the nodes of your cluster either as a Hub or as a Leave. Thus differentiating by default which nodes should have a dedicated ASM instance ( Hub) and which nodes will communicate remotely with one of the Hub-Asm instances. After install i learned that in 12.2 as a default 3 ASM instances will be created  no matter how many nodes  there are in your cluster.

  • Scan listener: Make sure as a preparation that the colleagues from Linux team have added the 3 Ips for your cluster in the Dns and try a nslookup first before installing. During installation when you have to  add the clustername ( here presented as mycluster) ,  the installing tool will also show the scan-name (and most likely you will have to alter it anyhow to meet with the information in  dns needed for the setup).( in dns mycluster-scan.prod.nl) needs to be present as 3 IP addresses

2018-05-06_075326

  • In the clusternode screen you will add all the nodes in your cluster. In this case I intended to set up each node as a hub ( thus expecting that there would be 8 asm instances in place too ( which was not the case but that is elaborated in other topic.
  • On this screen you add the nodes using the add button.
  • On this screen you can set up SSH connectivity between all the nodes. On the web it was not clear to me in various blogs and in the documentation neither, what is the preferred way to do this. I had the tool setup ssh connectivity between all nodes and i was happy with result.
  • Once completed press next and the tool will show something like “validating node readiness”.

2018-05-06_080405

  • In the specify Network Interface usage screen:
  • Best practice  / Lessons learned: Make sure you have consulted with  the Linux team about the interfaces. In my specific case > ETH0 is admin lan . You should put it to do not use. Eth2 and Eth7 are the private interconnects. Make sure that only one of the is have the option Private, ASM.  (In a flex asm cluster ASM needs a way to communicate via its dedicated listener. Since as per default you will have only ASM listener, make sure only one of the private interconnects is using this combo of private and ASM).

2018-05-06_082605

Note: This installation was implemented on New Hardware , coming from Dell. During the install we found out that in the original setup the DELL systems use a Range of Ips ( 169.*) which is also used by the HA-IPS of Oracle. And even when the colleagues of Linux might grumble, it is mandatory that the range of 169* is not in use!! In the first setup the Dell systems had  the 169* enabled for their idRac interface. This IPs have been disabled.

  • For the setup of two Diskgroups ( one for the OCR and voting disks and one for the GIMR ( grid infra structure Management Repository) make sure that the Linux Admins have delivered ASM disks). In my case i got 2 times 3 Disks , so i could setup Normal redundancy Diskgroups for both Diskgroups.
  • On the Summary screen , pay extra attention to make sure that all the cluster nodes that you intend to have included in your soon to be cluster are showing ! ( Hub nodes: this should show all the nodes. If this is not the case you can select Edit ( which will rerun all steps as of Cluster node information).

Well that is all for now .. To be continued in a galaxy near you …

As always happy reading and till we meet again.

Mathijs

 

 

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