Shulou(Shulou.com)06/01 Report--

This article focuses on "how Zabbix monitors Oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how Zabbix monitors Oracle.

Introduction to Orabbix

Orabbix is a plug-in designed to monitor Oracle databases for zabbix, providing multiple levels of monitoring, including availability and server performance metrics.

It provides an effective mechanism for collecting from many Oracle instances, which in turn provides monitoring and performance metrics for this information. You can then take advantage of zabbix's reporting feature for all the data collected and provide analysis. The current release includes a set of predefined templates, including alarm and graphics capabilities from the initial deployment. However, these can be fine-tuned to meet your needs and data / monitoring requirements.

What is monitored by Orabbix

1. Database version

2. Archived logs and production trend

3. Trigger, table / process hit ratio

4. Logical IO performance

5. Physical IO performance

6 、 PGA

7 、 SGA

8. Shared pool

9 、 Session

10. Database size

Orabbix is a Zabbix plug-in for monitoring Oracle database instances. (plug-in installed on zabbix-server side)

Download address: http://www.smartmarmot.com/product/orabbix/download/

Operation on zabbix-server

Orabbix connects to the oracle database through JDBC, and then filters the desired data, so the jdk environment is needed.

I have tested jdk1.7 and 1.8, either directly yum installation, or download rpm package, rpm-ivh installation, can

Here, I installed it directly from rpm.

[root@zabbix ~] # rpm-ivh jdk-8u102-linux-x64.rpm

Preparing... # [100%]

1:jdk1.8.0_102 # # [100%]

Create a new orabbix directory under the / opt directory:

[root@oracle orabbix] # mkdir-p / opt/orabbix

(it is recommended that if you place another directory in this directory, you need to change the startup file orabbix of orabbix later. The startup file is written in the opt/orabbix directory by default.)

[root@zabbix opt] # cd orabbix/

Upload orabbix-1.2.3.zip

[root@zabbix orabbix] # ls

Orabbix-1.2.3.zip

Decompression

[root@zabbix orabbix] # unzip-Q orabbix-1.2.3.zip

[root@zabbix orabbix] # ls

Conf init.d lib orabbix-1.2.3.zip orabbixw.exe run.sh uninstall.cmd

Doc install.cmd orabbix-1.2.3.jar orabbix.exe run.bat template

Authorization

[root@zabbix orabbix] # cd..

[root@zabbix opt] # chmod-R axix orabbix/

[root@zabbix ~] # cd / opt/orabbix/conf/

[root@zabbix conf] # ls

Config.props log4j.properties query_a_1.props query_b.props query.props

Config.props.sample log4j.properties.sample query_a.props query-Copy.props query.props.sample

[root@zabbix conf] # cp config.props.sample config.props

Edit the configuration file:

[root@zabbix conf] # vim config.props

Source profile

[root@zabbix conf] # egrep "^ [acopyright ZJI 1-9]" config.props.sample

ZabbixServerList=ZabbixServer1,ZabbixServer2

ZabbixServer1.Address=IP_ADDRESS_OF_ZABBIX_SERVER

ZabbixServer1.Port=PORT_OF_ZABBIX_SERVER

ZabbixServer2.Address=IP_ADDRESS_OF_ZABBIX_SERVER

ZabbixServer2.Port=PORT_OF_ZABBIX_SERVER

OrabbixDaemon.PidFile=./logs/orabbix.pid

OrabbixDaemon.Sleep=300

OrabbixDaemon.MaxThreadNumber=100

DatabaseList=DB1,DB2,DB3

DatabaseList.MaxActive=10

DatabaseList.MaxWait=100

DatabaseList.MaxIdle=1

DB1.Url=jdbc:oracle:thin:@server.domain.example.com::DB1

DB1.User=zabbix

DB1.Password=zabbix_password

DB1.MaxActive=10

DB1.MaxWait=100

DB1.MaxIdle=1

DB1.QueryListFile=./conf/query.props

DB2.Url=jdbc:oracle:thin:@server2.domain.example.com::DB2

DB2.User=zabbix

DB2.Password=zabbix_password

DB2.QueryListFile=./conf/query.props

DB3.Url=jdbc:oracle:thin:@server3.domain.example.com::DB3

DB3.User=zabbix

DB3.Password=zabbix_password

DB3.QueryListFile=./conf/query.props

After modification

[root@zabbix conf] # egrep "^ [acopyright ZJI 1-9]" config.props

ZabbixServerList=ZabbixServer1 # (the name of zabbixserver, which is referenced by both address and port in the following line, so the prefix names of the following two lines should be the same as the name here)

ZabbixServer1.Address=127.0.0.1 # I operate on zabbix server here, so this is 127.0.0.1

Service port of ZabbixServer1.Port=10051 # zabbix server

OrabbixDaemon.PidFile=./logs/orabbix.pid

OrabbixDaemon.Sleep=300

OrabbixDaemon.MaxThreadNumber=100

DatabaseList=172.20.10.183-oracle,192.168.56.99-oracle # here is very important, corresponding to the following

DatabaseList.MaxActive=10

DatabaseList.MaxWait=100

DatabaseList.MaxIdle=1

172.20.10.183-oracle.Url=jdbc:oracle:thin:@172.20.10.183:1521:orcl # 172.20.10.183 address of oracle database, port 1521, orcl database instance name

172.20.10.183-oracle.User=zabbix # authorized account

172.20.10.183-oracle.Password=zabbix # authorized password

172.20.10.183-oracle.MaxActive=10

172.20.10.183-oracle.MaxWait=100

172.20.10.183-oracle.MaxIdle=1

The statements to be queried in 172.20.10.183-oracle.QueryListFile=./conf/query.props # are all defined here. If you are not satisfied with it, change it yourself.

Operation on the oracle host (this step can be omitted, as long as you know the database instance, account number, password)

The following is the authorization statement of oracle 11g (12c syntax is different) as follows: user creation, authorization, all these operations are performed under the sqlplus of oracle database.

Log in to the database and grant the relevant permissions to the user.

The hostname of the monitored database is placed in the DatabaseList, which should be consistent with the machine name in the zabbix server interface.

You will find that some monitoring items dbfilesize,dbsize have no value. This is because the / opt/orabbix/conf/query.props file does not add these two items to the query List, which can be copied from query.props.sample.

Add dbfilesize,dbsize at the end of QueryList

Then add at the end of the file:

one

two

three

four

five

six

seven

eight

Dbfilesize.Query=select to_char (sum (bytes/1024/1024/10), 'FM99999999999999990') retvalue from dba_data_files

Dbsize.Query=SELECT to_char (sum (NVL (a.bytes/1024/1024/10-NVL (f.bytes/1024/1024/10, 0), 0)), 'FM99999999999999990') retvalue\

FROM sys.dba_tablespaces d,\

(select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a,\

(select tablespace_name, sum (bytes) bytes from dba_free_space group by tablespace_name) f\

WHERE d.tablespace_name = a.tablespace_name (+) AND d.tablespace_name = f.tablespace_name (+)\

AND NOT (d.extent_management like 'LOCAL' AND d.contents like' TEMPORARY')

Create a zabbix user on the oracle database and authorize

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

CREATE USER zabbix

IDENTIFIED BY zabbix

DEFAULT TABLESPACE SYSTEM

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK

-2 Roles for ZABBIX

GRANT CONNECT TO ZABBIX

GRANT RESOURCE TO ZABBIX

ALTER USER ZABBIX DEFAULT ROLE ALL

-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

If you need strict permission control, you can execute the following script

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

CREATE USER zabbix

IDENTIFIED BY zabbix

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK

GRANT SELECT ON V_$INSTANCE TO zabbix; GRANT SELECT ON DBA_USERS TO zabbix; GRANT SELECT ON V_$LOG_HISTORY TO zabbix; GRANT SELECT ON V_$LOG TO zabbix; GRANT SELECT ON V_$PARAMETER TO zabbix; GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO zabbix; GRANT SELECT ON V_$LOCK TO zabbix; GRANT SELECT ON DBA_REGISTRY TO zabbix; GRANT SELECT ON V_$LIBRARYCACHE TO zabbix; GRANT SELECT ON V_$SYSSTAT TO zabbix; GRANT SELECT ON V_$PARAMETER TO zabbix; GRANT SELECT ON V_$LATCH TO zabbix; GRANT SELECT ON V_$PGASTAT TO zabbix; GRANT SELECT ON V_$SGASTAT TO zabbix GRANT SELECT ON V_$LIBRARYCACHE TO zabbix; GRANT SELECT ON V_$PROCESS TO zabbix; GRANT SELECT ON DBA_DATA_FILES TO zabbix; GRANT SELECT ON DBA_TEMP_FILES TO zabbix; GRANT SELECT ON DBA_FREE_SPACE TO zabbix; GRANT SELECT ON V_$SYSTEM_EVENT TO zabbix; GRANT SELECT ON V_$locked_object TO zabbix; GRANT SELECT ON dba_objects TO zabbix; GRANT SELECT ON dba_tablespaces TO zabbix; GRANT SELECT ON v_$SESSION TO zabbix

Start orabbix

[root@zabbix orabbix] # cp init.d/orabbix / etc/init.d/

[root@zabbix orabbix] # ll / etc/init.d/orabbix

-rwxr-xr-x 1 root root 1693 Sep 14 21:40 / etc/init.d/orabbix

[root@zabbix orabbix] # / etc/init.d/orabbix start

Linux7 starts orabbix

one

two

three

Chkconfig-add orabbix

Systemctl start orabbix

Chkconfig orabbix on

Starting Orabbix service:

If you report an error, check whether the configuration file, database address, port, instance name, account number and password are correct. You need to verify what to do for those who do not understand oracle. Search commands. I have also come all the way, all kinds of holes.

Then read the diary inside.

[root@zabbix orabbix] # tailf / opt/orabbix/logs/orabbix.log

[main] INFO Orabbix-- on Database-> orcl

[pool-1-thread-1] WARN Orabbix-Error while executing-> dbversion- on database-> 172.20.10.183 Murray-Exception received ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.UTL_INADDR", line 4

ORA-06512: at "SYS.UTL_INADDR", line 35

ORA-06512: at line 1

The occurrence of these logs does not affect subsequent operations.

If you are an oracle11g user, you also need to open ACL access control.

one

two

three

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

At this point, when the installation and configuration is complete, you can directly import the template by visiting the monitoring interface on the web page

Templates are placed under / opt/orabbix/template

Orabbix_export_full.xml # just need to import this one template

Add Host

Then link to the template of oracle

Add the effect after completion

View the latest data

At this time, the picture is also available.

Common mistakes

[root@zabbix orabbix] # tailf / opt/orabbix/logs/orabbix.log

[main] ERROR Orabbix-Error on Configurator for database 172.20.10.183 oracle-- > ORA-01017: invalid username/password; logon denied

The password of the database account is incorrect in the config.props configuration file, or the user is not authorized in the oracle database

At this point, I believe you have a deeper understanding of "how Zabbix monitors Oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.