DEV Community

aderayevans
aderayevans

Posted on

Fixing ORA-65096 error when creating automated tests in Django using Oracle

#django #oracle #backend #aderayevans

Table of Contents

  1. Introduction
  2. Finding clues
  3. Solution
  4. References

Error: ORA-65096: invalid common user or role name in oracle

Introduction

Hello guys,
I'm new to backend and django, so i had decided to follow along with django tutorial

Here is some detail what i was using to meet and fix this error:

  • Django version 3.2.5
  • Database: Oracle Database Express Edition (XE) Release 18.4.0.0.0 (18c)
  • Windows 11

Next semester, I have a course using Oracle so I decided to use Oracle instead of using Sqlite as the Django tutorial used
I've been struggling with this line 'ORA-65096: invalid common user or role name in oracle' for two days
So I want to create this post as a guide for anyone who meet this problem as i am.

Finding clues

python manage.py test polls
Enter fullscreen mode Exit fullscreen mode

What we should get

Creating test database for alias 'default'...
System check identified no issues (0 silenced).
F
======================================================================
FAIL: test_was_published_recently_with_future_question (polls.tests.QuestionModelTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/path/to/mysite/polls/tests.py", line 16, in test_was_published_recently_with_future_question
    self.assertIs(future_question.was_published_recently(), False)
AssertionError: True is not False

----------------------------------------------------------------------
Ran 1 test in 0.001s

FAILED (failures=1)
Destroying test database for alias 'default'...
Enter fullscreen mode Exit fullscreen mode

What we actually get :(

Creating test database for alias 'default'...
    Failed (ORA-01543: tablespace 'TEST_SYSTEM' already exists)
    It appears the test database, test_system, already exists. Type 'yes' to delete it, or 'no' to cancel: yes
    Destroying old test database for alias 'default'...
    Creating test user...
    Failed (ORA-65096: invalid common user or role name)
    Got an error creating the test user: ORA-65096: invalid common user or role name
Enter fullscreen mode Exit fullscreen mode

So the program failed when trying to create a new user

Let's open sqlplus to create a new user manually, I get the same error when try to create new user
"ORA-65096: invalid common user or role name"

Why?
I am logging in as an administrator user with full privileges

And so, I do some researches and find out that

SQL> show con_name

CON_NAME
-----------------------------------
CDB$ROOT
Enter fullscreen mode Exit fullscreen mode

We are in the container 'CDB$ROOT', which is the keeper of all the PDBs that are part of the collection
PDB is a Pluggable database
All PDBs are plugged in CDB$ROOT, this structure is called a container database (CDB)
learn more

Aside with those kind of headache, all we need to know is

99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB.
Enter fullscreen mode Exit fullscreen mode
  • So we need an user have con_name is PDB to create user on that PDB 
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
--------------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
Enter fullscreen mode Exit fullscreen mode

Here you can see there is a Pluggable database named XEPDB1, because I am using Oracle XE
If you are using Oracle 12 it will be ORCLPDB

Go to solution to see how to create user with PDB

Here we go again, new error showed up

django.db.utils.DatabaseError: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Check settings.py file

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'mydatabase',
        'USER': 'mydatabaseuser',
        'PASSWORD': 'mypassword',
        'HOST': '127.0.0.1',
        'PORT': '1521',
    }
}
Enter fullscreen mode Exit fullscreen mode

Try to login to sqlplus with the user we have just created

PS D:\Workplace\Backend\mysite> sqlplus django/django

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 28 15:56:57 2021
Version 18.4.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied
Enter fullscreen mode Exit fullscreen mode

Hmm, why???

I have tried to create an user with that same username, it says

ORA-01920: user name 'DJANGO' conflicts with another user or role name
Enter fullscreen mode Exit fullscreen mode

Why ????????

Do some research
Found out we cannot use users at Pluggable Database connect to root container

And how do we connect to a specific PDB though?

You are connecting to root container by using sqlplus testtest/password where the user doesn't exist.
Instead, you can use EZConnect or you can create a TNS name to connect to the PDB.
Enter fullscreen mode Exit fullscreen mode

Ok then learn something about ezconnect syntax
sqlplus username/password@hostname:port/pdbname

PS D:\Workplace\Backend\mysite> sqlplus django/django@localhost:1521/XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 28 16:05:09 2021
Version 18.4.0.0.0

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

Last Successful login time: Wed Jul 28 2021 14:18:57 +07:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>
Enter fullscreen mode Exit fullscreen mode

Try to create user

SQL> create user test identified by test;

User created.
Enter fullscreen mode Exit fullscreen mode

Perfect

But how do we tell django using this kind of syntax though, it keep throwing errors to my face

The HOST and PORT keys need to be left out of the dictionary - else Django will try connecting with the complete "NAME" as an SID.
Enter fullscreen mode Exit fullscreen mode

Ok then
Let try to login with service name instead
Try to login with service name in sqlplus

PS D:\Workplace\Backend\mysite> sqlplus -L "django/django@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)))"

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 28 12:06:33 2021
Version 18.4.0.0.0

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

Last Successful login time: Wed Jul 28 2021 12:02:04 +07:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Enter fullscreen mode Exit fullscreen mode

Change settings.py a little bit

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'localhost:1521/XEPDB1',
        'USER': 'django',
        'PASSWORD': 'django',
    }
}
Enter fullscreen mode Exit fullscreen mode

Solution

  • Connect to sqlplus as sys 
PS D:\Workplace\Backend\mysite> sqlplus "sys AS SYSDBA"

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 28 12:47:31 2021
Version 18.4.0.0.0

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

Enter password:

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Enter fullscreen mode Exit fullscreen mode
  • Create PDB account 
SQL> alter session set container = XEPDB1;

Session altered.

SQL> create user django identified by django;

User created.

SQL> grant all privileges to django;

Grant succeeded.
Enter fullscreen mode Exit fullscreen mode
  • Create/Edit Database connection with that user databse-connection Remember to change service name to the container we have used above If you're using Oracle 12, container will be ORCLPDB, hit the connect button
  • Open file yoursite/yoursite/settings.py 
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'localhost:1521/XEPDB1',
        'USER': 'django',
        'PASSWORD': 'django',
    }
}
Enter fullscreen mode Exit fullscreen mode

If you meet this error, follow my guide here
django.db.utils.DatabaseError: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

  • Apply the migrations for app (again because we have connected to a new database) 
python manage.py migrate
Enter fullscreen mode Exit fullscreen mode
  • Run test 
PS D:\Workplace\Backend\mysite> python manage.py test polls
Creating test database for alias 'default'...
Creating test user...
System check identified no issues (0 silenced).
F
======================================================================
FAIL: test_was_published_recently_with_future_question (polls.tests.QuestionModelTests)
---------------------------------------------------------------------------
Traceback (most recent call last):
  File "D:\Workplace\Backend\mysite\polls\tests.py", line 12, in test_was_published_recently_with_future_question
    self.assertIs(future_question.was_published_recently(), False)
AssertionError: True is not False

---------------------------------------------------------------------------
Ran 1 test in 0.006s

FAILED (failures=1)
Destroying test database for alias 'default'...
Destroying test user...
Destroying test database tables...
Enter fullscreen mode Exit fullscreen mode
  • Succeed, thanks for reading

References

Tutorial link
Blogs and stackoverflow-s that helped me through this error:

Top comments (1)

Collapse
 
bsaavedrazets profile image
Bastian Saavedra •

Thanks man, I really need this post. I appreciate it

Read next

saad4software profile image

Django Rest framework with Swagger

Saad Alkentar -

akdevelop profile image

Development Changed Forever

akdevelop -

mrcaption49 profile image

Compound Trigger in Oracle SQL

Pranav Bakare -

mrcaption49 profile image

Mutating trigger in Oracle SQL

Pranav Bakare -