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


Exadata Database Machine Administration Workshop

I spent last week in downtown Chicago attending this class. It was a very good class and the instructor, Andy Fortunak was excellent. We had lots of labs to work through. The computers we used ran¬†virtual database servers and virtual storage servers. This setup caused some problems, mainly slowness. My virtual database machine’s clusterware failed towards the end of the week but the virtual storage servers continued to work fine.

The main message was: invest the time needed to set up services and Database Resource Manager.  IO Resource Manager and Instance Caging depends on it.

We also discussed consolidation and using RAC one-node. We are consolidating scores of databases on Exadata.¬†Most of them do not require multiple RAC nodes for workload but we do want high availability and load balancing. This week I tested manually moving one-node databases between nodes and it works great. The client does not get disconnected but does get an error message. Srvctl actually makes it a two node RAC and then stops the original node.¬†I tested automatic failover of rac one-node and compared it with a full rac two-node with one instance down. They both work the way I wanted, a second instance starts up. I can’t see any technical reason for going with one-node.

Installing ASM/RAC without asmlib

I’m installing RAC (using ASM) on Red Hat Linux 6.2. Oracle has decided not to provide asmlib for Red Hat 6 and above. So I needed to figure out how to ensure correct permissions and ownership for the shared disks. Red Hat has an article on the support website which recommends using udev or multipath. I set up multipath on my VMware systems but it did not see any disks. VMware was not reporting the UUID’s of the disks to Linux. A little research on the VMware website gave me a clue, and I inserted this line into the vmx files:

disk.EnableUUID = “TRUE”

After rebooting, the multipath software could see the shared disks and automatically created the multipath devices.

CRS-2800: Cannot start resource ‘ora.asm’ as it is already in the INTERMEDIATE state on server

I got this error when I was installing Oracle RAC 11.2 on Red Hat Enterprise Linux 5.6. ¬†I was installing Clusterware¬†using ASM on VMware shared disks.¬† When I created the¬†independent persistent virtual disks, I left the “allocate¬†all disk space now” option unselected. Oracleasm¬†was happy on both¬†RAC nodes.¬†The Oracle installer was happy when it created¬†+ASM1¬†on the first RAC¬†node. But¬†when the¬†ASM instance started on node 2 it did not like the “virtually¬†provisioned” disk. The +ASM2¬†instance was not open and was complaining about one of the shared disks being corrupt at a certain byte.¬†When I checked the virtual disk files,¬†I saw that Oracle was trying to read past the end of the file.¬†I started all over with new¬†fully allocated shared disk and that fixed the problem. Everything is up and running now.