Friday, February 24, 2017

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

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

On Part I of this series, we got our Oracle Express database up and running against Debian Testing. It involved quite a bit of fiddling but we seemed to get there in the end. In Part II we shall now finish the configuration of the Oracle database and set up the application dependencies. On Part III we will finally get to the Dogen model, and start to make use of ODB.

What's in a Schema?

The first thing we need to do to our database is add the "application users". This is a common approach to most server side apps, where we tend to have "service users" that login to the database and act upon user requests on their behalf. We can then use audit tables to stamp the user actions so we can monitor them. We can also have application level permissions that stop users from doing silly things. This is of course a step up from the applications in the nineties, where one would have one database account for each user - allowing all sorts of weird and wonderful things such as users connecting directly to databases via ODBC and Excel or Access. I guess nowadays developers don't even know someone thought this to be a good idea at one point.

When I say "database user", most developers exposed to RDBMS' immediately associate this to a user account. This is of course how most databases work, but obviously not so with Oracle. In Oracle, "users" and "schemas" are conflated, so much so it's hard to tell if there is any difference between them. For the purist RDBMS user, a schema is a schema - a collection of tables and other database objects, effectively a namespace - and a user is a user - a person (real or otherwise) that owns database objects. In Oracle these two more or less map to the same concept. So when you create a user, you have created a schema and you can start adding tables to it; and when you refer to database objects, you prefix them by the user name just as you would if they belonged to a schema. And, of course, you can have users that have no database objects for themselves, but which were granted permission to access database objects from other users.

So our first task is to create two schemas; these are required by the Dogen model which we will use as our "application". They are:

  • basic
  • northwind

As I mentioned before, I had created some fairly basic tests for ODB support in Dogen. Those entities were placed in the aptly named schema basic. I then decided to extend the schema with something a bit more meaty, which is where northwind comes in.

For the oldest readers, especially those with a Microsoft background, Northwind is bound to conjure memories. Many of us learned Microsoft Access at some point in the nineties, and in those days the samples were pure gold. I was lucky enough to learn about relational databases in my high-school days, using Clipper and dBASE IV, so the transition to Microsoft Access was more of an exercise in mapping than learning proper. And that's where Northwind came in. It was a "large" database, with forms and queries and tables and all sorts of weird and wonderful things; every time you needed something done to your database you'd check first to see how Northwind had done it.

Now that we are much older, of course, we can see the flaws of Northwind and even call for its abolition. But you must remember that in the nineties there was no Internet for most of us - even dial-up was pretty rare where I was - and up-to-date IT books were almost as scarce, so samples were like gold dust. So for all of these historic reasons and as an homage to my olden days, I decided to implement the Northwind schema in Dogen and ODB; it may not cover all corner cases, but it is certainly a step up on my previous basic tests.

Enough about history and motivations. Returning to our SQLPlus from Part I, where we were logged in as SYSTEM, we start first by creating a table space and then the users which will make use of that table space:

SQL> create tablespace tbs_01 datafile 'tbs_f01.dbf' size 200M online;

Tablespace created.

SQL> create user basic identified by "PASSWORD" default tablespace tbs_01 quota 100M on tbs_01;
User created.

SQL> create user northwind identified by "PASSWORD" default tablespace tbs_01 quota 100M on tbs_01;

User created.

Remember to replace PASSWORD with your own passwords. This is of course a very simple setup; in the real world you would have to take great care setting the users and table spaces up, including thinking about temporary table spaces and so forth. But for our simplistic purposes this suffices. Now we need to grant these users a couple of useful privileges - again, for a real setup, you'd need quite a bit more:

SQL> GRANT create session TO basic;
GRANT create session TO basic;

Grant succeeded.

SQL> GRANT create table TO basic;
GRANT create table TO basic;

Grant succeeded.

SQL> GRANT create session TO northwind;
GRANT create session TO northwind;

Grant succeeded.

SQL> GRANT create table TO northwind;
GRANT create table TO northwind;

Grant succeeded.

If all went well, we should now be able to exit the SYSTEM session, start a new one with one of these users, and play with a test table:

$ sqlplus northwind@XE

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 24 10:20:10 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> create table test ( name varchar(10) );

Table created.

SQL> insert into test(name) values ('kianda');
insert into test(name) values ('kianda');

1 row created.

SQL> select * from test;

NAME
----------
kianda

SQL> grant select on test to basic;

Grant succeeded.

SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
$ sqlplus basic@XE

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 24 10:23:04 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 * from northwind.test;

NAME
----------
kianda

This all looks quite promising. To recap, we logged in with user northwind, created a table, inserted some random data and selected it back; all looked ok. Then for good measure, we granted the rights to see this test table to user basic; logged in as that user and selected the test table, with the expected results.

At this point we consider our Oracle setup completed and we're ready to enter the application world.

Enter ODB

Setting up ODB is fairly easy, especially if you are on Debian: you can simply obtain it from apt-get or synaptic. The only slight snag is, I could not find the oracle dependencies (i.e. libodb-oracle). Likely this is because they depend on OCI, which is non-free, so Debian either does not bother to package it at all or you need some kind of special (non-free) repo for it. As it was, instead of losing myself on wild goose chases, I thought easier to build from source. And since I had to build one from source, might as well build all (or almost all) to demonstrate the whole process from scratch as it is pretty straightforward, really.

Before we proceed, one warning: best if you either use your package manager or build from source. You should probably only mix-and-match if you really know what you are doing; if you do and things get tangled up, it may take you a long while to figure out the source of your woes.

So, the manual approach. I first started by revisiting my previous notes on building ODB; as it happens, I had covered installing ODB from source previously here for version 2.2. However, those instructions have largely bit-rotted at the Dogen end and things have changed slightly since that post, so a revisit is worthwhile.

As usual, we start by grabbing all of the packages from the main ODB website:

  • odb 2.4.0-1 amd64.deb: the ODB compiler itself.
  • libodb-2.4.0: the main ODB library, required by all backends.
  • libodb-pgsql-2.4.0: the PostgreSQL backend. We don't need it today, of course, but since PostgreSQL is my DB of choice I always install it.
  • libodb-oracle-2.4.0: the Oracle backend. We will need this one.
  • libodb-boost-2.4.0: the ODB boost profile. This allows using boost types in your Dogen model and having ODB do the right thing in terms of ORM mapping. Our Northwind model does not use boost at present, but I intend to change it as soon as possible as this is a very important feature for customers.

Of course, if you are too lazy to click on buttons, just use wget:

$ mkdir odb
$ cd odb
$ wget http://www.codesynthesis.com/download/odb/2.4/odb_2.4.0-1_amd64.deb -O odb_2.4.0-1_amd64.deb
$ wget http://www.codesynthesis.com/download/odb/2.4/libodb-2.4.0.tar.gz -O libodb-2.4.0.tar.gz
$ wget http://www.codesynthesis.com/download/odb/2.4/libodb-pgsql-2.4.0.tar.gz -O libodb-pgsql-2.4.0.tar.gz
$ wget http://www.codesynthesis.com/download/odb/2.4/libodb-oracle-2.4.0.tar.gz -O libodb-oracle-2.4.0.tar.gz
$ wget http://www.codesynthesis.com/download/odb/2.4/libodb-boost-2.4.0.tar.gz -O libodb-boost-2.4.0.tar.gz

We start with the DEB, as simple as always:

# dpkg -i odb_2.4.0-1_amd64.deb
Selecting previously unselected package odb.
(Reading database ... 549841 files and directories currently installed.)
Preparing to unpack odb_2.4.0-1_amd64.deb ...
Unpacking odb (2.4.0-1) ...
Setting up odb (2.4.0-1) ...
Processing triggers for man-db (2.7.6.1-2) ...

I tend to store locally built software under my home directory, so that's where we'll place the libraries:

$ mkdir ~/local
$ tar -xaf libodb-2.4.0.tar.gz
$ cd libodb-2.4.0/
$ ./configure --prefix=/full/path/to/local
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-2.4.0'
$ make install
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-2.4.0'

Remember to replace /full/path/to/local with your installation directory. The process is similar for the other three packages, with one crucial difference: you need to ensure the environment variables are set to place all required dependencies in the include and link path. This is achieved via the venerable environment variables CPPFLAGS and LDFLAGS (and LD_LIBRARY_PATH as we shall see). You may bump into --with-libodb. However, be careful; the documentation states:

If these libraries are not installed and you would like to use their build directories instead, you can use the --with-libodb, and --with-boost configure options to specify their locations, for example:

./configure --with-boost=/tmp/boost

So if you did make install, you need the environment variables instead.

Without further ado, here are the shell commands. First boost; do note I am relying on the presence of Debian's system boost; if you have a local build of boost, which is not in the flags below, you will also need to add a path to it.

$ cd ..
$ tar -xaf libodb-boost-2.4.0.tar.gz
$ cd libodb-boost-2.4.0/
$ CPPFLAGS=-I/full/path/to/local/include LDFLAGS=-L/full/path/to/local/lib ./configure --prefix=/full/path/to/local
<snip>
config.status: executing libtool-rpath-patch commands
$ make -j5
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-boost-2.4.0'
$ make install
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-boost-2.4.0'

For PostgreSQL again I am relying on the header files installed in Debian. The commands are:

$ cd ..
$ tar -xaf libodb-pgsql-2.4.0.tar.gz
$ cd libodb-pgsql-2.4.0/
$ CPPFLAGS=-I/full/path/to/local/include LDFLAGS=-L/full/path/to/local/lib ./configure --prefix=/full/path/to/local
<snip>
config.status: executing libtool-rpath-patch commands
$ make -j5
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-pgsql-2.4.0'
$ make install
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-pgsql-2.4.0'

Finally, Oracle. For this we need to supply the locations of the downloaded drivers or else ODB will not find the Oracle header and libraries. If you recall from the previous post, they are located in /usr/include/oracle/12.1/client64 and /usr/lib/oracle/12.1/client64/lib, so we must augment the flags with those two paths. In addition, I found configure was failing with errors finding shared objects, so I added LD_LIBRARY_PATH for good measure. The end result was as follows:

$ cd ..
$ tar -xaf libodb-oracle-2.4.0.tar.gz
$ cd libodb-oracle-2.4.0
$ LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib CPPFLAGS="-I/full/path/to/local/include -I/usr/include/oracle/12.1/client64" LDFLAGS="-L/full/path/to/local/lib -L/usr/lib/oracle/12.1/client64/lib" ./configure --prefix=/full/path/to/local
<snip>
config.status: executing libtool-rpath-patch commands
$ make -j5
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-oracle-2.4.0'
$ make install
<snip>
make[1]: Leaving directory '/path/to/build/directory/odb/2.4/libodb-oracle-2.4.0'

And there you are; all libraries built and installed into our local directory, ready to be used.

Conclusion

In this part we've configured the Oracle Express database with the application users, and we sanity checked the configuration. Once that was out of the way, we built and installed all of the ODB libraries required by application code.

On Part III we will finally start making use of this setup and attempt to connect to the Oracle database. Stay tuned!

Created: 2017-02-24 Fri 12:32

Emacs 25.1.1 (Org mode 8.2.10)

Validate

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