How to solve ORA-12505 without SID

Question

I'm working on a django project, and when trying to connect to an Oracle database I get this message:

DataBaseError: ORA-12505: TNS:listener does not currently know of SID given in
connect descriptor

All the answers online have said "You need to make sure you have the correct SID", so I asked the person at my work who manages all the databases and he said "We don't have SID's for these guys. We use service names instead."

I can access the db via the command line, but trying to get django to do this is driving me crazy. Any help would be greatly appreciated. Thanks

EDIT: Here is my settings.py file. It's not too interesting... this is a fresh project.

DEBUG = True
TEMPLATE_DEBUG = DEBUG

ADMINS = ( ,
)

MANAGERS = ADMINS

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': '',
        'USER': 'a_user',                     
        'PASSWORD': 'a_password',                 
        'HOST': 'a_host',
        'PORT': '1521',
    }
}


TIME_ZONE = 'Europe/London'

LANGUAGE_CODE = 'en-us'

SITE_ID = 1

USE_I18N = True

USE_L10N = True

USE_TZ = True

MEDIA_ROOT = ''

MEDIA_URL = ''

STATIC_ROOT = ''

STATIC_URL = '/static/'

STATICFILES_DIRS = (

)


STATICFILES_FINDERS = (
    'django.contrib.staticfiles.finders.FileSystemFinder',
    'django.contrib.staticfiles.finders.AppDirectoriesFinder',
#    'django.contrib.staticfiles.finders.DefaultStorageFinder',
)


TEMPLATE_LOADERS = (
    'django.template.loaders.filesystem.Loader',
    'django.template.loaders.app_directories.Loader',
#     'django.template.loaders.eggs.Loader',
)

MIDDLEWARE_CLASSES = (
    'django.middleware.common.CommonMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    # Uncomment the next line for simple clickjacking protection:
    # 'django.middleware.clickjacking.XFrameOptionsMiddleware',
)

ROOT_URLCONF = 'sandbox.urls'

WSGI_APPLICATION = 'sandbox.wsgi.application'

TEMPLATE_DIRS = (

)

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    # 'django.contrib.admin',
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
)

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'filters': {
        'require_debug_false': {
            '()': 'django.utils.log.RequireDebugFalse'
        }
    },
    'handlers': {
        'mail_admins': {
            'level': 'ERROR',
            'filters': ['require_debug_false'],
            'class': 'django.utils.log.AdminEmailHandler'
        }
    },
    'loggers': {
        'django.request': {
            'handlers': ['mail_admins'],
            'level': 'ERROR',
            'propagate': True,
        },
    }
}

Answer 1

If you're not a DBA or don't have access to the SID for some reason, but you have access to a tsnames.ora file, then you can set up the 'NAME' key in the database set up by using the value on the left side of the equals operator. For example, if you had this entry in the tsnames.ora file:

DATABASE1 = 
    (DESCRIPTION = ...
    )

... your DATABASES variable in settings.py would be set up with 'DATABASE1' being the value of the 'NAME' key (host and port will be blank, since those are referenced by the DATABASE1 entry in your tsnames.ora file).

This, of course, assumes that you have your environment variables (ORACLE_HOME, particularly) established so that the system can access these when referencing this variable.

Answer 2

For anyone else that might face a similar issue here is another way that worked for me....if you just have the Service name and not the SID then use the following steps to setup your Django settings: 1. Get the instance name using following SQL

select sys_context('userenv','instance_name') from dual; --=><Myinstance>

1. Replace that in settings.py

   DATABASES = { 'default': { 'ENGINE': 'django.db.backends.oracle', 'NAME': '<Myinstance>', 'USER': 'a_user',
   'PASSWORD': 'a_password',
   'HOST': 'dbserver.mynetwork', 'PORT': '1521', } }

Answer 3

If you have DBA access, you can get the SID with:

select instance from V$THREAD;

Typically, it's the same name used in your TNSNAMES.ORA file (MYDB in the below case).

 MYDB =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver.mynetwork)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = MYDB.mynetwork)
     )
   )

So the settings would be:

 DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.oracle',
         'NAME': 'MYDB',
         'USER': 'a_user',                     
         'PASSWORD': 'a_password',                 
         'HOST': 'dbserver.mynetwork',
         'PORT': '1521',
     }
 }