Hi there,
work was not getting better and getting Oracle to work was on hold. So I decided to push a night of coding after I had prepared the MERGE queries.
And yes, it was successful – initial support for Oracle is done!
Oracle driver requirements
You”ll catch the problems with libdbi and Oracle in older blog posts. The new driver proposed by myself was ocilib, developed by Vincent Rogier. Currently it’s kind of a break up, so you need to have the libdbi installed and then install ocilib as an add-on (and Oracle libs and includes i.e. the Oracle Instant Client). As far as I know ocilib is not in the repositories right now so get the latest version and compile them yourself (documentation is really good!).
Modifying Configure for Oracle
If you enable IDOUtils during configure you now have the opportunity to use the flag –enable-oracle – if you didn’t install ocilib to default path /usr/local/ [lib/include] you can use
--with-ocilib-lib=/path/to/ocilib/lib
--with-ocilib-inc=/path/to/ocilib/include
to point configure to ocilib. It will be linked at runtime so you do not need to tell configure where $ORACLE_HOME and $LD_LIBRARY_PATH relays. Configure will output the following
- export LD_LIBRARY_PATH in ido2db Initscript where ocilib resides (OCI_IMPORT_RUNTIME)
- enable #define USE_ORACLE and ocilib.h for the compiler
- create ido2db.cfg-sample with Oracle support
After that you can perform a normal install.
Whencompiling the code it heavily depends on #define USE_ORACLE – if you plan to change back to another RDBM using libdbi you’ll have to issue
# make distclean
# make clean
# ./configure --enable-idoutils
Oracle Database Setup
In module/idoutils/db/ you will find oracle.sql and oracle-drop.sql. The first one creates the table definitions, a time conversion function and all the triggers and sequences needed for commonly used insert ids. oracle-drop.sql is just for testing purposes and cleans the database scheme.
Make sure you setup the Oracle DB with an appropriate scheme with username/password. Then copy oracle.sql to your $ORACLE_HOME at the db server and import it e.g. by using sqlplus
# su - oracle
$ sqlplus dbuser/dbpass
SQL> @oracle.sql
Then edit your ido2db.cfg for using Oracle. Please note that Oracle ignores the db_host, instead point db_name to //DBSERVER/DBNAME
db_servertype=oracle
db_port=1521
db_user=icinga
db_pass=icinga
That should do the trick. If you are experiencing problems turn the debug_level=-1 and debug_verbosity=2 and make sure the max_debug_file_size is set to at least 100 MB – the improved debug output will put a lot of output into that file.
Changes to the code
The biggest part has been done already getting Postgres to work – rewrite the INSERT OR UPDATE queries and extract the unique constraints for UPDATE conditions. Those queries have been adapted to use the MERGE trick. Some queries tried to issue an UPDATE where the unique constraint contained a value to be updated too. That failed heavily but introduces a really nice feature of ocilib.
By using OCI_Initialize it is possible to register an error handler function. This function simply gets the last OCI error and writes that to syslog and debug output. So it is really easy to find out why queries will fail and the nice thing is – the function doesn’t need to be called everywhere, just register it to ocilib.
Another heavy task was getting the insert id – MySQL supports last_insert_id but Postgres and Oracle don’t. In Postgres it’s rather easy defining the PK as SERIAL and getting the sequence id in order to get the last insert id. For Oracle, there are several ON INSERT TRIGGERs defined in oracle.sql which auto increment the id (primary key). Given that a specified function reads that values from the opened session.
Simply said, you do the following for an Oracle DB query in IDOUtils
- OCI_Initialize(ido2db_ocilib_err_handler, NULL, OCI_ENV_DEFAULT)
- oci_connection = OCI_ConnectionCreate(dbname,username, password, OCI_SESSION_DEFAULT);
- oci_statement = OCI_StatementCreate(oci_connection);
- OCI_ExecuteStmt(oci_statement, MT(“SELECT * FROM ….”));
- OCI_Commit(oci_connection);
- oci_resultset = OCI_GetResultset(oci_statement);
- instance_id = OCI_GetUnsignedInt(idi->dbinfo.oci_resultset, 1);
- OCI_ConnectionFree(oci_connection);
- OCI_Cleanup();
There were some other minor and major changes to the code…
- changed NOW() to SYSDATE
- modify table serviceescalation_contactgroups to serviceescalationcontactgroups (30 chars max in Oracle)
- primary key only is id anstead of [tablename]_id (30 chars max in Oracle)
- dropped table_prefix – ido2db.cfg setting will be ignored
- long_output uses CLOB (Character Large Object) since varchar2 supports 4000 bytes at maximum
I’ve also added a runtime version check for ocilib – if the library contains errors and does not export symbols correctly ido2db will quit correctly.
Conclusion
This is the initial version of Oracle support for Icinga IDOUtils. It was a bunch of work but there are many things to follow:
- rewrite heavily used queries (host/service/check/status, timedevents) to prepared statements and parameter bindings
- improve housekeeping DELETE queries with partitioned COMMITs
- improve getting the insert ids
- do not depend on libdbi if –enable-oracle is used
Oracle support for IDOUtils will be in Icinga 1.0 RC – watch out for the upcoming release and have fun testing! Please report any bugs or feature requests to the mailinglists and/or our dev tracker!
Many thanks to David Schmidt for implementing the first version of NDOUtils Oracle, many ideas have been improved within here. And also many many many thanks to Vincent Rogier for implementing such a great Oracle driver within the project ocilib. It is a pleasure coding based on ocilib, reading the documentation meanwhile and getting instant support for free! :-)
Since this is the third RDBM to be maintained by only 2 Core team members, please contact us if you like to participate and/or help us improving more RDBM support! :)
