Ever had the need to pass an OS level parameter to an Oracle SQL script? I have and after some work I was able to make it to happen.
The scenario here is to gather the total amount of diskspace in the ASM diskgroups and then load it into a database. The biggest challenge is that ASM is not easy to connect to remotely…even with the (UR=A) added to the tnsnames.ora string. Especially when you have 15 environments that you’re trying to report on in one place.
This is how I was able to facilitate this need. This is Oracle 11gR2 hosted on AIX in a two node RAC.
The first challenge is to obtain the information from ASM on the database server. This is only needed on one node of the cluster.
. /home/oracle/.profile export ORACLE_HOME=$GRID_HOME export ORACLE_SID=+ASM1 sqlplus -s "/ as sysdba" < /tmp/asm_total_space.log SET HEADING off SELECT to_char(sum(total_mb)) FROM v\$asm_diskgroup ORDER BY name / exit; EOF
Now that I’ve gathered the information I need from ASM I will need to clean it up some, as the process creates an extra row at the top of the output file for some reason.
cat /tmp/asm_total_space.log | head -2 | tail -1 > /tmp/asm_total_space_clean.log
Now that the OS file is clean I’ll go ahead and read the value from within it and pass the value into a SQL script to load into Oracle via the $ASM_SIZE parameter.
. /home/oracle/.profile export ASM_SIZE=`cat /exports/ASM/asm_total_space_clean.log` sqlplus username/password@asm_total_space.sql $ASM_SIZE
Here are the details of the sql script being called. Note two items here.
- The substitution variable &1 is being used to pass the OS parameter $ASM_SIZE into.
- The datatype of size_mb is number and not a char.
set verify off delete from schema.table where environment = '11g Production'; commit; insert into schema.table (size_mb, environment) values ('&1','11g Production'); commit; exit;
**Note that some of the sensitive information has been replaced with simple things such as table, schema, username, and password. This blog post is written with the assumption that you can adapt this to your needs.