django.db.utils.DatabaseError: Error while trying to retrieve text for error ORA-01804 when running Django with httpd

[arun6106]

Give your database version. : Oracle 18.12.0.0.0

Also run Python and show the output of:
$> python3.6 test.py
platform.platform: Linux-3.10.0-1160.6.1.el7.x86_64-x86_64-with-centos-7.9.2009-Core
sys.maxsize > 2**32: True
platform.python_version: 3.6.8

Q1. What versions are you using?

Ans.

Python 3.6.12 

OS : CentOS 7 64-bit

DB : Oracle 18c 

Django 2.2 

cx_Oracle : 8.1.0

Q2. Describe the problem

Ans. While running server with "python3 manage.py runserver" application is able to contact Oracle DB and show the Django Administration page and login also works.
But when we access the application using the Apache (HTTPD) based URL over secure SSL port, we do see the Django page and the admin page as well but Login to Admin page with Internal server error.

In the logs, we see

"django.db.utils.DatabaseError: Error while trying to retrieve text for error ORA-01804"
cx_oracle is otherwise able to connect to the database properly, another application is also using the same database behind the same httpd proxy and works fine

Q3. Show the directory listing where your Oracle Client libraries are installed (e.g. the Instant Client directory). Is it 64-bit or 32-bit? Ans. 64-bit

Q4. Show what the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) is set to?

LD_LIBRARY_PATH=/srv/vol/db/oracle/product/18.0.0/dbhome_1/lib:/lib:/usr/lib
PATH=$ORACLE_HOME/bin:/srv/vol/db/oracle/product/18.0.0/dbhome_1/lib:$PATH

Q5. Show any Oracle environment variables set (e.g. ORACLE_HOME, ORACLE_BASE).

ORACLE_HOME=/srv/vol/db/oracle/product/18.0.0/dbhome_1
TNS_ADMIN=$ORACLE_HOME/network/admin
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
ORACLE_BASE=/srv/vol/db/oracle
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/lib

Any suggestions/help is highly appreciated. Thank you

[cjbj]

Make sure the Apache environment has all those Oracle variables set. (Note that you are setting TNS_ADMIN to the default location, so you don't actually need this variable. Also you don't need CLASSPATH for cx_Oracle).

How you set the variables will depend on the Apache version. On current Oracle Linuxes you would do something like this in /etc/sysconfig/httpd:

ORACLE_HOME=/srv/vol/db/oracle/product/18.0.0/dbhome_1
LD_LIBRARY_PATH=/srv/vol/db/oracle/product/18.0.0/dbhome_1/lib
NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Be aware that variable expansion doesn't occur so you can't use $ORACLE_HOME on the right-hand side.

On older Linux releases with Apache circa version 2.2 you could use shell syntax in /etc/sysconfig/httpd like:

export ORACLE_HOME=/srv/vol/db/oracle/product/18.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[arun6106]

Hello @cjbj

Thank you for the answer.

We're using apache httpd 2.4.6 and we've declared all these variables in "/etc/sysconfig/httpd".
We had initially not done that and we were getting 64-bit library libclntsh.so not found error

So now, we're confident that it is able to read the libraries but its something else that's missing

Here's how our /etc/sysconfig/httpd file looks like.

LANG=C
ORACLE_HOME=/srv/vol/db/oracle/product/18.0.0/dbhome_1
LD_LIBRARY_PATH=/srv/vol/db/oracle/product/18.0.0/dbhome_1/lib:/lib:/usr/lib
PATH=/srv/vol/db/oracle/product/18.0.0/dbhome_1/bin:/srv/vol/db/oracle/product/18.0.0/dbhome_1/lib:$PATH
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
ORA_INVENTORY=/srv/vol/db/oracle/oraInventory
LANG=en_US.UTF-8
ORACLE_SID=TOPSID10
ORACLE_BASE=/srv/vol/db/oracle

We've expanded the values of $ORACLE_HOME variable on the right, as you suggested.

But we still get this same error ORA-01804. Any clue what we might be missing?

We can run this application with the os user, root user directly, with python3 manage.py runserver
That works fine but not this 🙄

Thanks,
Have a great day!

[cjbj]

Make sure that the ORACLE_HOME has the correct permissions to be accessed by the Apache process. Typically the /home/oracle directory doesn't let other users access files. (Though since you seem to be able to access libclntsh, this may not be a problem). Check the message files are accessible. Check you don't have Instant Client RPMs installed that may be clashing.

Unless you have some special reason, I don't see the value in setting ORACLE_BASE or ORA_INVENTORY or PATH in /etc/sysconfig/httpd And do you need ORACLE_SID set - does the app specify a connection string when it connects? If so, then you shouldn't need ORACLE_SID.

Make sure ORA_TZFILE is not set.

Triple check everything.

[arun6106]

Hi @cjbj

I found the problem :) But, thanks a lot for your valuable suggestions. Otherwise, I had almost given up on this 😅

1. First, I removed all the variable declarations from /etc/sysconfig/httpd and checked, the application was still able to access the lib files, so these were now redundant.

2. Then I undid all variable declarations I had done earlier in .localsh and .localrc files for the os users. to start from scratch,

So now, what I saw was, cx_Oracle was looking for the lib files in wrong directory
/srv/vol/db/oracle/product/18.0.0/dbhome_1/client_1/lib
instead of
/srv/vol/db/oracle/product/18.0.0/dbhome_1/lib

DPI-1047: Cannot locate a 64-bit Oracle Client library: "/srv/vol/db/oracle/product/18.0.0/dbhome_1/client_1/lib/libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
We do not have any subfolder named "client_1" inside dbhome_1
so I just created a symlink client_1 that points to dbhome_1 (its strange why it goes in and looks for client_1. its not declared anywhere) 🙄

3. So, now, this error was gone but now again ORA-01804 was coming. 😑

I had read somewhere that this error can be fixed by adding "libociei.so" but I did not have one on my instance, so I generated it using these commands:-

mkdir -p $ORACLE_HOME/rdbms/install/instantclient/light
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk igenliboci

Then I just moved this libociei.so file from
$ORACLE_HOME/instantclient to $ORACLE_HOME/lib

4. Now there was a new error (so.. progress 😉 ):
   ORA-12546 - TNS Permission Denied.

This was easy to solve 😀

I used this command to address this :-
setsebool -P httpd_can_network_connect on

And...... 😃
Here we go -

Thanks again for all your help on this case. Really appreciate it. Python isn't one of my skills so I was really struggling with all this Django Oracle setup.

[cjbj]

To me, it sounds like some environment was still set and cx_Oracle was doing its library search heuristic to help you. E.g. https://github.com/oracle/odpi/blob/v4.1.0/src/dpiOci.c#L1916 You can look at what is being searched for by setting the environment variable DPI_DEBUG_LEVEL to 64

Having to build the igenliboci target just looks 'wrong'.

But I am not the developer with the machine in front of me, and I don't know what install option you chose, or if you rebuilt cx_Oracle somehow. :)

Overall you might have found it easier just to install an Instant Client 'Basic' zip file, make sure that Apache has LD_LIBRARY_PATH set to the Instant Client dir, and make sure that ORACLE_HOME is not set.

[arun6106]

Actually, my oracle installation is there on the same host, so I didn't install any instant client or anything Oracle stuff by myself. The DBA had done that years ago as we have another application using that database.

For this setup, I just had to point the LD_LIBRARY_PATH to the ORACLE_HOME/lib of that Oracle installation.

Having to build the igenliboci - is something I also didn't want to do. Because it worked fine from another OS user without this library. But since nothing was working, ORA-01804 was persistent, I thought of giving it a try and it worked

I think this library libociei.so is bundled in the instant client but maybe not in Database installation.
Anyways, it works well.

I'll also try and do same setup on another clean centOs machine, just to be double sure of the steps taken. This would be handy when we come to deploy the application on Production

Thanks again for all your help & support 👍