Twitter

rac-status.sh : an overview of your RAC / GI 11g,12c, 18c ,19c, 21c, 23c resources in a glimpse


Consolidation has been a fancy word and concept for years in IT resulting for us DBAs with more and more databases and instances running on more and more powerful / bigger clusters running RAC (Real Application Cluster) before and now GI (Grid Infrastructure) from 12c version.

It is kind of nice to have to manage all these big clusters / appliances but sometimes what was easy before become less easy like quickly answering these questions :
  • what is up ?
  • what is supposed to be up ? (which is fairly different from the previous one)
  • where is it up ?
  • have we missed a problem ?
And quickly answering these questions on a Full Exadata Rack with 100 databases running on is not that easy with the common tools.

I then made a tool to be able to answer these questions and have a nice result in a glimpse (please note that all the below screenshots come from real implementations, I have just anonymized the outputs for obvious reasons.

Feel free to also check the the rac-status.sh: FAQ and the rac-status.sh what's new post


Overview

Please find below an overview of the rac-status.sh script on an Exadata X8-2 half rack :

You will find in this output :
  • The Exadata model (if available, if not, it shows nothing) or nothing for a non-Exadata RAC
  • The first column on the left contains the names of the databases running across the cluster; please note that this column is auto adaptive if you have long database names
  • The second column contains the version (grabbed from the ORACLE_HOME path, the GI has no information on the installed version) and you will find a number "(1)" which refers to the ORACLE_HOME path the database is running against. You will find the ORACLE_HOME list below the table, it is very handy when you have many ORACLE_HOME installed
  • The next columns are the status of each instance on each node (one column per node), a blue hyphen "-" shows that the instances are not there because they are not supposed to be there, the status are quite self explanatory and colored
  • Last but not least, the "DB Type" column shows if the database is a RAC, a RACOneNode or a Single instance. This column can also be colored in White if the database is a Primary database or in Red if it is a Standby database.
  • The owner and group of the ORACLE_HOME which is very handy when you have different owners.


A large implementation

Please find a screenshot of a large implementation; I have removed half of the databases running there as it was not fitting my screen but you can see here a nice and clear output of this full Exadata rack in no time




Please note that here, 4 ORACLE_HOMEs are installed, the reference number in the Version column shows how useful it is in this scenario.

Different DB Types

Here is an example of an implementation with RAC and RACOneNode database types (you may also find some Single instances.



Identifying issues 

This clear output of the status of all your cluster instances is also really helpful to quickly see if something wrong happened as shown in the below screenshot :


Please note that this screenshot output is slighlty different than the previous ones; Indeed, the ORACLE_HOME references in the Version column were not implemented when I took it and as it is not really easy to crash some databases just to renew my screenshots, I am still using it.

Listeners

rac-status.sh can also show the listeners:
Note: if your listeners listen on many ports, the port list is written on the right outside of the table to keep everything visible and well aligned.

Services

rac-status.sh can also show the services:


Tech resources

The "tech resources" (diskgroups, VIPs, ACFS, ONS, etc ...) are also shown (option -t which is included in the -a option which shows everything):

If you use ACFS, the filesystem where ACFS is mounted on will also appear on the right on the table as shown on the below example:


Quickly detect a recently restarted resource

rac-status.sh also shows with a yellow background any resource recently restarted which is very handy to quickly see if something wrong has happened in a recent past:

The default shows any resource restarted less than 24 hours ago. You can modify this default in the script with the variable DIFF_HOURS:
DIFF_HOURS="24"
Or in the command line with the -w option knowing that hours is the default unit but you can also use d for day, w for week, m for month and y for year:
$ ./rac-status.sh -w 360        # 360 hours
$ ./rac-status.sh -w 3d         # 3 days
$ ./rac-status.sh -w 1w         # 1 week
$ ./rac-status.sh -w 2m         # 2 months
$ ./rac-status.sh -w 5y         # 5 years

When STATE and TARGET are different

In the case of a resource STATE is different than its TARGET (meaning there is an issue with this resource), it will be highlighted in red with a legend below the table as shown in this screenshot -- not that databases rely on USR_ORA_OPEN_MODE instead of STATE and TARGET (from January 31st 2022 as per the what's new):

Disabled resources

It may happen that resources are disabled, rac-status.sh will then show a "x" next to the disabled resources as well as a legend about this below the table. This is very useful when you enabled and disable some resources during maintenances; it works for listeners, services and instances:

Output customization

The default I ship with the script code is to show the Listeners and the Database by default and not the Services because . . . it suits my needs. As it may not suit yours, you can easily modify this default behavior. You have few ways modifying this to fit your needs.

1/ Modify the default variables in the script:

The default behavior of the script is defined by the below variables so just comment out what you do not want as default in the script (the last uncommented value wins):
  SHOW_DB="YES"                 # Databases
 #SHOW_DB="NO"
SHOW_LSNR="YES"                 # Listeners
#SHOW_LSNR="NO"
 SHOW_SVC="YES"                 # Services
 SHOW_SVC="NO"

2/ Using the command line:

3 options are available from the command line to dynamically change the output:
-d        Revert the behavior defined by SHOW_DB  ; if SHOW_DB   is set to YES to show the databases by default, then the -d option will hide the databases
-l        Revert the behavior defined by SHOW_LSNR; if SHOW_LSNR is set to YES to show the listeners by default, then the -l option will hide the listeners
-s        Revert the behavior defined by SHOW_SVC ; if SHOW_SVC  is set to YES to show the services  by default, then the -s option will hide the services
These variables revert the default behavior. I have coded it like this as it is very efficient. Indeed, the default of the script shows the Listener and the Databases and not the Services. If I want to show the services as well, I just use the -s option.

3/ Show / hide everything:

I have also implemented 2 others handy options:
-a        Show everything regardless of the default behavior defined with SHOW_DB, SHOW_LSNR and SHOW_SVC
-n        Show nothing  regardless of the default behavior defined with SHOW_DB, SHOW_LSNR and SHOW_SVC
These are very handy as whatever the default is, they will erase it. For example, when I prepare the action plan of a maintenance, I want to save the status of every single resource across my cluster whatever the default is, I then write:
$ ./rac-status.sh -a > status_before_maintenance
If you do not know the current default of the script and do not want to bother with it, you can just show the databases only by using:
$ ./rac-status.sh -n -d              # -n shows nothing and -d revert the do not show the database from -n 
You can try many combinations to get used to, it is harmless.

4/ Adapt the colors:

There are also 2 options that may be useful for you depending on your needs. Option -u to show an Uncolored output:



And the -r option to Revert the colors; it is very useful if you are using a clear terminal background:


5/ Sort the database output:

By default, the database output is sorted by database name on a alphabetic order. You can modify this using the -c option.
The -c can receive the below parameters:
c                             # followed by a number for the column number you want to sort by
d                             # to sort by database name
v                             # to sort by version
s                             # to sort by status; it can be followed by a number which
                              # is the node number you want to sort the status by
                              # if no number is specified, it is sorted by the node 1 status
t                             # to sort by DB Type
r                             # can be added to the previous parameters to make the sort reverse
Examples:
./rac-status.sh -c d      # sort by database name (the default)
./rac-status.sh -c v      # sort by version
./rac-status.sh -c vr     # sort by version in reverse order
./rac-status.sh -c c4r    # sort by the 4th column (the node 2) in reverse order
./rac-status.sh -c 4r     # same as above as "c" is default and optional
./rac-status.sh -c s2r    # sort by the status of the second node (same as c4r)
./rac-status.sh -c s      # sort by the status of the first node
./rac-status.sh -c tr     # sort by DB type in reverse order
Few points to keep in mind:
  • Any other option is compatible with -c
  • You can modify the default sort by updating the SORT_BYvariable in the script and then always enjoying your favorite way of sorting the database output like for example:
  • SORT_BY="c3r"      # to always sort by the 3rd column in reverse order
    

grep and ungrep

When you work with big implementations, the rac-status.sh output may exceed the size of your screen (especially when you show the services) and/or you may want to see the specific status of a listener or a database or only the 12c databases, etc ...
I have then implemented 2 options for the purpose of grepping or ungrepping (grep -v) in the rac-status.sh output:
-g        Act as a grep command to grep a pattern from the output (key sensitive)
-v        Act as "grep -v" to ungrep from the output  (key sensitive)
Few examples:
$ ./rac-status.sh -g 12            # Only grep 12c databases
$ ./rac-status.sh -a -g Shut       # Only the lines containing "Shut"
$ ./rac-status.sh -n -l -v Online  # Only the listeners that are not Online
Feel free to test any combination.

Environment setting

By default, rac-status.sh relies on oraenv to set the ASM environment and then access the crsctl command. If oraenv does not work for you, you can use the -e option to use your current environment. Note that the current environment has to give access to crsctl.
You can also set on top of the script USE_ORAENV="NO" instead of the default USE_ORAENV="YES" if you never want to use oraenv and always rely on the current environment.

Usages

On top of using this rac-status.sh script during my daily activities to have a quick look at what is running on a GI implementation, I also found it useful in the below scenarios :

  • Patching progression

rac-status.sh is very useful when patching Exadatas; Indeed, I can then easily follow what's being patched during a database nodes rolling patch (below node 2 is being patched)


  • Before and after a maintenance

I also use this script before and after maintenances (the output is easy to copy and paste) to be able to quickly compare the outputs and see if something is missing at the end of a maintenance, an instance that did not restart properly, etc...
$ ./rac-status.sh -a > status_before_maintenance
. . .  do your maintenance . . .
$ ./rac-status.sh -a > status_after_maintenance
$ diff status_before_maintenance status_after_maintenance
By doing these quick and simple steps, you can ensure being idempotent.
  • A quick monitoring tool

Please have a look at rac-mon.sh for a quick and efficient tool to monitor your cluster based on rac-status.sh.

-h to sum up the options

Feel free to use the -h option to sum up each available option with some examples:
$  ./rac-status.sh -h
NAME
        rac-status.sh - A nice overview of databases, listeners and services running across a GI 12c

SYNOPSIS
        ./rac-status.sh [-a] [-n] [-d] [-l] [-s] [-g] [-v] [-h]

DESCRIPTION
        rac-status.sh needs to be executed with a user allowed to query GI using crsctl; oraenv also has to be working
        rac-status.sh will show what is running or not running accross all the nodes of a GI 12c :
                - The databases instances (and the ORACLE_HOME they are running against)
                - The type of database : Primary, Standby, RAC One node, Single
                - The listeners (SCAN Listener and regular listeners)
                - The services
        With no option, rac-status.sh will show what is defined by the variables :
                - SHOW_DB       # To show the databases instances
                - SHOW_LSNR     # To show the listeners
                - SHOW_SVC      # To show the services
                These variables can be modified in the script itself or you can use command line option to revert their value (see below)

OPTIONS
        -a        Show everything regardless of the default behavior defined with SHOW_DB, SHOW_LSNR and SHOW_SVC
        -n        Show nothing  regardless of the default behavior defined with SHOW_DB, SHOW_LSNR and SHOW_SVC
        -a and -n are handy to erase the defaults values:
                        $ ./rac-status.sh -n -d                         # Show the databases output only
                        $ ./rac-status.sh -a -s                         # Show everything but the services (then the listeners and the databases)

        -d        Revert the behavior defined by SHOW_DB  ; if SHOW_DB   is set to YES to show the databases by default, then the -d option will hide the databases
        -l        Revert the behavior defined by SHOW_LSNR; if SHOW_LSNR is set to YES to show the listeners by default, then the -l option will hide the listeners
        -s        Revert the behavior defined by SHOW_SVC ; if SHOW_SVC  is set to YES to show the services  by default, then the -s option will hide the services
                  Note : the options are cumulative and can be combined with a "the last one wins" behavior :
                        $ ./rac-status.sh -a -l              # Show everything but the listeners (-a will force show everything then -l will hide the listeners)
                        $ ./rac-status.sh -n -d              # Show only the databases           (-n will force hide everything then -d with show the databases)

        -g        Act as a grep command to grep a pattern from the output (key sensitive)
        -v        Act as "grep -v" to ungrep from the output (key sensitive)
        -g and -v examples :
                        $ ./rac-status.sh -g Open                       # Show only the lines with "Open" on it
                        $ ./rac-status.sh -v 11                         # Do not show 11g databases
                        $ ./rac-status.sh -g "Open|Online"              # Show only the lines with "Open" or "Online" on it
                        $ ./rac-status.sh -g "Open|Online" -v 12        # Show only the lines with "Open" or "Online" on it but no those containing 12

        -h        Shows this help
$

Where to download ?

The code is hosted and maintained in my github repository, help yourself and enjoy rac-status !



I hope you'll be enjoying this script as much as I enjoy it on a daily basis !

Any suggestion, comment or bug, please let me know in the comments sections.



116 comments:

  1. Hi, nice script!
    I made a few corrections to make it work for GI 11.2 and oracle Restart 12.2.
    it works perfectly ;-)

    ReplyDelete
    Replies
    1. Mathieu, Can you please share modified version of GI 11.2 & 12.2 if possible.

      Thanks Fred For nice work!!!

      Delete
    2. Also interested in what modifications are needed for GI 11.2

      Delete
    3. What corrections are needed for GI 11.2?

      Delete
    4. Hi,

      I have implemented 11g support and Oracle support in the latest version, please download the latest version and enjoy !

      Fred

      Delete
  2. Thanks, I have also added few options recently to show the services (https://unknowndba.blogspot.com/2018/10/rac-statussh-now-shows-services-and-more.html) and the listeners (https://unknowndba.blogspot.com/2018/10/rac-statussh-now-shows-listeners-as-well.html)

    I'll update this post to show a detail of these options but the code pointed by this blog contains the latest versions then with these very useful options.

    ReplyDelete
  3. doesn't work on solaris
    getting the below errors


    sed: illegal option -- i
    awk: syntax error near line 1
    awk: bailing out near line 1

    awk: syntax error near line 1
    awk: bailing out near line 1

    ReplyDelete
    Replies
    1. Hi,

      Please download the script again, it is now fixed for Solaris,

      Fred

      Delete
  4. Great, really very useful!!

    Thanks for sharing this...

    ReplyDelete
  5. Replies
    1. I'll be publishing a fix tomorrow that will make it work on Solaris.

      As of now, you can use this workaround on Solaris:
      $ export PATH=/app/oracle/product/12c/grid/bin:/usr/gnu/bin:/usr/bin:/usr/sbin
      $ ./rac-status.sh

      Then it will use /usr/gnu/bin/awk instead of the old default awk.

      Let me know,

      Fred

      Delete
    2. The latest version (downloadable here : https://github.com/freddenis/oracle-scripts/blob/master/rac-status.sh) should be working on Solaris.

      Let me know.

      Delete
    3. Nice Work Done Fred!!!. Thanks for sharing in details.

      Please suggest does this work with normal 12c RAC database on AIX 7.1 version?. (Without Exadata)

      Thanks
      Mohammed

      Delete
    4. Thanks Mohammed,

      Yes it should work on AIX, I have no AIX handy so I haven't tested but if you run RAC with ASM, it should be good. please give a go on AIX and let me know if any issue, I'll fix them for you.

      If you use GPFS instead of ASM, I'll be happy to discuss with you and make it work.

      Delete
  6. for AIX, you need to install gawk (GNU awk) v>=4 and change AWK=/usr/bin/gawk (match function has only 2 arguments on posix awk)
    There is some minor bugs concerning colors on AIX but it works.
    Julien

    ReplyDelete
    Replies
    1. Thanks for your comment ! I have then modified the script with:
      AWK=`which gawk` ;
      for AIX so it should be good now right ?

      Delete
  7. Hi Fred,

    This script is running fine on linux and on Exadata machines but on AIX it is giving parasing errors.
    We don't have an gawk on AIX. I have modified it to AWK.
    Is it mandatory to have gawk installed or do we have any other work around to fix the script on AIX.

    Thanks for your support and help for sharing your hard work.

    ReplyDelete
    Replies
    1. Hi,

      Could you please send me the errors you get on AIX ? On the LinkedIn chat or by email.

      You can also install the gnu tools on AIX with no problem.

      Delete
    2. Here are the Linux tools for AIX -- https://www.ibm.com/developerworks/aix/library/aix-toolbox/

      Delete
  8. Hi Dines.

    I am Hiran Horta from Brazil. Tks a lot!
    Nice script, work so well here in your EXADATA X6-2 ZDLRA Elastic Rack HC 8TB.
    I try in ZDLRa with this configuration.

    [root@xxxxxxx ~]# imageinfo

    Kernel version: 4.1.12-94.8.4.el6uek.x86_64 #2 SMP Sat May 5 16:14:51 PDT 2018 x86_64
    Image kernel version: 4.1.12-94.8.4.el6uek
    Image version: 18.1.6.0.0.180529
    Image activated: 2018-07-20 17:09:31 -0300
    Image status: success
    System partition on device: /dev/mapper/VGExaDb-LVDbSys1

    ReplyDelete
  9. Hi 18c The script under grid home did not work.
    Waiting as follows, what could be the problem?
    -------------------------
    crsctl query crs activeversion -f
    Oracle Clusterware active version on the cluster is [18.0.0.0.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [696331593].
    -------------------------
    imageinfo

    Kernel version: 4.1.12-124.26.12.el6uek.x86_64 #2 SMP Wed May 8 22:12:22 PDT 2019 x86_64
    Image kernel version: 4.1.12-124.26.12.el6uek
    Image version: 18.1.16.0.0.190513.2
    Image activated: 2019-05-26 14:25:39 +0300
    Image status: success
    System partition on device: /dev/mapper/VGExaDb-LVDbSys1
    -------------------------

    ***************/bin/bash -x rac-status.sh
    + TMP=/tmp/status30721.tmp
    + TMP2=/tmp/status230721.tmp
    + DBMACHINE=/opt/oracle.SupportTools/onecommand/databasemachine.xml
    + GREP=.
    + UNGREP=nothing_to_ungrep_unless_v_option_is_used30721
    + USE_ORAENV=YES
    + REVERSE=NO
    + WITH_COLORS=YES
    + WHITE=37m
    + DIFF_HOURS=24
    + SHOW_DB=YES
    + SHOW_LSNR=YES
    + SHOW_SVC=YES
    + SHOW_SVC=NO
    + COL_NODE_OFFSET=99
    ++ uname
    + OS=Linux
    + case ${OS} in
    ++ which awk
    + AWK=/bin/awk
    ++ which sed
    + SED=/bin/sed
    + [[ ! -f /bin/awk ]]
    + [[ ! -f /bin/sed ]]
    + getopts andslhg:v:o:f:eruw: OPT
    + [[ NO == \Y\E\S ]]
    + '[' -n '' ']'
    + '[' -z '' ']'
    + [[ YES == \Y\E\S ]]
    ++ ps -ef
    ++ grep pmon
    ++ grep asm
    ++ /bin/awk '{print $NF}'
    ++ sed s/asm_pmon_//
    ++ egrep '^[+]'
    + ORACLE_SID=+ASM1
    + export ORAENV_ASK=NO
    + ORAENV_ASK=NO
    + . oraenv

    ReplyDelete
    Replies
    1. Hi,

      is oraenv working ?
      . oraenv <<< +ASM1

      Is there an ASM entry in /etc/oratab ? if not, you may suffer from https://unknowndba.blogspot.com/2019/01/lost-entries-in-oratab-after-gi-122.html


      Alternatively, you can set up the ASM/GI environment manually and use -e to use the current environment:

      ./rac-status.sh -e

      Let me know

      Delete
  10. Hi guys i really do appreciate the effort to share knowledge on this platform especially the ./rac-status.sh script on 12c.
    Can some one share that of 11g please.

    ReplyDelete
  11. Thanks for the script, great work!!!

    ReplyDelete
  12. Hi Fred, can you add an option to your tool in order to get opatch status of every node?
    Best regards.

    ReplyDelete
    Replies
    1. Hi, what do you mean by an opatch status of every node ?

      Thanks,

      Delete
    2. I mean opatch lsinventory, opatch lspatches output.
      Regards.

      Delete
    3. It's not in rac-status but here: https://unknowndba.blogspot.com/2018/07/lspatchessh-oracle-patch-reporting-tool.html?m=1

      Delete
  13. Very good script and useful. Works perfectly. Thank you !

    ReplyDelete
  14. Hi - Thanks for sharing!

    I am unable to download the script. Could you help me here?


    Thanks,

    ReplyDelete
    Replies
    1. You can find the code here: https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-status.sh

      Delete
  15. I am able to download now. Really helpful. Thanks a million.

    ReplyDelete
  16. Hi Fred,

    Awesome script.. Thanks much for sharing..

    ReplyDelete
  17. Fantastic script - thank you!
    It would be good to show the Status and Target when they are different so we can see which node it is different on and what the target and status are.

    ReplyDelete
    Replies
    1. Thanks, i'm afraid so many information make the output messy.

      Delete
  18. Hi Dennis,

    The script is working well but one problem I am getting in the output, my standby database is showing DB_Type as RAC(P) instaead of RAC(S).

    ReplyDelete
    Replies
    1. Really ? Please do

      ./rac-status.sh -o output.txt

      And please send me the file output.txt by email.

      Thanks,

      Delete
  19. Hi Fred,

    Need you email id.

    Regards,
    Rupdipta

    ReplyDelete
  20. Hi There,

    I am looking out Goldengate lag gap to be captured and send an email to us.
    So that we can proactively work and resolve.
    Please help me in providing the script for the same.

    ReplyDelete
  21. Awesome script Fred.. appreciate your sharing it..

    ReplyDelete
  22. In AIX 7.1
    change gawk --> awk but executing :

    oracle@dexdbs1:/home/oracle> ./rac_status.sh

    Cluster dexdbs-cluster

    /usr/bin/sed: Not a recognized flag: i
    Usage: sed [-n] [-u] Script [File ...]
    sed [-n] [-u] [-e Script] ... [-f Script_file] ... [File ...]
    awk: 0602-542 There is an extra } character.
    The source line is 150.
    The error context is
    >>> } <<<
    Syntax Error The source line is 150.
    awk: 0602-543 There are 6 extra } characters.
    awk: 0602-543 There are 2 extra ) characters.
    awk: 0602-500 Quitting The source line is 150.

    - these are misvariables of environment

    ORACLE_SID=+ASM2; export ORACLE_SID
    GRID_HOME=/oragrid122/product/grid_home; export GRID_HOME
    ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
    PATH=$ORACLE_HOME/tfa12c/tfa/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$BASE_PATH:$PATH; export PATH
    TFA_HOME=$GRID_HOME/tfa12c/tfa/dexdbs1/tfa_home
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    LIBPATH=$ORACLE_HOME/lib; export LIBPATH
    CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

    ReplyDelete
    Replies
    1. Hi,

      You need gawk for rac-status; also your sed does not seem to have -i; these tools are freely available for AIX https://www.ibm.com/support/pages/aix-toolbox-linux-applications-overview

      Thanks,

      Delete
  23. Hi Fred,

    This is a very useful Script for day to day maintenance. One more enhancement would be great. If the script can also list all the PDB's and there status in all the containers, then it can be great.

    Cheers.

    ReplyDelete
    Replies
    1. Hi Chandu,

      Unfortunately, CRS/GI does not know about PDBs, only about CDBs. CRS does not even know if a database is a non-CDB or a CDB.

      I would then have to connect to any CDB and check in v$pdbs. I am not saying that it is not possible but far from simple (and slower) specially if you use different owners:

      -- If you use the same owner you run rac-status with, I could connect to any CDB (if / as sysdba possible) and check for this information
      -- If you use different owners, you would need SSH keys from the users running rac-status and the target users
      -- If you use different users, you could also run rac-status as root but it is not really something we want to do

      20c (CDB only) should be available in the Cloud in January so I'll check there how GI works in 20c and I'll try to find a way to easily show the PDBs status in rac-status -- I am afraid the multi owner configurations won't have access to this information easily. Also, having to connect to every CDB to have this information is really far from easy, let's see what comes with 20c and what I can do about this.

      Cheers,

      Fred

      Delete
    2. Hi Chandu,

      It seems that the PDBs will be listed in CRS in 20c: https://docs.oracle.com/en/database/oracle/oracle-database/20/newft/pluggable-database-cluster-resources.html:

      *************************************************************
      Pluggable Database Cluster Resources

      Pluggable Database (PDB) Cluster Resources enables direct mapping and control of those PDB resources. Unlike in previous versions, in which cluster resources for Multitenant databases were mapped against the Container Database (CDB) using a control of Pluggable Databases (PDBs) using services.

      Pluggable Database (PDB) Cluster Resources enable a tighter and more effective control of PDBs in an Oracle RAC Database.
      *************************************************************

      GI 20c is not available yet at this time but as soon as it is, I'll check and implement this ! (for 20c+ then)

      Thanks,

      Delete
  24. Thanks for the reply and apology for my late response. Can you provide me guidelines for checking the same when we are having the same owner.

    Cheers

    ReplyDelete
    Replies
    1. Hi Chandru,

      I haven't checked how CRS works with 20c as it is not available yet but I am not sure if I will implement this feature; rac-status is based on CRS information, PDBs are different concept that CRS is not aware of and would need to connect to every single database to get the information which is not that easy knowing the many different environments setting people can have.

      It would be better if Oracle could give the PDBs information in CRS (same as the dataguard lag by the way).

      Regards,

      Delete
    2. Hi All,
      The script is useful, could you kindly share the script is enhanced that shows PDB info as well. Many Thx!!

      best regards.
      Arjun

      Delete
    3. Hi,

      As discussed above, this information should have been implemented in GI 20c which won't be released so we'd need to wait for GI21c to have this information.

      But I am working on adding the PDB name on the same line as the service (this info is available) which would be a good information to have.

      Keep you posted when available,

      Delete
  25. Hi Fred,
    I have an environment with an Oracle RAC with two instances and one standby.
    How do I do to display my primary database and my stb in only one execution? I also want to show my RAC services, how do I do that?
    I sent you an e-mail with some prints of my environment to explain better what I want to do.
    Thanks in advance.

    ReplyDelete
    Replies
    1. Hi Diego,

      Thanks for your comment.

      >> I also want to show my RAC services, how do I do that?
      ./rac-status.sh -a # -a shows everything: databases, services and listeners


      >> How do I do to display my primary database and my stb in only one execution
      On your primary database server you can execute:
      ./rac-status.sh -a; ssh ./rac-status.sh -a

      I have also developed https://unknowndba.blogspot.com/2019/09/yal-yet-another-launcher.html to execute random commands or scripts on different servers but if you have only 2 systems, the previous command line would do the job perfectly.

      Let me know,

      Fred

      Delete
    2. Hi Fred,

      >> ./rac-status.sh -a # -a shows everything: databases, services and listeners
      I have configured SHOW_SVC parameter with YES and, even runing rac-status.sh with -a option, the services aren't being listed.

      >> On your primary database server you can execute:
      ./rac-status.sh -a; ssh ./rac-status.sh -a

      That worked good for me, thanks for help.

      Diego

      Delete
    3. Hi Diego,

      As we discussed by email, you have to create the services in GI with srvctl (which is the recommended way) and not use service_names to allow rac-status to show them.

      Thanks,

      Delete
  26. Wow just brilliant work! Thank you for sharing this awesome tool with us. I was about to re-invent the wheel to monitor our Exadata systems but you saved me the pain and labor.

    Cheers,
    Ben

    ReplyDelete
    Replies
    1. My pleasure, happy you like it !

      Have a good day,

      Delete
  27. Absolutely fantastic script. I've been struggling with adapting older scripts to work with RAC instance and the way Exadata Cloud at Customer presses out databases and correlating /etc/oratab with pmon names.

    ReplyDelete
  28. Would it be possible to add another option? -q to query any or all databases and maybe pass a sql script parameter? I would love to help if you would be willing to add it.

    ReplyDelete
    Replies
    1. Hi,

      Please have a look at https://unknowndba.blogspot.com.au/2018/04/rac-onalldbsh-easily-execute-query-on.html

      also may be https://unknowndba.blogspot.com/2019/09/yal-yet-another-launcher.html could be useful

      Regards,

      Delete
    2. Does this script can start and stop databases, listener services. If not can we add an option to it.

      Delete
    3. No, it just shows the status. I do not intend to implement this in this script.

      Delete
  29. Thanks for the script.

    What does it mean when an instance has just a dash and the expected value is Open?

    Thanks

    ReplyDelete
    Replies
    1. Hi Fernando,

      Dash is when there is no status reported by GI. What you describe does not seem to be expected.

      Could you do rac-status.sh -o fileforfred.txt and send me this file by email ? I'll have as look.

      Thanks,

      Delete
    2. Hi,
      I am also having this problem. I noticed that it occurs when using oracle restart.
      Thank you for sharing with us.

      BREAK_HERE
      NAME=ora.banco.db
      TYPE=ora.database.type
      ACL=owner:grid:rwx,pgrp:asmdba:r-x,other::r--,group:oinstall:r-x,user:oracle:rwx
      ORACLE_HOME=/u01/app/oracle/product/XX.X.X.X/dbhome_1
      DATABASE_TYPE=SINGLE
      ROLE=PRIMARY
      ENABLED=1
      GEN_USR_ORA_INST_NAME=BANCO

      BREAK_HERE
      NAME=ora.banco.db
      TYPE=ora.database.type
      LAST_SERVER=server123
      STATE=ONLINE
      TARGET=ONLINE
      LAST_RESTART=1679947667
      LAST_STATE_CHANGE=1679947667
      STATE_DETAILS=Open,HOME=/u01/app/oracle/product/XX.X.X.X/dbhome_1

      BREAK_HERE
      NAME=ora.LISTENER.lsnr
      TYPE=ora.listener.type
      LAST_SERVER=server123
      STATE=ONLINE
      TARGET=ONLINE
      LAST_RESTART=1679945255
      LAST_STATE_CHANGE=1679945255

      BREAK_HERE
      NAME=ora.LISTENER.lsnr
      TYPE=ora.listener.type
      ACL=owner:grid:rwx,pgrp:asmdba:r-x,other::r--,group:oinstall:r-x,user:grid:rwx
      ENABLED=1
      ENDPOINTS=TCP:1521

      Thanks,
      Pedro

      Delete
    3. Hi Pedro, this should have then been fixed in the latest version; please try the latest version and if it does not work please let me know

      Delete
  30. Lovely script!!!! works like charm. Thanks a lot for sharing mate!

    ReplyDelete
  31. How to order the below OH set (here it shows 19c,12c,11g), seems it is randomly picking. But sometimes in order like 19c,11g,12c, due too that if I use rac-mon.sh then it is showing DIFFERENCE. Please help me.

    ORACLE_HOME references listed in the Version column ("''" means "same as above")

    1 : /apps/oh/19c/19.8.0.0 oracle oinstall
    2 : /apps/oh/12c/12.1.0.2 '' ''
    3 : /apps/oh/11g/11.2.0.4 '' ''

    ReplyDelete
    Replies
    1. Hi,

      Thanks for your comment. I'll have a look and let you know. Do not hesitate to send me an email about it.

      Fred

      Delete
    2. Sorry it took me a while but it is now fixed ! please check in "what's new ?"

      Fred

      Delete
  32. nice script ,wish i had found it earlier , keep up the good work

    ReplyDelete
  33. currently, I am using the 19c RAC grid on the Exadata machine.
    I have deployed rac-status.sh script on one of the Exadata servers. when I ran the script, the DB status on the nodes column is not showing.
    Please suggest me.

    ReplyDelete
    Replies
    1. Hi,

      Can you please do:

      rac-status.sh -o fileforfred.txt

      And send me that file by email ?

      Thanks,

      Delete
  34. We have 100+ databases running in 16 nodes cluster (without exadata), rac-status.sh script doesn't show me count of databases which are primary db and standby db , pls let me know how I can get databases role count summary in this script.

    ReplyDelete
    Replies
    1. Do you mean the number of primary databases and the number of standby databases ?

      Delete
    2. Yes, I need count of primary and standby databases ,

      Delete
    3. Interesting idea, I add this to my todo list and see what I can do.

      Delete
  35. Hello,

    getting below error -


    awk: cmd. line:273: pdb[temppdb[1]][temppdb[2]] = PDB ;
    awk: cmd. line:273: ^ syntax error
    awk: cmd. line:273: pdb[temppdb[1]][temppdb[2]] = PDB ;
    awk: cmd. line:273: ^ syntax error
    awk: cmd. line:776: for (x in pdb[l_db]) {tempopdb[x]=x;} ;
    awk: cmd. line:776: ^ syntax error


    using it on exadata having different db versions from 11g to 19c

    ReplyDelete
    Replies
    1. This syntax which I have used to code the future new GI 21c PDB feature is implemented in awk 4 which has been released in 2012 (not 2021, 2012, 9 years ago) but OEL 6 ships awk 3 released in ... 2009 :/

      As RH6/OEL6 should be dying soon, I was not very keen on remodifying all the code ready for GI 21+c so instead I created a RH6/OEL6 compatible version without this 21c PDB feature: https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-status-rh6.sh

      Delete
    2. Thanks for the tip Fred.
      working now.
      this is a awesome code.

      Delete
  36. How do i download the rac-status.sh script?

    ReplyDelete
    Replies
    1. Check the "Where to download ?" section in this post

      You also have a "download" link in the "Scripts" menu on top of each page.

      Delete
  37. Its getting error whenwe running

    Cluster *****7 is a X7-2 Elastic Rack HC 10TB (upgrade state is NORMAL)

    awk: cmd. line:278: pdb[temppdb[1]][temppdb[2]] = PDB ;
    awk: cmd. line:278: ^ syntax error
    awk: cmd. line:278: pdb[temppdb[1]][temppdb[2]] = PDB ;
    awk: cmd. line:278: ^ syntax error
    awk: cmd. line:782: for (x in pdb[l_db]) {tempopdb[x]=x;} ;
    awk: cmd. line:782: ^ syntax error

    ReplyDelete
    Replies
    1. Hi,

      This is documented in the October 20th 2021 What's new; please use https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-status-rh6.sh

      Delete
  38. Hello,

    Congratulations! Very cool your job and your scripts!

    I tried to implement this script to send the result by email every day through crontab, but it loses all formatting. Do you have any recommendations for this situation?

    Cluster [1;36mCluster-c1 [m is a [1;36mX8M-2 Eighth Elastic Rack HC 14TB [m (upgrade state is [1;32mNORMAL [m)

    [1;37m Listener [0m| [1;37m Port [0m| [1;37m dbadm01 [0m| [1;37m dbadm02 [0m| [1;37m Type [0m|
    [1;37m---------------------------------------------------------------------------------------- [0m
    [1;37m ASMNET1LSNR_ASM [0m| [1;37m TCP:1525 [0m! [1;32m Online [0m| [1;32m Online [0m| [1;37m Listener [0m|
    [1;37m LISTENER [0m| [1;37m TCP:1521 [0m! [1;32m Online [0m| [1;32m Online [0m| [1;37m Listener [0m|
    [1;37m LISTENER_SCAN1 [0m| [1;37m TCP:1521 [0m! [1;34m - [0m| [1;32m Online [0m| [1;37m SCAN [0m|
    [1;37m LISTENER_SCAN2 [0m| [1;37m TCP:1521 [0m! [1;32m Online [0m| [1;34m - [0m| [1;37m SCAN [0m|
    [1;37m LISTENER_SCAN3 [0m| [1;37m TCP:1521 [0m! [1;32m Online [0m| [1;34m - [0m| [1;37m SCAN [0m|
    [1;37m---------------------------------------------------------------------------------------- [0m

    [1;37m DB [0m| [1;37m Version [0m| [1;37m dbadm01 [0m| [1;37m dbadm02 [0m| [1;37m DB Type [0m|
    [1;37m---------------------------------------------------------------------------------------- [0m
    [1;37mprod1 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod2 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod3 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod4 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod5 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod6 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod7 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37mprod8 [0m! [1;37m 19.0.0.0 [0m [1;37m (1) [0m| [1;32m Open [0m| [1;32m Open [0m| [1;37m RAC (P) [0m|
    [1;37m---------------------------------------------------------------------------------------- [0m
    ORACLE_HOME references listed in the Version column

    1 : /u01/app/oracle/product/19.0.0.0/dbhome_1 oracle oinstall



    ReplyDelete
    Replies
    1. Use the -u option which will remove the shell color codes which are not understood by mail clients.

      Delete
  39. Hi, This is very useful script. I want to send the output the status in email. Incase if any failures it has to send the email to DBA team. Let me know how to add the email option for the failure messages.

    ReplyDelete
    Replies
    1. Please have a look at https://unknowndba.blogspot.com/2019/01/rac-mon-sh-efficient-way-to-monitor-gi-12c-18c.html

      Delete
  40. the error says :

    Cluster cluster-bidb is a X7-2 Eighth Rack HC 10TB (upgrade state is NORMAL)

    awk: cmd. line:279: pdb[temppdb[1]][temppdb[2]] = PDB ;
    awk: cmd. line:279: ^ syntax error
    awk: cmd. line:279: pdb[temppdb[1]][temppdb[2]] = PDB ;
    awk: cmd. line:279: ^ syntax error
    awk: cmd. line:789: for (x in pdb[l_db]) {tempopdb[x]=x;} ;
    awk: cmd. line:789: ^ syntax error

    ReplyDelete
    Replies
    1. This is because you run an old RH6, please use this version instead: https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-status-rh6.sh

      Delete
  41. Hello Fred, is the script working with Exadata 9? I don't see the services or the databases as up or down...

    ReplyDelete
    Replies
    1. I cheked with other users and yes, no worries on X9. For issues, please check https://unknowndba.blogspot.com/2022/01/rac-statussh-faq.html and "What information should I provide for you to investigate a bug ?"

      Delete
    2. Yes but the thing is I can't send you any files... :(

      Delete
  42. What does that "STATUS and TARGET are different" indicate. I see that all the clusterware resource are online in that nodes if i check using crsctl but it is marked as red when i check using your script

    It is not displaying the status of the clusterware resources on that particular node

    ReplyDelete
    Replies
    1. TARGET is the status a resource is supposed to have and STATUS is the status your resource is at. If for example you want an instance to be down, TARGET will be offline then if STATUS is online for this instance, something wrong has happened. Usually it is more that the target is ONLINE and the instance has not started then it appears in red.

      I am not sure to understand your second point, could you please send me a screenshot by email and the trace file required for debug as described in the FAQ.

      Delete
    2. I will share the details detail over linkedin. Thanks a lot for your quick response fred

      Delete
  43. HI Fred,
    The script looks to be very useful.
    However it doesn't seems to be working as expected in my exadata environment.
    My grid and db version is 19c and the os version is oracle linux 7

    What does that "STATUS and TARGET are different" indicate. I have a 4 node rac . It highlights all the cluster resource as red on the 1st node as red even though all the resource in the node 1 looks to online which i check from crsctl

    ReplyDelete
  44. Hi Fred,

    The script doesn't show the status of the database in my case, do you know why ?

    [root@ra-db-01 ~]# /u01/app/oracle/scripts/rac_status.sh

    Cluster ra-db-prod-cluster (upgrade state is NORMAL)

    Listener | Port | db-01 | db-02 | Type |
    ------------------------------------------------------------------------------------------
    ASMNET1LSNR_ASM| TCP:1525 | - | - | Listener |
    LISTENER | TCP:1521 | - | - | Listener |
    LISTENER_SCAN1 | TCP:1521 | - | - | SCAN |
    LISTENER_SCAN2 | TCP:1521 | - | - | SCAN |
    LISTENER_SCAN3 | TCP:1521 | - | - | SCAN |
    ------------------------------------------------------------------------------------------

    DB | Version | db-01 | db-02 | DB Type |
    ------------------------------------------------------------------------------------------
    npbl | 11.2.0 (1) | - | - | RAC (P) |
    nztrprod | 11.2.0 (1) | - | - | RAC (P) |
    prod11gr | 11.2.0 (1) | - | - | RAC (P) |
    ------------------------------------------------------------------------------------------
    ORACLE_HOME references listed in the Version column

    1 : /u01/app/oracle/product/11.2.0/db_1 oracle oinstall

    ReplyDelete
    Replies
    1. Please do:
      rac-status.sh -a -o fileforfred.txt

      and send me this file by email.

      Also please try rac-status.sh -L (you have hyphen in the names of your db nodes may be this is the issue)

      Delete
    2. Please re download the script, I fixed the problem

      Delete
  45. Hi Anonymous, when i executed rac_status.sh on solaris i get the following errors, can you help me to solve it?

    oracle@db01:~/RAC$ ./rac_status.sh -V

    The current version of rac_status.sh is 20230317.
    oracle@db01:~/RAC$ ./rac_status.sh
    ./rac_status.sh: line 432: rev: command not found
    ./rac_status.sh: line 432: rev: command not found
    sed: illegal option -- E

    Cluster raccluster is a SuperCluster 1 M7 Elastic Rack X6_HC 8TB 1HCA (upgrade state is NORMAL)

    Listener | Port | db01 | db02 | Type |
    ------------------------------------------------------------------------------------------------
    ASMNET1LSNR_ASM| TCP:1525 | - | - | Listener |
    LISTENER | TCP:1521 | - | - | Listener |
    LISTENER_IB | TCP:1522 | - | - | Listener |
    LISTENER_SCAN1 | TCP:1521 | - | - | SCAN |
    LISTENER_SCAN2 | TCP:1521 | - | - | SCAN |
    LISTENER_SCAN3 | TCP:1521 | - | - | SCAN |
    ------------------------------------------------------------------------------------------------

    DB | Version | db01 | db02 | DB Type |
    ------------------------------------------------------------------------------------------------
    db1 | 19.3.0.0 (3) | - | - | RAC (P) |
    db2 | 19.3.0.0 (3) | - | - | RAC (P) |
    db3 | (4) | - | - | RAC (P) |
    db4 | (4) | - | - | RAC (P) |
    db5 | 11.2.0.4 (1) | - | - |RACOneNode (P)|
    db6 | (2) | - | - |RACOneNode (P)|
    ------------------------------------------------------------------------------------------------
    ORACLE_HOME references listed in the Version column ("''" means "same as above")

    1 : /u01/oracle/VCP/11.2 oravcp oinstall
    2 : /u02/oracle/VCP/19c oravcpp ''
    3 : /u03/app/oracle/product/19.0/dbhome_1 oracle ''
    4 : /u04/oracle/PROD/19 oraprod ''

    ReplyDelete
  46. Hello,

    First I'd like to thank you for the work and efforts you put into this script. I've ben using it for ages and find it really usefull.

    I've recently been faced with a display problem. Problem is "related" to line 413 when LONG_NAMES=NO (default value). Seems like this is happening when moving from OL7 to OL8 but not sure.
    I tried to understand the code logic but I must be missing something cause I don't get your intent!

    When no -C cluster name is given, « $(olsnodes | head -1 | sed s'/,.*$//g' | tr '[:upper:]' '[:lower:]') == *"${P_CLUSTER_L}"* » is true (because *"${P_CLUSTER_L}"* then matches anything), and when running the code at line 432 «  CLUSTER_NAME=$(olsnodes | head -1 | rev | sed -E 's/.*bd(.)/\1/' | rev) », the CLUSTER_NAME becomes the first computer name output by olsnodes.

    Infos:
    [root@srv-ora-prod-41 (oracle-prod:3) bin]# if [[ $(olsnodes | head -1 | sed s'/,.*$//g' | tr '[:upper:]' '[:lower:]') == *"${P_CLUSTER_L}"* ]] ; then echo VRAI; fi
    VRAI
    [root@srv-ora-prod-41 (oracle-prod:3) bin]# echo $P_CLUSTER_L

    [root@srv-ora-prod-41 (oracle-prod:3) bin]# olsnodes | head -1 | sed s'/,.*$//g' | tr '[:upper:]' '[:lower:]'
    srv-ora-prod-41

    And then no instance status is displayed for this first cluster node.
    Workaround found :
    * either set LONG_NAMES="YES".
    * or init P_CLUSTER=" "

    I don't think this is intended :)

    Can you check?

    Regards,
    Yoann Mainguy.

    ReplyDelete
    Replies
    1. Could you please send me by email the broken output?

      Thanks,

      Delete
    2. Hello,
      Just sent the sample output.
      Regards,
      Yoann Mainguy

      Delete
  47. Thanks again for this awesome script - been using it now for several years.

    One thing I can't seem to get to work is to only show the Services.

    With the following set

    #SHOW_DB="YES" # Databases
    SHOW_DB="NO"
    SHOW_PDB="YES" # PDBs
    #SHOW_PDB="NO"
    #SHOW_LSNR="YES" # Listeners
    SHOW_LSNR="NO"
    #SHOW_SVC="YES" # Services
    SHOW_SVC="NO"
    #SHOW_TECH="YES" # Tech (DGs, ONS, etc ...)
    SHOW_TECH="NO"

    If I run rac-status.sh -s I do not get any output.

    No matter what combination of those variables I set, I cant seem to just get a list of services, unless I combine it with showing the databases - is this by design?

    ReplyDelete
    Replies
    1. Hi, you cannot indeed only show the services, the databases will also show up. This is because the databases I formation have to be collected when showing the services as the services are linked to the databases. I should nevertheless be able to collect the required databases information but not show them; I’ll have a look into this.
      Fred

      Delete
  48. That would be great! Thanks Fred

    ReplyDelete
    Replies
    1. Hi,

      Could you please test this one ? https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-status_20220220.sh

      Let me know,

      Fred

      Delete
    2. Thanks Fred - works great for me!

      Delete
  49. Hello, please, what can i do to see the colors?, i use secureCRT as ssh client, the value of the variable WITH_COLORS is "YES", but i only see white and black

    ReplyDelete
    Replies
    1. Hi,

      I havent used secure CRT since last century but this might help: https://www.vandyke.com/support/tips/colorconfig.html

      Regards,

      Delete

Load 2 billion rows with SQL LOADER

I worked on few Extract data / transform data (nor not) / load data into an Oracle database projects during my professional life and SQL ...