Мониторинг БД и приложений Гидры

Введение

Перед запуском АСР «Гидра» в промышленную эксплуатацию необходимо в обязательном порядке настроить мониторинг её работы.

Контроль должен отслеживать следующие группы параметров:

• Базовые параметры сервера АСР (load avg, использование CPU и RAM, I/O массива, количество процессов, свободное пространство на дисковом массиве).
• Показатели экземпляра БД (количество сессий, текущая нагрузка, состояние SGA и т.д.).
• Состояние заданий АСР «Гидра».
• Контроль количества нетарифицированных CDR и дату последней загрузки.
• Количество строк в жизненноважных таблицах EX_CALL_DATA_REC, EX_DATA_COLLECT, EX_TRAFFIC_COLLECT_C, SD_GOOD_MOVES.

Для мониторинга АСР «Гидра» можно использовать любую распространенную систему, которая позволяет получать данные с помощью скриптов, запускаемых из командной строки. Наиболее популярными системами мониторинга среди российских операторов связи являются бесплатные Zabbix и Nagios.

Для мониторинга показателей экземпляра БД можно использовать скрипты (раздел Oracle), разработанные для Zabbix.

Контроль выполнения заданий системы

Чтобы настроить контроль выполнения заданий АСР «Гидра», можно воспользоваться одним из специально разработанных скриптов (1) либо hydra_monitoring.sh

#!/bin/sh
 
. /etc/profile
 
rval=0
 
ORA_USER="AIS_NET"
ORA_PASS="mypass"
SQLPLUS_PATH="$ORACLE_HOME/bin/sqlplus"

if [ -n "$3" ]; then
       ORA_SID="$3"
       export ORACLE_SID=$ORA_SID
fi
 
sql=""
 
case $1 in
 
'job_state')
	if [ -n "$2" ]; then
		echo "
		SELECT to_char(N_JOB_STATE_ID, 'FM99999999999999990') FROM SS_V_JOBS WHERE N_JOB_ID=$2;
		" | ${SQLPLUS_PATH} -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} |
		awk '{  if ($1 == 2034) print "Running" 
			else if($1 == 1034) print "Waiting"
			else if($1 == 3034) print "Starting"
			else if($1 == 4034) print "Locked"
			else if($1 == 5034) print "Deleted"
			else if($1 == 6034) print "Error"
			else if($1 == 7034) print "Cant Start"
		}'
	else
		rval=1
		echo "No JOB_ID" >&2
	fi
	;;
'job_last_start')
	if [ -n "$2" ]; then
		sql="
		SELECT to_char((sysdate - D_LAST_START) * (86400), 'FM99999999999999990') FROM SS_V_JOBS WHERE N_JOB_ID=$2;
		"
	else
		rval=1
		echo "No JOB_ID" >&2
	fi
	;;
*)
	echo "Hydra monitoring tool"
	echo "usage:"
	echo "    $0 job_state <JOB_ID> [SID]  -- Check job status."
	echo "    $0 job_last_start <JOB_ID> [SID]  -- Check job last start date/time."
	rval=1
	exit $rval
	;;
esac
 
if [ -n "$sql" ]; then
       echo "$sql" | ${SQLPLUS_PATH} -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID}
fi
rval=$?

exit $rval
 

Различаются 7 состояний:

Не нормальными являются состояния, помеченные буквой Н. Исключение: задание, заблокированное принудительно.

Перечень заданий и их идентификаторы следует брать со страницы АСР Администрирование -> Задания - Назначенные задания.
Контроль рекомендуется вести по всем заданиям системы.

Контроль за сеансами выполнения заданий

Помимо мониторинга состояния заданий, описанного выше, важно следить за состоянием выполнения сеансов (запусков) задания. С помощью данного мониторинга возможно отслеживать не нормальные состояния завершения, а также длительное время выполнения заданий. Для получения информации по 10 последним сеансам задания следует использовать следующий SQL-запрос:

WITH JOB_SEANCES AS (
  SELECT N_JOB_STATUS,
         VC_JOB_STATUS,
         D_START,
         D_FINISH,
         ROW_NUMBER() OVER (ORDER BY D_START DESC) N_ROW
  FROM   SS_V_JOB_SEANCES
  WHERE  N_JOB_ID = <num_N_JOB_ID>)
SELECT *
FROM   JOB_SEANCES
WHERE  N_ROW <= 10;

где:

• num_N_JOB_ID — идентификатор задания.

Различаются 5 результатов выполнения (N_JOB_STATUS в SQL-запросе выше):

Не нормальными являются состояния, помеченные буквой Н.

В полях D_START и D_FINISH SQL-запроса возвращается дата начала и дата окончания запуска. Для выполняющихся заданий D_FINISH — NULL.

Перечень заданий и их идентификаторы следует брать со страницы АСР Администрирование -> Задания - Назначенные задания.
Контроль рекомендуется вести по всем заданиям системы.

Для мониторинга создания новых сеансов выполнения заданий, можно использовать следующий запрос:

SELECT COUNT(*) 
FROM ss_v_job_seances
WHERE d_start > SYSDATE - 15/24/60
AND   c_reason = 'A'

Данный запрос проверяет наличие новых сеансов за последние 15 минут.
Если сеансы отсутствуют, значит системные задания не выполняются и запрос будет возвращать "0".

Контроль табличных пространств

Необходимо мониторить состояние табличных пространств (tablespaces) БД. Нужную информацию даёт следующий SQL-запрос:

WITH TBLSP_TOTAL AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) ALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) MAX_MB
  FROM   DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) ALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) MAX_MB
  FROM   DBA_TEMP_FILES
  GROUP BY TABLESPACE_NAME),
TBLSP_FREE AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) FREE_MB
  FROM   DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME,
         ROUND(FREE_SPACE/(1024*1024)) FREE_MB
  FROM   DBA_TEMP_FREE_SPACE),
TBLSP_USED AS (
  SELECT TS.TABLESPACE_NAME,
         TS.ALLOCATED_MB - NVL(FS.FREE_MB, 0)                USED_MB,
         NVL(FS.FREE_MB, 0)                                  FREE_MB,
         TS.ALLOCATED_MB                                     TOTAL_MB,
         TS.MAX_MB                                           TOTAL_MAX_MB,
         TS.MAX_MB - (TS.ALLOCATED_MB - NVL(FS.FREE_MB, 0))  FREE_MAX_MB
  FROM   TBLSP_TOTAL TS,
         TBLSP_FREE  FS
  WHERE  FS.TABLESPACE_NAME(+) = TS.TABLESPACE_NAME)
SELECT TABLESPACE_NAME  "TABLESPACE",
       USED_MB          "Used MB",
       FREE_MB          "Free MB",
       TOTAL_MB         "Total MB",
       TOTAL_MAX_MB     "Total Max MB",
       FREE_MAX_MB      "Free Max MB",
       ROUND(100*(FREE_MAX_MB/TOTAL_MAX_MB))  "Pct. Free"
FROM   TBLSP_USED
ORDER BY TABLESPACE_NAME;

В результате запроса получится таблица приблизительно следующего вида:

Значения полей:

• TABLESPACE — название табличного пространства
• Used MB — объем несвободных блоков в занятом объеме на диске
• Free MB — объем свободных блоков в занятом объеме на диске
• Total MB — занятый объем на диске
• Total Max MB — максимально возможный объем на диске, который разрешено захватить СУБД с ростом данных. Может быть больше объема доступного дискового пространства.
• Free Max MB — максимально возможный объем, на который может разрешено увеличиться с ростом данных. Может быть больше объема доступного дискового пространства.
• Pct. Free — процент свободного объема. Расчитывается как отношение Free Max MB к Total Max MB.

Здесь особое внимание нужно уделить показателю Pct. Free для пространств HYDRA и HYDRA_INDEX. При слишком малых значениях его (менее 20%) необходимо автоматически информировать администратора о проблеме со свободным местом. В противном случае в БД, например, могут остановиться важные системные задания с такой подобной ошибкой:

Ошибка при запуске JB_DATA_COLLECT_PKG.EX_DATA_COLLECT_ACCOUNTING
[ORA-01654: unable to extend index AIS_NET.EX_TRAFFIC_COL_C_FIRM_IDX by 8192
in tablespace HYDRA_INDEX]

Контроль нетарифицированных телефонных звонков

Приведенный ниже запрос возвращает количество нетарифицированных телефонных звонков за последний час:

SELECT COUNT(*)
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID   = SYS_CONTEXT('CONST', 'CDR_TYPE_PhoneCall')
AND    N_CDR_STATE_ID  = SYS_CONTEXT('CONST', 'CDR_Status_Finished')
AND    D_END          >= SYSDATE - 1/24
AND   (N_SUM_A IS NULL AND N_SUM_B IS NULL)
AND    N_DURATION_SEC != 0;

Нормой считается 0, т.е. полное отсутствие нетарифицированных звонков.

Мониторинг даты последней загрузки CDR

Следующий запрос показывает в какое время была произведена последняя загрузка CDR в АСР (его удобно использовать для анализа человеком):

SELECT DECODE(MAX(D_LOG_CREATE), NULL, 'Never',
       TO_CHAR(MAX(D_LOG_CREATE), 'DD.MM.YYYY HH24:MI:SS')) VC_LAST_LOAD_DATE
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID = SYS_CONTEXT('CONST', 'CDR_TYPE_PhoneCall');

В результате выполнения запроса может быть либо точная дата в следующем виде:

LAST_DATE_LOAD
-------------------
29.01.2013 11:05:54

либо строка «Never», если в БД нет CDR:

LAST_DATE_LOAD
-------------------
Never

В следующем запросе в качестве результата выводится либо время, когда была произведена последняя загрузка CDR (в секундах от даты загрузки до текущего момента времени), либо «-1», если в БД нет CDR. Данный запрос удобно применять для обработки в системах мониторинга:

SELECT DECODE(MAX(D_LOG_CREATE), NULL, -1, TO_CHAR((SYSDATE-MAX(D_LOG_CREATE))*(60*60*24), 'FM99999999999999990')) N_LAST_LOAD_SEC
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID = SYS_CONTEXT('CONST', 'CDR_TYPE_PhoneCall');

Триггер для мониторинга следует настраивать в зависимости от интервала загрузки CDR или RADIUS-аккаунтинга.

Мониторинг даты последнего обновления сессий по передаче данных

Следующий запрос показывает дату последнего обновления сессий по передаче данных абонентов в АСР (его удобно использовать для анализа человеком):

SELECT DECODE(MAX(D_END), NULL, 'Never',
       TO_CHAR(MAX(D_END), 'DD.MM.YYYY HH24:MI:SS')) VC_LAST_UPDATE
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID IN (SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WithCharging'), SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WOCharging'));

В результате выполнения запроса может быть либо точная дата в следующем виде:

LAST_DATE_LOAD
-------------------
26.11.2013 10:01:38

либо строка «Never», если в БД нет интернет-сессий:

LAST_DATE_LOAD
-------------------
Never

В следующем запросе в качестве результата выводится либо время, когда было произведено последнее обновление сессий (в секундах от даты последнего обновления до текущего момента времени), либо «-1», если в БД нет сессий. Данный запрос удобно применять для обработки в системах мониторинга:

SELECT DECODE(MAX(D_END), NULL, -1, TO_CHAR((SYSDATE-MAX(D_END))*(60*60*24), 'FM99999999999999990')) N_LAST_UPDATE_SEC
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID IN (SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WithCharging'), SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WOCharging'));

Триггер для мониторинга следует настраивать в зависимости от интервала загрузки RADIUS-аккаунтига.

Мониторинг даты последней успешной загрузки платежа

Следующий запрос показывает дату последней успешной загрузки платежа из внешних систем в АСР (его удобно использовать для анализа человеком):

SELECT DECODE(MAX(D_LOAD), NULL, 'Never',
       TO_CHAR(MAX(D_LOAD), 'DD.MM.YYYY HH24:MI:SS')) VC_LAST_LOAD
FROM   EX_V_PAYMENTS
WHERE  N_RATING = 100;

В результате выполнения запроса может быть либо точная дата в следующем виде:

LAST_DATE_LOAD
-------------------
25.03.2014 08:09:43

либо строка «Never», если в БД нет успешных платежей из внешних систем:

LAST_DATE_LOAD
-------------------
Never

Для получения даты последней успешной загрузки в разрезе платежной системы необходимо пользоваться следующим запросом:

SELECT DECODE(MAX(D_LOAD), NULL, 'Never',
       TO_CHAR(MAX(D_LOAD), 'DD.MM.YYYY HH24:MI:SS')) VC_LAST_LOAD
FROM   EX_V_PAYMENTS
WHERE  N_RATING        = 100
AND    N_TO_ACCOUNT_ID = <num_N_TO_ACCOUNT_ID>;

где:

• num_N_TO_ACCOUNT_ID — идентификатор счета платежной системы у юридического лица оператора связи.

В следующем запросе в качестве результата выводится либо время, когда была произведена последняя успешная загрузка платежа (в секундах от даты последней загрузки до текущего момента времени), либо «-1», если в БД нет успешных платежей из внешних систем. Данный запрос удобно применять для обработки в системах мониторинга:

SELECT DECODE(MAX(D_LOAD), NULL, -1, TO_CHAR((SYSDATE-MAX(D_LOAD))*(60*60*24), 'FM99999999999999990')) N_LAST_LOAD_SEC
FROM   EX_V_PAYMENTS
WHERE  N_RATING = 100;

Аналогичный запрос в разрезе платежной системы:

SELECT DECODE(MAX(D_LOAD), NULL, -1, TO_CHAR((SYSDATE-MAX(D_LOAD))*(60*60*24), 'FM99999999999999990')) N_LAST_LOAD_SEC
FROM   EX_V_PAYMENTS
WHERE  N_RATING        = 100
AND    N_TO_ACCOUNT_ID = <num_N_TO_ACCOUNT_ID>;

где:

• num_N_TO_ACCOUNT_ID — идентификатор счета платежной системы у юридического лица оператора связи.

Триггер для мониторинга следует настраивать в зависимости от интенсивности поступления платежей.

Контроль выполнения событий

Приведенный ниже запрос возвращает количество ошибок и предупреждений в очереди событий за последний час:

SELECT COUNT(*)
FROM   SS_V_EVENTS_QUEUE
WHERE  N_EVENT_STATE_ID IN (SYS_CONTEXT('CONST', 'EVENT_QUE_STATE_Warning'),
                            SYS_CONTEXT('CONST', 'EVENT_QUE_STATE_Error'))
AND    D_ACK            >= SYSDATE - 1/24;

Нормой считается 0, т.е. полное отсутствие ошибок и предупреждений.

Проверка корректности репликации

Для проверки репликации необходимо выполнить следующий запрос на обеих БД от пользователя с правами SYSDBA (иначе на standby сервере он не выполнится).

SELECT SEQUENCE# FROM V$LOG_HISTORY WHERE RECID = (SELECT MAX(RECID) FROM V$LOG_HISTORY);

Результат на обоих серверах должен быть одинаковым. Допускается отличие на 1-2 версии, из-за задержки с копированием.

Для настройки мониторинга репликации в /etc/sudoers необходимо добавить возможность пользователя hzabbix выполнять oracle скрипт, который будет подключаться к БД под SYSDBA.

zabbix       ALL=(oracle) NOPASSWD:/etc/zabbix-agent/monrep.sh

Запуск скрипта

zabbix@hydra ~ $ sudo -u oracle /etc/zabbix-agent/monrep.sh

Скрипт monrep.sh с запросом на получения max(sequence#)

#!/bin/bash

. /etc/profile
. /etc/environment
export ORAENV_ASK=NO
. oraenv > /dev/null
sql="SELECT SEQUENCE# FROM V\$LOG_HISTORY WHERE RECID = (SELECT MAX(RECID) FROM V\$LOG_HISTORY);"
echo -e $sql | sqlplus -s / as sysdba

Контроль количества строк в таблицах

Пример запроса на получение количества строк в таблице: 

SQL> SELECT COUNT(*)
FROM SD_GOOD_MOVES;

Таблица пороговых значений

Для определения количества завершенных РРР-сессий в месяц можно использовать следующий запрос:

SELECT COUNT(*)
FROM   EX_V_CDR
WHERE  N_SERVICE_ID = 40223001 -- Идентификатор службы
AND    D_END BETWEEN TO_DATE('01.05.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND TO_DATE('31.05.2013 23:59:59', 'DD.MM.YYYY HH24:MI:SS') -- Задаём период
AND    N_CDR_STATE_ID IN (SYS_CONTEXT('CONST', 'CDR_Status_Finished'),
 						  SYS_CONTEXT('CONST', 'CDR_Status_FinForced'));

SD_GOOD_MOVES

В данной таблице содержатся строки состава инвойсов и счетов.
При превышении порогового значения необходимо настроить в системе агрегацию и архивацию данных. См. руководство пользователя: раздел "Работа с системой" -> "Администрирование" -> "Задания" -> "Стандартные задания" -> "Архивация инвойсов".

EX_DATA_COLLECT

В данной таблице содержится статистика по трафику PPP-сессий. При превышении порогового значения в настройках заданий по удалению старых CDR необходимо уменьшить значение параметра Срок давности для удаления отработанных записей EX_DATA_COLLECT с кумулятивным обновлением.

EX_CALL_DATA_REC

В данной таблице содержатся данные о CDR и PPP-сессиям.

root@sever:~# PIDFILE="/var/run/hydra/hoper/unicorn.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PIDFILE="/var/run/hydra/hupo/unicorn.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PIDFILE="/var/run/hydra/hdd/hdd_default.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@server:~# PIDFILE="/var/run/hydra/hamd.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@server:~# PORT=8889; lsof -i :$PORT -n > /dev/null ; echo $?

root@sever:~# PIDFILE="/var/run/hydra/hard.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PORT=11080; lsof -i :$PORT -n > /dev/null ; echo $?

root@sever:~# PIDFILE="/var/run/hydra/hcd.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PORT=8888; lsof -i :$PORT -n > /dev/null ; echo $?

root@sever:~# PIDFILE="/var/run/hydra/hid.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PORT=10080; lsof -i :$PORT -n > /dev/null ; echo $?

root@sever:~# PIDFILE="/var/run/hydra/hpd.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PORT=9080; lsof -i :$PORT -n > /dev/null ; echo $?

root@sever:~# PIDFILE="/var/run/radiusd/radiusd.pid" ; if [ -f $PIDFILE ] ; then kill -0 `cat $PIDFILE` > /dev/null 2>&1 ; echo $? ; else echo "1" ; fi

root@sever:~# PORT=1812; lsof -i :$PORT -n > /dev/null ; echo $?

Приложение: скрипт для создания пользователя БД с необходимыми правами

Следующий скрипт содержит в себе команды по созданию пользователя с необходимыми правами для мониторинга системы и Oracle, его необходимо выполнять под пользователем SYS:

CREATE USER &&username PROFILE DEFAULT IDENTIFIED BY &&password
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
/ 
GRANT SELECT ON V_$LOG_HISTORY TO &&username;
GRANT SELECT ON V_$PARAMETER TO &&username;  

GRANT CONNECT TO &&username;
GRANT RESOURCE TO &&username;
  
GRANT SELECT ON SS_V_JOBS TO &&username;
GRANT SELECT ON SI_V_USERS TO &&username;
GRANT SELECT ON SS_V_JOB_SEANCES TO &&username;
GRANT SELECT ON SS_V_MANAGER_JOBS TO &&username;
  
GRANT EXECUTE ON SI_SUBJECTS_PKG_S TO &&username;
GRANT EXECUTE ON SI_OBJECTS_PKG_S TO &&username;
GRANT EXECUTE ON SI_REF_PKG_S TO &&username;

-- count.q*
GRANT SELECT ON SS_V_EVENTS_QUEUE to &&username;
-- count.dbc
GRANT SELECT ON V_$DATABASE_BLOCK_CORRUPTION TO &&username;
-- count.uretenop
GRANT SELECT ON V_$UNDOSTAT TO &&username; 
-- count.cdr; count.lastcdr
GRANT SELECT ON EX_V_CDR TO &&username;
-- count.gm
GRANT SELECT ON SD_GOOD_MOVES TO &&username;
-- count.ecrd
GRANT SELECT ON EX_CALL_DATA_REC TO &&username;
-- count.edc
GRANT SELECT ON EX_DATA_COLLECT TO &&username;
-- count.etcc
GRANT SELECT ON EX_TRAFFIC_COLLECT_C TO &&username;
-- count.active
GRANT SELECT ON SI_SUBJ_GOODS TO &&username;
-- tblspace.discovery
GRANT SELECT ON DBA_SEGMENTS TO &&username;
-- tblspace.pcf
GRANT SELECT ON DBA_DATA_FILES TO &&username;
GRANT SELECT ON DBA_FREE_SPACE TO &&username;
GRANT SELECT ON DBA_TEMP_FILES TO &&username;
GRANT SELECT ON DBA_TEMP_FREE_SPACE TO &&username;
-- checkactive
GRANT SELECT ON V_$INSTANCE TO &&username;
-- rcachehit
GRANT SELECT ON V_$SYSSTAT TO &&username;
-- activeusercount
GRANT SELECT ON V_$SESSION TO &&username;
-- dbsize
GRANT SELECT ON DBA_FREE_SPACE TO &&username;
GRANT SELECT ON DBA_TABLESPACES TO &&username;
-- lastarclog
GRANT SELECT ON V_$LOG TO &&username;
-- freebufwaits
GRANT SELECT ON V_$SYSTEM_EVENT TO &&username;
GRANT SELECT ON V_$EVENT_NAME TO &&username;
GRANT SELECT ON EX_V_PAYMENTS TO &&username;
GRANT SELECT ON SS_SESSION_LOGS TO &&username;
/
QUIT;