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 .

Missing or Corrupted Spfile in ASM instance in Rac

Introduction:

Old saying is , always expect the unexpected, well  this time this was another proof of that. During patching of an 8 node cluster on the first node we came across 2 issues , both requiring a work around. Issue one was  that after applying January 2019 Cluster did not start. This workaround is not part of this note btw. The second issue was that once we had the first Workaround in place the asm instance on the first node would not start. This note is explaining the steps followed to create a new spfile for the ASM instance(s) in a rac cluster.

General information:

This was the scenario where we found ourselves in: Patching in a rolling way had started on the  first node with January 2019 on 12.2 Oracle (GI and RDBMS) the first node was patched but crs would not start ( and hmm never liked opatchauto a lot 2 b honest). Together with Oracle support a work around was provided  but after that the ASM instance still would not start. During compare of environments it showed one very significant  memory setting on this cluster. Maybe this would have been MEMORY_*  and we are using hugepages on that cluster)  that prevented asm from starting once the patching  on the first node completed .

As a work around we created a pfile and once the cluster on node one was up we started the asm instance with the  pfile ( which was altered by me).

However we could no longer update the spfile for the other asm instances since oracle told us that in a rolling upgrade mode  you cannot make changes to the spfile . That meant that on all 8 Nodes we performed the patching , and once crs was up we had to start the asm with a copy of the init.ora  we used on 1st node too. In our case 8 nodes with a local copy of the init.ora which did not make us happy at all. That however brought us to below scenario where you need to bring  the asm back to using an spfile .

Important note: Since oracle 11.2  the GPNP profile is the key for such change !!!!

From old days , came up with below scenario to create a pfile,  alter that file to meet your needs and bring that as a spfile for the asm instance :

Scenario

  • Could be used with a missing spfile  – or  corrupted .
  • With an existing spfile with wrong settings , but where  you cannot alter since you started  patching already ( spfile updates are prohibited in rolling upgrade scenario ) ,  so maybe best practice is to analyse spfile before.
  • Important message: Scenario  has the requirement that the FULL cluster stack is down and you will work with ONE node only!!

#### With spget you can check current location of spfile  in asmcmd.

ASMCMD [+] > spget

+VOTE/mysrvr18cl/ASMPARAMETERFILE/registry.253.978015605

#### created and altered the pfile on the first node. ( and copied it to all other nodes during the workaround).

oracle@mysrvr1dr:/app/grid/product/12201/grid/dbs []# cd /app/oracle/admin/+ASM1/pfile

oracle@mysrvr1dr:/app/oracle/admin/+ASM1/pfile []# ls -ltr

total 4

-rw-r–r–. 1 oracle dba 2433 Feb  8 09:23 initASM.ora

##### starting  cluster  1st attempt (recalled that the cluster needed 2 b in some part of restricted mode for that, so all of cluster was stopped , then below command was issued). But Oracle showed mercy , telling to use the correct syntax:

mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl start crs restrict

Parse error:

  ‘restrict’ is an invalid argument

Usage:

  crsctl start crs [-excl [-nocrs | -cssonly]] | [-wait | -waithas | -nowait] | [-noautostart]

     Start OHAS on this server

where

     -excl        Start Oracle Clusterware in exclusive mode

     -nocrs       Start Oracle Clusterware in exclusive mode without starting CRS

     -nowait      Do not wait for OHAS to start

     -wait        Wait until startup is complete and display all progress and status messages

     -waithas     Wait until startup is complete and display OHASD progress and status messages

     -cssonly     Start only CSS

     -noautostart Start only OHAS

## Then started cluster in exclusive mode ,  that failed too  btw since it tried to start the asm instance, which was still holding the original spfile with the incorrect information.

mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl start crs  -excl

  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr1dr’
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr1dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr1dr’
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr1dr’
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr1dr’ succeeded
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr1dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr1dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr1dr’ succeeded
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr1dr’
  • CRS-2674: Start of ‘ora.asm’ on ‘mysrvr1dr’ failed
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr1dr’
  • ORA-15077: could not locate ASM instance serving a required diskgroup
  • CRS-2674: Start of ‘ora.storage’ on ‘mysrvr1dr’ failed
  • CRS-2679: Attempting to clean ‘ora.storage’ on ‘mysrvr1dr’
  • CRS-2681: Clean of ‘ora.storage’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.ctssd’ on ‘mysrvr1dr’ succeeded
  • CRS-4000: Command Start failed, or completed with errors.

## Next attempt ,  whole cluster was down , on first node performed  

  • mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl start crs -excl -nocrs
  • ## in this nocrs mode  we were able to start the +ASM1 instance  manually now.
  • SQL> STARTUP PFILE=’/app/oracle/admin/+ASM1/pfile/initASM.ora’;
  • ## Once ASM had started  create the new SPFILE
  • create spfile = ‘+VOTE’ from PFILE=’/app/oracle/admin/+ASM1/pfile/initASM.ora’;
  • ## in Alert noticed this which is good SINCE this shows gpnp profile had been updated accordingly
  • 019-02-08T16:31:32.839547+01:00
  • NOTE: updated gpnp profile ASM SPFILE to
  • NOTE: header on disk 0 advanced to format #2 using fcn 0.0
  • NOTE: header on disk 2 advanced to format #2 using fcn 0.0
  • NOTE: updated gpnp profile ASM diskstring: /dev/mapper/ASM_*
  • NOTE: updated gpnp profile ASM diskstring: /dev/mapper/ASM_*
  • 2019-02-08T16:31:34.381619+01:00
  • NOTE: updated gpnp profile ASM SPFILE to +VOTE/mysrvr18cl/ASMPARAMETERFILE/registry.253.999707493

##### Checked our activities  in ASMCMD:

  • oracle@mysrvr1dr:/app/oracle/admin/+ASM1/pfile [+ASM1]# asmcmd
  • [Option  -p will be used ]
  • ASMCMD [+] > spget
  • +VOTE/mysrvr18cl/ASMPARAMETERFILE/registry.253.999707493

### Checked our activities in gpnptool

oracle@mysrvr1dr:/app/oracle/admin/+ASM1/pfile [+ASM1]# gpnptool get

Warning: some command line parameters were defaulted. Resulting command line:

         /app/grid/product/12201/grid/bin/gpnptool.bin get -o-

<?xml version=”1.0″ encoding=”UTF-8″?><gpnp:GPnP-Profile Version=”1.0″ xmlns=”http://www.grid-pnp.org/2005/11/gpnp-profile” xmlns:gpnp=”http://www.grid-pnp.org/2005/11/gpnp-profile” xmlns:orcl=”http://www.oracle.com/gpnp/2005/11/gpnp-profile” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=”http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd” ProfileSequence=”7″ ClusterUId=”afc024ecfd5ffff8ffbeda0a212bebe1″ ClusterName=”mysrvr18cl” PALocation=””><gpnp:Network-Profile><gpnp:HostNetwork id=”gen” HostName=”*”><gpnp:Network id=”net1″ IP=”198.19.11.0″ Adapter=”bond0″ Use=”public”/><gpnp:Network id=”net2″ IP=”192.168.10.0″ Adapter=”eth3″ Use=”asm,cluster_interconnect”/><gpnp:Network id=”net3″ IP=”192.168.11.0″ Adapter=”eth5″ Use=”cluster_interconnect”/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id=”css” DiscoveryString=”+asm” LeaseDuration=”400″/><orcl:ASM-Profile id=”asm” DiscoveryString=”/dev/mapper/ASM_*” SPFile=”+VOTE/mysrvr18cl/ASMPARAMETERFILE/registry.253.999707493″ Mode=”remote” Extended=”false”/><ds:Signature xmlns:ds=”http://www.w3.org/2000/09/xmldsig#“><ds:SignedInfo><ds:CanonicalizationMethod Algorithm=”http://www.w3.org/2001/10/xml-exc-c14n#“/><ds:SignatureMethod Algorithm=”http://www.w3.org/2000/09/xmldsig#rsa-sha1“/><ds:Reference URI=””><ds:Transforms><ds:Transform Algorithm=”http://www.w3.org/2000/09/xmldsig#enveloped-signature“/><ds:Transform Algorithm=”http://www.w3.org/2001/10/xml-exc-c14n#“> <InclusiveNamespaces xmlns=”http://www.w3.org/2001/10/xml-exc-c14n#” PrefixList=”gpnp orcl xsi”/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm=”http://www.w3.org/2000/09/xmldsig#sha1“/><ds:DigestValue>QH9UPO559zhufkrc7tFxQts6oF0=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>aL2hOnxyLt5YwMcPjGg8LUDx2KD97Y75eLv+

yqvcfQ5O705K8ceQPCnwnsTs4Wn5E1jNeYCEzXnrVp5zM3hMbz9LdEEP2GKk9XJInQprWc39z7JKxm4uEw

NX3Ocs54FqxP1JdBX7PRiMh/

ePd8CoJIVtIaVMD29giX078uGwXcQ=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

### since we have started cluster with -excl –nocrs  time to stop the cluster and start it normally

mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl stop crs

  • CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.crsd’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.crsd’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.crf’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.crf’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.gpnpd’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.storage’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.storage’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.mdnsd’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.ctssd’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.cssd’ on ‘mysrvr1dr’

### starting cluster normally on first node in normal mode

mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl start crs

###  Had small issue , so decided to stop the cluster on node 1 with force option

mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl stop crs  -f

  • CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.mdnsd’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.gpnpd’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.evmd’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.asm’ on ‘mysrvr1dr’
  • CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.ctssd’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.evmd’ on ‘mysrvr1dr’ succeeded
  • CRS-2677: Stop of ‘ora.asm’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.cssd’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.cssd’ on ‘mysrvr1dr’ succeeded
  • CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘mysrvr1dr’
  • CRS-2677: Stop of ‘ora.gipcd’ on ‘mysrvr1dr’ succeeded
  • CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘mysrvr1dr’ has completed
  • CRS-4133: Oracle High Availability Services has been stopped.

#### Time to start the cluster in normal mode for all nodes

mysrvr1dr:root:/root $ cd /app/grid/product/12201/grid/bin

mysrvr1dr:root:/app/grid/product/12201/grid/bin $ ./crsctl start cluster -all

  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr6dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr6dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr4dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr2dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr3dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr8dr’
  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr2dr’
  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr4dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr7dr’
  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr3dr’
  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr8dr’
  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr7dr’
  • CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘mysrvr5dr’
  • CRS-2672: Attempting to start ‘ora.evmd’ on ‘mysrvr5dr’
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr4dr’ succeeded
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr8dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr4dr’
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr4dr’
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr8dr’
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr2dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr8dr’
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr3dr’ succeeded
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr6dr’ succeeded
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr7dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr2dr’
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr6dr’
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr2dr’
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr3dr’
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr6dr’
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr7dr’
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr3dr’
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr4dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr7dr’
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr8dr’ succeeded
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr2dr’ succeeded
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr6dr’ succeeded
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr3dr’ succeeded
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr7dr’ succeeded
  • CRS-2676: Start of ‘ora.cssdmonitor’ on ‘mysrvr5dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.cssd’ on ‘mysrvr5dr’
  • CRS-2672: Attempting to start ‘ora.diskmon’ on ‘mysrvr5dr’
  • CRS-2676: Start of ‘ora.diskmon’ on ‘mysrvr5dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr6dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr2dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr4dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr8dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr3dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr7dr’ succeeded
  • CRS-2676: Start of ‘ora.evmd’ on ‘mysrvr5dr’ succeeded
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr8dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr8dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr8dr’
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr2dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr2dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr2dr’
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr5dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr5dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr5dr’
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr8dr’ succeeded
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr2dr’ succeeded
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr7dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr7dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr7dr’
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr5dr’ succeeded
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr4dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr4dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr4dr’
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr3dr’ succeeded
  • CRS-2676: Start of ‘ora.cssd’ on ‘mysrvr6dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr3dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr3dr’
  • CRS-2672: Attempting to start ‘ora.ctssd’ on ‘mysrvr6dr’
  • CRS-2672: Attempting to start ‘ora.cluster_interconnect.haip’ on ‘mysrvr6dr’
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr7dr’ succeeded
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr8dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr8dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr8dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr8dr’
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr4dr’ succeeded
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr3dr’ succeeded
  • CRS-2676: Start of ‘ora.ctssd’ on ‘mysrvr6dr’ succeeded
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr2dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr2dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr2dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr2dr’
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr8dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr8dr’
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr8dr’ succeeded
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr2dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr2dr’
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr5dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr5dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr5dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr5dr’
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr2dr’ succeeded
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr5dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr5dr’
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr7dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr7dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr7dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr7dr’
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr5dr’ succeeded
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr7dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr7dr’
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr6dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr6dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr6dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr6dr’
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr4dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr4dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr4dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr4dr’
  • CRS-2676: Start of ‘ora.cluster_interconnect.haip’ on ‘mysrvr3dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.asm’ on ‘mysrvr3dr’
  • CRS-2676: Start of ‘ora.asm’ on ‘mysrvr3dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.storage’ on ‘mysrvr3dr’
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr7dr’ succeeded
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr6dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr6dr’
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr4dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr4dr’
  • CRS-2676: Start of ‘ora.storage’ on ‘mysrvr3dr’ succeeded
  • CRS-2672: Attempting to start ‘ora.crsd’ on ‘mysrvr3dr’
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr6dr’ succeeded
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr4dr’ succeeded
  • CRS-2676: Start of ‘ora.crsd’ on ‘mysrvr3dr’ succeeded
  • CRS-4690: Oracle Clusterware is already running on ‘mysrvr1dr’ à fine since  we kept cluster running on node 1

CRS-4000: Command Start failed, or completed with errors.

### checks performed :

On each node: ps -ef|grep d.bin

On each node: crsctl stat res -t -init

On a node: crsctl check cluster -all

Happy reading,

And till next time

Mathijs

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