OCIEnvNlsCreate() failed

Wanted to work on an Oracle project from home last weekend, but I received this message:

Warning (2): ocilogon() [function.ocilogon]: OCIEnvNlsCreate() failed. There is something wrong with your system – please check that ORACLE_HOME and LD_LIBRARY_PATH are set and point to the right directories [CORE/cake/libs/model/datasources/dbo/dbo_oracle.php, line 171]

A little bit of system background here. I’m using Linux Mint 8, running oracle-xe 10.2.0.1-1.1. oci8 was compiled via pecl, with one notable difference from my previous guide. I did not use instantclient (i.e. instantclient,/usr/lib/oracle/11.1/client/lib), so the phpinfo output for the oci8 section was quite different.

It looked like:

OCI8 Support enabled
Version 1.4.1
Revision $Revision: 293235 $
Active Persistent Connections 0
Active Connections 0
Compile-time ORACLE_HOME /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
Libraries Used -Wl,-rpath,/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -lclntsh
Temporary Lob support enabled
Collections support enabled

I did some searching. I figured it was something to do with environment variables, as my colleague had helped me to resolve the problem previously. I’d forgotten the fix, so this post will serve as a reminder, if needed.

Now ORACLE_HOME and LD_LIBRARY_PATH were defined via my .bashrc, which consists of just one line:

. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
echo $ORACLE_HOME
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
echo $LD_LIBRARY_PATH
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib:

Some people suggested using Apache’s SetEnv — which I tried — but the error did not go away. That same colleague suggested this morning I use the envvars file in Ubuntu instead, which helped to resolve my problem, at least:

Adding these two lines solves my problem:

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib:

Seriously, my journey with Oracle has always been a rocky one. I wonder why this product is so… inaccessible.

#apache, #oracle, #ubuntu

ORA-01033: ORACLE initialization or shutdown in progress

Encountered this issue when I came into the office this morning.

The verbiage indicates that Oracle is starting up (or down), but waiting a bit doesn’t cause the error to go away. Time for Google-ing.

sqlplus sys/xxxxxxx as sysdba

Replace the ‘xxxxxxx’ part with the actual password, of course. If the ‘sqlplus’ command is not recognized, it’s probably a problem with your envvars, but that’s a whole can of worms altogether. But I digress. Let’s see whether the db can be mounted:

SQL> alter database mount;
ERROR at line 1:
ORA-01100: database already mounted

OK, that’s a start. How about open-ing?

SQL> alter database open;
ERROR at line 1:
ORA-01113: file 1 needs media recovery

OK let’s do recovery:

ORA-01110: data file 1: '/usr/lib/oracle/xe/oradata/XE/system.dbf'
SQL> recover datafile '/usr/lib/oracle/xe/oradata/XE/system.dbf'
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> quit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Hope this helps somebody.

#oracle

OCI_COMMIT_ON_SUCCESS

I received the following error in my CodeIgniter web application today:

Notice: Use of undefined constant OCI_COMMIT_ON_SUCCESS – assumed ‘OCI_COMMIT_ON_SUCCESS’ in …

today. It turns out that, in MEPIS at least, you need the packages bc, libaio, even if you’ve compiled support for oci8. I re-added the packages, restarted Apache and it worked, once again.

#codeigniter, #oracle

Oracle and PHP5 in Debian-like systems

This post helped me install Oracle XE on my developer machine successfully! It worked for my Debian Lenny system previously, and I’ve personally verified it to work with MEPIS 8.0, Ubuntu 9.04 and Linux Mint 7, Linux Mint 8 and Xubuntu 10.04.

1. Oracle Express Edition (XE)

This step is optional if you already have an Oracle server to use, but in my experience, it’s far better to have one setup locally.

Add the following repository to your apt sources:

deb http://oss.oracle.com/debian unstable main non-free

Install Oracle XE:

wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
sudo apt-get update
sudo apt-get install oracle-xe-universal

It’s a pretty big download (~260 MB). The installation, like most in Debian systems, is a breeze. When the installer completes there is one extra step:

sudo /etc/init.d/oracle-xe configure

You’ll be asked to create a system/sys (administrative user) password, and whether or not to start Oracle on boot.

2. Oracle Instant Client

You can skip this step if Step 1 was completed. The Instant Client is required if Oracle XE is unavailable.

Downloaded the RPMs from here. You’ll need the basic, devel packages for Linux. Once the download is complete, use alien to convert .rpm into .deb. I also converted it to deb using alien (which you may not have, but can install using ‘sudo apt-get install alien’, and then installed:

# If you have alien, skip the following line
sudo apt-get install alien

# Converting and installing RPMs
sudo alien oracle*.rpm
sudo dpkg -i oracle*.deb

3. oci8 Static Object (SO)

This step is required for Oracle/PHP to play nice.

# If php-pear, php5-dev are installed, skip the following line
sudo apt-get install php-pear php5-dev

# Compling the oci8 static object (SO)
sudo pecl install oci8

At some point during this process, you’ll be prompted to input $ORACLE_HOME. If you used Step 1, input “/usr/lib/oracle/xe/app/oracle/product/10.2.0/server”. If you used Step 2, input “instantclient,/usr/lib/oracle/11.2/client/lib”. The compilation takes awhile.

4. Loading oci8.so

Save a new file as /etc/php5/conf.d/oci8.ini. Add the text “extension=oci8.so”, so that it becomes available everywhere.

5. Restart Apache

sudo /etc/init.d/apache2 restart

#debian-linux, #oracle

Oracle SQL Developer is ****

As the title says, this is a rant.

Here’s the background. I’d been assigned an (old) IBM laptop running Windows XP at work. Except I like to use my own (newer) laptop to write code. So the only reason I even boot up the laptop is to use Visio, or maybe SQL Developer.

Usually I just krdc into the database server, and run SQL Plus off it. Oh, except today, I wanted to get the length of the column names, which is not available (why?!?) in SQL Plus. Or maybe there is something else other than “desc tableName” that I don’t know about, but I digress.

I booted up the aforementioned, crusty old laptop — What specs? see below — and waited. And waited.

cpu-z

My goodness. I heard from a fellow developer that they’re gonna phase out SQL Plus (why?!?) in the next version of Oracle, but, judging by the (not sweet) performance of SQL Developer, this is not a good idea.

Of course, it’s an old laptop (2004), and I’m obviously comparing apples to oragnes, but seriously I’d take SQL Plus anytime.

#oracle

Singapore PHP User Group Meetup @ Oracle

Attended this yesterday. I was terribly late, but Blair Layton did give a good presentation on Oracle technologies. I do not have much practical experience with websites that require hardware load balancing, a hundred web/database servers so I can’t comment on what 11g brings to the table, but I’ll certain explore the developer DVD they so kindly provided.

Also demo-ed was the Oracle Application Express, which is a step-by-step (simple) web application builder. From a CSV file, a CSS-styled, one-table application with list(sortable)/add/edit/delete functionality was built in a matter of minutes. That’s pretty impressive.

If you’d like to attend more of such events, do check out: blog.php.com.sg. The presentation was videoed, so the slides/video should be up on the website fairly quickly.

#oracle, #php