Thursday, February 23, 2017

Nerd Food: Northwind, or Using Dogen with ODB - Part I

Nerd Food: Northwind, or Using Dogen with ODB - Part I

Thanks to my first Dogen paying customer, I finally got a chance to work with ODB - Code Synthesis' amazingly interesting C++ Object-Relational Mapping tool, built on the back of the GCC plugin system. I've personally always been in awe of what Boris Kolpackov has achieved, and, of course, it being a South African company made me all the more keen to use their products. More importantly: the product just seems to rock in terms of polish, features and documentation.

Astute readers of this blog will point out that Dogen has been supporting ODB for quite some time. That is indeed true, but since I haven't used this feature in anger, I wasn't sure how good the support really was; our fairly trivial database model (Dia) explored only a small fraction of what is possible. Now that I finally had a chance to use it in production, I needed to expand the tests and try to replicate the customer's scenario as close as possible. As always in these situations, there was a snag: instead of using PostgreSQL - the RDBMS I had originally used in my Dogen tests - they were using Oracle. So my first task was to setup Oracle locally on my beloved Debian Linux.

Never one to miss an opportunity, I decided this adventure was worthy of a quick blog post; it soon turned out to be a series of posts, if I was to do any justice to this strange and wild experiment, through all of its twists and turns. But hopefully it is worth the effort, as it also demonstrates what you can do with Dogen and ODB. And so, here we are.

The first part of the series deals with just trying to convince Oracle to run on Debian Testing - something that obviously Oracle does not support out of the box.

Before we proceed, a word to the wise: this is a highly experimental Oracle setup, which I embarked upon just because I could; follow it at your own peril, and do not use it anywhere near production. More generally, if you want to stick to the beaten track, use Oracle on RHEL, CentOS or - god forbid - even Windows. All of that said, if like me, you are a Debian person, well, there's not much for it other than to fire off a VM and start looking for those damned faint tracks in the jungle.

Alien Worlds

The very first stumbling block was Oracle itself. After all, for all the many years of using this RBDMS at work - more than I care to admit in polite company - I suddenly realised I actually never used it at home. Or course, Oracle has supported Linux for a little while now; and the good news is they have a "free" version available: Oracle Database Express Edition (XE). A quick glance at the Oracle website revealed RPM's for 64-bits (Intel only, of course). So before anything else, I decided to brush up my knowledge of Alien.

Alien is a debian package that converts RPMs into DEBs. I've used it in the past for another (lovely) Oracle technology: Java. It had worked wonderfully well then so I thought I'd give it a try. The Ubuntu Alien HowTo is pretty straightforward, and so is Debian's. Basically, obtain Alien:

sudo apt-get install alien

And then apply it to the RPM in question. So the next quest was obtaining those darn RPMs.

Of course, once you move away from the easy world of Free and Open Source Software, things start to get a bit more complicated. Those lovely links you can easy Google for don't actually work until you sign up for an Oracle developer account, asking all sorts of personal information. Sadly, even listening to Tesla earnings conferences requires registering these days. Undaunted, I filled all required fields, obtained my developer account and returned to download my loot. For Oracle Express it's rather straightforward: there is a grand-total of one package for Linux 64-bit, so you can't really go wrong. Here's the link, just in case:

  • Oracle Express: download the 64-bit Linux RPM oracle-xe-11.2.0-1.0.x86_64.rpm.zip.

It is interesting that they decided to zip the RPM but you can easily unzip it with the unzip tool. The contents are the RPM Alien expects, plus a few oracle specific files which I decided to ignore for now:

$ unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
Archive:  oracle-xe-11.2.0-1.0.x86_64.rpm.zip
   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

From a quick glance at the instructions, it appeared the Oracle Express package contained just the database server - that meant it did not include a command line client, or the APIs to build applications that talk to the database. To be fair, this is not an entirely uncommon approach; Debian also packages the PostgreSQL server separately from the development libraries. But behind apt-get and synaptic, installation of packages is all so trivial. Not so when you have to go through lots of detailed explanations of different packages and variations. But; onwards! In the Instant client page, I settled on the downloading the following:

  • Basic: client shared libraries. Package: oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
  • SQL Plus: command-line client. Package: oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
  • SDK: header files to compile code. Package: oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

Update: As it turns out, I was wrong on my original expectations, and you don't really need the SQL Plus package - its already included with Oracle Express. But I only figured it out much later, so I'll leave the steps as I originally followed them.

With all of these packages in hand, I swiftly got busy with Alien, only to also rather swiftly hit an issue:

$ cd Disk1
$ alien --scripts oracle-xe-11.2.0-1.0.x86_64.rpm
Must run as root to convert to deb format (or you may use fakeroot).

Yes, sadly you cannot run alien directly as an unprivileged user. I did not wish to start reading up on FakeRoot - seems straightforward enough, to be fair, but hey - so I took the easy way out and ran all the Alien commands as root. Note also the --scripts to ensure the scripts will also get converted across. This will bring us some other… interesting issues, shall we say, but seems worthwhile doing.

Quite a few seconds later (hey, it was a 300 MB RPM!), a nice looking DEB was generated:

# alien --scripts oracle-xe-11.2.0-1.0.x86_64.rpm
oracle-xe_11.2.0-2_amd64.deb generated

A rather promising start. For good measure, I repeated the process with all RPMs, all with similar results:

# alien oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb
oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb generated

# alien oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus_12.1.0.2.0-2_amd64.deb generated

# alien  oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb generated

Voila, all DEBs generated. Of course, as the English love to say, the proof is in the pudding - whatever that means, exactly. So before one can celebrate, you should try to install the generated packages. That can be easily done with our old trusty dpkg:

# dpkg -i oracle-xe_11.2.0-2_amd64.deb
Selecting previously unselected package oracle-xe.
(Reading database ... 564824 files and directories currently installed.)
Preparing to unpack oracle-xe_11.2.0-2_amd64.deb ...
Unpacking oracle-xe (11.2.0-2) ...
Setting up oracle-xe (11.2.0-2) ...
Executing post-install steps...
/var/lib/dpkg/info/oracle-xe.postinst: line 114: /sbin/chkconfig: No such file or directory
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

Processing triggers for libc-bin (2.24-8) ...
Processing triggers for systemd (232-8) ...
Processing triggers for desktop-file-utils (0.23-1) ...
Processing triggers for gnome-menus (3.13.3-8) ...
Processing triggers for mime-support (3.60) ...
Processing triggers for mime-support (3.60) ...

As it turns out, it seems the error for chkconfig is related to setting up the service to autostart. Since this was not a key requirement for my purposes, I decided to ignore it. The remaining RPMs - or should I say DEBs - installed beautifully:

# dpkg -i oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb
Selecting previously unselected package oracle-instantclient12.1-basic.
(Reading database ... 564801 files and directories currently installed.)
Preparing to unpack oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb ...
Unpacking oracle-instantclient12.1-basic (12.1.0.2.0-2) ...
Setting up oracle-instantclient12.1-basic (12.1.0.2.0-2) ...
Processing triggers for libc-bin (2.24-8) ...
# dpkg -i oracle-instantclient12.1-sqlplus_12.1.0.2.0-2_amd64.deb
Selecting previously unselected package oracle-instantclient12.1-sqlplus.
(Reading database ... 567895 files and directories currently installed.)
Preparing to unpack oracle-instantclient12.1-sqlplus_12.1.0.2.0-2_amd64.deb ...
Unpacking oracle-instantclient12.1-sqlplus (12.1.0.2.0-2) ...
Setting up oracle-instantclient12.1-sqlplus (12.1.0.2.0-2) ...
# dpkg -i oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb
Selecting previously unselected package oracle-instantclient12.1-devel.
(Reading database ... 567903 files and directories currently installed.)
Preparing to unpack oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb ...
Unpacking oracle-instantclient12.1-devel (12.1.0.2.0-2) ...
Setting up oracle-instantclient12.1-devel (12.1.0.2.0-2) ...

Talking to the Oracle

So, at this point in time we have a bunch of stuff installed in all sorts of random (read: Oracle-like) locations. The database itself is under /u01/app/oracle/product/11.2.0/, and all the other packages seemed to have gone into /usr/lib/oracle/12.1/client64/ and /usr/include/oracle/12.1/client64/. The first task is now to start the database server. For this we can rely on the scripts we installed earlier on. However, before we proceed, one little spoiler: we need to ensure the scripts can find awk at /bin/awk (these days it lives in /usr/bin/awk). For this we can do a swift (and brutal) hack:

# ln -s /usr/bin/awk /bin/awk

Now we can configure it. I accepted all of the defaults, and setup a suitably sensible password:

# cd /etc/init.d/
# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:

Confirm the password:


Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y
y

Starting Oracle Net Listener...Done
Configuring database...
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

Notice how your port 8080 has been hogged. If you are using it for other work, you may need to move the Oracle Application Express server to some other port. At any rate, after this I could indeed see a whole load of Oracle processes running:

$ ps -ef | grep oracle
oracle   20228     1  0 22:35 ?        00:00:00 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inhe
oracle   21251     1  0 22:36 ?        00:00:00 xe_pmon_XE
oracle   21253     1  0 22:36 ?        00:00:00 xe_psp0_XE
oracle   21257     1  0 22:36 ?        00:00:00 xe_vktm_XE
oracle   21261     1  0 22:36 ?        00:00:00 xe_gen0_XE
oracle   21263     1  0 22:36 ?        00:00:00 xe_diag_XE
oracle   21265     1  0 22:36 ?        00:00:00 xe_dbrm_XE
oracle   21267     1  0 22:36 ?        00:00:00 xe_dia0_XE
oracle   21269     1  0 22:36 ?        00:00:00 xe_mman_XE
oracle   21271     1  0 22:36 ?        00:00:00 xe_dbw0_XE
oracle   21273     1  0 22:36 ?        00:00:00 xe_lgwr_XE
...

To the untrained eye, this seems like a healthy start; but for more details, there are also a bunch of useful logs under the Oracle directories:

# ls -l /u01/app/oracle/product/11.2.0/xe/config/log
ls -l /u01/app/oracle/product/11.2.0/xe/config/log
total 20
-rw-r--r-- 1 oracle dba 1369 Feb 23 22:36 CloneRmanRestore.log
-rw-r--r-- 1 oracle dba 7377 Feb 23 22:36 cloneDBCreation.log
-rw-r--r-- 1 oracle dba 1278 Feb 23 22:36 postDBCreation.log
-rw-r--r-- 1 oracle dba  227 Feb 23 22:36 postScripts.log

Now, at this point in time, if all had gone according to plan we should be able to connect to our new instance. A typical trick in Oracle is to use tnsping to validate the setup. For this we need to know what to ping, and that is where TNS Names comes in handy:

$ cat /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lorenz)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
...

The magic word is XE (the net service name, i.e. what we will be connecting against). Now we can simply do:

$ . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
$ tnsping XE

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 23-FEB-2017 22:52:04

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lorenz)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

Success! Worth noticing that the first step was to call oracle_env.sh to bring in all the required environment variables of our Oracle setup.

The final test at this stage is to ensure we can connect with SQL Plus. For this we will just rely on the SYSTEM user.

$ sqlplus SYSTEM@XE

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 23 22:56:31 2017

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

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select table_name from all_tables where rownum < 4;
select table_name from all_tables where rownum < 4;

TABLE_NAME
------------------------------
ICOL$
CON$
UNDO$

And there you go. We have an absolutely minimal, bare-bones setup of Oracle Express running on Debian Linux. Worth bearing in mind that if you want to make use of SQL Plus from within emacs you must make sure you start emacs on a shell that has all the variables defined in oracle_env.sh.

Conclusions

In this first part we simply setup Oracle Express, and the client libraries. We also managed to prove that the setup is vaguely working by connecting to it first at a low-level via TNS ping and then at a proper client level using SQL Plus. The next part will wrap things up with the Oracle setup and then move on to ODB.

Created: 2017-02-23 Thu 23:36

Emacs 25.1.1 (Org mode 8.2.10)

Validate

No comments: