Twitter

rac-on_all_db.sh : easily execute a query on all the databases


Any DBA has already faced at least once this kind of Friday 5pm manager's call :

- Manager  : "hey, could you please give me the value of the parameter XXXX on all the databases running on the production / dev cluster ?"
- DBA         : "well yes... now ?"
- Manager  : "yes, it is very urgent, blabla . . ."

Whatever the reason this kind of demand suddenly becomes very urgent on a Friday afternoon and whatever is the parameter, you want to get the information as quickly as possible to head on a well deserved week-end.

Assuming you use rac-status.sh, you can then quickly know where all the databases instances are running like shown on the below output :


We can see on the above output that the db01 database has an instance opened on the node 3, the db02, db03 and db04 databases have an instance opened on the node 1, db05 has an instance available on node 3, etc...

A way to proceed to achieve the manager's request would then be to :
  1. ssh to the node where an instance is running 
  2. set the correct environment (. oraenv)
  3. connect as sqlplus
  4. execute the SQL to check the parameter value (select name, value from v$parameter . . .)
  5. get the database name and the parameter value and paste it in a nice output
And these 5 steps have to be done for each database which can be quite painful if you have many databases running which is most likely the case for any RAC / Exadata environment.


This is to achieve this that I wrote the rac-on_all_db.sh script. Indeed, it will do all the above steps for you and make a nice output ready to copy and paste or screenshot :



Nice and easy, isn't it ?

And to customize the SQL you want to execute against all the databases, simply edit the script and insert the SQL you want in this section :


Et voila !


There're few small prerequisites to be able to run this script :
  • oraenv should be working on each database node 
  • The script should be executed as the owner of the databases (oracle)
  • The user that executes the script must have his ssh key deployed to all the other database nodes (this is a prerequisite for a RAC installation then it should already been done on your configuration)


Feel free to download the code and I hope you'll be enjoying this script as much as I do !



35 comments:

  1. It's excellent and very helpful

    ReplyDelete
  2. Hello,

    rac_all_db.sh script is not showing any output from the databases. Please see below and let me know how to rectify the same.


    ./rac_all_db.sh


    Query Result on ccbprd1@mpimpdb01
    ----------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------


    Query Result on ccbprod1@mpimpdb01
    ----------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------


    Query Result on devaim2@mpimpdb02
    ----------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------

    ReplyDelete
    Replies
    1. Hi,

      Is oraenv working on your server ? Can you execute the SQL you insert in the script manually against one instance and see the output ?

      Thanks,

      Delete
  3. Hi Fred,

    I am getting blank lined when executing the same script on the 12cr2. SQL query is running fine at the db level.

    ReplyDelete
    Replies
    1. Could you please send me more details by email ?

      Thanks,

      Delete
  4. Hi Fred,
    I'm trying to use your script but I'm receiving only the server banner.
    I'm need to create a restore point in all instances and then check if they have been successfully created.

    ReplyDelete
  5. We have a dozen or more databases and for some reason, this script cannot log into any of them.

    Query Result on rrta7@dleccp0201vm07
    ----------------------------------------------------------------------------------------------------------------------
    | ERROR at line 1:
    | ORA-01034: ORACLE not available
    | Process ID: 0
    | Session ID: 0 Serial number: 0
    |
    |
    | select instance_name, host_name, version, sysdate from v$instance
    | *
    | ERROR at line 1:
    | ORA-01034: ORACLE not available
    | Process ID: 0
    | Session ID: 0 Serial number: 0
    |
    |
    | ORA-01034: ORACLE not available
    | Process ID: 0
    | Session ID: 0 Serial number: 0
    |
    |
    | ORA-01034: ORACLE not available
    | Process ID: 0
    | Session ID: 0 Serial number: 0
    |
    |
    ----------------------------------------------------------------------------------------------------------------------


    Query Result on samqa1ts7@dleccp0201vm07
    ----------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------
    [oracle@dleccp0202vm07 ~]$

    ReplyDelete
    Replies
    1. It looks like oraenv is unable to set the environment. Could you please check that your oraenv is working ?

      Delete
  6. It work in that it sets the $OH and SID, bit it doesnt set the SID correctly. On Exadata Cloud at Customer, it sets the SID to the db name, not the instance name. I have yet to find any way to parse actual running SID name from ps -ef and correlate it to /etc/oratab and trunk the instance number off correctly.

    ReplyDelete
    Replies
    1. You can adapt the code to fit your environment here:

      **********************************************************
      INSTANCE=${DB}`echo "${SERVER: -1}"`
      . oraenv <<< ${DB} > /dev/null 2>&1
      export ORACLE_SID=${INSTANCE}
      **********************************************************

      Delete
    2. I fixed up my /etc/oratab, but now it complains it can't find crsctl. I tried as root, oracle and grid.

      [root@pleccp0103vm01 tools]# ./rac-on_all_db.sh

      ./rac-on_all_db.sh: line 124: crsctl: command not found
      [root@pleccp0103vm01 tools]# pwd
      /dc2_common/tools
      [root@pleccp0103vm01 tools]# su - oracle
      [oracle@pleccp0103vm01 ~]$ /dc2_common/tools/rac-on_all_db.sh

      /dc2_common/tools/rac-on_all_db.sh: line 124: crsctl: command not found
      [oracle@pleccp0103vm01 ~]$ exit
      logout
      [root@pleccp0103vm01 tools]# su - grid
      [grid@pleccp0103vm01 ~]$ /dc2_common/tools/rac-on_all_db.sh

      /dc2_common/tools/rac-on_all_db.sh: line 124: crsctl: command not found
      [grid@pleccp0103vm01 ~]$ vi /dc2_common/tools/rac-on_all_db.sh
      [grid@pleccp0103vm01 ~]$

      Delete
    3. crsctl is in ASM env; is your oraenv ASM working ?

      If not, you may suffer from https://unknowndba.blogspot.com/2019/01/lost-entries-in-oratab-after-gi-122.html

      Delete
  7. Our ASM env is owned by the grid user. But I often use it as root also. See below:

    [root@dleccp0201vm08 ~]# . oraenv
    ORACLE_SID = [root] ? +ASM1
    The Oracle base has been set to /u01/app/grid
    [root@dleccp0201vm08 ~]# crsctl check crs <============as root it's fine
    CRS-4638: Oracle High Availability Services is online
    CRS-4537: Cluster Ready Services is online
    CRS-4529: Cluster Synchronization Services is online
    CRS-4533: Event Manager is online
    [root@dleccp0201vm08 ~]# id
    uid=0(root) gid=0(root) groups=0(root)
    [root@dleccp0201vm08 ~]# su - grid
    [grid@dleccp0201vm08 ~]$ crsctl check crs <== As grid it's fine.
    CRS-4638: Oracle High Availability Services is online
    CRS-4537: Cluster Ready Services is online
    CRS-4529: Cluster Synchronization Services is online
    CRS-4533: Event Manager is online
    [grid@dleccp0201vm08 ~]$ env|grep ORA
    ORACLE_SID=+ASM1
    ORACLE_HOME=/u01/app/18.1.0.0/grid
    [grid@dleccp0201vm08 ~]$

    [root@dleccp0201vm08 ~]# su - oracle
    [oracle@dleccp0201vm08 ~]$ . oraenv
    ORACLE_SID = [oracle] ? +ASM1
    ORACLE_BASE environment variable is not being set since this
    information is not available for the current user ID oracle.
    You can set ORACLE_BASE manually if it is required.
    [oracle@dleccp0201vm08 ~]$ crsctl check crs <===============Even as oracle, it works fine.
    CRS-4638: Oracle High Availability Services is online
    CRS-4537: Cluster Ready Services is online
    CRS-4529: Cluster Synchronization Services is online
    CRS-4533: Event Manager is online
    [oracle@dleccp0201vm08 ~]$

    ReplyDelete
    Replies
    1. Could you execute bash -xv rac-on-all-db.sh and send me the output by email ?

      Delete
  8. I sent you the output of execute bash -xv rac-on-all-db.sh

    ReplyDelete
  9. Hello, is this shell working?

    My oraenv is working well.

    ReplyDelete
    Replies
    1. I haven't used it for a while but as far as I know yes it is working.

      Delete
    2. I am getting the same as this guy, I copied what he posted...

      ./rac_all_db.sh


      Query Result on ccbprd1@mpimpdb01
      ----------------------------------------------------------------------------------------------------------------------
      ----------------------------------------------------------------------------------------------------------------------


      Query Result on ccbprod1@mpimpdb01
      ----------------------------------------------------------------------------------------------------------------------
      ----------------------------------------------------------------------------------------------------------------------


      Query Result on devaim2@mpimpdb02
      ----------------------------------------------------------------------------------------------------------------------
      ----------------------------------------------------------------------------------------------------------------------

      Delete
    3. Can you send me by email the code you execute ?

      Delete
    4. Of course, but I don't get your question.

      The code I execute is the one in your github

      https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-on_all_db.sh

      Maybe you meant something else?

      Delete
    5. Which SQL are you trying to execute ? The one you put in here:

      --------------------------------
      -- Query customization --
      --------------------------------

      set echo off ;
      set term off ;
      set wrap on ;
      col name for a50 ;
      col value for a60 ;
      col host_name for a26 ;
      col instance_name for a18 ;
      set pages 5000 ;
      alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS' ;
      select instance_name, host_name, version, sysdate from v\\\$instance ;
      -- select name, value from v\\\$parameter where name like '%exafusion%' ;
      select name, value from v\\\$parameter order by name ;

      --------------------------------
      -- End of query customization --
      --------------------------------


      Can you try with a simple "show parameter sga" ??
      Could you send me by email a bash -xv rac-on_all_db.sh output ?

      Thannks

      Delete
  10. I just tried the default SQLs you have in there, I did not change it.

    I'll send you the email tomorrow morning :)

    ReplyDelete
  11. Hi Fred, I'm back. :-D

    I'm still looking for a way to execute your rac-on_all_db script for each instance on any given node. The problem with you script as you may well be aware is that it doesn't know how to get the right SID.

    I have an idea, and maybe you can code it. If you run this as is:

    crsctl stat res -v -w "TYPE = ora.database.type" |grep ID|grep -v CARDINALITY_ID

    It will print out a list, which you use the sub() functions on to get the db name from the ID=ora.xxxxxx.db X X

    The first X is the instance number, so the result of the above command might return a result like this:

    ID=ora.proddb1 1 1

    Then parse the above ID into sub(("^ora.", "", $2) and sub(".db$" , "", $2) and append the first X above. In this case the instance number is 1.

    Make sense?

    Cheers,
    --Rich

    ReplyDelete
  12. I know this can be done. But I'm not sure exactly how.

    Take this piece of code:

    for X in `crsctl stat res -v -w "TYPE = ora.database.type" |\
    awk ' BEGIN {FS="="}
    {
    if (($1 ~ /^NAME/) && ($0 ~ //)) # <== Change this from NAME to the ID string which also contains the instance number and concatenate them.
    {
    sub("^ora.", "", $2) ;
    sub(".db$" , "", $2) ;
    DB=$2 ;
    while(getline)
    {
    if (($1 ~ /STATE/) && ($2 ~/ONLINE/))
    {
    gsub (".*on ", "", $2) ;
    print DB"|"$2 ;
    next ;
    }
    }
    }
    }'`
    do
    DB=`echo $X | awk -F "|" '{print $1}'`
    SERVER=`echo $X | awk -F "|" '{print $2}'`
    INSTANCE=${DB}`echo "${SERVER: -1}"`
    done

    Run the above code as is and you will see what I mean.

    ReplyDelete
  13. When i run the rac-on_all_db.sh script i also get no results back.
    I also see the below error , would you please help

    tput: No value for $TERM and no -T specified
    /tmp/check_dbs.sh1306: line 18: bc: command not found

    ReplyDelete
    Replies
    1. It seems that the bc command is not installed on your system. What system are you using ?

      Delete
  14. im running the below :
    NAME="Oracle Linux Server"
    VERSION="7.8"
    ID="ol"
    ID_LIKE="fedora"
    VARIANT="Server"
    VARIANT_ID="server"
    VERSION_ID="7.8"
    PRETTY_NAME="Oracle Linux Server 7.8"
    ANSI_COLOR="0;31"
    CPE_NAME="cpe:/o:oracle:linux:7:8:server"
    HOME_URL="https://linux.oracle.com/"
    BUG_REPORT_URL="https://bugzilla.oracle.com/"

    ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
    ORACLE_BUGZILLA_PRODUCT_VERSION=7.8
    ORACLE_SUPPORT_PRODUCT="Oracle Linux"
    ORACLE_SUPPORT_PRODUCT_VERSION=7.8

    ReplyDelete
  15. ~/local/scripts/yal > sudo yum install bc
    Loaded plugins: package_upload, product-id, search-disabled-repos, subscription-manager
    SUN_ORACLE_Flexera_Flexera | 2.1 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Katello | 2.1 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Katello-agent | 2.1 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7 | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_oVirt_4_3 | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_Addons | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_Gluster_6 | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_KVM_Utilities | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_Optional_Latest | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_Software_Collection_3_0 | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_UEK_Release_3 | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_UEK_Release_4 | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Oracle_Linux_7_x86_64_oVirt_4_3_Extra | 1.8 kB 00:00:00
    SUN_ORACLE_Oracle_Enterprise_Linux_7_Puppetlabs | 1.8 kB 00:00:00
    Package bc-1.06.95-13.el7.x86_64 already installed and latest version
    Nothing to do

    the package installed already

    ReplyDelete
    Replies
    1. Is bc in your path ? Error says bc cannot be found: line 18: bc: command not found

      Delete
    2. Hi,

      I have removed the use of bc which was not necessary.

      I will try to fix the instance issue reported by Rich in the coming weeks.

      Regards,

      Delete
  16. Its getting blank when we runing the script


    Query Result on hostname1
    ----------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------


    Query Result on hostname2
    ----------------------------------------------------------------------------------------------------------------------
    ^C

    Query Result on hostname3
    ----------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------

    ReplyDelete
    Replies
    1. Is your oratab env correctly set ?
      What query do you try to run ?

      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 ...