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 !



16 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

Exadata: ILOM hostname change

I am not sure about the root cause of this one but it was very weird finding that an ILOM had a wrong hostname (yes, wrong hostname) -- belo...