Monitor multiple database loads simultaneously using Shell scripts

Monitor multiple database loads simultaneously using Shell scripts

In normal work, there are still a lot of databases to be managed. Because of the relationship between remote access and permissions, some graphic tools cannot be accessed. Sometimes it feels like a serial process when performing checks, in this way, the check is not targeted enough. For example, we do not know the scope of database load during the check period. If some databases have extremely high load, you need to pay special attention to the more targeted analysis and check. If 20 databases need to be managed at the same time and there is no focus, it is still a headache. Checking the database load is still a good indicator. We can monitor multiple databases at the same time based on this benchmark, and it can be displayed on a large screen.

I wrote a Shell script on my own, and found that the effect was good. In this way, the database load is very clear, which databases need to be focused on When busy, and which databases are basically in sleep status, so you don't need to pay too much attention.

The implementation script is as follows:
# Getload. sh
Function showsnap
{
Sqlplus-s $1 <EOF
Break on db_name
Set pages 50
Set linesize 65
Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~
Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v \ $ database d,
V \ $ instance I;
Select
Begin_snap
, End_snap
, Snapdate
, Round (END_INTERVAL_TIME + 0)-(BEGIN_INTERVAL_TIME + 0) * 24*60) dur_mins
, Round (select round (sum (e. value )-
Sum (B. value)/1000000/60, 2) dbtime
FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL B
WHERE
E. STAT_NAME = 'db time'
And B. snap_id = begin_snap
And e. snap_id = end_snap
AND B. STAT_NAME = 'db time'
Group by e. snap_id, B. snap_id) dbtime
From
(
Select
S. snap_id begin_snap
, Lead (s. snap_id, 1, s. snap_id) over (order by s. end_interval_time) end_snap
, To_char (s. end_interval_time, 'dd Mon YYYY HH24: Mi') snapdate
, S. end_interval_time
, S. begin_interval_time
From dba_hist_snapshot s
, Dba_hist_database_instance di
Where
(Di. dbid, di. instance_number) in
(Select d. dbid
, I. instance_number inst_num
From v \ $ database d,
V \ $ instance I)
And di. dbid = s. dbid
And di. instance_number = s. instance_number
And di. startup_time = s. startup_time
And to_char (END_INTERVAL_TIME, 'yyyymmdd') = '$2'
And EXTRACT (hour from END_INTERVAL_TIME) between $3-1 and $4 + 1
Order by db_name, instance_name, snap_id
);
EOF
}

 

Curr_hr = 'date' + % h''
Pre_hr = 'expr $ curr_hr-3'
DATE = 'date' + % Y % m % d''
# Echo $ curr_hr $ pre_hr
SH_DB_SID = 'echo "$1" | awk-F @ '{print $2}' | tr' [a-z] ''[A-Z]''
Showsnap $1 $ DATE $ pre_hr $ curr_hr> tmp _ $ {SH_DB_SID }_$ {DATE} _ load

Function format_rpt
{
Awk'
BEGIN {
Print "###################################### ###########################"
Printf "%-65s \ n", "DB workload"
Print "###################################### ###########################"
}
{
Printf "%-65s \ n", $0
} '$1> $2
}
Format_rpt tmp _ $ {SH_DB_SID }_$ {DATE} _ load
Rm tmp _ $ {SH_DB_SID }_$ {DATE} _ load
Cat $ {SH_DB_SID }_$ {DATE} _ load

The Second Script integrates these output results and flexibly specifies monitoring metrics in a dynamic manner,
# Showall. sh
# Get db load input getload
# Get db tsps input showtsps
Act_type = $1
Ksh $ {act_type}. sh xxx/xxx @ xxxx> tmp_b4 cat b7

For example, if we have a script getload. sh that monitors the database load, we can run the script showall. sh getload. To view the table space usage, run the script showtsps. sh to run showall. sh showtsps. DB_CONN_STR = XXX/XXX
# Get db load input getload
# Get db tsps input showtsps
Act_type = $1

#### DB01
SH_DB_SID = XXX
Ksh $ {act_type}. sh $ DB_CONN_STR @ $ SH_DB_SID> a1

#### DB02
SH_DB_SID = XXX
Ksh $ {act_type}. sh $ DB_CONN_STR @ $ SH_DB_SID> a2

#### DB03
SH_DB_SID = XXX
Ksh $ {act_type}. sh $ DB_CONN_STR @ $ SH_DB_SID> a3

Cat a1
Paste a2 a3
Paste a4 a5
Paste a6 a7
Cat a8

This article permanently updates the link address: