TNSNAMES.ORA – Technology Geek

Regarding to oracle documentation :

TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

Example how connection define :

orcl1 =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = my-server )(PORT = 1521))
   )
(CONNECT_DATA =
   (SERVICE_NAME = orcl1)
)
)

Sometimes you can Bypass the tnsnames.ora and connect to sqlplus without even Create new connection, all you have to do is put all of the connectivity information in your connection string this type of connection called “EZCONNECT”.

sqlplus username/password@[//]host[:port][/service_name]

and to enable EZCONNECT you should add the below line in sqlnet.ora which is located in the same directory $ORACLE_HOME/network/admin
check the below examples :

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

The Below using the default listener port 1521 :

1- sqlplus scott/tiger@myservername/orcl1

Different Listener port will be like the below :

2- sqlplus scott/tiger@myservername:1522/orcl1

3- sqlplus USER/PASSWORD@//myservername:1521/orcl1

and if you want to disable EZCONNECT For Secuirty Reasons add the below line in :

NAMES.DIRECTORY_PATH=( tnsnames)

Thank you
Osama Mustafa

SQL> set ORACLE_SID=ORCL
SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1332552 bytes
Variable Size             222300856 bytes
Database Buffers           33554432 bytes
Redo Buffers                6451200 bytes

C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production 

Service "ORCL" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:59
         LOCAL SERVER
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Want Solution Do the following :

you can add the new TNS connect string (UR = A) to the tnsnames entry.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
      (UR = A)
    )
  )

if the above Solution not working Make sure your listener look like :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oraserver\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oraserver\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME=orcl)
      (ORACLE_HOME=D:\app\oraserver\product\11.2.0\dbhome_1)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

Thank you

Osama Mustafa

Tag: TNSNAMES.ORA

Connect to Oracle without Tnsnames.ora

ORA-12528 tns listener all appropriate instances are blocking new connections