Zabbix 安装配置 连接Oracle

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 Overview

For 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 Installation

In 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 Required

If 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 Installation

Run 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 pdksh

These are the kernel parameters that Oracle requires:

cat >> /etc/sysctl.conf << EOF
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.semopm = 100
kernel.sem = 250 32000 100 128
fs.file-max = 6915744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
EOF
sysctl -p

User and Groups:

groupadd oinstall
groupadd dba
useradd -m -g oinstall -G dba oracle
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app
passwd oracle

I ran into issues with the Oracle root scripts with SELinux enabled.

setenforce permissive
sed -i 's/enforcing/permissive/g' /etc/sysconfig/selinux

More Oracle User requirements:

cat >> /etc/security/limits.conf << EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

cat >> /etc/pam.d/login << EOF
session     required     pam_limits.so
EOF

cat >> /etc/profile <<EOF
if [ \$USER = "oracle" ]; then
 if [ \$SHELL = "/bin/ksh" ]; then
 ulimit -p 16384
 ulimit -n 65536
 else
 ulimit -u 16384 -n 65536
 fi
 umask 022
fi
EOF

cat >> /etc/csh.login <<EOF
if ( \$USER == "oracle" ) then
 limit maxproc 16384
 limit descriptors 65536
 umask 022
endif
EOF

ASM Installation

Install 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.rpm

We then need to configure the ASM service:

service oracleasm configure

User: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/sdb1
service oracleasm createdisk  VOL2 /dev/sdc1
service oracleasm createdisk  VOL3 /dev/sdd1
chkconfig oracleasm on

Now 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.zip
cd 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 Installation

unzip linux_11gR2_database_1of2.zip
unzip linux_11gR2_database_2of2.zip
cd 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 Configuration

We 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 << EOF
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export EDITOR=vim
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin
EOF

cat >> /etc/sysconfig/httpd << EOF
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export  ORACLE_SID=orcl
export EDITOR=vim
export  ORACLE_BASE=/u01/app/oracle
export  TNS_ADMIN=$ORACLE_HOME/network/admin
export  PATH=$PATH:$ORACLE_HOME/bin
EOF

Last 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.

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_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$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
        ;;
esac
EOF
chkconfig --add oraboot
chkconfig oraboot on

Zabbix Installation

Download Zabbix  from here.

Setting up the Database

Now we need to create the Zabbix database user, give the user a quota on the tablespace and grant a few extra permissions.

sqlplus / as sysdba
CREATE 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";
Exit

cd zabbix-1.8.2/create
sed -i 's%/home/zabbix/zabbix/create%'$PWD'%g'  data/images_oracle.sql

If 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.sql

You 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.sql
exit
cd ..

Prerequisites

Add the Oracle libraries to ldconfig

echo $ORACLE_HOME > /etc/ld.so.conf.d/oracle.conf
ldconfig

The 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-devel

Download 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-devel

If 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/fping
chmod +s /usr/sbin/fping6

Compilation & 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_HOME
make  && make install
chmod a+x misc/init.d/redhat/8.0/zabbix_server
sed -i 's%/usr/local/zabbix/bin%/usr/local/sbin/%g misc/init.d/redhat/8.0/zabbix_server
cp 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_server
chkconfig zabbix_server on
adduser zabbix
mkdir /etc/zabbix/
cp zabbix-1.8.2/misc/conf/zabbix_server /etc/zabbix/
chown -R zabbix:zabbix /etc/zabbix
chmod 770 /etc/zabbix
chmod 660 /etc/zabbix/*
mkdir /var/log/zabbix
chown zabbix:zabbix /var/log/zabbix
chmod 775 /var/log/zabbix

Set 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_server
tail /var/log/zabbix/zabbix_server.log

Zabbix PHP Frontend Configuration

cp zabbix-1.8.2/frontends/php/* /var/www/html -R
yum install httpd php php-devel php-bcmath php-gd php-mbstring php-ldap php-xml
wget http://pecl.php.net/get/oci8
tar -xzvf oci8
cd oci8-1.4.1
phpize
./configure
make && make install

Setup 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 on
service httpd start

We also need to allow the zabbix setup page to save the configuration.

chmod 777 /var/www/html/conf

Open 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