I’ve been using ansible to manage a large number of oracle databases spread across many servers and found it to be very useful. Seems most people are using ansible for much cooler, web-scale things like applications that use mongo instead of relational databases (with a nod to xtranormal). But maybe there’s someone somewhere who’s doing stuff similar to me.
I’ve developed two modules that I find very useful and use regularly myself - one for pulling facts specifically about Oracle databases and a second for running arbitrary SQL on them. The two can easily be combined in a playbook with a loop to run arbitrary SQL across all databases or even all schemas in your infrastructure and fetch results to a single central location. Note: I would personally lean away from gathering oracle facts in the central setup module. The facts module I wrote runs queries against Oracle’s data dictionary to get some extra info, and personally I wouldn’t want this running against every database in my data centers on every ansible run by default… only when explicitly needed by a playbook.
These two modules are both written in Python and use the AnsibleModule common code. I’d like them to be written well enough for core if the gods find them suitable enough some day. In the meantime, I’ve posted the code on my github and would welcome testing, feedback, and collaboration from anyone else who’s using ansible on infrastructure that has oracle databases in it.
I’m probably not the one to ask for review time now (ENOTIME) – plenty of others here and many hopefully digging Oracle things too, but I’m thinking two things that might be very good to include in core some day would be a module to create a db and user, kind of like (roughly) what we have for postgresql and mysql.
Another thing that would be also interesting is if we could get some reviews on this Oracle installer role, or updates if needed:
Thanks for this - I’ve been searching around for some oracle modules, as we have lots of oracle servers to automate! These look really useful to me.
I also have a need to be able to change oracle parameters - I was thinking doing this in a declarative way with ansible would be a nice approach - e.g. oracle_parameter: db=sid max_cursors=1000, so I will probably write a module to do that part too. I have tested your two modules and both work fine for me as sysdba. I will keep using them as I write our automation stuff and feedback any problems I find, or enhancements I need to make.
I think a nice addition would be a way to pull out JSON output from the SQL query you run - but it’s a little tricky without making modifications to the database to do it I think.
Thanks Chris. I actually have made a few updates which we're using
over here but I never pushed to the public github repository - since
you are looking at the code, I'll push those enhancements up.
I never had a chance to look closely at the galaxy roles Michael
mentioned in this thread, but those might be worth checking out.
Mikael has done a lot of good work and he's still actively developing
the code. It is able to completely provision not only single instance
databases but also RAC clusters. Recently he also has coordinated his
work with the racattack automation project, so that his ansible code
together with Alvaro's vagrant/packer code can automatically stand up
a cluster on a laptop with virtualbox. Pretty cool work that these
guys have been doing!
This is really helpful stuff. We don't really do any work with RAC yet, but might look at it more in the future perhaps. We make social care software which we support on both Oracle and SQL Server, so I am going to have to do some windows stuff too later! I think I can utilise some of the many existing powershell scripts out there to some degree though.
I want to get some utility modules working first, and get things like fact collection working, as we are managing hundreds of servers for different customers, and don't have any sort of automation at all at the moment. So I think your modules will really help with that. It will help to be able to see exactly what's on each server, and put that somewhere automatically rather than maintaining wiki pages manually.
We'll also be automating environments and deployment to weblogic, and the application upgrades, so lots to do.
The company I work for is www.corelogic.co.uk in case you are interested.
I’ve just started playing around with Ansible, so wrote a module for running any arbitrary SQL via Oracle’s Python API. I come
more from a DBA background, so I was thinking of writing various modules for…
I’m quite interested in this and in general these look pretty solid.
We have existing modules for PostgreSQL and MySQL, so I don’t think we’d want to replace those – but new things for DB2 and Oracle would be interesting – edit: on looking again, these appear to be additional postgresql modules, which seems reasonable.
I think the best step would be to get some peer review and testing from others that had DB2/Oracle setups to test with, and we could offer some feedback.
Ultimately I think I’d be seeking roughly the same kind of module interface as the other DB modules, i.e. user/database creation, etc, and I would assume schema management is usually left to migration scripts.
One of the first things I think I’d like to see is if the modules could be standardized (i.e. use_underscores not camelCase in parameters, try to match parameter names to the MySQL/PostgreSQL modules) a bit.
There are also a few typos like mentioning Oracle in the PostgreSQL module, which are minor.
The next steps are probably getting pull requests – which should probably be split into one module each, adding each to the github.com/ansible/ansible-modules-extras repo.
Another frequent question is often the installation of Oracle, and while there’s one role on Galaxy for this, I would be intersted in your experiences – did you use something based on this role, or modify something else, etc?
All the modules I’ve added are new - no point in duplicating work
If I get a chance, I’ll happily edit the params to be more consistent for _ and no uppercase/lowercase, but I prefer
to keep the ‘pg’ and ‘db2’ type of names on the params, because they are DB specific.
I’ll make the Oracle params similar though.
Most of these modules are ones I’ve added for my own POC and things I thought might be useful as future use examples.
If people find them useful, then they are there - if not, then no harm done.
Schema admin would generally be left to migration scripts yes, which was why I wrote the adhoc SQL modules. The Oracle
admin ones were really just for POC purposes (and to satisfy a specific request I had for this).
I’m going to add some backup/restore modules, but other than that, I’m more or less done now unless people have something
specific that they think might be useful.
I’ll be happy to tidy the existing ones up and fix params etc., then see how things go.
If the spirit of oracledb modules is approved, I’d like to contribute something similar for vertica.
Currently we have a vertica installer which might belong in galaxy, but there is a lot configuration which must be done via sql commands.
We have to use the vsql cli tool, but wrapped with ansible, idempotency is hard and error feedback awkward.
Settings like resource pool allocations are configured via SQL statements, so a module like vertica_sql or vsql would be helpful.
I agree. DB2 and Postgres also require a lot of configuration/installation via SQL (especially DB2). This
is one reason why I wrote the SQL script processing modules.
I had a quick chat with Michael today about the modules I have done and what was suggested is that I’ll
tidy them up - parameters etc. - then create a role for each of the DBs and load the roles into Ansible-Galaxy.
That way they can be available for use or not as people want.
I realize this is an old thread but just had the need to run arbitrary oracle against some oracle hosts and evaluate the output. Did you ever get a chance to work on this some more? I started with your oracle_runscripts.py module and added a few features. Might be worth merging them together.
Sorry, not been keeping track of this thread so missed the comments.
I think it would be great if all the features people hsve developed could be bought together into one library which could then
be put into a database role/package as I had originally planned to do and never got around to
I do not have the time to at the moment, but if no one else can, then I probably could pick it up in a month
or two.
I’m still willing to collaborate test on this as well. I can share the changes I made to Tim’s initial module if people would find it helpful. Most of my needs are around running arbitrary sql and parsing the response.
hi,
for anyone who is interested, I have also written two ansible modules for oracle.
One is to maintain oratab and one is to run queries on oracle databases.
The latter might be especially interesting, since it is using cx_Oracle (python library to connect to Oracle databases).
It might be a good basis for other modules like to create users, tables, etc.
For me it is just fine like it is now, since running queries
is just as powerfull as one might need and it has the options creates and removes, which helps in creating idempotency.
But for anyone interested, please use it for you own benefit.
By the way, I’m looking for a maintainer, since I have found a new job and I think I wil not have access to Oracle databases that much anymore.
Let me know if you would like too maintain, or if you migth have any questions about functionality.
And please use for your own benefit anyway you can…
P.s. I’m still working on the pg_hba module, which is a bit more complex since I want to sort lines from fine grained (anly applies to single IP, database and user)
to larger grained (applies too larger subnet, more databases, more users), so that the most finegrained rule hits.
Howver, apart from reading an hba file this is still very much work in progress…
