12 Oracle database setup

Overview

This section contains instructions for creating Oracle database and configuring connections between the database and Zabbix server, proxy, and frontend.

Database creation

We assume that a zabbix database user with password password exists and has permissions to create database objects in ORCL service located on the host Oracle database server. Zabbix requires a Unicode database character set and a UTF8 national character set. Check current settings:

  1. sqlplus> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTERSET' or parameter='NLS_NCHAR_CHARACTERSET';

Now prepare the database:

  1. shell> cd /path/to/zabbix-sources/database/oracle
  2.        shell> sqlplus zabbix/[email protected]_host/ORCL
  3.        sqlplus> @schema.sql
  4.        # stop here if you are creating database for Zabbix proxy
  5.        sqlplus> @images.sql
  6.        sqlplus> @data.sql

Please set the initialization parameter CURSOR_SHARING=FORCE for best performance.

Connection set up

Zabbix supports two types of connect identifiers (connection methods):

  • Easy Connect
  • Net Service Name

Connection configuration parameters for Zabbix server and Zabbix proxy can be set in the configuration files. Important parameters for the server and proxy are DBHost, DBUser, DBName and DBPassword. The same parameters are important for the frontend: $DB[“SERVER”], $DB[“PORT”], $DB[“DATABASE”], $DB[“USER”], $DB[“PASSWORD”].

Zabbix uses the following connection string syntax:

  1. {DBUser/DBPassword[@<connect_identifier>]}

<connect_identifier> can be specified either in the form of “Net Service Name” or “Easy Connect”.

  1. @[[//]Host[:Port]/<service_name> | <net_service_name>]

Easy Connect

Easy Connect uses the following parameters to connect to the database:

  • Host - the host name or IP address of the database server computer (DBHost parameter in the configuration file).
  • Port - the listening port on the database server (DBPort parameter in the configuration file; if not set the default 1521 port will be used).
  • <service_name> - the service name of the database you want to access (DBName parameter in the configuration file).

Example:

Database parameters set in the server or proxy configuration file (zabbix_server.conf and zabbix_proxy.conf):

  1. DBHost=localhost
  2.        DBPort=1521
  3.        DBUser=myusername
  4.        DBName=ORCL
  5.        DBPassword=mypassword

Connection string used by Zabbix to establish connection:

  1. DBUser/[email protected]:DBPort/DBName

During Zabbix frontend installation, set the corresponding parameters in the Configure DB connection step of the setup wizard:

  • Database host: localhost
  • Database port: 1521
  • Database name: ORCL
  • User: myusername
  • Password: mypassword

12 Oracle database setup - 图1

Alternatively, these parameters can be set in the frontend configuration file (zabbix.conf.php):

  1. $DB["TYPE"]                     = 'ORACLE';
  2.        $DB["SERVER"]                   = 'localhost';
  3.        $DB["PORT"]             = '1521';
  4.        $DB["DATABASE"]                 = 'ORCL';
  5.        $DB["USER"]                     = 'myusername';
  6.        $DB["PASSWORD"]                 = 'mypassword';

Net service name

Since Zabbix 5.4.0 it is possible to connect to Oracle by using net service name.

<net_service_name> is a simple name for a service that resolves to a connect descriptor.

In order to use the service name for creating a connection, this service name has to be defined in the tnsnames.ora file located on both the database server and the client systems. The easiest way to make sure that the connection will succeed is to define the location of tnsnames.ora file in the TNS_ADMIN environment variable. The default location of the tnsnames.ora file is:

  1. $ORACLE_HOME/network/admin/

A simple tnsnames.ora file example:

  1. ORCL =
  2.          (DESCRIPTION =
  3.            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  4.            (CONNECT_DATA =
  5.              (SERVER = DEDICATED)
  6.              (SERVICE_NAME = ORCL)
  7.            )
  8.          )

To set configuration parameters for the “Net Service Name” connection method, use one of the following options:

  • Set an empty parameter DBHost and set DBName as usual:
  1. DBHost=
  2.        DBName=ORCL
  • Set both parameters and leave both empty:
  1. DBHost=
  2.        DBName=

In the second case, the TWO_TASK environment variable has to be set. It specifies the default remote Oracle service (service name). When this variable is defined, the connector connects to the specified database by using an Oracle listener that accepts connection requests. This variable is for use on Linux and UNIX only. Use the LOCAL environment variable for Microsoft Windows.

Example:

Connect to a database using Net Service Name set as ORCL and the default port. Database parameters set in the server or proxy configuration file (zabbix_server.conf and zabbix_proxy.conf):

  1. DBHost=
  2.        #DBPort=
  3.        DBUser=myusername
  4.        DBName=ORCL
  5.        DBPassword=mypassword

During Zabbix frontend installation, set the corresponding parameters in the Configure DB connection step of the setup wizard:

  • Database host:
  • Database port: 0
  • Database name: ORCL
  • User: myusername
  • Password: mypassword

12 Oracle database setup - 图2

Alternatively, these parameters can be set in the frontend configuration file (zabbix.conf.php):

  1. $DB["TYPE"]                     = 'ORACLE';
  2.        $DB["SERVER"]                   = '';
  3.        $DB["PORT"]           = '0';
  4.        $DB["DATABASE"]                 = 'ORCL';
  5.        $DB["USER"]                     = 'myusername';
  6.        $DB["PASSWORD"]                 = 'mypassword';

Connection string used by Zabbix to establish connection:

  1. DBUser/[email protected]

Known issues

To improve performance, you can convert the field types from nclob to nvarchar2, see known issues.