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 :
- ssh to the node where an instance is running
- set the correct environment (. oraenv)
- connect as sqlplus
- execute the SQL to check the parameter value (select name, value from v$parameter . . .)
- get the database name and the parameter value and paste it in a nice output
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 !
It's excellent and very helpful
ReplyDeleteHello,
ReplyDeleterac_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
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
Hi,
DeleteIs oraenv working on your server ? Can you execute the SQL you insert in the script manually against one instance and see the output ?
Thanks,
Hi Fred,
ReplyDeleteI am getting blank lined when executing the same script on the 12cr2. SQL query is running fine at the db level.
Could you please send me more details by email ?
DeleteThanks,
Hi Fred,
ReplyDeleteI'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.
Hi Diego,
DeleteIs oraenv working on your servers ?
We have a dozen or more databases and for some reason, this script cannot log into any of them.
ReplyDeleteQuery 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 ~]$
It looks like oraenv is unable to set the environment. Could you please check that your oraenv is working ?
DeleteIt 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.
ReplyDeleteYou can adapt the code to fit your environment here:
Delete**********************************************************
INSTANCE=${DB}`echo "${SERVER: -1}"`
. oraenv <<< ${DB} > /dev/null 2>&1
export ORACLE_SID=${INSTANCE}
**********************************************************
I fixed up my /etc/oratab, but now it complains it can't find crsctl. I tried as root, oracle and grid.
Delete[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 ~]$
crsctl is in ASM env; is your oraenv ASM working ?
DeleteIf not, you may suffer from https://unknowndba.blogspot.com/2019/01/lost-entries-in-oratab-after-gi-122.html
Our ASM env is owned by the grid user. But I often use it as root also. See below:
ReplyDelete[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 ~]$
Could you execute bash -xv rac-on-all-db.sh and send me the output by email ?
DeleteI sent you the output of execute bash -xv rac-on-all-db.sh
ReplyDeleteHello, is this shell working?
ReplyDeleteMy oraenv is working well.
I haven't used it for a while but as far as I know yes it is working.
DeleteI am getting the same as this guy, I copied what he posted...
Delete./rac_all_db.sh
Query Result on ccbprd1@mpimpdb01
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
Query Result on ccbprod1@mpimpdb01
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
Query Result on devaim2@mpimpdb02
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
Can you send me by email the code you execute ?
DeleteOf course, but I don't get your question.
DeleteThe 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?
Which SQL are you trying to execute ? The one you put in here:
Delete--------------------------------
-- 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
I just tried the default SQLs you have in there, I did not change it.
ReplyDeleteI'll send you the email tomorrow morning :)
Hi Fred, I'm back. :-D
ReplyDeleteI'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
Yes, this is the way to go :)
DeleteI know this can be done. But I'm not sure exactly how.
ReplyDeleteTake 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.
When i run the rac-on_all_db.sh script i also get no results back.
ReplyDeleteI 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
It seems that the bc command is not installed on your system. What system are you using ?
Deleteim running the below :
ReplyDeleteNAME="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
yum install bc
DeleteShould fix your issue then
~/local/scripts/yal > sudo yum install bc
ReplyDeleteLoaded 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
Is bc in your path ? Error says bc cannot be found: line 18: bc: command not found
DeleteHi,
DeleteI 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,
Its getting blank when we runing the script
ReplyDeleteQuery Result on hostname1
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
Query Result on hostname2
----------------------------------------------------------------------------------------------------------------------
^C
Query Result on hostname3
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
Is your oratab env correctly set ?
DeleteWhat query do you try to run ?