Connecting to Oracle Database Using Service Name Instead of SID

852 views Asked by At

The idea here is to be able to connect to my Database using the service name instead of SID. Here's my tnsnames.ora:

# tnsnames.ora Network Configuration File: C:\Users\jj\Downloads\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.myDomain.int)
    )
  )
  
DB_PBD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myServiceName)
    )
  )  

And here's my listener.ora:

# listener.ora Network Configuration File: C:\Users\jj\Downloads\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Users\jj\Downloads\WINDOWS.X64_193000_db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Users\jj\Downloads\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
    )
   (SID_DESC = 
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = C:\Users\jj\Downloads\WINDOWS.X64_193000_db_home)
        (SID_NAME = orcl)
    )
   (SID_DESC = 
        (GLOBAL_DBNAME = DB_PBD)
        (ORACLE_HOME = C:\Users\jj\Downloads\WINDOWS.X64_193000_db_home)
        (SID_NAME = orcl)
    )   
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

So far, I'm able to establish the connection using ORCL, orcl.myDomain.int and DB_PBD.

However, when I try to connect to it using myServiceName which is the SERVICE_NAME of the entry DB_PBD, I get the following error:

TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

Is there something wrong with the way myServiceName is defined?

EDIT: When I viewed my service list in my database, I didn't find myServiceName:

SQL> select name from V$SERVICES;

    NAME
    ----------------------------------------------------------------
    orclXDB
    SYS$BACKGROUND
    orcl.myDomain.int
    SYS$USERS
    orclpdb

Should I add it here as well?

0

There are 0 answers