News

  1. Home
  2. All posts
All posts, Linux, RAC, Unix by Team Ajara

Script for DB Services and ASM Disk info

Automation Solution for DB Services and ASM Disk info.

Capturing Database Services details and Automatic storage Management (ASM) Disk info  has been automated to minimize startup and  post maintenance issues.100% manual efforts reduced

Challenge

  • ​Before every platform change or any Database change that involves bounce of the server or database ,DBAs take services details and ASM disk info in a text file manually.
  • If Any shared mount point is unmounted, difficult to find out what mount point(s) were existing as the info was not captured regularly.
  • If the platform team implements any change without notice(on non-prod) it is difficult to capture the already running services info and ASM disk info, some times DBAs used to miss this info.
  • While restarting the databases it used to take lot of time for trouble shooting if  disks are not mounted or correct listener is not started, or Network settings  are not restored as per  before Server bounce.

Solution

  • Manual procedure has been automated by using shell script and scheduled from crontab weekly twice and DB services info will be captured in text file with date.
  • SQL query will be run from the shell script if the Database files are stored in Automatic storage management..
  • Implemented on all platforms like Linux , HP , AIX and Sun Supercluster.

 

Results

  • Lot of time is being saved while troubleshooting if there are any issues.
  • 100% manual efforts reduced to collect data before DB/Server maintenance.
  • Post maintenance issues are minimized.

 

Script name: db_services_n_ASM.sh

Purpose: To capture db services and ASM details before every DB or Server maintenance

Parameters to be passed:None

Technology: Shell script ,and Oracle SQL

sub script name:  ASM_details.sql

Script Content:

  • cat db_services_n_ASM.sh
DT=`date +%d_%b_%Y_%H_%M`

LOG_FILE=$HOME/${ORACLE_SID}_services_${DT}.log

export DT

export LOG_FILE

date>>$LOG_FILE

uptime>>$LOG_FILE

ps -ef |grep pmon >>$LOG_FILE

ps -ef|grep tns >>$LOG_FILE

lsnrctl status >>$LOG_FILE

ifconfig -a >>$LOG_FILE

df -h >>$LOG_FILE

pwd >>$LOG_FILE

cd /dev

pwd >>$LOG_FILE

ls -ltr >>$LOG_FILE

cd

pwd >>$LOG_FILE

sudo su – grid <<EOF>>$LOG_FILE

ORAENV_ASK=NO

ORACLE_SID=+ASM1

export ORACLE_SID

. oraenv >/dev/null

DT=`date +%d_%b_%Y_%H_%M`

ASM_LOG=ASM_details_${DT}.log

crsctl status res -t

$ORACLE_HOME/bin/sqlplus -s  ‘/as sysasm’

spool ASM_details_`date +%d_%b_%Y_%H_%M`.log

@/export/home/grid/ASM_details.sql

spool off

EOF

 

Sub script /called script content:

cat ASM_details.sql

select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state,

dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

 

How to run manually :

Connect as oracle user and run

$ sh db_services_ASM.sh

Crontab entry:

10  06 * * 1,4 $HOME/db_services_ASM.sh  >/dev/null

 

Logfile location :

In the user HOME directory

 

Script on AIX platform:

DT=`date +%dd%mm%yy%HH%Mmin`

ORAENV_ASK=NO

ORACLE_SID=+ASM

export ORACLE_SID

. oraenv >/dev/null

LOG_FILE=$HOME/${SERVER}_services_${DT}.log

export DT

export LOG_FILE

date >>${LOG_FILE}

uptime>>${LOG_FILE}

ps -ef |grep pmon>>${LOG_FILE}

ps -ef|grep tns>>${LOG_FILE}

lsnrctl status >>${LOG_FILE}

ifconfig -a>>${LOG_FILE}

lsdev -C -c processor>>${LOG_FILE}

df -g>>${LOG_FILE}

cd /dev

pwd >>$LOG_FILE

ls -ltr>>${LOG_FILE}

cd

pwd >>$LOG_FILE

crsctl check has>>${LOG_FILE}

crsctl stat res -t>>${LOG_FILE}

$ORACLE_HOME/bin/sqlplus ‘/as sysasm’  <<EOF >>$LOG_FILE

select name,total_mb/1024,free_mb/1024 from v$\asm_diskgroup;

SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state,

dg.name AS diskgroup FROM V\$ASM_DISKGROUP dg, V\$ASM_DISK d WHERE dg.group_number = d.group_number;

EOF

 

 

See Also: