Setting up Ansible and cx_Oracle

I have been automating and centralizing dba and system administration processes using Python recently. One of the problems I solved was how to create Bash and SQL scripts to run on the remote database servers, copy them to the database servers and then execute them as another Linux account. After I got my Python scripts working perfectly, I discovered that I had re-invented Ansible ūüôā

My scripts would have been much less complex and easier to write if I had done them with Ansible in mind. So that’s how I will write them going forward. I also wanted to implement a DBA Data Warehouse so I added cx_Oracle to the mix and used it to store the admin data in a database. I have been using a local virtual machine for Python development and the database was just a local¬†installation on the VM.

Before moving to production I needed to figure out an easy way to get this stuff installed on the production Linux box. Setting it up on my VM was an adventure in Googling and trial and error. So here’s the documentation on how to set it up. You need to define the shell variables first.

export http_proxy=http://$my_userid:$my_password@$proxy_server:$port
export https_proxy=https://$my_userid:$my_password@$proxy_server:$port
wget -O - | python
easy_install pip
export ORACLE_HOME=/base/oracle/product/12102
yum install python-devel
pip install --proxy=https://$my_userid:$my_pasword@$proxy_server:$port cx_Oracle
pip install --proxy=https://$my_userid:$my_pasword@$proxy_server:$port ansible


Change SQLPlus color according to database name

I am in and out of lots of databases every day. I wanted a way to color code my session so that I could tell at a glance if I was logged on to production or non-production. So  I added this to my login.sql file:


set termout off
set timing off
COLUMN  host_cmd_col  NEW_VALUE host_cmd

          when sys_context('USERENV', 'DB_NAME')
          in (  'PROD', 'IPROD', 'KPROD',     -- rac instances
                'PA', 'PALP', 'PP2', 'BPROD', -- db server 1
                'ERP', 'QPROD', 'PR4',        -- db2 server
                'HR', 'HTA'                   -- db3 server
  else 'COLOR 70'
        END) host_cmd_col
FROM dual;

HOST  &host_cmd

COLOR [attr]

attr        Specifies color attribute of console output

Color attributes are specified by TWO hex digits -- the first corresponds to the
background; the second the foreground. Each digit can be any of the below values.

0 = Black    8 = Gray
1 = Blue     9 = Light Blue
2 = Green    A = Light Green
3 = Aqua     B = Light Aqua
4 = Red      C = Light Red
5 = Purple   D = Light Purple
6 = Yellow   E = Light Yellow
7 = White    F = Bright White

set termout on
set timing on