I couldn’t find any decent documentation online about getting Zabbix configured with an Oracle backend so I decided to write one myself. The oracle installation section of this guide is based on John Smileys guide (available here), but has been adapted for 11GR2.
As usual I’ll be basing this on a CentOS 5.4 i386 installation. Please note that to install Oracle you need to have X11 installed. The requirements for an x64 installation are a little different. I prefer to use X11 forwarding over SSH when it’s available I also need to note here that you can’t use su and then have X11 forwarding work.
For the record, I’ve never used the Oracle Database product before this week, so I’m sure there’s bad practices all over the place. If there is leave a comment and I’ll sort out my mistakes. Oracle OverviewFor those of you, who like me haven’t had to touch oracle before, I’ve drawn up a pretty picture which outlines how Oracle hangs together in terms of database design and storage.
Oracle ASM is the Automated Storage management, which is capable of handling disk redundancy if requested.
For those who have come from the land of MSSQL or MYSQL, the structure and terminology can get a bit confusing so I’ll explain it here:
In MSSQL you have an Instance, which can contain multiple databases, with each database having its own file for storage. In Oracle the Instance is called a Database. Each Oracle Database can contain multiple tablespaces, each tablespace can have one or multiple Datafiles assosciated with it which can be located on either an OS partition or an ASM Instance. Oracle does not have “databases” in the same concept that MSSQL does, instead each user has a default tablespace and a schema assosciated to the user. For example, in MSSQL you could have a database called ZABBIXDB, and a user with permission to access this database. In Oracle you have a user (ZABBIXDBUSER) with a quota in one or more tablespaces. Each user has a default tablespace to store tables in, and each table is labeled zabbixdbuser.tablename. If you wanted to store a table on another tablespace you would use zabbixdbuser.tablename on tablespace. Oracle InstallationIn terms of physical hardware, I have a Dell PE2650 with 5 72GB SCSI drives installed, the first two are in a RAID1 and the other three are passed directly to the OS to be managed by ASM. I would highly recommend looking at the system requirements here.
All of the commands that I list here in black will need to be run as root, the oracle user will be in dark green, and the zabbix user will be in dark blue. Downloads RequiredIf you are going to be using Oracle ASM you’ll need to download the following from here.
oracleasm-2.6.18-164.15.1.el5PAE-2.0.5-1.el5.i686.rpm <- This needs to match your uname -r
oracleasmlib-2.0.4-1.el5.i386.rpm
oracleasm-support-2.1.3-1.el5.i386.rpm
linux_11gR2_grid.zip
For the Oracle Database Instance you’ll need to download the following files from here.
linux_11gR2_database_1of2.zip
linux_11gR2_database_2of2.zip Prerequisites for Oracle InstallationRun the following command to install the packages that Oracle needs
yum install compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc glibc-devel glibc-common gcc gcc-c++ kernel-headers libgcc libaio libaio-devel libstdc++ libstdc++-devel unixODBC unixODBC-devel sysstat binutils make sysstat pdkshThese are the kernel parameters that Oracle requires:
cat >> /etc/sysctl.conf << EOFkernel.shmall = 2097152kernel.shmmax = 536870912kernel.shmmni = 4096kernel.semopm = 100kernel.sem = 250 32000 100 128fs.file-max = 6915744net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 4194304net.core.wmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_max = 1048576fs.aio-max-nr = 1048576EOFsysctl -pUser and Groups:
groupadd oinstallgroupadd dbauseradd -m -g oinstall -G dba oraclemkdir -p /u01/app/oraclechown -R oracle:oinstall /u01/appchmod -R 775 /u01/apppasswd oracleI ran into issues with the Oracle root scripts with SELinux enabled.
setenforce permissivesed -i 's/enforcing/permissive/g' /etc/sysconfig/selinuxMore Oracle User requirements:
cat >> /etc/security/limits.conf << EOForacle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536EOFcat >> /etc/pam.d/login << EOFsession required pam_limits.soEOFcat >> /etc/profile <<EOFif [ \$USER = "oracle" ]; then if [ \$SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi umask 022fiEOFcat >> /etc/csh.login <<EOFif ( \$USER == "oracle" ) then limit maxproc 16384 limit descriptors 65536 umask 022endifEOFASM InstallationInstall the RPMs that we downloaded
rpm -ivh oracleasm-*.rpm oracleasmlib-2.0.4-1.el5.i386.rpm oracleasm-support-2.1.3-1.el5.i386.rpmWe then need to configure the ASM service:
service oracleasm configureUser:oracle
group:dba
onboot:yes
scanonboot:yes
We’ll then need to label our disks for ASM. You will need to preformat the drives before labelling.
service oracleasm createdisk VOL1 /dev/sdb1service oracleasm createdisk VOL2 /dev/sdc1service oracleasm createdisk VOL3 /dev/sdd1chkconfig oracleasm onNow we can finally get around to installing ASM. Remember that you need X11 forwarding turned on if you’re doing this over SSH, or you need to have X started if you’re doing this locally.
unzip linux_11gR2_grid.zipcd grid./runInstaller-Install and Configure Grid Infrastructure for a Standalone Server
-Configure ASM disks as required
-Configure Passwords as required
-Chose dba group for all groups
-Assuming you’ve followed the above, prerequisites shouldn’t be an issue.
-Install
-Run scripts as requested as the root user Oracle Database Installationunzip linux_11gR2_database_1of2.zipunzip linux_11gR2_database_2of2.zipcd database./runInstaller-Install and Configure a Database
-Server Class
-Single instance Database Installation
-Advanced Install
-Enterprise Edition
-Default Install Location
-General Purpose
-Default Configuration Options
-In Management, goto Character Sets then select Use Unicode (AL32UTF8) – This is needed for nvarchar2 column size of >2000
-Insert your ASMSNMP Password if you are using ASM, else select where you want to store your database.
-No Automated Backups
-Set Passwords as Required
-Install
-Run scripts as requested as root, if you get a prompt to overwrite files in /usr/local/sbin/ you can overwrite them. Database ConfigurationWe need to set a number on ENVVARs across the server for the zabbix, apache and oracle users. I spent a good 2 days trying to figure out why zabbix_server couldn’t see the database. I would suggest either logging out and logging back in after setting these as we’ll be using them shortly, or just set them manually.
cat >>/etc/profile << EOFexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1export ORACLE_SID=orclexport EDITOR=vimexport ORACLE_BASE=/u01/app/oracleexport TNS_ADMIN=$ORACLE_HOME/network/adminexport PATH=$PATH:$ORACLE_HOME/binEOFcat >> /etc/sysconfig/httpd << EOFexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1export ORACLE_SID=orclexport EDITOR=vimexport ORACLE_BASE=/u01/app/oracleexport TNS_ADMIN=$ORACLE_HOME/network/adminexport PATH=$PATH:$ORACLE_HOME/binEOFLast thing we need to do is set the database to startup automatically.
In your /etc/oratab you will have something similar to the following, change the :N at the end of the line to be a :Y.
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y
Then we need to add the following script to startup the database on boot:
cat >> /etc/init.d/oraboot << EOF#!/bin/sh# chkconfig: 345 99 10# description: Oracle auto start-stop script.## Set ORA_HOME to be equivalent to the $ORACLE_HOME# from which you wish to execute dbstart and dbshut;## Set ORA_OWNER to the user id of the owner of the# Oracle database in ORA_HOME.ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1ORA_OWNER=oracleif [ ! -f $ORA_HOME/bin/dbstart ]then echo "Oracle startup: cannot start" exitficase "$1" in 'start') # Start the Oracle databases: # The following command assumes that the oracle login # will not prompt the user for any values su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" su - $ORA_OWNER -c $ORA_HOME/bin/dbstart touch /var/lock/subsys/dbora ;; 'stop') # Stop the Oracle databases: # The following command assumes that the oracle login # will not prompt the user for any values su - $ORA_OWNER -c $ORA_HOME/bin/dbshut su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop" rm -f /var/lock/subsys/dbora ;;esacEOFchkconfig --add orabootchkconfig oraboot onZabbix InstallationDownload Zabbix from here. Setting up the DatabaseNow we need to create the Zabbix database user, give the user a quota on the tablespace and grant a few extra permissions.
sqlplus / as sysdbaCREATE USER "ZABBIXDBUSER" PROFILE "DEFAULT" IDENTIFIED BY "**********" ACCOUNT UNLOCK;GRANT "CONNECT","RESOURCE" TO "ZABBIXDBUSER";ALTER USER "ZABBIXDBUSER" QUOTA UNLIMITED ON "USERS";ALTER USER "ZABBIXDBUSER" QUOTA 0 on "SYSAUX";ALTER USER "ZABBIXDBUSER" QUOTA 0 on "SYSTEM";REVOKE UNLIMITED TABLESPACE FROM "ZABBIXDBUSER";GRANT CREATE ANY DIRECTORY TO "ZABBIXDBUSER";GRANT DROP ANY DIRECTORY TO "ZABBIXDBUSER";Exitcd zabbix-1.8.2/createsed -i 's%/home/zabbix/zabbix/create%'$PWD'%g' data/images_oracle.sqlIf you forgot to set the Character Set for the database, you can run the following command to change the nvarchar2 size.
sed -i 's/2048/2000/g' schema/oracle.sqlYou can run the following as root so long as the ENVVARs have been set. The set def off command bypasses the & character, which would normally be used in a sqlplus script to prompt you for a variable.
sqlplus zabbixdbuser/****set def off@schema/oracle.sql@data/data.sql@data/images_oracle.sqlexitcd ..PrerequisitesAdd the Oracle libraries to ldconfig
echo $ORACLE_HOME > /etc/ld.so.conf.d/oracle.confldconfigThe following are the requirements for each of the Zabbix configure options
–with-jabber
Download and install ikesemel and isekemel-devel from http://dag.wieers.com/rpm/packages/iksemel/
–with-libcurl
yum install curl curl-devel–with-net-snmp
yum install net-snmp net-snmp-devel–with-ssh2
yum install openssl-devel zlib-develDownload and install libssh2 and libssh2-devel from ftp://ftp.pramberger.at/systems/linux/contrib/rhel5/i386/
–with-ldap
yum install openldap-devel openldap–with-openipmi
yum install OpenIPMI OpenIPMI-develIf you are planning on using fping to monitor any of your hosts download the RPM from http://dag.wieers.com/rpm/packages/fping/, install and then
chmod +s /usr/sbin/fpingchmod +s /usr/sbin/fping6Compilation & Installation of zabbix_server./configure --enable-server --enable-ipv6 --with-jabber --with-libcurl --with-unixodbc --with-net-snmp --with-ssh2 --with-ldap --with-openipmi --with-oracle=$ORACLE_HOMEmake && make installchmod a+x misc/init.d/redhat/8.0/zabbix_serversed -i 's%/usr/local/zabbix/bin%/usr/local/sbin/%g misc/init.d/redhat/8.0/zabbix_servercp zabbix-1.8.2/misc/init.d/redhat/8.0/zabbix_server /etc/init.d/If you look at my previous post I had written a init script for CentOS that causes the zabbix_server service to delay on booting since the database can take a while to register with the TNS Listener.
chkconfig --add zabbix_serverchkconfig zabbix_server onadduser zabbixmkdir /etc/zabbix/cp zabbix-1.8.2/misc/conf/zabbix_server /etc/zabbix/chown -R zabbix:zabbix /etc/zabbixchmod 770 /etc/zabbixchmod 660 /etc/zabbix/*mkdir /var/log/zabbixchown zabbix:zabbix /var/log/zabbixchmod 775 /var/log/zabbixSet the following in your /etc/zabbix/zabbix_server.conf. Even though the DBHost is supposed to default to localhost, you should still set it to your hostname. The DBName must match the service name in $TNS_ADMIN\tnsnames.ora
LogFile=/var/log/zabbix/zabbix_server.log
LogFileSize=10
DBHost=zabbix
DBName=orcl.fqdn.name
DBUser=zabbixdbuser
DBPassword=*******
DBPort=1521
Su to your zabbix user then run the following to make sure that it can connect successfully to the database.
zabbix_servertail /var/log/zabbix/zabbix_server.logZabbix PHP Frontend Configurationcp zabbix-1.8.2/frontends/php/* /var/www/html -Ryum install httpd php php-devel php-bcmath php-gd php-mbstring php-ldap php-xmlwget http://pecl.php.net/get/oci8tar -xzvf oci8cd oci8-1.4.1phpize./configuremake && make installSetup your /etc/php.ini with the following:
date.timezone = Your TZ according to this.
memory_limit = 256M
max_input_time = 600
max_execution_time = 600
post_max_size = 32M
upload_max_filesize = 16M
extension=oci8.so
Lets start the service
chkconfig httpd onservice httpd startWe also need to allow the zabbix setup page to save the configuration.
chmod 777 /var/www/html/confOpen your web browser to your server. All prerequisites should pass. http://servername
When configuring the database connection, make sure that you use the same database name etc as you did in your zabbix_server.conf
Lets clean up the conf directory again
chmod 755 /var/www/html/conf