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 wares. 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.
No comments:
Post a Comment