오라클 Alert Log에 에러 발생시 SMS로 전송하는 쉘 스크립트

아래는 장애발생시 오라클의 alert 로그에 “ORA-“와 같이 쌓이는것을 착안하여 만든 프로그램이다.

#! /bin/ksh
export ORACLE_HOME=/rtd_orahome
export ORACLE_SID=XXXXX
export ORACLE_OWNER=AAAAA
export ORAWEB_HOME=$ORACLE_HOME/ows/3.0
export ORAWEB_SITE=web
export LANG=korean
export NLS_LANG=American_America.KO16KSC5601
export ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data
export PATH=$PATH:.:/opt/SUNWspro/bin:/usr/ccs/bin:/usr/bin:/usr/ucb:/etc:/usr/sbin:/usr/openwin/bin:$ORACLE_HOME/bin:$ORAWEB_HOME/bin:.
export LD_LIBRARY_PATH=/opt/SUNWspro/lib:/usr/lib:/usr/ccs/lib:/usr/openwin/lib:/usr/ucblib:$ORACLE_HOME/lib

SMS_CLIENT=/rtd_home/monitor/bin/SmsSender 
CONFIG_FILE=/rtd_home/monitor/cfg/config.ini
HOST_NAME=`hostname`

# 로그의 라인수 저장파일
LOG_CNT=/rtd_home/monitor/temp/alertlog_line_cnt.txt

touch $LOG_CNT
# 이전에 기록한 라인수를 읽는다
cat $LOG_CNT | read intPrevLineCnt

# 전체라인수 카운트
intTotLineCnt=$(cat /rtd_orahome/admin/RTDORA7/bdump/alert_RTDORA7.log | wc -l)

# Cat할 라인수 지정
let LoopCnt=intTotLineCnt-intPrevLineCnt 

cat /rtd_orahome/admin/RTDORA7/bdump/alert_RTDORA7.log  | tail -$LoopCnt | grep ORA- | while read ORAMSG
do
	grep -w "ORACLE_NOTIFY_PHONE_NUMBER" $CONFIG_FILE | while read TEMP NOTIFY_PHONE_NUMBER
	do
		$SMS_CLIENT 'Moramcnt' $NOTIFY_PHONE_NUMBER 021234567 "$HOST_NAME $ORAMSG"	
		sleep 1
	done
	print `date '+%Y-%m-%d %T'` "$HOST_NAME $ORAMSG" >> /rtd_home/monitor/logs/check_alertlog_err.log

done
rm -f $LOG_CNT
print $intTotLineCnt >> $LOG_CNT
exit 0

위의 쉘 프로그램은 Config.ini와 SMSSender라는 프로그램이 필요하다.
Config.ini의 파일의 내용은 다음과 같이 sms를 수신받을 대상이 기록되어 있다.

[ORACLE]
ORACLE_NOTIFY_PHONE_NUMBER		0101234567

또한 SMSSender는 오라클 Pro*C로 간단한게 구현되어 있다.

#include 
#include 
#include 
#include 


#define	TRUE		1
#define	FALSE		0
#define	CONSOLE_MODE	0

int Connection( char *, char *, char *);
int Commit( void);
int RollBack( void);
int Trim(char *);
int SendSMS( char *, char *, char *, char *);

int Connection( char *szUserId, char *szPasswd, char *szConnection)
{
EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR vcConnection[21];
    VARCHAR vcUserId[21];
	VARCHAR vcPasswd[21];
EXEC SQL END DECLARE SECTION;

	strcpy( (char *)vcUserId.arr, szUserId);
	vcUserId.len	= strlen( szUserId);

	strcpy( (char *)vcPasswd.arr, szPasswd);
	vcPasswd.len	= strlen( szPasswd);

	/* If Connection String Is Not Empty */ 
	if (szConnection[ 0] != NULL)
	{
		strcpy( (char *)vcConnection.arr, szConnection);
		vcConnection.len	= strlen( szConnection);
	}
	else
	{
		vcConnection.arr[0] = '\0';
		vcConnection.len	= 0;
	}

	
	/* Connection */
	EXEC SQL CONNECT :vcUserId IDENTIFIED BY :vcPasswd USING :vcConnection;
	if( sqlca.sqlcode != 0)
	{
#ifdef CONSOLE_MODE
        printf("Oracle Connect Fail : %s\n", sqlca.sqlerrm.sqlerrmc);
#endif
		return FALSE;
	}

#ifdef CONSOLE_MODE
    printf("Oracle Connected.\n");
#endif
	return TRUE;
}


int Commit( void)
{

EXEC SQL COMMIT WORK RELEASE;

  	if (sqlca.sqlcode != 0)
	{
		/* ORA-1034 : ORACLE not available */
		if (sqlca.sqlcode == 1034) return FALSE;
#ifdef XCONSOLE_MODE
		printf("Oracle Commit Fail : %s\n", sqlca.sqlerrm.sqlerrmc);
#endif
		return FALSE;
    }
#ifdef CONSOLE_MODE
	printf("Oracle Commit Work Relese, Oracle DisConnected.\n");
#endif
    return TRUE;
}


int RollBack( void)
{
	EXEC SQL ROLLBACK WORK;
	if (sqlca.sqlcode != 0) 
	{
		/* ORA-1034 : ORACLE not available */
		if (sqlca.sqlcode==1034) return FALSE;
#ifdef CONSOLE_MODE
		printf("Oracle Rollback Fail : %s\n", sqlca.sqlerrm.sqlerrmc);
#endif
		return FALSE;
	}
	return TRUE;
}

int Trim(char *szText)
{
    int iCnt, iLength = strlen(szText);

    for (iCnt = iLength-1; iCnt >= 0; iCnt--)
	{
        if (szText[iCnt]==' ' || szText[iCnt]=='\n' || szText[iCnt]=='\r')
		{
            szText[iCnt] = '\0';
            iLength--;
        }
		else
			break;
    }
    return iLength;
}


/*==================================================================================
 * SMS 전송
 ===================================================================================*/
int SendSMS( char *szTranId, char *szTransPhone, char *szCallBackNo, char* szMessage)
{

EXEC SQL BEGIN	DECLARE	SECTION;
	VARCHAR		vcTranId[21];
	VARCHAR 	vcTransPhone[16];
	VARCHAR		vcTranCallBack[16];
	VARCHAR		vcMessage[256];
EXEC SQL END	DECLARE	SECTION;

	memset( &vcTranId,		0, sizeof(vcTransPhone));
	memset( &vcTransPhone,	0, sizeof(vcTransPhone));
	memset( &vcTranCallBack, 0, sizeof(vcTranCallBack));
	memset( &vcMessage,		0, sizeof(vcMessage));


	strncpy(vcTranId.arr, szTranId, 20);
	vcTranId.len	= Trim( (char *)vcTranId.arr);
	vcTranId.arr[ vcTranId.len] = '\0';


	strncpy(vcTransPhone.arr, szTransPhone, 15);
	vcTransPhone.len	= Trim( (char *)vcTransPhone.arr);
	vcTransPhone.arr[ vcTransPhone.len] = '\0';

	strncpy(vcTranCallBack.arr, szCallBackNo, 15);
	vcTranCallBack.len	= Trim( (char *)vcTranCallBack.arr);
	vcTranCallBack.arr[ vcTranCallBack.len] = '\0';

	strncpy(vcMessage.arr, szMessage, 255);
	vcMessage.len	= Trim( (char *)vcMessage.arr);
	vcMessage.arr[ vcMessage.len] = '\0';

#ifdef CONSOLE_MODE
	printf("SMS szTranId...%s|\n", vcTranId.arr);
	printf("SMS szTransPhone...%s\n", vcTransPhone.arr);
	printf("SMS szCallBackNo...%s\n", vcTranCallBack.arr);
	printf("SMS szMessage...%s\n", vcMessage.arr);
#endif	

EXEC SQL INSERT INTO
		em_tran(tran_pr, tran_id, tran_phone, tran_callback, tran_status, tran_date, tran_msg, tran_type)
		VALUES(EM_TRAN_PR.NEXTVAL, :vcTranId, :vcTransPhone, :vcTranCallBack, '1', SYSDATE, :vcMessage, '1');

	if (sqlca.sqlcode == 0)
	{
		if (!Commit())
		{	
			RollBack();
			return FALSE;
		}
#ifdef CONSOLE_MODE
    printf("SMS Sent...\n");
#endif	

		return TRUE;
	}
	else
		return FALSE;
}



int main(int argc, char* argv[])
{
	if( !Connection("오라클계정", "오라클패스워드", "오라클SID")) return -1;
	SendSMS( argv[ 1], argv[ 2], argv[ 3], argv[ 4]);
}