Page History

This article in English Monitoring Hydra's Database and Applications

#!/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
 

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;

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

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;

Ошибка при запуске 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;

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

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

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');

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

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

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'));

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

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>;

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>;

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            >= TRUNC(SYSDATE, 'HH');

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

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

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

#!/bin/bash

. /etc/profile
. /etc/environment
export ORAENV_ASK=NO
. oraenv > /dev/null
sql="select max(sequence#) 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'));

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.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;