Use Shell to generate Orabbix automated configuration script

When using Orabbix to monitor Oracle, the biggest difference between it and zaabixagent is that using Orabbix does not need to install an independent agent for each database instance,

When using Orabbix to monitor Oracle, the biggest difference between it and zaabix agent is that using Orabbix does not need to install a separate agent for each database instance,

When using Orabbix to monitor Oracle, the biggest difference between it and zaabix agent is that using Orabbix does not need to install a separate agent for each database instance, an Orabbix instance can correspond to multiple database instances. Orabbix is implemented based on JDBC. Therefore, configuration is particularly important.

There are three things to consider when configuring Orabbix for a database instance.

The corresponding port is opened in the firewall when the Orabbix agent is used, in order to allow the Orabbix agent to access the database through jdbc, we need to open the corresponding port in the firewall

For example

Iptables-I input-s 10. xx.133.xx-p tcp-m multiport -- dports $ db_port-I eth0-j ACCEPT

Of course, when using Orabbix, you still need to create a response Archive user in the database instance. The main role of this user is to access the data dictionary table. The performance View is purely a query.

The final task is to configure the jdbc connection string.

For example, if the database is test_internal, you can configure it in the Orabbix configuration file.

Test_internal.Url = jdbc: oracle: thin: @ 10.127.xxxx.xx: 1523: test_internal
Test_internal.User = orabbix
Test_internal.Password = orabbix
Test_internal.QueryListFile =./conf/query. props
As a whole, these three steps do not work much, but they are complicated in practice. On the one hand, they are server ip addresses. for Orabbix agent, ip addresses may be directly used for direct connection, to use hostname, you also need to configure another layer of resolution in/etc/hosts. the ports of each database instance may be different. if you need to configure dozens of machines, the complexity of work and the discipline of mistakes will be much higher. I hope that I can be faster, but I always feel that I cannot get up quickly, and I am always stuck in some small places, in general, this configuration still takes some time, and it is actually very careful.
In order to make it faster and easier to use, I made up my mind to improve it. so I wrote the following shell script. a script directly generates all the scripts, so that I can see at a glance when and when to do it, dynamic Configuration scripts can be generated by dynamically assigning values to specific environment variables.
The main task of this script is to use tnsping to parse the link string, and then change the host in the connection string from the host name to the ip address to parse the port number, some gaps between the master and slave databases are considered, and the script has also been improved.
$ ORACLE_HOME/bin/tnsping 'sqlplus-s/as sysdba <col value format a10
Set pages 0
Set linesize 200
Set feedback off
Select value from v \\$ parameter where;
EOF '| sed's/(// \ n/g' | sed's /) /\ n/g' | grep-I 'host \ | PORT \ | SERVICE_NAME \ | SID_NAME '> tns_tmp.lst

Tmp_host_name = 'grep-I HOST tns_tmp.lst | awk-F = '{print $2 }''
Host_name = 'grep-w $ tmp_host_name/etc/hosts | awk '{print $1 }''
Db_port = 'grep-I PORT tns_tmp.lst | awk-F = '{print $2 }''
Db_name = 'grep-I 'SERVICE _ NAME \ | SID_NAME 'tns_tmp.lst | awk-F =' {print $2 }''
Eth_name = 'ifconfig | head-1 | awk '{print $1 }''

Echo "#### use below to open firewall to zabbix server with dedicated db port ####"
Echo "iptables-I input-s 10.127.133.82-p tcp-m multiport -- dports $ db_port-I $ eth_name-j ACCEPT"

Echo "#### use below script to create orabbix user accounts ####"
Echo "create user orabbix"
Echo "identified by orabbix"
Echo "default tablespace users"
Echo "temporary tablespace temp"
Echo "profile default"
Echo "account unlock ;"
Echo "-2 Roles for orabbix"
Echo "grant connect to orabbix ;"
Echo "grant resource to orabbix ;"
Echo "alter user orabbix default role all ;"
Echo "-3 System Privileges for orabbix"
Echo "grant select any table to orabbix ;"
Echo "grant create session to orabbix ;"
Echo "grant select any dictionary to orabbix ;"
Echo "grant unlimited tablespace to orabbix ;"
Echo "grant select any dictionary to orabbix ;"

Echo "exec dbms_network_acl_admin.create_acl (acl => 'resolve. XML', description => 'resolve acl', principal => 'orabbix', is_grant => true, privilege => 'resolve ');"
Echo "exec dbms_network_acl_admin.assign_acl (acl => 'resolve. XML', host => '*');"

Echo "#### add below in orabbix config files ####"
Echo $ db_name.Url = jdbc: oracle: thin: @ $ host_name: $ db_port: $ db_name | sed's // g'
Echo $ db_name.User = orabbix
Echo $ db_name.Password = orabbix
Echo $ db_name.QueryListFile =./conf/query. props
The script runs as follows:
#### Use below to open firewall to zabbix server with dedicated db port ####
Iptables-I input-s 10.127.xxxx.xx-p tcp-m multiport -- dports 1526-I eth0-j ACCEPT
#### Use below script to create orabbix user accounts ####
Create user orabbix
Identified by orabbix
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
Account unlock;
-2 Roles for orabbix
Grant connect to orabbix;
Grant resource to orabbix;
Alter user orabbix default role all;
-3 System Privileges for orabbix
Grant select any table to orabbix;
Grant create session to orabbix;
Grant select any dictionary to orabbix;
Grant unlimited tablespace to orabbix;
Grant select any dictionary to orabbix;
Exec dbms_network_acl_admin.create_acl (acl => 'resolve. XML', description => 'resolve acl', principal => 'orabbix', is_grant => true, privilege => 'resolve ');
Exec dbms_network_acl_admin.assign_acl (acl => 'resolve. XML', host => '*');
#### Add below in orabbix config files ####
Test_internal.Url = jdbc: oracle: thin: @ 10.127.xxxx.xx: 1526: test_internal
Test_internal.User = orabbix
Test_internal.Password = orabbix
Test_internal.QueryListFile =./conf/query. props

All right, the script is ready, and it is just waiting for the convenience of work. it is not difficult to configure dozens of database instances in a short time. who knows who should use it.

This article permanently updates the link address: