Right Outer Join

18 July 2014

Hierarchical JSON from Oracle

Filed under: Master Data Management, Oracle — Tags: , , , — mdahlman @ 14:03

Background

Semarchy manages master data hierarchies (corporate structures, product group hierarchies, employee management trees, etc.) easily with out of the box functionality. By this I mean it can validate the data, match up different sources, enrich the data from external systems, manage an audit log of changes and so forth. It’s all great stuff.  But on a recent project  I wanted to display hierarchical data using an intuitive visual interface. A plethora of visualization libraries exist, and I was leaning toward using D3 since it appears to be one of the most polished, most used, and most actively developed at the moment.

Problem

The D3 example I wanted to use is designed to accept data in JSON format. My data is in Oracle, and Oracle doesn’t provide a simple way to generate a complex JSON output.

Likely Solutions

A few people pointed me to plsql-utils, aka Alexandria, as the best starting point. It’s a really useful Oracle resource, and I spent some time investigating this idea. Morten Braten’s 2010 article about it is excellent. But in the end I didn’t find it to be the right tool for this problem. It made it very easy to take a result set and convert it to valid JSON where each row was a JSON record. But this was just tabular data as JSON, so this was not what I needed to feed into the D3 engine. I have no doubt that I could write a stored procedure which could loop through my data to get a more appropriate hierarchical structure and then use plsql-utils to convert this to JSON. But the level of effort required seemed high.

I found several references to PL/JSON. This project shows potential, but it doesn’t appear to be actively developed (as of mid 2014).

There’s an interesting answer at the greatest of all answer sites from Mike Bostock, the author of D3(!). That example is focused on converting comma separated values (CSV) data to JSON. The concepts could be applied here. But my data, though tabular, is not actually CSV. I would prefer to use the D3 sample with the smallest number of changes possible. So I would much prefer to return the data to D3 already JSON-ified if I can.

Then I found Lucas Jellema’s 2011 article about generating a JSON string directly from a query. This presented a more intuitive approach for me. He uses a common table expression (CTE) to easily create a sub-select to gather the hierarchical information along with the LIST_AGG analytic function to present it well. Clever. In the end I didn’t actually use LIST_AGG and I didn’t really use a CTE. (OK, my sample query below has a CTE… but it could be changed into a standard subquery with trivial effort.)

My Solution

In the end I decided to use Oracle’s inherent abilities to handle hierarchical information (mainly the CONNECT BY syntax) and then convert it to JSON with the additional of simple string logic. The key concepts needed in this conversion are:

  • The CONNECT BY query can return the data in a specified logical order.
  • By knowing if the next record is at a higher, lower, or equal level in the hierarchy, we can generate JSON brackets correctly.
  • We can know if the next record is at a higher, lower, or equal level in the hierarchy by using analytic windowing functions like LAG and LEAD.

Here’s the commented SQL used to return the data

WITH connect_by_query as (
  SELECT 
     ROWNUM                               as rnum
    ,FIRST_NAME || ' ' || LAST_NAME       as FULL_NAME
    ,LEVEL                                as Lvl
  FROM GD_EMPLOYEE emp1
  START WITH EMPLOYEE_NUMBER = 100
  CONNECT BY PRIOR EMPLOYEE_NUMBER = F_MANAGER
  ORDER SIBLINGS BY EMPLOYEE_NUMBER
)
select 
  CASE 
    /* the top dog gets a left curly brace to start things off */
    WHEN Lvl = 1 THEN '{'
    /* when the last level is lower (shallower) than the current level, start a "children" array */
    WHEN Lvl - LAG(Lvl) OVER (order by rnum) = 1 THEN ',"children" : [{' 
    ELSE ',{' 
  END 
  || ' "name" : "' || FULL_NAME || '" '
  /* when the next level lower (shallower) than the current level, close a "children" array */
  || CASE WHEN LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl <= 0 
     THEN '}' || rpad( ' ', 1+ (-2 * (LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl)), ']}' )
     ELSE NULL 
  END as JSON_SNIPPET
from connect_by_query
order by rnum;

Here’s an example of the data returned (formatting was added afterwards, but the data was returned exactly like this):

{
  "name": "Steven King",
  "children": [{
    "name": "Neena Kochhar",
    "children": [{
      "name": "Nancy Greenberg",
      "children": [{
        "name": "Daniel Faviet"
      }, {
        "name": "John Chen"
      }, {
        "name": "Ismael Sciarra"
      }, {
        "name": "Jose Manuel Urman"
      }, {
        "name": "Luis Popp"
      }]
    }]
  }, {
    "name": "Lex De Haan",
    "children": [{
      "name": "Alexander Hunold",
      "children": [{
        "name": "Bruce Ernst"
      }, {
        "name": "David Austin"
      }, {
        "name": "Valli Pataballa"
      }, {
        "name": "Diana Lorentz"
      }]
    }]
  }, {
    "name": "Den Raphaely",
    "children": [{
      "name": "Alexander Khoo"
    }, {
      "name": "Shelli Baida"
    }, {
      "name": "Sigal Tobias"
    }, {
      "name": "Guy Himuro"
    }, {
      "name": "Karen Colmenares"
    }]
  }]
}

With the data in that form, it was easy to implement this D3 sample inside Semarchy Convergence for MDM:

Employee Hierarchy in Semarchy MDM

The left side shows the standard tree view. Practical.
The right side shows the D3 tree visualization. Awesome.
(And practical in different ways.)

 

Here is the SQL (creates, inserts, and the complete select statement) to try it yourself:

Oracle select query to generate JSON data

 

Advertisements

6 August 2013

Oracle SQL*Loader on Amazon Linux

Filed under: Linux, Oracle — Tags: , , , , , , — mdahlman @ 14:22

SQL*Loader on Amazon Linux

I’m using Oracle on Amazon RDS. I want to load some data into it from an EC2 instance. SQL*Loader (sqlldr) is a reasonable way to load data into Oracle. Amazon agrees. But for someone who’s a little rusty with Oracle installation procedures, it’s a bit harder to get the SQL*Loader client installed than I had hoped.

Find the Oracle Client

I didn’t think this section would need to exist. But it was harder to find than one might expect.

First, don’t be fooled into thinking Oracle Database Instant Client will be instantly useful. Or even eventually useful. It doesn’t have SQL*Loader.

11g is listed under 12c

11g is listed under 12c

Second, don’t be fooled into thinking Oracle’s download page for Oracle 12c includes downloads for Oracle 12c. Well… it does… but it also includes the downloads for Oracle 11g. Go figure.

Third, don’t be fooled into thinking the lack of links to anything labeled “client” is a problem. Just follow the link “See All” to get to the client downloads. Of course. It’s even explained in the improbably punctuated note below the links, “- See All, page contains unzip instructions plus Database Client, Gateways, Grid Infrastructure, more”.

The “See All” link corresponding to “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Linux x86-64” got me to the correct spot:

Get the Oracle Client

With the link in hand it’s trivially easy to download the installer. Not so fast. It’s possible to download the installer to my laptop and then upload it to my EC2 instance. But that’s slow, and it’s a terrible waste of bandwidth. I want to download it directly onto the EC2 instance.

The problem is that the download page requires me to accept the license terms before the download link will work, but the EC2 instance has no GUI in which to easily do this.

A naive attempt like this will fail:

wget http://www.oracle.com/correct/download/link.zip

The issue addressed in a blog on My Oracle Support. I’m optimistic that it ought to work as indicated. But the solution was old, seemingly brittle (failed based on locale), and strangely unofficial feeling. I don’t need to automate the process, so it’s much easier to understand with a quick manual process.

  1. Login. Accept the license agreement. (This is done while browsing from your local machine. (This step is genuinely easy! Hooray!))
  2. Get the relevant cookie. This was harder than I expected. Chrome and Firefox store their cookies in a SQLite database. Various browser extensions and database clients allow you to get at them. But I found the Chrome extension Cookie.txt export to be the simplest way to get the info. Just click the button that the extension creates and copy the complete contents of the popup.
  3. Save the cookie information. On the Amazon EC2 instance create a new file called cookies.txt. Paste in the text copied in the previous step. (Details are left as an exercise for the reader. Use vi or cat or whatever. If you get stuck here feel free to post a comment.)
  4. Run wget using the new cookie file:
wget -x --load-cookies cookies.txt -O linux.x64_11gR2_client.zip http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_client.zip

Run the Oracle Client Installation

This final step sounds trivial… but once again I realized I needed a few sub-steps. I’m using Amazon Linux which is decidedly un-GUI. I had forgotten that the Oracle Client doesn’t have a simple interactive text version. It’s all-or-nothing silent install or GUI install.

Install x11:

sudo yum install xorg-x11-xauth
exit

Then log back in. But… don’t forget to use the -X option. I’m on Mac, so this part works easily. On Windows you can do the equivalent with PuTTY, but you’ll need to look up the details.

ssh -X -i mykey.pem ec2-user@ec2-123-456-246-579.us-west-1.compute.amazonaws.com

Test that x11 will work as intended:

sudo yum install xclock
xclock

If xclock pops up, then the Oracle Client installation should be good as well:

./client/runInstaller
Oracle Client Installer

Oracle Client Installer

And finally, don’t forget to choose the Administrator installation type. After all, the whole point was to get SQL*Loader, and that’s the only option where it’s included.

Bonus Appendix

Once you have SQLPlus installed, you’ll want rlwrap installed. It will allow you to hit the up arrow to get your command history. SQLPlus is miserable without it. The Amazon Linux repositories do not have rlwrap. But EPEL does. So here’s how to install it with a single line:

sudo yum -y install rlwrap --enablerepo=epel

Here’s a good way to transparently launch SQLPlus with rlwrap giving you access to your command history.

#Add these lines to .bashrc for both ec2-user and oracle:
alias sqlplus="rlwrap sqlplus"

Error Appendix

The first time I tried to run the install I got this error:

ubuntu@ip-10-48-138-63:~/wget_test/download.oracle.com/otn/linux/oracle11g/R2/client$ ./runInstaller
Starting Oracle Universal Installer...
...
>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-08-06_07-26-42PM. Please wait ...ubuntu@ip-10-48-138-63:~/wget_test/download.oracle.com/otn/linux/oracle11g/R2/client$ Exception in thread "main" java.lang.NoClassDefFoundError
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)

This is clearly a Java problem. It clearly has nothing to do with X11. Except… well… it does. Installing xterm or x11 (installing xorg-x11-xauth as indicated above) solved it for me.

10 July 2013

Oracle on Ubuntu

Filed under: Oracle, Semarchy — Tags: , , — mdahlman @ 11:52

Background

I mostly use Mac OS X, but I needed to install Oracle to work with Semarchy MDM. I created a VM with Ubuntu 12.04 LTS to run Oracle. But installing Oracle XE was significantly harder than I had expected, so I’m documenting what I went through for both my own future reference and to help others.

Extra background details

You can skip this if you don’t care why I chose the different pieces that I chose.

  • Oracle. Semarchy requires Oracle. This is just a demo environment, so I want Oracle Express Edition (XE). I want the current version of Oracle (11g R2 as I write this). I would like Oracle to run on Mac. It did, but it doesn’t:
    “Oracle Database 10g Release 2 [… is] fully certified on Mac OS X.” –Oracle Technology Network
    Oracle 11g Client Tools are available for Mac, but there’s no server install.” -Myself (since it’s hard to find articles or press releases announcing lack of support for something)
    Downloads for Mac OS are conspicuously absent on the Oracle XE download page. But Linux is supported: “Oracle Database Express Edition 11g Release 2 for Linux x64”.
  • Ubuntu. It’s among the most popular distributions (perhaps the most popular). I considered Ubuntu 13.04, but I settled on Ubuntu 12.04 LTS because it’s a long-term support release. I don’t have any need to be on the cutting edge with this project. I want a GUI to have flexibility to install other tools, so I chose Ubuntu Desktop over Ubuntu Server. These instructions should work equally well on Ubuntu Server. I chose 64-bit because… well, this is 2013 and 32-bit just seems wrong. Actually, Oracle XE isn’t available for 32-bit, so that wasn’t really an option. CentOS could be a reasonable choice. But in my experience they’re much more server focused. It’s great on AWS, but I don’t know how it is on the desktop. More folks in my company use Ubuntu than other releases, so I’ll benefit from their experience.
    Oracle XE does not work in Windows x64, so Windows feels like a choice destined to cause extra problems in the future.
  • VMWare Fusion. Other choices like VirtualBox would surely be fine. But I already knew Fusion, and I already had a license for it.
    Update: I exported an .ova from Fusion and imported into VirtualBox. It worked just like it’s supposed to.

Ubuntu Installation

Armed with downloads and instructions, I installed Ubuntu. Then I discovered some eccentricities of Oracle that forced me modify my Ubuntu configuration. I ended up breaking things, so it because easier to simply re-install Ubuntu. Then I had to do it again. In the end I fully documented it:

  • The best way to install Ubuntu 12.04 LTS and configure to VMware Fusion to be ready to install Oracle.
  • All or most of the article applies to all or most versions of Ubuntu… but I only tested Ubuntu 12.04.

Oracle Installation – Zoinks

This was harder than I expected. The pain of installing and configuring Oracle on Ubuntu is what motivated this article.

First, I want to give credit to this outrageously helpful article about installing Oracle on Ubuntu. I have no idea who “Dude” is, but he’s clearly The Dude. I could not possibly installed Oracle on Ubuntu without this guide. Much of my article is based directly on that one.

But… that article is really hard to read. This is partly due to formatting. Presumably the forum changed some things after the initial post, so it wasn’t Dude’s fault. But it’s really hard to read now. It also lacks some updates for Ubuntu 12.04. Dude added the relevant details in this follow-up post. But it would be much better for Ubuntu 12.04 users to have that fix incorporated into the original code.

Also, the article contains too much detail in some places. It contains multiple ways of solving some problems. As a reference, that can be useful. But in this case I prefer just a single canonical Ubuntu solution. (Get it? Canonical. That’s a little Ubuntu joke.) Most of all, it documents how to fix your Ubuntu setup when you need to make changes to accommodate Oracle. But I was in a position to anticipate these issues and simply install Ubuntu with appropriate configuration settings. So the “Oracle Install” part of my document can focus on installing Oracle. I moved the “Configuring Ubuntu” sections into a separate Installing Ubuntu article.

So with a well-configured Ubuntu 12.04 machine as your starting point, let’s install Oracle 11g XE.

Launch Terminal

Oracle 11g Express Edition requires additional software that is not installed by default:

sudo apt-get install alien libaio1 unixodbc

If you followed my Ubuntu install you have plenty of swap space. Whether you installed like that or not, you should confirm that you actually have plenty of swap space.

cat /proc/meminfo | grep -i swap
SwapCached: 0 kB
SwapTotal: 3879932 kB
SwapFree: 3879932 kB

Yep. 3879932 kB is nearly 4 GB. Oracle XE requires 2 GB.
If you do not have at least 2 GB swap space, then fix it before proceeding. The awesome post I mentioned above is a good source to help with that.

Modify Kernel Parameters

Oracle 11gR2 Express Edition requires the following kernel parameters.

Log in as root:

sudo su -

Cut & paste the following directly into a command shell (not a text editor):

cat > /etc/sysctl.d/60-oracle.conf <<-EOF
# Oracle 11g XE kernel parameters
fs.file-max=6815744
net.ipv4.ip_local_port_range=9000 65500
kernel.sem=250 32000 100 128
# kernel.shmmax=429496729
kernel.shmmax=107374183
EOF

Log out from being root:

exit

Load and verify the new kernel parameters:

sudo service procps start
sudo sysctl -q fs.file-max
sudo sysctl -q kernel.shmmax
sudo sysctl -q net.ipv4.ip_local_port_range
sudo sysctl -q kernel.sem

The SHMMAX kernel parameter defines the upper memory limit of a process. It is a safeguard to stop a bad process from using all memory and causing RAM starvation. The Linux default is 32 MB. The official Oracle XE installation documentation suggests a value of 4 GB – 1 byte (429496729 bytes). Since Oracle 11g XE has a 1 GB memory limit, a smaller footprint will be a better safeguard for the complete system. Setting the SHMMAX parameter to 107374183 will be sufficient.

Oracle Home Directory

This should already be well configured. Confirm that this is really true:

df -h /u01

This df command should have a result similar to this:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2       3.7G   72M  3.5G   3% /u01

This is important because if /u01 isn’t there, then the Oracle Installer will have big problems. If you don’t have it, then start over and re-install Ubuntu with a better configuration or else fix it. Re-installing Ubuntu is easier. But if that’s not an option for you then the fully documented, if quite complex, process to fix the existing Ubuntu instance is available.

ORA-00845: MEMORY_TARGET

Oracle 11gR2 XE under Ubuntu 12.04 will result in “ORA-00845: MEMORY_TARGET not support on this system” either at Oracle database startup or during the initial installation. Ubuntu 12.04 uses a newer version of the “systemd” system and session manager and has migrated away from /dev/shm and other common directories in favor of /run.

Here’s how to avoid the problem.
Login as root:

sudo su -

Cut & paste the following directly into a command shell (not a text editor):

cat > /etc/init.d/oracle-shm <<-EOF
#! /bin/sh
# /etc/init.d/oracle-shm
#
#
case "\$1" in
  start)
    echo "Starting script /etc/init.d/oracle-shm"
    # Run only once at system startup
    if [ -e /dev/shm/.oracle-shm ]; then
      echo "/dev/shm is already mounted, nothing to do"
    else
      rm -f /dev/shm
      mkdir /dev/shm
      # Good for Ubuntu 11. Bad for 12. Instead use this:
      # mount -B /run/shm /dev/shm
      mount --move /run/shm /dev/shm
      mount -B /dev/shm /run/shm
      touch /dev/shm/.oracle-shm
    fi
    ;;
  stop)
    echo "Stopping script /etc/init.d/oracle-shm"
    echo "Nothing to do"
    ;;
  *)
    echo "Usage: /etc/init.d/oracle-shm {start|stop}"
    exit 1
    ;;
esac
#
### BEGIN INIT INFO
# Provides:          oracle-shm
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6 
# Short-Description: Bind /run/shm to /dev/shm at system startup.
# Description:       Fix to allow Oracle 11g use AMM.
### END INIT INFO
EOF

Log out from being root:

exit

Install the oracle-shm init script that you just created:

sudo chmod 755 /etc/init.d/oracle-shm
sudo update-rc.d oracle-shm defaults 01 99

You will see a result like this:

 Adding system startup for /etc/init.d/oracle-shm ...
   /etc/rc0.d/K99oracle-shm -> ../init.d/oracle-shm
   /etc/rc1.d/K99oracle-shm -> ../init.d/oracle-shm
   /etc/rc6.d/K99oracle-shm -> ../init.d/oracle-shm
   /etc/rc2.d/S01oracle-shm -> ../init.d/oracle-shm
   /etc/rc3.d/S01oracle-shm -> ../init.d/oracle-shm
   /etc/rc4.d/S01oracle-shm -> ../init.d/oracle-shm
   /etc/rc5.d/S01oracle-shm -> ../init.d/oracle-shm

Reboot

Once the machine restarts, verify that all went well:

sudo cat /etc/mtab | grep shm

Expected (desired) result:

none /run/shm tmpfs rw,nosuid,nodev 0 0
/run/shm /dev/shm none rw,bind 0 0

If you run the command before rebooting you would see the first line with tmpfs but not the second line.

Verify the available shared memory:

sudo df -h /run/shm

The upper limit of shared memory under Linux is set to 50% of the installed RAM by default. So a good result on a machine with 2 GB of RAM allocated is the following:

Filesystem      Size  Used Avail Use% Mounted on
none            999M  152K  998M   1% /dev/shm

Machine configuration

There are a few configuration changes needed to accommodate what the Oracle installer expects to find.

The following needs to be set for compatibility:

sudo ln -s /usr/bin/awk /bin/awk

Ubuntu uses different tools to manage services and system startup scripts. The “chkconfig” tool required by the Oracle installer is not available in Ubuntu. The following will create a file to simulate the “chkconfig” tool.

Log in as root:

sudo su -

Cut & paste the following directly into a command shell (not a text editor):

cat > /sbin/chkconfig <<-EOF
#!/bin/bash
# Oracle 11gR2 XE installer chkconfig hack for Debian based Linux (by dude)
# Only run once.
echo "Simulating /sbin/chkconfig..."
if [[ ! \`tail -n1 /etc/init.d/oracle-xe | grep INIT\` ]]; then
cat >> /etc/init.d/oracle-xe <<-EOM
#
### BEGIN INIT INFO
# Provides:              OracleXE
# Required-Start:        \\\$remote_fs \\\$syslog
# Required-Stop:         \\\$remote_fs \\\$syslog
# Default-Start:         2 3 4 5
# Default-Stop:          0 1 6
# Short-Description:     Oracle 11g Express Edition
### END INIT INFO
EOM
fi
update-rc.d oracle-xe defaults 80 01
EOF

Log out from being root:

exit

Set execute  privileges for the script that you just created:

sudo chmod 755 /sbin/chkconfig

The preparation steps are complete! It’s finally time to begin the actual installation of Oracle 11g XE.

Install Oracle

Begin by unzipping the installer zip file. Most folks will do this by right-clicking and choosing “Extract Here“. But here’s the command line version for the sake of completeness (and for folks on Ubuntu Server).

cd ~/Downloads
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

The Debian Linux based package management of Ubuntu is not compatible with the Red Hat package manager this installer is delivered in. The Oracle installer needs to be converted using the following commands:

cd ~/Downloads/Disk1
sudo alien --to-deb --scripts oracle-xe-11.2.0-1.0.x86_64.rpm

This alien command took 3 minutes to run on my instance. You can delete the zip and the original installer to save space:

rm ~/Downloads/oracle-xe-11.2.0-1.0.x86_64.rpm.zip
rm ~/Downloads/Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

Install Oracle 11gR2 XE:

cd ~/Downloads/Disk1
sudo dpkg --install ./oracle-xe_11.2.0-2_amd64.deb

This dpkg command took 30 seconds to run on my instance. If you look through the output you’ll see the line, “You must run ‘/etc/init.d/oracle-xe configure’ as the root user to configure the database.” Do that next:

sudo /etc/init.d/oracle-xe configure
  • HTTP port: During the interactive configuration I set the port for Oracle Application Express to 8181 instead of the default value of 8080. I plan to install Tomcat, and I prefer to use 8080 for Tomcat instead.
  • Database listener: Keep the default value of 1521
  • Password for SYS and SYSTEM: MANAGER (It’s traditional. It’s totally insecure, but it’s simple. For my demo machine it’s perfect.)
  • Start on boot: Yes

This configure procedure took 2 minutes to run on my instance.

Set a password for the Oracle account:

sudo passwd oracle

In order to use sqlplus and other tools, the Oracle account requires specific environment variables.

Log in as oracle:

su - oracle

Copy the default account skeleton files and add the Oracle env script to .profile:

cp /etc/skel/.bash_logout ./
cp /etc/skel/.bashrc ./
cp /etc/skel/.profile ./
echo "" >>./.profile
echo '. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh' >>./.profile

Log out from being oracle. (This is important because you need to log out and log back in before the sqlplus command below will work.)

exit

Enable remote logins to the XE GUI. Log in as oracle:

su - oracle

Login as SYSDBA then execute the relevant stored procedure:

sqlplus / as sysdba
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
SQL> exit
[this exits from SQL*Plus]
exit
[this logs out from being oracle]

Lots more information is available here: Oracle® Database Express Edition Getting Started Guide

According to the Oracle documentation, the password for the INTERNAL and ADMIN Oracle Application Express user accounts is initially the same as the SYS and SYSTEM administrative user accounts. Well, I tried several times without success. [“I” refers to “Dude” in this sentence. I copied, reused, rearranged, and reformatted much of Dude’s work. But I’m not attempting to claim his work as my own. (“I” refers to “Matt” in these last two sentences.)] Reset the Apex Admin password:
Log in as oracle:

su - oracle

Log in as SYSDBA:

sqlplus / as sysdba

At the SQL prompt, execute apxxepwd.sql using the following command. You will be prompted to change the password:

SQL> @?/apex/apxxepwd.sql
SQL> exit

Now you can log in to the Apex Admin from a remote machine with this url:
http://ubuntu64oracle11gxe:8181/apex/apex_admin

Of course you can always log in locally like this:
http://localhost:8181/apex/apex_admin

It will prompt you to reset the password. It uses the most restrictive password rules that I have ever encountered. My first attempt at a password for ADMIN was ‘ADMIN’. It failed no fewer than six complexity rules. I eventually settled on this as the simplest acceptable password I could find:

abc!=XYZ1

For me, one of the most annoying things about sqlplus is that by default I cannot simply hit the up arrow to get back to the last command. Fortunately, the problem is easily solved.

First get the readline wrapper utility:

sudo apt-get install rlwrap

Then create an alias to use rlwrap with SQL*Plus:

su - oracle
cat >> ~/.bashrc <<-EOF
alias sqlplus="rlwrap sqlplus"
EOF

Log out from being oracle. (This is important because you need to log out and log back in before the new alias will take effect and sqlplus will have that wonderful up-arrow-for-retrieving-history functionality.)

exit

Your Oracle instance should now be installed and configured and ready for you to use. This post made it possible for me to write my article. I hope I have not introduced too many errors. Please let me know if you found the article helpful.

It’s time for me to do some Master Data Management.

9 July 2013

Ubuntu 12.04 on VMware Fusion

Filed under: Linux, Oracle — Tags: , , , , — mdahlman @ 07:20

Summary

Installing Ubuntu 12.04 using VMware Fusion was easy enough. But it wasn’t easy to get it exactly the way I wanted it. So I ran through the process several times to get exactly what I wanted. It might be useful for other folks investigating Ubuntu. I wrote this as the first of two articles explaining how to install Ubuntu and Oracle. The subsequent article covers installing Oracle on Ubuntu.

Background

  • Download the relevant iso file: Ubuntu 12.04 LTS 64-bit
  • The standard install is indeed very simple. But it was insufficient for nicely preparing the machine for an Oracle installation.
  • I started with this Fantastic guide to installing Ubuntu on VMware Fusion. It was very helpful, and I want to give full credit to Eirik Didriksen and Hans Petter Langtangen for this excellent document.
    One early step was confusing because Fusion has changed, but that’s a pretty minor complaint.
  • But in the end it was mainly the need for custom partions that drove me to document this Oracle-specific version of an Ubuntu installation.

Everything should apply outside of Fusion as well. If you want to install Ubuntu on a brand new machine, the same things apply. Just skip the Fusion sections.

Fusion Stuff

  • Obtain and install VMware Fusion. (Or VMware Workstation. But I tested with Fusion.)
  • Create New Virtual Machine.

New

New Virtual Machine

  • Continue without disc

Continue without disc

Continue without disc

  • Use operating system installation disc or image: ubuntu-12.04.2-desktop-amd64.iso (Note that Eirik & Hans say “Create a custom virtual machine”. This is probably an alternative path to the same destination.)

Installation media

Installation media

  • Linux / Ubuntu 64-bit

Choose operating system

Choose operating system

  • NOT “Use Easy Install”

Not easy install

Not easy install

  • NOT “Finish” but rather “Customize Settings
  • Save As: NOT the proposed name of “Ubuntu 64-bit” but rather “Ubuntu64Oracle11gXE
    The default proposed name is “Ubuntu 64-bit”. This caused me some pain later. In hindsight I found it important to avoid spaces: “Ubuntu64Oracle11gXE”.
  • This pops up the general Fusion “Settings” window.
  • Change memory from 1024 MB to 2048 MB
  • Info: My hard disk size defaulted to 20 GB; I kept this default.
  • Info: My Networking defaulted to “Share with my Mac”; I kept this default.
  • Info: I did not specify a startup device, so it starts from the [unspecified-by-me] default device.
Customize settings

Customize settings

Customize memory

Customize memory

Customize shared folders

Customize shared folders

  • Startup the VM for the first time

Start the VM

Start the VM

At this point you are done with the “Fusion” configuration. The following steps are Ubuntu configuration, so they will apply equally to anyone using Ubuntu whether it’s in a virtual machine of some sort or not.

Ubuntu Installation Stuff

  • “Install Ubuntu” (not “Try Ubuntu”. I chose English.)

Install Ubuntu

Install Ubuntu

  • “Download updates while installing” (You might as well update to the latest stuff while getting started.)
Preparing: default settings

Preparing: default settings

Preparing: my choices

Preparing: my choices

  • CRITICAL (for Oracle): “Something else”
    It’s possible to make changes later. It’s nicely documented how to fix partitions in this forum post. But I’m attempting to install everything correctly to prevent problems rather than waiting for the problems to arrive and then working around or solving them.

Installation type: something else

Installation type: something else

  • Set up partitions as shown in the detailed partitioning section below. The key idea is creating an ext3 partition.
Partitions: default setup

Partitions: default setup

Partitions: good for Oracle XE

Partitions: good for Oracle

  • “Install Now” and it does its thing. It prompts for location, keyboard, etc.
  • NOT the proposed machine name of “vmadmin-virtual-machine” but rather “Ubuntu64Oracle11gXE”.
  • For example, I used these values:
    VMadmin
    Ubuntu64Oracle11gXE
    vmadmin
    vmpass  (Ubuntu lets you know that this is weak)
Install now

Install now

Who are you

Who are you

 

  • Depending on network speed, spend a long time seeing screen with this at the bottom:
    Retrieving file 12 of 57 (26s remaining)
  • Go get coffee. I went for lunch. The install followed by the reboot takes a while.

Welcome (retrieving)

Welcome (retrieving)

  • After the initial reboot, log in and run the Update Manager. In my case there were 256 updates available, and I installed all of them. It requires another reboot.

Update manager

Update manager

Detailed Partitioning Info

Oracle 11g does not support ext4. Ubuntu 12.04’s default filesystem is ext4. Doh!
I don’t know the exact implications of using ext4 with Oracle. Maybe it would mostly work just fine. Maybe it will fail at the most inconvenient possible moment and reduce your machine to smoking pile of rubble. Using a supported file system seems like a good idea. It’s easy enough to do; it’s a lot easier to get it right when you get started rather than fixing it later, so I did that. (But fixing it later is possible.)

  • By default everything goes into one big partition: /dev/sda
    Instead of accepting that, we’ll create a few separate partitions.
  • “New Partition Table…”, Continue
Partitions: default

Partitions: default

Create new partition table

Create new partition table

  • Now you see “free space”

Free space

Free space

  • Select “free space”, “Add…”
  • 13500 MB Ext4 at / (primary)
Free space: Add...

Free space: Add…

Add Primary ext4 partition

Add Primary ext4 partition

  • Select “free space”, “Add…”
  • 4000 MB Ext3 at /u01 (primary)
    Is it important to choose ‘primary’ rather than logical? Sergey says, “it does not matter much
    If you choose logical partition then it will mount slightly differently: /dev/sda5
    I don’t know why it uses sda5 instead of sda2. I suspect that either way is perfectly fine.Is it important to choose ‘ext3’? Yes!
    That’s the main reason we’re going through this partitioning.Is it important to choose ‘u01’ for the mount point? YES!!
    It’s vitally important to use ‘u01’. The Oracle installer will rely on this.

Add Primary ext3 partition

Add Primary ext3 partition

  • Select “free space”, “Add…”
  • All remaining space (3974 MB) for the swap partition (primary, swap area, no mount point)

Add Primary swap partition

Add Primary swap partition

  • Change “Device for boot loader installation” to /dev/sda1
  • Return to the main workflow above.
    Find the step: “Install Now” and it does its thing. It prompts for location, keyboard, etc.

Perfectly partitioned

Perfectly partitioned

More Fusion Stuff

Install VMWare Tools

The VMWare tools are useful for copy files, copy/pasting with the clipboard, etc. It’s documented reasonably well in Knowledge Base Article 1022525. I won’t repeat the steps here; just don’t forget to do it.

Use a static IP address

I want my instance to always reboot with the same IP address. That makes it much easier to connect to services running on the instance. In my case I have a Tomcat instance on my Mac running Semarchy MDM pointing to this Oracle instance. By default the VM will get an IP address from the VMWare host. Depending on config settings it might get the same address as last time. In practice I found the my instance incremented its IP address by one each time. Lots of articles already exist describing this issue: this one, that one, another one, and even vmware knowledge base articles. They all say basically the same thing. And they’re all basically correct. But…

I found they were universally imprecise about what the “vm-hostname” actually is. And they universally ignored the issue of hostnames with spaces in them. If you worked through this full article, then you saw that I used a name with no spaces or special characters, Ubuntu64Oracle11gXE, even though alternative names were proposed at a few points during the process. That’s because I ran into problems with the suggested DHCP configuration options working correctly when I was on one network but getting ignored when I was on another network. I couldn’t ever track down precisely what the issue was. So  I wanted get everything correct the first time through and complete avoid trying to trouble shoot it later.

  • Find the MAC address used by the guest VM for network connectivity ifconfig
  • Update dhcpd.conf like this:
sudo vi /Library/Preferences/VMware\ Fusion/vmnet8/dhcpd.conf
####### VMNET DHCP Configuration. End of "DO NOT MODIFY SECTION" #######
# START added by mdahlman
host Ubuntu64Oracle11gXE {
    hardware ethernet 00:0c:29:cc:7e:ab;
    fixed-address 192.168.191.11;
}
# END added by mdahlman
  • Edit /etc/hosts like this:
sudo vi /etc/hosts
  • Add this line:
192.168.191.11  Ubuntu64Oracle11gXE
  • Reboot the machine and confirm that it comes up with the address 192.168.191.11
  • Now it’s time to install Oracle. (Or just enjoy your Ubuntu instance if you don’t need Oracle.)

Blog at WordPress.com.