Monitoring Oracle Databases with Zabbix

Introduction to orambix
To monitor Oracle database, we need to install the ZABBIX plug-in provided by the third party. First, we test the famous orambix, http://www.smartmarmot.com/product/orabbix/
As can be seen from the deployment architecture diagram, orabbix is connected to the monitored database through JDBC. In fact, the agent does not have to be installed on the database host. The host running orabbix can be ZABBIX server, or any host other than the database host and ZABBIX server. For the convenience of testing, we install orabbix on the ZABBIX server.
Download software and installation services
? download orambix and upload to your ZABBIX server
? unzip software to this directory / opt / orabbix
? installation services:
? copy startup scripts
#cp /opt/orabbix/init.d/orabbix /etc/init.d/orabbix
? operation permission:
#chmod +x /etc/init.d/orabbix
#chmod +x /opt/orabbix/ run.sh
? #chkconfig --add orabbix
Establish monitoring user and authorization
CREATE USER ZABBIX
IDENTIFIED BY welcome1
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
REM 2 Roles for ZABBIX
GRANT CONNECT TO ZABBIX;
GRANT RESOURCE TO ZABBIX;
ALTER USER ZABBIX DEFAULT ROLE ALL;
REM 5 System Privileges for ZABBIX
GRANT SELECT ANY TABLE TO ZABBIX;
GRANT CREATE SESSION TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;
GRANT UNLIMITED TABLESPACE TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;
For an 11g database, execute the following statement:
exec dbms_ network_ acl_ admin.create_ acl(acl => ‘ resolve.xml ‘,description => ‘resolve acl‘, principal =>‘ZABBIX‘, is_ grant => true, privilege => ‘resolve‘);
exec dbms_ network_ acl_ admin.assign_ acl(acl => ‘ resolve.xml ‘, host =>‘*‘);
commit;
Modify orabbix configuration file
#cd /opt/orabbix/conf
#cp  config.props.sample  config.props
Modify the file according to the actual deployment, such as:
#comma separed list of Zabbix servers
ZabbixServerList=ZabbixServer1
Zabbixserver1. Address = 192.168.0.41
Zabbixserver1. Port = 10051? ZABBIX server port
#pidFile
OrabbixDaemon.PidFile= ./logs/ orabbix.pid
#frequency of item‘s refresh
OrabbixDaemon.Sleep=300
#MaxThreadNumber should be >= than the number of your databases
OrabbixDaemon.MaxThreadNumber=100
#put here your databases in a comma separated list
Database list = db1, DB2, db3 ා database list, name arbitrarily
#Configuration of Connection pool
#if not specified Orabbis is going to use default values (hardcoded)
#Maximum number of active connection inside pool
DatabaseList.MaxActive=10
#The maximum number of milliseconds that the pool will wait
#(when there are no available connections) for a connection to be returned
#before throwing an exception, or <= 0 to wait indefinitely.
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1
#define here your connection string for each database
DB1. Url=jdbc:oracle:thin:@192.168.0.31 : 1521: test1 database connection string
Db1. User = ZABBIX ා monitoring database user name
Db1. Password = welcome1 ා monitoring database password
Save the configuration file and restart orabbix.
Import template
There are four template files in the orabbix software package. This template file can be imported on the way below, including the contents of all other template files.
Applicable to orambix
When configuring database monitoring on the ZABBIX interface, it should be noted that orabbix configures each database as a "host", which is a bit awkward. In addition, when configuring the host, the name must be the same as that of the host config.props The name of the database configured in the file is the same as db1
As mentioned above, the address of the "host" can be written freely, because the monitored host does not need to have an agent. However, in order to facilitate management, I think it is better to write the address of the Oracle host.
Once the host is defined, the predefined monitoring items, triggers and charts in the template can be applied.
Comparison table:
For example, the simplest way to check the database is to use the triggers available:
Of course, the action of the corresponding trigger still needs to be configured by yourself.
Custom SQL check
Orabbix provides the monitoring of the table space, and the SQL corresponding to the monitoring items:
tbl_ space.Query=SELECT  * FROM ( \
select ‘- Tablespace ->‘,t.tablespace_ name ktablespace, \
‘- Type->‘,substr(t.contents, 1, 1) tipo, \
‘- Used(MB)->‘,trunc((d.tbs_ size-nvl(s.free_ space, 0))/1024/1024) ktbs_ Em_ uso, \
‘- ActualSize(MB)->‘,trunc(d.tbs_ size/1024/1024) ktbs_ size, \
‘- MaxSize(MB)->‘,trunc(d.tbs_ maxsize/1024/1024) ktbs_ maxsize, \
‘- FreeSpace(MB)->‘,trunc(nvl(s.free_ space, 0)/1024/1024) kfree_ space, \
‘- Space->‘,trunc((d.tbs_ maxsize - d.tbs_ size + nvl(s.free_ space, 0))/1024/1024) kspace, \
‘- Perc->‘,decode(d.tbs_ maxsize, 0, 0, trunc((d.tbs_ size-nvl(s.free_ space, 0))*100/d.tbs_ maxsize)) kperc \
from \
( select SUM(bytes) tbs_ size, \
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_ maxsize, tablespace_ name tablespace \
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_ name \
from dba_ data_ files \
union all \
select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_ name \
from dba_ temp_ files \
)
group by tablespace_ name \
) d, \
( select SUM(bytes) free_ space, \
tablespace_ name tablespace \
from dba_ free_ space \
group by tablespace_ name \
) s, \
dba_ tablespaces t \
where t.tablespace_ name = d.tablespace(+) and \
t.tablespace_ name = s.tablespace(+) \
order by 8) \
where kperc > 93 \
and tipo <>‘T‘ \
and tipo <>‘U‘
tbl_ space.NoDataFound=none
This SQL will return 93% full table space information. For this monitoring item, orabbix also defines a trigger. Because the return value of the monitoring item is text, and the string "None" is returned when there are no records that meet the conditions, the trigger corresponding to the monitoring item will check whether the return value starts with none. If not, the alarm will be given. In this way, the user will not only receive the warning information, but also return the string "None" You can see which table space is nearly full from the specific value of the return value.
Of course, most time monitoring items return none, so we can't plot the space consumption time curve of normal table spaces that are not full. Only when it is over 93% slow can we know the specific occupancy situation.
If you want to collect and retain more information, you need to use a custom query by using the query.props Add the SQL you want to check in the file. For example, if we want to know the table space information, we can add the following SQL:
customtbl.Query=select  ‘TBL:‘||a.tablespace_ name||‘,‘ TBL, \
‘Total Size:‘||trunc(sum(a.tots) / 1024 / 1024, 2)||‘,‘ Tot_ Size_ mb, \
‘Free MB:‘||round(sum(a.sumb) / 1024 / 1024, 2)||‘,‘ Tot_ Free_ mb, \
‘PCT Free:‘||round(sum(a.sumb) * 100 / sum(a.tots), 2)||‘,‘ Pct_ Free, \
‘Max Free MB:‘||round(sum(a.largest) / 1024 / 1024, 2)||‘,‘ Max_ Free_ mb, \
‘Chunks Free:‘||sum(a.chunks)||‘,‘ Chunks_ Free \
from (select tablespace_ name, \
0 tots, \
sum(bytes) sumb, \
max(bytes) largest, \
count(*) chunks \
from dba_ free_ space a \
group by tablespace_ name \
union \
select tablespace_ name, sum(bytes) tots, 0, 0, 0 \
from dba_ data_ files \
group by tablespace_ name) a \
group by a.tablespace_ name
customtbl.NoDataFound=none
Then add the monitoring item customtbl to the template corresponding to orabbix. The following is the SQL result returned from the latest data menu:
Of course, if we want to do further analysis and demonstration, we need to use other tools or development tools. This is not the scope of this article.
Lack of orambix
Preliminary tests showed that orabbix had two obvious defects
1. The monitored database information should be written into the configuration file one by one. If the number of databases is large, the management will be more troublesome
2. The account information of the monitored database is written in the configuration file, and the password is stored in plaintext, which has great security risks
3. Support for RAC is still very rudimentary
Monitoring Oracle database with ZABBIX