CODEWITHSUNDEEP
pythondjangooracle-database
Prasanna
Prasanna

Reputation: 391

Upgrading Oracle XE from 11g to 21c broke Django's ability to talk to TNS Listener

I have an old application that is running django 1.10 with an oracle XE 11g back end. We are trying to upgrade the XE to 21c.

The issue is that django cannot find the TNS listener even though it is there and running. sqlplus works correctly so there is no issue with the listener itself.

The django application and the XE database are both running on the same server so there is no networking issue.

The main change as far as I can tell between the 11g and 21c is that the newer oracle database has plugin databases and needs to be addressed via the easy connect naming mechanism. Is that supported by django 1.10?

Directly using cx_Oracle to connect to the database from python3 also works without throwing errors.

Any pointers appreciated.

Updated with more information as requested: The settings.py in django defines the DB section as:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'centos8/XEPDB1'
        'PORT' : '1521',
        'HOST' : '127.0.0.1',
        'USER' : 'raxak3',
        'PASSWORD' : '*****', #blanked for security
        'CONN_MAX_AG' : None,
        'OPTIONS' : {
            'threaded' : True,
        }
    } }

I have tried setting the name in various ways: @centos8/XEPDB1, //centos8/XEPDB1, etc. but cannot figure out the right way to address the DB.

Using sqlplus works:

(raxak) [raxak@centos8 raxak]$ sqlplus raxak3@centos8/XEPDB1

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Dec 3 13:26:01 2021 Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Enter password: Last Successful login time: Tue Nov 30 2021 12:03:30 -05:00

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0

SQL>

The listener is alive and well:

[root@centos8 dbhomeXE]# lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 03-DEC-2021 13:27:21

Copyright (c) 1991, 2021, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centos8)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date
30-NOV-2021 11:48:06 Uptime 3 days 1 hr. 39 min. 14 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service
XE Listener Parameter File
/opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora Listener Log File
/opt/oracle/diag/tnslsnr/centos8/listener/alert/log.xml Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos8)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "d1f80698dc870fb5e0555054000661f1" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "xepdb1" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... The command completed successfully

But running from django gives this error:

(raxak) [raxak@centos8 raxak]$ python3 manage.py migrate Traceback (most recent call last): File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection self.connect() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 171, in connect self.connection = self.get_new_connection(conn_params) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/oracle/base.py", line 216, in get_new_connection return Database.connect(conn_string, **conn_params) cx_Oracle.DatabaseError: ORA-12541: TNS:no listener

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "manage.py", line 22, in execute_from_command_line(sys.argv) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/init.py", line 367, in execute_from_command_line utility.execute() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/init.py", line 359, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/base.py", line 305, in run_from_argv self.execute(*args, **cmd_options) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/base.py", line 353, in execute self.check() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/base.py", line 385, in check include_deployment_checks=include_deployment_checks, File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 62, in _run_checks issues.extend(super(Command, self)._run_checks(**kwargs)) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/management/base.py", line 372, in _run_checks return checks.run_checks(**kwargs) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/checks/registry.py", line 81, in run_checks new_errors = check(app_configs=app_configs) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/checks/urls.py", line 14, in check_url_config return check_resolver(resolver) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/core/checks/urls.py", line 24, in check_resolver for pattern in resolver.url_patterns: File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/utils/functional.py", line 35, in get res = instance.dict[self.name] = self.func(instance) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/urls/resolvers.py", line 310, in url_patterns patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/utils/functional.py", line 35, in get res = instance.dict[self.name] = self.func(instance) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/urls/resolvers.py", line 303, in urlconf_module return import_module(self.urlconf_name) File "/usr/lib64/python3.6/importlib/init.py", line 126, in import_module return _bootstrap._gcd_import(name[level:], package, level) File "", line 994, in _gcd_import File "", line 971, in _find_and_load File "", line 955, in _find_and_load_unlocked File "", line 665, in _load_unlocked
File "", line 678, in exec_module File "", line 219, in _call_with_frames_removed File "/u01/rp/raxak/raxakprotect/urls.py", line 22, in from users.views import RaxakManage, Terms File "/u01/rp/raxak/users/views.py", line 40, in from common import appdetails File "/u01/rp/raxak/common/appdetails.py", line 17, in from resources.views import code_version File "/u01/rp/raxak/resources/views.py", line 38, in from .helper_functions import * File "/u01/rp/raxak/resources/helper_functions.py", line 38, in cursor = connection.cursor() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 233, in cursor cursor = self.make_cursor(self._cursor()) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 204, in _cursor self.ensure_connection() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection self.connect() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/utils.py", line 94, in exit six.reraise(dj_exc_type, dj_exc_value, traceback) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise raise value.with_traceback(tb) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection self.connect() File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/base/base.py", line 171, in connect self.connection = self.get_new_connection(conn_params) File "/home/raxak/.virtualenvs/raxak/lib/python3.6/site-packages/django/db/backends/oracle/base.py", line 216, in get_new_connection return Database.connect(conn_string, **conn_params) django.db.utils.DatabaseError: ORA-12541: TNS:no listener

Upvotes: 0

Views: 822

Answers (1)

Prasanna
Prasanna

Reputation: 391

Finally found the issue:

I had a prod.py and a dev.py in settings, and since I was only invoking the prod.py I did not notice that dev.py still had the older, non PDB addressing in the DATABASES definition. Usually both these are identical. All changes I was testing with in prod were being silently being overridden.

Found the issue by putting print statements in

/home/raxak/.virtualenvs//lib/python3.6/site-packages/django/db/backends/oracle/base.py

just before the Database.connect(..) statement in get_new_connection(..) to see what it was trying to connect with.

The final DATABASES definition that worked was:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',                                   
        'NAME' : '127.0.0.1:1521/XEPDB1',
        'PORT' : '',
        'HOST' : '',
        'USER' : <username>,
        'PASSWORD' : <password>,
        'CONN_MAX_AGE' : None,
        'OPTIONS' : {
            'threaded' : True,
        }
    }

Thanks to all who helped, particularly Christopher Jones,

Upvotes: 1

Related Questions