Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【Oracle】pyora监控oracle,在linux下Oracle client的安装

05 Aug 2019 » Oracle

Oracle client安装

基于CentOS系统,安装Oracle client。为了可以批量操纵,运维以及监控多个数据库。

1.准备Oracle客户端安装软件

instantclient-basic-linux.x64-11.2.0.4.0.zip 客户端基础安装包

instantclient-sdk-linux.x64-11.2.0.4.0.zip sdk(有的地方叫devel)

instantclient-sqlplus-linux.x64-11.2.0.4.0.zip sqlplus命令行管理工具

2.安装软件包

步骤一,建立目录/opt/oracle

mkdir -p /root/oracle

并将下载的三个文件包拷贝到/root/oracle目录下。

步骤二,创建Oracle用户

#groupadd oinstall
#useradd -g oinstall oracle
#passwd oracle  

步骤三,创建安装目录并为oracle用户授予操作权限

#mkdir -p /opt/oracle/lib
#mkdir -p /opt/oracle/sdk
#mkdir -p /opt/oracle/network/admin
#chown –R oracle:oinstall /opt/oracle
#chmod –R 775 /opt/oracle 

步骤四,解压安装client软件

#cd /root/oracle
#unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
#unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip  
#cd instantclient_11_2
#mv * /opt/oracle/lib
#unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip 
#cd instantclient_11_2
#mv * /opt/oracle/sdk 

步骤五,配置环境变量

vi /etc/profile

在打开的文件中添加如下记录:
export ORACLE_HOME=/opt/oracle
export LD_LIBRARY_PATH=/opt/oracle/lib
export TNS_ADMIN=/opt/oracle/network/admin

source /etc/profile

步骤六,配置tns文件

tnsnames.ora配置:

mkdir -p /opt/oracle/network/admin

tnsnames.ora

cnic =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.xxx.xx)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = oradb)
     )
   )

最终,测试oracle client连接

[root@localhost lib]# ./sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 5 10:22:03 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn arpdba/xxxx@cnic
Connected.
SQL> 
如果出现:Connected 。连接成功。 

Python监控oracle表空间

步骤1,安装pip

在linux环境下,默认没有安装pip,通过下列操作安装pip

wget   https://bootstrap.pypa.io/ez_setup.py 

[root@oradb pip-9.0.1]# python setup.py install


wget https://pypi.python.org/packages/11/b6/abcb525026a4be042b486df43905d6893fb04f05aac21c32c638e939e447/pip-9.0.1.tar.gz#md5=35f01da33009719497f01a4ba69d63c9
    
tar zxvf pip-9.0.1.tar.gz 
cd pip-9.0.1
python setup.py install

步骤2,安装cx_oracle

pip install cx_Oracle

安装cx_Oracle(python连接oracle的包)

wget http://downloads.sourceforge.net/project/cx-oracle/5.1.2/cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm

rpm -ivh cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm

步骤3,编写脚本,测试链接oracle数据库

oracle01.py如下:

import cx_Oracle

conn = cx_Oracle.connect('arpdba/xxxx@172.31.xxx.34/xxx')
##conn = cx_Oracle.connect('system/xx@172.17.xx.204/oradb')
curs = conn.cursor()
sql = 'select * from product_component_version'
curs.execute(sql)

for result in curs:
    print(result)

curs.close()
conn.close()

效果如下:

[root@localhost oracle]# python oracle01.py 
('NLSRTL ', '11.2.0.4.0', 'Production')
('Oracle Database 11g Enterprise Edition ', '11.2.0.4.0', '64bit Production')
('PL/SQL ', '11.2.0.4.0', 'Production')
('TNS for Linux: ', '11.2.0.4.0', 'Production')

步骤3,使用pyora监控oracle

运用pyora.py,效果:不用配置tns。自动可以连接数据库做监控操作.

[root@localhost oracle]# python pyora.py --username system --password xx --address 172.30.xx.34 --database oradb version

[root@localhost oracle]# python pyora.py --username arpdba --password xx --address 172.31.xx.34 --database oradb tablespace SYSTEM
4

server端,验证pyora脚本

[root@localhost sbin]# yum install zabbix-get
[root@localhost sbin]# zabbix_get -s 172.31.1.xx -p 10050 -k "system.cpu.load[all,avg1]"
0.140000
[root@localhost ~]# zabbix_get -s 172.31.1.xx -k "pyora[arpdba,arp7101,172.31.xx.34,oradb,version]"
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production