I am a danish programmer living in Bangkok.
Read more about me @ rasmus.rummel.dk.
Webmodelling Home > Oracle connection - explanation & examples

Oracle Connection Tutorial

Sep 2014. Connecting to Oracle is often a frustating experience for beginners - this tutorial provides explanation & examples of the Oracle connection process, how to connect to Oracle and how to debug Oracle connection problems.

Tutorial Content :

References :




Concepts & Acronyms

  • Oracle database : the files comprising an Oracle installation including the data files.
  • Oracle instance : a running Oracle database.
  • SID : System IDentifier : unique name of a database instance.
  • SERVICE_NAME : alias of one or more database instances (in case of replication sales could be an alias of sales1 & sales2).
  • Connect Descriptor : describes where and how to connect to an Oracle instance.
  • Net Service Name : a.k.a. TNS Name : each entry in the tnsnames.ora file is a net service name. A net service name is mapped to a connect descriptor, so then referencing the net service name in a connection string, the net service name is translated to it's mapped connect descriptor.
  • Important folders :
    • $ORACLE_BASE : default C:\app\Administrator\ : Oracle was installed by the Administrator user.
    • $ORACLE_HOME : default C:\app\Administrator\product\11.2.0\dbhome_1\ : Oracle version is 11.2.0.


Connecting Process

Then an Oracle client (eg. sql*plus, Navicat or Oracle.DataAccess.Client etc.) request a connection to an Oracle database server, it must do so using an Oracle Net Services component, which requires :

  • a host name or an IP address
  • a port number
  • a protocol (typically TCP)
  • an identifier (SID) or an alias (SERVICE_NAME) for selecting which Oracle instance on the given host.
The client pass these connection details to the Oracle Net Services component which using the host, port & protocol information will contact the Oracle Listener.

If the Listener exists, the Listener will then check if there is an Oracle instance with the supplied SID or SERVICE_NAME and if there is, the Listener will create a connection session.

The connection is created by spawning a new process on the database server and opening a new port number for the given protocol and assign that port number to the new server process. The port number will be send back to the client and the connection session is established. The session is between the client and the server and can live even if the Listener is shut down.




Oracle Network Configuration

There are 3 connection relevant network configuration files : (all found in $ORACLE_HOME\network\admin\)

  • listener.ora : server configuration.
  • tnsnames.ora : client configuration.
  • sqlnet.ora : not explored in this document.


listener.ora

(Oracle documentation)

The Oracle Net Listener, commonly referred to as the Listener, is a process (TNSLSNR.exe) on the database server that listens for client connection requests and establishes the connection.

The Oracle Net Listener is configured using the listener.ora text file.

A default Oracle installation will create 3 entries in the listener.ora text file :

  • A listener entry :
  • A SID list entry for all the instances for whom the listener is listening
  • Parameters

listener.ora

listener entry

  • listener = : name of the listener, here the name is just listener.
    • (DESCRIPTION_LIST =
      • (DESCRIPTION = : a listener entry must have a description element containing all the ADDRESS'es on which you can reach the listener.
        • (ADDRESS = (PROTOCOL=IPC)(KEY=EXTPROC1521))
        • (ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
        • (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.210)(PORT=1521))
      • )
    • )

    The listerner is listening for both interprocess calls (IPC) and for client connection requests.

    Interprocess calls are for the database instance requesting a session with Oracle CLR Host, which run in another process than the database instance (but on the same computer). The Oracle CLR Host allows Oracle to execute .NET procedures & functions (as if they were PL/SQL).

    As interprocess calls is for communicating between processes on the same computer, it gives no meaning to specify a HOST parameter, instead we need to specify a KEY parameter with a value (here EXTPROC1521) that uniquely identifies a service on this computer.

    The second ADDRESS element specifies that the listener is listening on localhost, so the listener can be contacted for a connection request by a local client (if the client is on the same computer as the database instance).

    The third ADDRESS element specifies that the listener is listening on 192.168.1.210, so any remote client must request a connection by specifying 192.168.1.210 as the host.

listener.ora

SID list entry

  • sid_list_listener = : sid_list_ + name of the listener that the SID list is for (this SID list is for the listener named listener).
    • (SID_LIST = : just a container for each SID description if there are multiple SIDs.
      • (SID_DESC = : here starts the description of a SID.
        • (SID_NAME = CLRExtProc)
        • (ORACLE_HOME = C:\app\win7-oracle\product\11.2.0\dbhome_1)
        • (PROGRAM = extproc)
        • (ENVS = "EXTPROC_DLLS=ONLY:C:\app\win7-oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      • )
    • )

    CLRExtProc is the default SID name for the Oracle CLR Host which is installed as part of the Oracle Database Extensions for .NET. The Oracle CLR Host runs in the extproc process (not the Oracle instance process) and loads an instance of the .NET CLR (Common Language Runtime).

    Oracle CLR Host makes it possible to execute procedures & functions within a .NET assembly. For Oracle to execute such a procedure on an external process, Oracle need to first contact the Listener using the IPC protocol to have the Listener setup a session more about Oracle CLR Host.

    The PROGRAM parameter is used to identify the service executable, the extproc agent. Eg if we write a PL/SQL procedure that loads an external program, we need the extproc agent to load it.

    The ENVS parameter together with EXTPROC_DLLS=ONLY is used to restrict the dll's that the extproc agent are allowed to load. If there is no ENVS parameter with EXTPROC_DLLS=ONLY, then all dll's inside $ORACLE_HOME\bin (or $ORACLE_HOME/lib on unix) could be loaded (on the other hand it is possible with EXTPROC_DLLS to load ddl's outside of $ORACLE_HOME also).

    The reason there is no SID_DESC for any Oracle instance is because default Oracle instance will register dynamically with the listener, which requires no entry in listener.ora. If we were to statically register an Oracle instance, indeed we would need to amend the above SID_LIST with a SID_DESC element for the Oracle instance (see the Listener Registration section below).

listener.ora

parameters entries

  • ADR_BASE_LISTENER = C:\app\win7-oracle : ADR_BASE_ + listener name (the value is default equal to $ORACLE_BASE) specifying the location of listener logging & tracing.

    While a default installation seems to only add the ADR_BASE_ parameter, listener.ora can contain many other parameters, see Oracle Net Listener Parameters .


tnsnames.ora

(Oracle documentation)

tnsnames.ora is a text file consisting of 1 or more net service names each of which maps to either a connect descriptor or a listener protocol address.

Example 1 : (defining a net service name mapping to a simple connect descriptor)

  • ORCL = : net service name
    • (DESCRIPTION = : starting the connect descriptor
      • (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      • (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl))
    • )

    You can see that a connect descriptor have 2 outer elements :
    • ADDRESS : describing host and port of the oracle instance as well as the protocol to use.
    • CONNECT_DATA : describing particularly the SERVICE_NAME of the oracle instance - so that multiple oracle instance can run on the same port on the same host distinguished by their SERVICE_NAME. It is also possible to use SID instead of SERVICE_NAME.

Example 2 : (defining a net service name mapping to a connect descriptor with load balancing)

  • ORCL = : net service name
    • (DESCRIPTION = : starting the connect descriptor
      • (LOAD_BALANCING=on)
      • (ADDRESS_LIST= : starting a list of possible ADDRESS'es
        • (ADDRESS = (PROTOCOL = TCP)(HOST = sales1.company.com)(PORT = 1521))
        • (ADDRESS = (PROTOCOL = TCP)(HOST = sales2.company.com)(PORT = 1521))
      • )
      • (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = sales))
    • )

    Then setting LOAD_BALANCING to on, yes or true, any Oracle client software must randomly select an ADDRESS from the ADDRESS_LIST.
    If omit or set LOAD_BALANCING to off, no or false, any Oracle client software must select an ADDRESS sequentially from the ADDRESS_LIST until one ADDRESS succeed.

    The destinction between SID >< SERVICE_NAME also becomes clear in the above example : sales is an alias for whatever database instance is selected, while a SID would uniquely identify an instance (within a host).

In addition to one or more net service name entries, you may also see a listener protocol address entry like this :

  • LISTENER_ORCL =
    • (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.213)(PORT=1521))

    This entry is only necessary if you are using dynamic listener registration on a port different from 1521 - so the above protocol address is actual unnessary because it is specifing port=1521.

    In the Common Oracle Network Configurations section below), I show how to use the listener protocol address entry in tnsnames.ora to change the listener port number.


sqlnet.ora

(Oracle documentation)

not explored in this document.




Listener Registration

Then the Listener receives a connection request from a client, the Listeners job is to identify the Oracle instance process and to setup a connection session between the requested Oracle instance and the client. There are 2 ways the Listener can know of Oracle instances :

  • Static registration : using listener.ora.
  • Dynamic registration : a.k.a. service registration : using PMON.

One thing we know for sure is : if local_listener have a value different from empty, then in tnsnames.ora there MUST be a listener protocol address with the same name as local_listener.

  • shell> sqlplus / as sysdba
  • sql> show parameter db_name;
  • sql> show parameter local_listener; : you can further set the local_listerner like this :
    1. sql> alter system set local_listener='listnerName';
    2. sql> alter system register; : persist the change (spfile will update).


Static Registration

Using static registration, we would need to add a SID_DESC element to the SID list entry in the listener.ora file like this :

  • sid_list_listener = : sid_list_ + name of the listener that the SID list is for (this SID list is for the listener named listener).
    • (SID_LIST = : just a container for each SID description if there are multiple SIDs.
      • (SID_DESC = : here starts the description of CLRExtProc SID.
        • (SID_NAME = CLRExtProc)
        • (ORACLE_HOME = C:\app\win7-oracle\product\11.2.0\dbhome_1)
        • (PROGRAM = extproc)
        • (ENVS = "EXTPROC_DLLS=ONLY:C:\app\win7-oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      • )
      • (SID_DESC = : here starts the description of the instance SID.
        • (GLOBAL_DBNAME = ORCL)
        • (SID_NAME = ORCL)
        • (ORACLE_HOME = C:\app\win7-oracle\product\11.2.0\dbhome_1)
      • )
    • )

    GLOBAL_DBNAME should match the SERVICE_NAME that clients are using in the connect descriptor (so it can really be anything). Note that it is common to set GLOBAL_DBNAME to db_name.db_domain, eg. SID.mydomain.com.

    SID_NAME is the instance SID, which must be identical to the db_name in the initialization parameter file (since your initialization parameter file depends on your configuration, it is best to lookup db_name using sql> show parameter db_name;). It is thus the SID_NAME that are used to find the Oracle instance.

    ORACLE_HOME identifies the home location of the (instance) service.

In addition we need to turn off dynamic registration, which we can do by adding the following parameter to bottom of the listener.ora file :
DYNAMIC_REGISTRATION_ListenerName = OFF



Dynamic Registration

Default an Oracle instance will register itself with the listener then the instance is starting up. The registering is done by the PMON process. PMON will register the instance information with the listener process. The registered information is mostly the same as would otherwise be needed using listener.ora :

  • Service name for each running instance of the database.
  • Instance names of the databases.
  • Service handlers available for each instance (enables the listener to create or select an appropriate server process for the connection session)
    • Dedicated server : a handle that allows the listener to create a process dedicated a single client connection.
    • Dispatcher : a handle that allows the listener to create a shared process for multiple client connections.
  • Dispatcher, instance and node load information.

Since the PMON is running then the Oracle instance is starting up, it can happen that the listener is not started yet in which case the registration will fail. While PMON will try again after a while, it can easily happen that you can see that both the database instance and the listener is started, but the listener does not know of any instance because the registration attempt failed on instance startup. You will then see the following error : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.




Connect Examples

Then a client software requests a connection, the client must be feeded a connection string, which consists of :

  • a username
  • a password
  • a connect identifier
, using the following format : connect username/password@ConnectIdentifer

The connect identifer can be either :

  • a connect descriptor or
  • a name that resolves to a connect descriptor (commonly a net service name from tnsnames.ora)

A connect descriptor can take several forms called naming methods :

  • Local naming : connect descriptors are stored in tnsnames.ora, each connect descriptor identified by a net service name.
  • Directory naming : connect descriptors are stored in an LDAP compliant server (eg. Windows Active Directory)
  • Easy Connect naming : the connect descriptor taks the form host[:port][/service_name] (details)

The connection string username/password@ConnectIdentifier can therefore have many faces :

  • scott/tiger@orcl : local naming using a net service name from tnsnames.ora.
  • scott/tiger@sales1.company.com : easy naming using only host.
  • scott/tiger@oracle.company.com:1521/sales : easy naming using port and service name (easy naming seems not to be able to handle load balancing nor failover except if all replicated oracle instances are on the same host).
  • scott/tiger@(description=(address=(protocol=tcp)(host=sales1.company.com)(port=1521))(connect_data=(service_name=sales))) : writing out the connect descriptor directly in the connection string.
  • scott/tiger@??? : I have no experience using LDAP, however Using Oracle Database with Microsoft Active Directory contains more information.

In case we cannot use easy connecting, it becomes pretty clear why using named connect descriptors is a good idea - that is exactly what net service names are : named connect descriptors.

Also note that then using easy connecting, the connect identifier is actually translated to a connect descriptor by the Oracle Net Services component.

Using sql*plus on the database server, we actually do not need to specify a ConnectIdentifier (in which case the listener is NOT in use). The following is possible on the database server with the listener stopped :

  • shell> sqlplus scott/tiger
  • shell> sqlplus / as sysdba



Common Oracle Network Configurations

While it is possible to setup Oracle in a seemingly endless number of ways, I will go through a couple of Oracle setups I have done myself (that's what I mean by 'common') and what these setups mean in terms of network configuration.

  • Changing the port number
  • Multiple Oracle Instances


Changing the port number

To change the port number on which the listener is listening, we just write that to ADDRESS element :

However, after changing the port number, the listener actually don't know the database instance, so we need to register the database with the listener - this can be done 2 ways :

  • Statically using listener.ora inserting a SID_DESC element in the SID_LIST_ entry
  • Dynamically using local parameter

Static listener registration on a non-default port

Under construction


Dynamic (PMON) listener registration on a non-default port

  1. First determine which initialization parameter file your database instance is using :
    1. shell> sqlplus / as sysdba : use sqlplus on the database server to logon to the database instance.
    2. sql> show parameter spfile : it will show you either :
      • $ORACLE_BASE\admin\SID\pfile\init.ora : you are using a text based initialization file.
      • $ORACLE_HOME\database\spfile : you are using a binary initialization file (don't edit the $ORACLE_HOME\database\spfileSID.ora file with a text editor)
  2. Change the appropriate initialization parameters :
    • If you are using the text based initalization paramater file :
      1. Open the text based initialization parameter file ($ORACLE_BASE\admin\SID\pfile\init.ora) and change the following parameter :
        • LOCAL_LISTENER=listener_alias
    • If you are using the binary initialization parameter file : (you need to use the ALTER SYSTEM SET command)
      1. shell> sqlplus / as sysdba : use sqlplus on the database server to logon to the database instance.
      2. sql> alter system set local_listener='listener_alias';
      3. NOTE : this will fail if there is no protocol address entry for the listener alias in tnsnames.ora - therefore in case of a binary parameter file, you need to do step 3 first
  3. listener_alias now needs to be resolved to a protocol address, which can be done using a tnsnames.ora file on the database server. Open tnsnames.ora ($ORACLE_HOME\network\admin\) and add the following entry :
    • listener_alias =
      • (DESCRIPTION =
        • (ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
      • )
  4. Restart the listener
  5. Restart the database service

Then the Oracle instance is starting up, the PMON process will get the listener alias from the initialization parameter file and look in tnsnames.ora for protocol address resolution and then register the instance with the listener specified by the protocol address. (Oracle Net Listener configuration authoritative documentation)



Multiple Oracle Instances

While multiple Oracle instances on the same computer is generally a complication, there are indeed situations in which we may end up with multiple Oracle instances on the same computer, eg. if we need to run different versions or if we need different language settings.

An important way to distinguish multiple Oracle instances onfigurations is whether the instances share the same $ORACLE_HOME or not :

  • Multiple Homes : each instance sharing $ORACLE_BASE, but with separate $ORACLE_HOME, listener process and network configuration files : this happens if you use the Oracle installer to install a second database.
  • Single Home : each instance sharing not only $ORACLE_BASE, but also $ORACLE_HOME, listener process and network configuration files : I think this is possible using the dbca tool.

Multiple Oracle Instances Multiple Homes

Let's say a second database have been installed in it's own $ORACLE_HOME - you now have 2 listener services and 2 sets of network configuration files. Initially this situation will be very confusing (it was for me) because :

  • Windows environment PATH variable will (among other) have the following content :
    • C:\app\Administrator\product\11.2.0\dbhome_2\bin;C:\app\Administrator\product\11.2.0\dbhome_1\bin;

      If you take a close look, you can see that all non-qualified (no path) Oracle executables will be executed from dbhome_2\bin never from dbhome_1\bin.

      What this means is that lsnrctl, tnsping, others and more importantly extproc and TNSLSNR (the listener) will if unqualified ALWAYS execute from dbhome_2\bin, so your listener.ora and tnsnames.ora in dbhome_1\network\admin\ may NEVER be read by any Oracle tool nor by any Oracle listener.

      If looking in services property for the listeners, we can see that tnslsnr is actually qualified, so the listener should run. However if using netstat, we can see that there is no process listening on port 1521 :
      • shell> netstat -abp tcp : we can find that tnslsnr is listening on port 1522, but no process is listening on port 1521.

Let's try a few standard operations and see how it plays out in a multi home environment :

Using the listener control program, lsnrctl, you can see that it will use your second listener :

  • shell> lsnrctl status : definitely current_listener named listener is from dbhome_2.
  • shell> sqlplus rasmus/1234@orcl : cannot connect because the net service name orcl is defined only in tnsnames.ora from dbhome_1 not in tnsnames.ora from dbhome_2.
  • shell> sqlplus rasmus/1234@orcl2 : can connect, indeed orcl2 is a net service name in tnsnames.ora fromn dbhome_2.

To fast get a listener free connection to the first database instance ORCL, you can do this :

  1. shell> set oracle_sid=orcl : define the environment variable oracle_sid so sqlplus will select that instance instead of the $ORACLE_HOME scoped instance.
  2. shell> sqlplus / as sysdba : ok, you are now logged on to ORCL.
  3. sql> show parameter db_name; : confirm that you are actual connected to ORCL.

Alternatively you can also open sqlplus from dbhome_1\bin - if you do that, ORCL will be default selected.

Also note that since the first listener is not running correctly after installation of the second database, it does not help you to specify a full connect descriptor to get a connection to ORCL.

While I cannot remember all details of a multiple homes installation, I know for sure how to make it work : (here I assume you have 2 databases, instances called ORCL and ORCL2)

  1. For both listener.ora disable dynamic registration by adding the following parameter to the bottom of each file :
    • DYNAMIC_REGISTRATION_LISTENER = OFF
  2. Add or edit the protocol address entries to this :
    • For the first listener.ora
      • listener =
        • (description =
          • (address = (protocol=ipc)(key=EXTPROC1521)) : the 1521 part of the unique identifier EXTPROC1521 is just a string - not a port number.
          • (address = (protocol=tcp)(host=localhost)(port=1521)) : so the listener can be contacted on localhost.
          • (address = (protocol=tcp)(host=192.168.1.213)(port=1521)) : so the listener can be contacted remote on the servers IP address.
        • )
    • For the second listener.ora
      • listener = : the listener name (here listener) only needs to be unique within the scope of $ORACLE_HOME, indeed lsnrctl can only connect to listeners within the scope of $ORACLE_HOME and if you keep the autoinstalled listener service to start automatically, that service will try to start a listener called listener.
        • (description =
          • (address = (protocol=ipc)(key=EXTPROC1522)) : the IPC identifier needs to be unique across the computer.
          • (address = (protocol=tcp)(host=localhost)(port=1522)) : note the port number is different (each listener need to have it's own port).
          • (address = (protocol=tcp)(host=192.168.1.213)(port=1522))
        • )
  3. Edit the SID_LIST entries :
    • For the first listener.ora
      • sid_list_listener =
        • (sid_list =
          • (sid_desc =
            • (SID_NAME = CLRExtProc)
            • (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
            • (PROGRAM = extproc)
            • (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
          • )
          • (sid_desc =
            • (global_dbname = S_ORCL) : global_dbname is the SERVICE_NAME here set to S_ORCL.
            • (sid_name = orcl) : the actual instance is orcl (sql> show parameter db_name;).
            • (oracle_home = C:\app\Administrator\product\11.2.0\dbhome_1)
          • )
        • )
    • For the second listener.ora
      • sid_list_listener =
        • (sid_list =
          • (sid_desc =
            • (SID_NAME = CLRExtProc)
            • (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_2) : note that this is dbhome_2 (not dbhome_1).
            • (PROGRAM = extproc)
            • (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_2\bin\oraclr11.dll")
          • )
          • (sid_desc =
            • (global_dbname = S_ORCL2) : global_dbname is the SERVICE_NAME here set to S_ORCL2.
            • (sid_name = orcl2) : the actual instance is orcl2 (sql> show parameter db_name;)
            • (oracle_home = C:\app\Administrator\product\11.2.0\dbhome_2)
          • )
        • )
  4. Restart both listeners (screenshot only show how to restart the first listener).
  5. Since we use static registration (not dynamic registration), there is no need to restart the database services.

Now you are able to connect to both ORCL & ORCL2. Using S_ORCL with the first listener (port 1521) you can connect to the ORCL instance, while using S_ORCL2 with the second listener (port 1522) you can connect to the ORCL2 instance. Let's try to connect using sqlplus with easy connect :

  • shell> sqlplus rasmus/1234@localhost:1521/S_ORCL : connecting to ORCL local from the server machine.
  • shell> sqlplus rasmus/1234@192.168.1.213:1521/S_ORCL : connecting to ORCL remote from any machine.
  • shell> sqlplus rasmus/1234@localhost:1522/S_ORCL2 : connecting to ORCL2 local.
  • shell> sqlplus rasmus/1234@192.168.1.213:1522/S_ORCL2 : connecting to ORCL2 remote.
  • shell> sqlplus rasmus/1234@(description=(address=(protocol=tcp)(host=192.168.1.213)(port=1521))(connect_data=(service_name=S_ORCL))) : connecting to ORCL remote using a full connect descriptor.

Multiple Oracle Instances Single Home

Under construction




Debugging Connection Problems

Oracle Network Test Tools : (Testing Connections Authoritative Documentation)

  • lsnrctl
    • shell> lsnrctl stop : stops the current_listener.
    • shell> lsnrctl stop listenerName : in case of multiple listeners, we can postfix the command with listener name.
    • shell> lsnrctl start : start the current_listener.
    • shell> lsnrctl status : lots of relevant information for the current_listener.
    • shell> lsnrctl : enter the lsnrctl utility command prompt
      • lsnrctl> help : list available commands.
      • lsnrctl> status : see status for current_listener.
      • lsnrctl> set current_listener listenerName : set the default listener to make commands on.
  • tnsping : test whether an Oracle service can be reached.
    • shell> tnsping netServiceName : show if the net service name can be resolved and shows the connect descriptor it resolves to.
    • shell> tnsping netServiceName 5 : resolve 5 times to see how long time each resolve is taking.

8 Common Oracle connection errors :



  1. ORA-12541 TNS: no listener

Reason : You are trying to connect to a listener that does not exists, typically your listener is not running or a filewall is blocking your request.

Solution :

  1. Restart the listener.
  2. Check that you use host & port as specified for the listener in listener.ora (remember if connecting remote, the listener needs to listen on an IP address not only localhost)
  3. shell> netstat -abp tcp : check that tnslsnr is listening on the port you are using.
  4. Disable all firewalls on the database server.
  1. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Reason : The listener is running and you can connect to the listener, however the listener does not recognize the SERVICE_NAME you have passed in the connect descriptor.

  • If you use dynamic registration it probably means that the PMON process have not yet registered the database instance with the listener that you connect to.
  • If you use static registration then the SERVICE_NAME in your connect descriptor does not match any global_dbname in your listener.ora file.

Solution :

  • If you use dynamic registration, then restart your database instance and be sure that your SERVICE_NAME is identical to your database instance SID.
  • If you use static registration, then be sure that SERVICE_NAME you are passing in the connect descriptor is identical to a global_dbname in the SID_LIST entry in listener.ora.
  1. ORA-12154: TNS:could not resolve the connect identifier specified

Reason : You try to connect using a net service name, however that net service name does not exist in the tnsnames.ora.

Debug example :

  • shell> tnsping netServiceName 5 : execute from the same computer from which you try to connect and see if your net service name resolves to a connect descriptor.
  1. ORA-12545: Connect failed because target host or object does not exist

Reason : The host in your connect descriptor is bad, eg. if you specify host=192.168.1.213 but the IP does not exists or you specify host=mydomain.com but mydomain.com does not resolve to an IP address that exists. To confirm this you can try to ping the host you are using in your connect descriptor.

Solution : Be sure your host value is correct.

  1. ORA-12560: TNS:protocol adapter error

Reason : This is a generic adapter error and can therefore have multiple errors.

Solution : You will need to test how far you can come. Do the testing on the database server :

  1. Test that the listener is running
  2. Test that the database instance is running
  3. shell> sqlplus / as sysdba : if cannot connect, then try :
    1. shell> set oracle_sid=SID : where SID is the SID of the instance you want to connect to.
    2. shell> sqlplus / as sysdba : see if it helped to explicitly set the SID.
  4. If you can connect using sqlplus / as sysdba, then try :
    1. shell> sqlplus username/password@host:port/SID : try easy connect
    2. shell> sqlplus username/password@(description=(address=(protocol=tcp)(host=host)(port=port))(connect_data=(service_name=service_name)))I : try with a full connect descriptor.
  1. ORA-12518: TNS:listener could not hand off client connection

Reason : Database instance is not properly started.

Solution : Restart your database service :

  1. Open services manager
  2. Find the relevant database service
  3. Right click the database service and select restart from the context menu.
  4. shell> sqlplus / as sysdba : check if the database instance is correctly started
    • If the database is not correctly started, eg. it can mount but not start (you will see "Connected to an idle instance"), you need to find the underlying reason :
      • sql> startup : then you execute the startup command and it fails, you will be shown the underlying reason.
  1. ORA-01034: ORACLE not available
  2. ORA-27101: shared memory realm does not exists

Reason : Something prohibits Oracle from starting up, eg. local_listener is incorrect. To check this is the case, do the following :

  1. shell> sqlplus / as sysadmin
  2. sql> startup : you can now see what the problem is - here the problem is that local_listener is set to LISTENER2_ORCL2 and that value is incorrect so Oracle cannot open the instance.

Solution : If the local_listener is incorrect, it will be easy to solve if you are using the text based initialization parameter file as you can just edit it, however if you are using a binary initialization parameter you will NOT be able to change it.

  1. Assume you are using a binary initialization parameter file (default), you are in trouble, because even if you can open the spfile in a text editor, Oracle will NOT startup correctly if you are saving the file using your text editor.
  2. You need to game the system by adding protocol address entry with the same name as the value of the local_listener to your tnsnames.ora file (yes that is correct - not the listener.ora).
  3. Open the relevant tnsnames.ora file and insert the following protocol address entry, there the entry have the same name as the value of the local_listener parameter :
    • LISTENER2_ORCL2 =
      • (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
  4. Restart the relevant listener (easy to use windows services if you have multiple listeners in multiple homes)
  5. Restart the relevant database service (again easy to use windows services)
  6. shell> sqlplus / as sysdba : use sqlplus to logon to the database instance
  7. sql> alter system set local_listener=''; : set local_listener to empty.
  8. sql> alter system register; : persist the change.
  1. ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Reason :

Solution :


Comments

You can comment without logging in
 
 B  U  I  S 
Words: Chars: Chars left: 
 Captcha 
 Nickname
Facebook
    


click to top Bacula Backup