Twitter

crsctl stat res: syntax and optimization

crsctl is the Oracle Clusterware Control utility which is used to manage your Oracle CRS/GI/Restart configuration and crsctl stat res is most likely the most used/known mix of options used with the crsctl command which provides information about all the resources registered in your cluster. Unfortunately, crsctl stat res is not really humanly readable and is restricted in term of what information is shown and you then need to dig more into crsctl stat res to have more interesting information which is what uses rac-status.sh for example.

For example, if you want to have all the information about the services, you need to use the below syntax (-p is for static configuration and it would be -v for the runtime configuration):
# crsctl stat res -p -w "TYPE = ora.service.type"
crsctl stat res also has some nice syntax which can be used in the filer operators (it is not regexp level but it is pretty cool):
Filter Operators: =, >, <, !=, co, st, en, nc, nci, coi, eqi
  where
    co  --> contains
    st  --> starts with
    en  --> ends with
    nc  --> does not contain
    coi --> contains, case-insensitive
    nci --> does not contain, case-insensitive
    eqi --> equals, case-insensitive
For example, if you want the information about the listeners, and knowing that there are many types of listeners in an Oracle cluster like ora.listener.type, ora.scan_listener.type, ora.leaf_listener.type, ora.asm_listener.type, you could write the easy:
# crsctl stat res -v -w "TYPE co listener"
which will list all the listener types.

As these crsctl stat res commands return many information you may not be interested in, you can grep for what you want from this output. The issue I started to face is that more and more systems have more and more resources (hundreds of services, databases, etc ...) and crsctl stat res (and then my scripts rac-status.sh and oraenv++) was becoming slow which is not what we want. Hopefully, there is an easy way of greatly optimising these performances by using the -attr option allowing you to select only the columns you want:
[root@exa01db01]# time crsctl stat res -p -w "TYPE = ora.service.type" > /dev/null
real    0m33.047s
user    0m31.989s
sys     0m0.390s
[root@exa01db01]# time crsctl stat res -p -w "TYPE = ora.service.type" -attr "NAME,TYPE,ACL,ENABLED,ENABLED@SERVERNAME(exa01db01),ENABLED@SERVERNAME(exa01db02),ROLE,PLUGGABLE_DATABASE" > /dev/null
real    0m2.249s   <== 16 times faster !
user    0m0.882s
sys     0m0.164s
[root@exa01db01]#
Wow, 16 times faster in specifying the columns you want ! this crsctl -attr is a rocket !










This is how I recently greatly improved the performances of rac-status.sh and oraenv++. I just found one weird behavior when using the -attr option, see below:
[root@exa01db01]# crsctl stat res -v -w "NAME co LISTENER_SCAN2"
NAME=ora.LISTENER_SCAN2.lsnr
LAST_RESTART=11/09/2021 19:38:10
LAST_STATE_CHANGE=11/09/2021 19:38:10
[root@exa01db01]# crsctl stat res -v -w "NAME co LISTENER_SCAN2" -attr "NAME,LAST_RESTART,LAST_STATE_CHANGE"
NAME=ora.LISTENER_SCAN2.lsnr
LAST_RESTART=1636447090
LAST_STATE_CHANGE=1636447090
[root@exa01db01]# 
See these date formats ? they are different whether you use the -attr option or not. The date format is a human readable one without -attr and is an epoch sec if you use the -attr option. No big deal but it is good to know if you work on the restart dates:
[root@exa01db01]# date -d @1636447090
Tue Nov  9 19:38:10 AEDT 2021
[root@exa01db01]#


Bye for now !

nfs-status.sh: quickly check and fix NFS

NFS are a very cool feature, no doubt about that. But NFS reliability is more questionable and a hung NFS is not easy to spot and can have dramatic consequences by creating a huge load on your system which may eventually die. Also, depending on how/who manages your systems, you may eventually find mounted NFS not in fstab (which will then not be remounted at boot) or unmounted NFS even if they are correctly declared in fstab.

As responsible of the systems, you want to be able to quickly be able to:
  • Find which NFS is in a hung state
  • Quickly umount these hung NFS
  • Find NFS configuration issues (NFS in fstab but umounted or nounted NFS not in fstab)

This is to achieve these goals that I have dev nfs-status.sh; let's have a look at a first output:
[root@prod01]# ./nfs-status.sh

------------------------------------------------------------------------------
Mount Point   | NFS                               | Status       | in fstab  |
-----------------------------------------------------------------------------
/nfs1         | 10.11.12.13:/nfsserver/nfs1       | Hung         |          |
/nfs2         | 10.11.12.13:/nfsserver/nfs2       | Healthy      |          |
/nfs3         | 10.11.12.13:/nfsserver/nfs3       | Not Mounted  |          |
/nfs4         | 10.11.12.13:/nfsserver/nfs4       | Healthy      |    xxx    |
/nfs5         | 10.11.12.13:/nfsserver/nfs5       | Healthy      |          |
/nfs6         | 10.11.12.13:/nfsserver/nfs6       | Not Mounted  |          |
------------------------------------------------------------------------------
[root@prod01]# echo $?
1
[root@prod01]#
The above output is self-explanatory enough not to have to comment it extensively; you can quickly see that /nfs1 is hung (this is tested with the stat -t command), that /nfs4 it not in fstab and that /nfs3 and /nfs6 are not mounted but should be mounted as they are in fstab.

Also, you can know how many NFS are hung usiong the return code of the script, it here shows that 1 NFS is hung. This makes it easy to check if all NFS are good before starting a maintenance and then not doing the maintenance if a NFS is hing using && and || as below:
# ./nfs-status.sh || exit 123
The script also has options to umount the hung NFS, show the mount command, mount the umounted NFS
  • --show: Show the umount commands to umount the hung NFS
  • --mount: Mount the unmounted NFS
  • --umount: Umount the hung NFS
  • --fstab: For non standard fstab, default is: /etc/fstab


Where to download ?

You can download nfs-status.sh from my public git repo or using the direct link to the source code from the Scripts menu on top of this page.

In case of issue/question, as usual, feel free to post a comment down below, contact me by email or linkedin chat.

I have been using this script for a little while and it has always been a situation it has been very useful !

oraenv++: an easy and powerful tool to set up your Oracle environment

I have never found a really good and portable way of setting up an Oracle/ASM environment. For sure, there is oraenv but it is based on oratab which is a static hardcoded text file and oraenv does not work well (at all ?) with RAC. Some tries to set up aliases to set up their environment but same, it is hardcoded, Oracle uses hardcoded env files in OCI, etc ... All of this works more and less well but it has always seemed a bit clumsy to me and during a recent email discussion I had with Osman, I decided to jump into that challenge to create something better, easier and more efficient than any of these tools and a fews days later was born oraenv++!

Key features

  • oraenv++ reads the environmental information from GI/CRS/Oracle Restart so no hardcoded configuration file is needed; sorry I cannot do anything if you do not have GI/CRS/Oracle Restart
  • oraenv++ sets the ORACLE_SID, ORACLE_HOME, ORACLE_BASE, PATH and can also set the ORACLE_PDB_SID environment variable to be able to directly connect to a PDB (from 18.8)
  • if you do not know the name of the database you want to set the env up, oraenv++ will show you a menu with a list of databases from your configuration to choose from
  • you can also grep and grep -v in the database list of your environment (regexp are supported)
  • GI storing the database names in lowercase regardless of their case, oraenv++ is not key sensitive regarding the database name nor the pattern(s) to grep/ungrep
  • oraenv++ has to be sourced (. oraenv++) and not executed (./oraenv++); I'll eventually write about the differences in my bash tips
  • I have tested it with GI 19c and I see no reason why it would not work from any GI 12c+ (including 21c+); not sure for GI 11g which should not be much around any more these days
  • I have been using oraenv++ for a little while now and I honestly find it











Examples

A first example to simply set up a database environment:
$ . oraenv++ prod12a
Database            : prod12a
ORACLE_HOME         : /u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : PROD12A1
ORACLE_PDB_SID      :
sqlplus is          : /u01/app/oracle/product/12.1.0.2/db_1/bin/sqlplus
$
Here, we have set up the prod12a environememnt with the correct instance name running on the host you are connected to. If you do not want to see this output, you can make it silent:
$ . oraenv++ prod12a --silent
$
or
$ OPP_SILENT=True
$ . oraenv++ prod12a
$
If you do not specify a database name, oraenv++ will list all the databases which have an instance on this host from your GI configuration and show you a menu to choose from (let's go with 6 below):
$ . oraenv++
    Database    SID          ORACLE_HOME
-----------------------------------------------------------------
  1/ asm        +ASM1      /u01/app/19.11.0.0/grid
  2/ dev12a     DEV12A1    /u01/app/oracle/product/12.1.0.2/db_1
  3/ dev12b     DEV12B1    /u01/app/oracle/product/12.1.0.2/db_1
  4/ dev19a     DEV19A1    /u01/app/oracle/product/19.0.0.0/db_1
  5/ dev19b     DEV19B1    /u01/app/oracle/product/19.0.0.0/db_1
  6/ misc1      MISC11     /u01/app/oracle/product/12.1.0.2/db_1
  7/ misc2      MISC21     /u01/app/oracle/product/19.0.0.0/db_1
  8/ test       TEST1      /u01/app/oracle/product/11.2.0.4/db_1
  9/ prod12a    PROD12A1   /u01/app/oracle/product/12.1.0.2/db_1
 10/ prod12b    PROD12B1   /u01/app/oracle/product/12.1.0.2/db_1
 11/ prod19a    PROD19A1   /u01/app/oracle/product/19.0.0.0/db_1
 12/ prod19b    PROD19B1   /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)
6
Database            : misc1
ORACLE_HOME         : /u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : MISC11
ORACLE_PDB_SID      :
sqlplus is          : /u01/app/oracle/product/12.1.0.2/db_1/bin/sqlplus
$
If you have many databases, you can narrow the selection down using --grep or --ungrep (acts as a grep -v); note that these 2 options allow mutiple comma separated pattern to be grepped/ungrepped; they also supports regexp -- this is far overkill but it is fun :)
$. oraenv++ --grep prod,dev --ungrep 12
     Database    SID          ORACLE_HOME
-----------------------------------------------------------------
  1/ dev19a     DEV19A1    /u01/app/oracle/product/19.0.0.0/db_1
  2/ dev19b     DEV19B1    /u01/app/oracle/product/19.0.0.0/db_1
  3/ prod19a    PROD19A1   /u01/app/oracle/product/19.0.0.0/db_1
  4/ prod19b    PROD19B1   /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)
This could also have been written (just to showcase a regexp :)):
$. oraenv++ --grep 19[ab]$
     Database    SID          ORACLE_HOME
-----------------------------------------------------------------
  1/ dev19a     DEV19A1    /u01/app/oracle/product/19.0.0.0/db_1
  2/ dev19b     DEV19B1    /u01/app/oracle/product/19.0.0.0/db_1
  3/ prod19a    PROD19A1   /u01/app/oracle/product/19.0.0.0/db_1
  4/ prod19b    PROD19B1   /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)
It indeed also works with ASM:
# . oraenv++ asm
Database            : asm
ORACLE_HOME         : /u01/app/19.11.0.0/grid
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : +ASM1
ORACLE_PDB_SID      :
sqlplus is          : /u01/app/19.11.0.0/grid/bin/sqlplus
#
If you want to direct connect to a PDB, you can also specify one with the --pdb option which will set the ORACLE_PDB_SID variable and you could then directly connect to this PDB (this works from 18.8):
$ . oraenv++ dev19a --pdb MY_PDB
Database            : dev19a
ORACLE_HOME         : /u01/app/oracle/product/19.0.0.0/db_1
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : DEV19A1
ORACLE_PDB_SID      : MYPDB
sqlplus is          : /u01/app/oracle/product/19.0.0.0/db_1/bin/sqlplus
$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
----------------
MY_PDB

TODO / other features / Ideas

First of all, feel free to post down below or contact me if you have ideas or remarks.

On my end, one cool feature to implement in a next future would be to list the PDBs per CDB showing a menu to be able to choose from as we have hundreds of PDBs per CDB and we cannot really remember of all them then choosing from a list would be far easier. The thing here is that the PDBs are real GI resource from 21c only, they are listed in the services before 21c which I already use to show them in rac-status.sh. The thing is that crsctl is slow to query all the services when there are a lot of them so this would slow oraenv++
. Having said that, the direct connection to a PDB feature takes few seconds to get connected and ssh in any Cloud I have experienced are also slow verifying your credentials, billing you, etc ... and no one complains about it :)

Where to download ?

You can download oraenv++ from my public git repo or using the direct link to the source code from the Scripts menu on top of this page.

In case of issue/question, as usual, feel free to post a comment down below, contact me by email or linkedin chat.

Enjoy !

crsctl stat res: syntax and optimization

crsctl is the Oracle Clusterware Control utility which is used to manage your Oracle CRS/GI/Restart configuration and crsctl stat res is ...