ORA-12528 TNS Listner

Question

When I try to connect the database using sql developer it gives the error below:

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

I have tried all the suggestions that I found on this website, but in vain, they are not working.

I checked the instance and database status, they are started and active. But why I still can not connect to that?

When I try the oracle - default database, that is ok, but I want to connect to my own database, So can you help me to solve that problem?

Answer 1

This means the that the listener is up and running, but the SID or SERVICE_NAME in the CONNECT_DATA section of your net service connect descriptor does not specify a SID or SERVICE_NAME that the listener is aware of. The listener does not know where to send your connection.

See the full text of the error message:

$oerr ora 12505
12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action: 
//  - Wait a moment and try to connect a second time.
//  - Check which instances are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SID parameter in the connect descriptor specifies
//    an instance known by the listener.
//  - Check for an event in the listener.log file.

Hope that helps.

Answer 2

I had the same problem today:

• stop you listener
• bkp you tns + listener
• using netmgr create a new entry in the tns + listener (use a port number bigger them 1521)
• start your listener now
• run the status on the listener

    [orahsync01@dc8002 ~]$ lsnrctl status HSYNC01

    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-MAR-2014 13:09:19

    Copyright (c) 1991, 2011, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dc8002)(PORT=9823)))
    STATUS of the LISTENER
    Alias                     HSYNC01
    Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date                11-MAR-2014 09:51:55
    Uptime                    0 days 3 hr. 17 min. 23 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u03/HSYNC01/app/11.2/db_1/network/admin/listener.ora
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc8002)(PORT=9823)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extprochsync01)))
    Services Summary...
    Service HSYNC01.fgv.br has 2 instance(s).
      Instance HSYNC01, status UNKNOWN, has 1 handler(s) for this service...
      Instance HSYNC01, status READY, has 1 handler(s) for this service...
    The command completed successfully

--status shows unknown

• login you database

    SQL> alter system register;

    System altered.

run the commnad :

      SQL> show parameter listener

        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- ------------------------------
        listener_networks                    string
        local_listener                       string      (DESCRIPTION= (ADDRESS=(PROTOC
                                                         OL=tcp)(HOST=dc8002)(PO
                                                         RT=9823))(CONNECT_DATA= (SERVI
                                                         CE_NAME=HSYNC01) (INSTANCE_NAM
                                                         E=HSYNC01)))
        remote_listener                      string

• check for the tnsnames entry

    cat tnsnames.ora

        HSYNC01 =
          (DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = dc8002)(PORT = 9823)
            )
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = HSYNC01)
            )
          )

--make sure the entries in the tnsnames under the SID in my case HSYNC01 are the same as in the local_listenr parameter.

• If they are not fix this using the commnad bellow :

    alter system set local_listener='(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dc8002)(PORT=9823))(CONNECT_DATA= (SERVICE_NAME=HSYNC01) (INSTANCE_NAME=HSYNC01))' scope=both;

no line breaks here !!

• reload your listener

You entry in the listener file should be like

SID_LIST_HSYNC01 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = HSYNC01)
      (ORACLE_HOME = /u03/HSYNC01/app/11.2/db_1)
      (SID_NAME = HSYNC01)
    )
  )

HSYNC01 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dc8002)(PORT = 9823))
    )
  )

LOGGING_HSYNC01 = OFF

DIAG_ADR_ENABLED_HSYNC01 = OFF

Answer 3

What do you mean default database and your database, please notice for the same oracle instance there is only one database while we can have several schemas. Also notice the difference between database name, global database name and instance name, although they can be identical usually. The SID specified should be the Global Database Name.