Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for March, 2018

Convert Standard ASM to Flex ASM.

Posted by FatDBA on March 15, 2018

Hi Mates,

Okay, you have an ASM instance crashed and at the same time the db instance failed on the instance … Expected behavior and many of us have faced this scenario in production RAC setups.
Answer to the question is ‘Flex ASM’ which provides us with something that was previously unattainable: the ability to run multiple, independent in cardinality, ASM instances. You can think of it what SCAN is to Database 11gR2.

Its been a while we have the Flex ASM available for 12c users, now the question – How to convert a Non-Flex ASM setup to Flex enabled ASM.
Below is the method to it, i performed a POC for one of the customer some time back and here are the steps.

Configuration:
RHEL 6, 64 Bit
2 Node 12cR1 RAC setup.
Hostname: rac1, rac2.
DB Instances: dixitdb1, dixitdb2

Let’s first check the network information of the cluster, the network interfaces and their IPv4 addresses, you can collect this info using oifcfg tool.

[oracle@rac1 ~]$ oifcfg getif
eth0  192.168.56.0  global  public
eth1  192.168.10.0  global  cluster_interconnect
[oracle@rac1 ~]$

Next lets check the ASM information and current mode.

[oracle@rac1 ~]$ srvctl status asm
ASM is running on rac1,rac2

[oracle@rac1 ~]$  srvctl config asm
ASM home: 
Password file: +DATA/orapwASM
ASM listener: LISTENER

[oracle@rac1 ~]$ asmcmd showclustermode
ASM cluster : Flex mode disabled
[oracle@rac1 ~]$

Okay, now lets do the conversion, we will be doing the silent conversion. You can use the ASMCA GUIas well to do the same.
Here used 192.168.10.0 as the IP and a free port for ASM LISTENER, we will use 1526 port here for listening all requests.

[oracle@rac1 ~]$ asmca -silent -convertToFlexASM -asmNetworks eth1/192.168.10.0 -asmListenerPort 1526

To complete ASM conversion, run the following script as privileged user in local node.
/u01/app/oracle/cfgtoollogs/asmca/scripts/converttoFlexASM.sh

Okay, so the last step generated an auto script which needs to be executed from root to do the real work. This will bounce all RAC components one by one on each node. By the end of the step we will have a new LISTENER exclusively created for the ASM instance and both of the two instances (ASM1, ASM2) will be registered with it.

[oracle@rac1 ~]$ su - root
Password:
[root@rac1 ~]# /u01/app/oracle/cfgtoollogs/asmca/scripts/converttoFlexASM.sh
CRS-2673: Attempting to stop 'ora.crsd' on 'rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.cvu' on 'rac1'
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.dixitdb.db' on 'rac1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac1'
CRS-2677: Stop of 'ora.cvu' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'rac2'
CRS-2677: Stop of 'ora.DATA.dg' on 'rac1' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'rac1' succeeded
CRS-2676: Start of 'ora.cvu' on 'rac2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.rac1.vip' on 'rac1'
CRS-2677: Stop of 'ora.scan2.vip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'rac2'
CRS-2677: Stop of 'ora.dixitdb.db' on 'rac1' succeeded
CRS-2677: Stop of 'ora.rac1.vip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.rac1.vip' on 'rac2'
CRS-2677: Stop of 'ora.mgmtdb' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'rac1'
CRS-2677: Stop of 'ora.scan3.vip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'rac2'
CRS-2676: Start of 'ora.scan2.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'rac2'
CRS-2677: Stop of 'ora.MGMTLSNR' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.MGMTLSNR' on 'rac2'
CRS-2676: Start of 'ora.rac1.vip' on 'rac2' succeeded
CRS-2676: Start of 'ora.scan3.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'rac2'
CRS-2676: Start of 'ora.MGMTLSNR' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.mgmtdb' on 'rac2'
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'rac2'
CRS-2676: Start of 'ora.oc4j' on 'rac2' succeeded
CRS-2676: Start of 'ora.mgmtdb' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac1'
CRS-2677: Stop of 'ora.ons' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1'
CRS-2677: Stop of 'ora.net1.network' on 'rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2673: Attempting to stop 'ora.storage' on 'rac1'
CRS-2677: Stop of 'ora.storage' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'
CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'rac1'
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac1'
CRS-2676: Start of 'ora.diskmon' on 'rac1' succeeded
CRS-2676: Start of 'ora.evmd' on 'rac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'
CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac1'
CRS-2676: Start of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'rac1'
CRS-2676: Start of 'ora.storage' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rac1'
CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded
Oracle Grid Infrastructure restarted in node rac1
PRCC-1014 : ASMNET1LSNR_ASM was already running
PRCR-1004 : Resource ora.ASMNET1LSNR_ASM.lsnr is already running
PRCR-1079 : Failed to start resource ora.ASMNET1LSNR_ASM.lsnr
CRS-5702: Resource 'ora.ASMNET1LSNR_ASM.lsnr' is already running on 'rac1'
CRS-5702: Resource 'ora.ASMNET1LSNR_ASM.lsnr' is already running on 'rac2'
ASM listener ASMNET1LSNR_ASM running already
CRS-2673: Attempting to stop 'ora.crsd' on 'rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.cvu' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'rac2'
CRS-2673: Attempting to stop 'ora.dixitdb.db' on 'rac2'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac2'
CRS-2677: Stop of 'ora.cvu' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'rac1'
CRS-2676: Start of 'ora.cvu' on 'rac1' succeeded
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'rac2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac2'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac2'
CRS-2677: Stop of 'ora.scan3.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'rac1'
CRS-2677: Stop of 'ora.scan2.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac2'
CRS-2677: Stop of 'ora.dixitdb.db' on 'rac2' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac2'
CRS-2677: Stop of 'ora.mgmtdb' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'rac2'
CRS-2676: Start of 'ora.scan3.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.rac2.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.rac2.vip' on 'rac1'
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'rac1'
CRS-2677: Stop of 'ora.MGMTLSNR' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.MGMTLSNR' on 'rac1'
CRS-2676: Start of 'ora.scan2.vip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'rac1'
CRS-2676: Start of 'ora.rac2.vip' on 'rac1' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'rac1' succeeded
CRS-2676: Start of 'ora.MGMTLSNR' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.mgmtdb' on 'rac1'
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac1' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'rac1'
CRS-2676: Start of 'ora.mgmtdb' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac2'
CRS-2677: Stop of 'ora.FRA.dg' on 'rac2' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'rac2' succeeded
CRS-2676: Start of 'ora.oc4j' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac2'
CRS-2677: Stop of 'ora.ons' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac2'
CRS-2677: Stop of 'ora.net1.network' on 'rac2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac2' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac2'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac2'
CRS-2673: Attempting to stop 'ora.storage' on 'rac2'
CRS-2677: Stop of 'ora.storage' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.ctssd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac2'
CRS-2677: Stop of 'ora.cssd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'
CRS-2672: Attempting to start 'ora.evmd' on 'rac2'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac2'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'
CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded
CRS-2676: Start of 'ora.evmd' on 'rac2' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'rac2'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac2'
CRS-2676: Start of 'ora.ctssd' on 'rac2' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac2'
CRS-2676: Start of 'ora.asm' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'rac2'
CRS-2676: Start of 'ora.storage' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rac2'
CRS-2676: Start of 'ora.crsd' on 'rac2' succeeded
Oracle Grid Infrastructure restarted in node rac2
[root@rac1 ~]#

Okay, so it is done with the reboot of clusterware components and back to the prompt.
Let’s verify if it has been done or not …

[root@rac1 ~]# srvctl status asm
ASM is running on rac1,rac2

[root@rac1 ~]# asmcmd showclustermode
ASM cluster : Flex mode enabled           >>>> Flex Mode is ON now.

[root@rac1 ~]#  srvctl config asm
ASM home: 
Password file: +DATA/orapwASM
ASM listener: LISTENER
ASM instance count: ALL
Cluster ASM listener: ASMNET1LSNR_ASM

And we have a new LISTENER named ‘ASMNET1LSNR_ASM’ created for ASM.

[root@rac2 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       rac1                     STABLE
               ONLINE  ONLINE       rac2                     STABLE


[root@rac1 ~]# ps -ef|grep tns
root        10     2  0 15:59 ?        00:00:00 [netns]
oracle   22167     1  0 18:46 ?        00:00:00 /u01/app/12.1.0/grid_1/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle   22291     1  0 18:46 ?        00:00:00 /u01/app/12.1.0/grid_1/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle   22532     1  0 18:46 ?        00:00:00 /u01/app/12.1.0/grid_1/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
oracle   22535     1  0 18:46 ?        00:00:00 /u01/app/12.1.0/grid_1/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
oracle   22544     1  0 18:46 ?        00:00:00 /u01/app/12.1.0/grid_1/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
root     30044 19089  0 18:52 pts/1    00:00:00 grep tns

[root@rac1 ~]# ps -ef|grep pmon
oracle   21494     1  0 18:46 ?        00:00:00 asm_pmon_+ASM1
oracle   22439     1  0 18:46 ?        00:00:00 ora_pmon_dixitdb1
oracle   22839     1  0 18:46 ?        00:00:00 mdb_pmon_-MGMTDB
root     30089 19089  0 18:52 pts/1    00:00:00 grep pmon
[root@rac1 ~]#


[root@rac1 ~]# lsnrctl status ASMNET1LSNR_ASM

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-MAR-2018 18:54:09

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))
STATUS of the LISTENER
------------------------
Alias                     ASMNET1LSNR_ASM
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                13-MAR-2018 18:46:25
Uptime                    0 days 0 hr. 7 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/asmnet1lsnr_asm/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=ASMNET1LSNR_ASM)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.1)(PORT=1526)))
Services Summary...
Service "+ASM" has 2 instance(s).
  Instance "+ASM1", status READY, has 2 handler(s) for this service...
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
The command completed successfully
[root@rac1 ~]#


[root@rac1 ~]# srvctl config listener -l ASMNET1LSNR_ASM
Name: ASMNET1LSNR_ASM
Type: ASM Listener
Owner: oracle
Subnet: 192.168.10.0
Home: 
End points: TCP:1526
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

[root@rac1 ~]# srvctl status listener -l ASMNET1LSNR_ASM
Listener ASMNET1LSNR_ASM is enabled
Listener ASMNET1LSNR_ASM is running on node(s): rac1,rac2

Let’s do some testing, i will here try to stop one of the ASM instance (+ASM1) on Node1 and will see if the DB Instance still alive and listens to requests.

[root@rac1 ~]# srvctl status database -db dixitdb -f -v
Instance dixitdb1 is running on node rac1. Instance status: Open.
Instance dixitdb2 is running on node rac2. Instance status: Open.

[root@rac1 ~]# srvctl modify asm -count 1
PRCA-1123 : The specified ASM cardinality 1 is less than the minimum cardinality of 2.

Well, this is an expected error because we are running on a 2 Node RAC and Flex ASM (Same as SCAN Listeners) needs at-least 2 Instance up and running which is not possible here in my case. But i will now kill the asm instance manually (Killing the PMON)

[root@rac1 ~]# ps -ef|grep pmon
root      4167  4142  0 19:41 pts/1    00:00:00 grep pmon
oracle   21494     1  0 18:46 ?        00:00:00 asm_pmon_+ASM1
oracle   22439     1  0 18:46 ?        00:00:00 ora_pmon_dixitdb1
oracle   22839     1  0 18:46 ?        00:00:00 mdb_pmon_-MGMTDB

[root@rac1 ~]# kill -9 21494
[root@rac1 ~]#  ps -ef|grep pmon
root      4200  4142  0 19:42 pts/1    00:00:00 grep pmon
oracle   22439     1  0 18:46 ?        00:00:00 ora_pmon_dixitdb1
oracle   22839     1  0 18:46 ?        00:00:00 mdb_pmon_-MGMTDB

Next, let’s see the ASM client connections info on avaialble instance (+ASM2)

SQL> select GROUP_NUMBER, DB_NAME, STATUS, INSTANCE_NAME from  v$asm_client;

GROUP_NUMBER DB_NAME  STATUS       INSTANCE_NAME
------------ -------- ------------ ----------------------------------------------------------------
           1 +ASM     CONNECTED    +ASM2
           2 +ASM     CONNECTED    +ASM2
           1 dixitdb  CONNECTED    dixitdb1
           2 dixitdb  CONNECTED    dixitdb1
           1 dixitdb  CONNECTED    dixitdb2
           2 dixitdb  CONNECTED    dixitdb2
           1 _mgmtdb  CONNECTED    -MGMTDB

7 rows selected.

And we have the Instance 1 (dixitdb1) connected with the +ASM2 instance, as +ASM1 is crashed/dead.
It’s listening all requests via ASM LISTENER, same can be verified or checked in asm listener logs.

13-MAR-2018 19:47:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=rac2.localdomain)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=ASMNET1LSNR_ASM)(VERSION=202375680)) * status * 0
13-MAR-2018 19:47:14 * (CONNECT_DATA=(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM2)(CID=(PROGRAM=oracle)(HOST=rac1.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.1)(PORT=39062)) * establish * +ASM * 0

Hope It Helps
Prashant Dixit

Advertisement

Posted in Advanced | Tagged: , | Leave a Comment »

Oracle DB Security Assessment Tool (DBSAT)

Posted by FatDBA on March 2, 2018

Hi Everyone,

Would like to discuss about one of the request came from my earlier projects to identify sensitive data (Tables, objects etc.) within their databases so that external policies can be enforced later on, but the customer only permitted us to use any inbuilt or Oracle branded audit tool and not any third party security/compliance auditing tools.

And then we landed to use Oracle In-Built database security assessment tool name as DBSAT.
DBSAT has three components: Collector, Reporter, and Discoverer. Collector and Reporter work together to discover risk areas and produce reports on those risk areas and produces the final assessment report in HTML and CSV formats.
You can use DBSAT report findings to:

– Fix immediate short-term risks
– Implement a comprehensive security strategy
– Support your regulatory compliance program
– Promote security best practices

Lets see what it is and how to use it.

Step 1: Unzip the package.

[oracle@dixitlab software]$ unzip dbsat.zip
Archive: dbsat.zip
inflating: dbsat
inflating: dbsat.bat
inflating: sat_reporter.py
inflating: sat_analysis.py
inflating: sat_collector.sql
inflating: xlsxwriter/app.py
inflating: xlsxwriter/chart_area.py
inflating: xlsxwriter/chart_bar.py
inflating: xlsxwriter/chart_column.py
inflating: xlsxwriter/chart_doughnut.py
inflating: xlsxwriter/chart_line.py
inflating: xlsxwriter/chart_pie.py
inflating: xlsxwriter/chart.py
inflating: xlsxwriter/chart_radar.py
inflating: xlsxwriter/chart_scatter.py
inflating: xlsxwriter/chartsheet.py
inflating: xlsxwriter/chart_stock.py
inflating: xlsxwriter/comments.py
inflating: xlsxwriter/compat_collections.py
inflating: xlsxwriter/compatibility.py
inflating: xlsxwriter/contenttypes.py
inflating: xlsxwriter/core.py
inflating: xlsxwriter/custom.py
inflating: xlsxwriter/drawing.py
inflating: xlsxwriter/format.py
inflating: xlsxwriter/__init__.py
inflating: xlsxwriter/packager.py
inflating: xlsxwriter/relationships.py
inflating: xlsxwriter/shape.py
inflating: xlsxwriter/sharedstrings.py
inflating: xlsxwriter/styles.py
inflating: xlsxwriter/table.py
inflating: xlsxwriter/theme.py
inflating: xlsxwriter/utility.py
inflating: xlsxwriter/vml.py
inflating: xlsxwriter/workbook.py
inflating: xlsxwriter/worksheet.py
inflating: xlsxwriter/xmlwriter.py
inflating: xlsxwriter/LICENSE.txt
inflating: Discover/bin/discoverer.jar
inflating: Discover/lib/ojdbc6.jar
inflating: Discover/conf/sample_dbsat.config
inflating: Discover/conf/sensitive_en.ini

Step 2: Configure the ‘dbsat configuration’ file.
Next you have to configre the main config file (dbsat.config) available under Discover/conf directory.

[oracle@dixitlab conf]$ pwd
/home/oracle/software/Discover/conf

[oracle@dixitlab conf]$ ls -ltrh
total 20K
-rwxrwxrwx. 1 oracle oinstall 13K Jan 16 22:58 sensitive_en.ini
-rwxrwxrwx. 1 oracle oinstall 2.4K Mar 1 22:12 dbsat.config

Few of the important parameters are given below.
vi dbsat.config

DB_HOSTNAME = localhost
DB_PORT = 1539
DB_SERVICE_NAME =tunedb
SENSITIVE_PATTERN_FILES = sensitive_en.ini >>>>> This param users sensitive_en.ini file for the English language patterns, which contains 75 patterns
ex: CREDIT_CARD_NUMBER, CARD_SECURITY_PIN, MEDICAL_INFORMATION, SOCIAL_SECURITY_NUMBER etc.

 

Step 3: Run the discoverer against the database to collect the information.

[oracle@dixitlab software]$ $(dirname $(dirname $(readlink -f $(which javac))))    --- To check the JAVAHOME.
-bash: /usr/java/jdk1.8.0_131: is a directory
[oracle@dixitlab software]$ export JAVA_HOME=/usr/java/jdk1.8.0_131

[oracle@dixitlab conf]$ cd ../..
[oracle@dixitlab software]$ ./dbsat discover -c Discover/conf/sample_dbsat.config tunedb_data

Database Security Assessment Tool version 2.0.1 (December 2017)

This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.

Enter username: system
Enter password:
Connection Successful- Retrying regarding "tunedb" as SID
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...

Enter password:
Verify password:
zip warning: tunedb_data_report.zip not found or empty
adding: tunedb_data_discover.html (deflated 88%)
adding: tunedb_data_discover.csv (deflated 84%)
Zip completed successfully.

We have the audit reports created under the tool directory.
Sample report attached with this report.

https://1drv.ms/f/s!Arob5fjpN041ga58isTgjF-wBPLI0A
tunedb_data – Oracle Database Security Risk Assessment

Hope It Helps
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

 
%d bloggers like this: