ORA-12505, TNS Listener does not currently know of SID given in connect descriptor

ziutek

    I change port in listener.ora and after computer restart I get this error when trying to connect to hr user. Ealier it works fine.

    Db is on the same computer and os. I've googled this problem, but it looks fine as far as I can tell, but I'm new to this.

    Here is listener.ora:

    SID_LIST_LISTENER =

      (SID_LIST =

      (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = I:\program\oracledb\app\oracle\product\11.2.0\server)

      (PROGRAM = extproc)

      )

      (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = I:\program\oracledb\app\oracle\product\11.2.0\server)

      (PROGRAM = extproc)

      )

      )

     

    LISTENER =

      (DESCRIPTION_LIST =

      (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      (ADDRESS = (PROTOCOL = TCP)(HOST = qwerty-Komputer)(PORT = 1521))

      )

      )

     

    DEFAULT_SERVICE_LISTENER = (XE)

     

     

    and tnsnames.ora

     

    XE =

      (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = qwerty-Komputer)(PORT = 1522))

      (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

      )

      )

     

    EXTPROC_CONNECTION_DATA =

      (DESCRIPTION =

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      )

      (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

      )

      )

     

    ORACLR_CONNECTION_DATA =

      (DESCRIPTION =

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      )

      (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

      )

      )

    I don't know if it's connected, but I tried twice to install db, because of not enough space at the first time.

    connection properties:

    screen.png

      • 1. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
        AndrewSayer

        In order to connect through the listener (what you are doing with the basic connection), the instance must have been started. Is that the case if you’ve just restarted the computer?

         

        Your tnsnames file has an entry for XE using a different port, it doesn’t matter here as you’re not trying to use the tnsnames entry, you’re using basic connection.

         

        If the instance is not started up then you’ll need to start it, you can do this using a local connection in sqlplus:

        From cmd

        set oracle_sid=xe

        sqlplus / As sysdba

        startup

         

        What exactly happens?

        Once the instance has started it will try to register with a listener, you can check what default listener is doing from the command line with:

        lsnrctl status

         

        That should be enough information to figure out what’s gone wrong

        • 2. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
          ziutek

          Andrew Sayer napisał(-a):

           

          That should be enough information to figure out what’s gone wrong

          No. This commands doesn't work:

           

          SQL> set oracle_sid=xe

          SP2-0735: unknown SET option beginning "oracle_sid..."

          SQL> sqlplus / As sysdba

          SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.

          SQL> startup

          ORA-01031: insufficient privileges

          SQL>

           

          in windows cmd it doesn't work either. It says that startup command isn't recognized.

          • 3. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
            AndrewSayer

            ziutek wrote:

             

            Andrew Sayer napisał(-a):

             

            That should be enough information to figure out what’s gone wrong

            No. This commands doesn't work:

             

            SQL> set oracle_sid=xe

            SP2-0735: unknown SET option beginning "oracle_sid..."

            SQL> sqlplus / As sysdba

            SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.

            SQL> startup

            ORA-01031: insufficient privileges

            SQL>

             

            in windows cmd it doesn't work either. It says that startup command isn't recognized.

            Thats because you ran the commands within sqlplus.

             

            Set is a command line command.

            sqlplus / As sysdba

            means from cmd, open up sqlplus using that connection string

             

            The startup is a sqlplus command that can be run as sysdba (which you should already be if you ran the commands As is from cmd)

             

            The last error message suggests you had already connected to the DB but as a user without the privilege to start up the DB. So how were you able to connect to the DB in sqlplus if you weren’t able to in sqldeveloper? I assume you just used a local connection? So the DB has started but just hasn’t registered with the listener?

             

            Whilst connected as a privileged user, in sqlplus,  you can run

            show parameter local_listener

            To see what listener the DB would Try to register With. Perhaps it’s not the same listener as you’ve started, and therefore hasn’t been possible.

            • 4. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
              John Thorton

              ziutek wrote:

               

              Andrew Sayer napisał(-a):

               

              That should be enough information to figure out what’s gone wrong

              No. This commands doesn't work:

               

              SQL> set oracle_sid=xe

              SP2-0735: unknown SET option beginning "oracle_sid..."

              SQL> sqlplus / As sysdba

              SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.

              SQL> startup

              ORA-01031: insufficient privileges

              SQL>

               

              in windows cmd it doesn't work either. It says that startup command isn't recognized.

              SET ORACLE_SID=XE

               

              above MUST be done at OS (cmd) level

               

              at OS level from command shell issue commands below

               

              lsnrctl status

              lsnrctl service

               

              use Windows COPY & PASTE to post results from 2 commands above back here.

               

              if/when you have already invoked sqlplus & you have "SQL>" prompt you should do as below

               

              SQL> connect / as sysdba

              • 5. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                ziutek

                John Thorton napisał(-a):

                at OS level from command shell issue commands below

                 

                lsnrctl status

                lsnrctl service

                 

                use Windows COPY & PASTE to post results from 2 commands above back here.

                 

                 

                here is what i get:

                C:\Users\qwerty>lsnrctl status

                 

                LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 17-GRU-2017 18:23

                :18

                 

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

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

                STATUS of the LISTENER

                ------------------------

                Alias LISTENER

                Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ

                ction

                Start Date 17-GRU-2017 13:43:02

                Uptime 0 days 4 hr. 40 min. 30 sec

                Trace Level off

                Security ON: Local OS Authentication

                SNMP OFF

                Default Service XE

                Listener Parameter File I:\program\oracledb\app\oracle\product\11.2.0\server\n

                etwork\admin\listener.ora

                Listener Log File I:\program\oracledb\app\oracle\diag\tnslsnr\qwerty-Kom

                puter\listener\alert\log.xml

                Listening Endpoints Summary...

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=qwerty-Komputer)(PORT=1521)))

                Services Summary...

                Service "CLRExtProc" has 1 instance(s).

                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                Service "PLSExtProc" has 1 instance(s).

                  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

                The command completed successfully

                 

                C:\Users\qwerty>lsnrctl service

                 

                LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 17-GRU-2017 18:23

                :28

                 

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

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

                Services Summary...

                Service "CLRExtProc" has 1 instance(s).

                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                  Handler(s):

                  "DEDICATED" established:1 refused:0

                  LOCAL SERVER

                Service "PLSExtProc" has 1 instance(s).

                  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

                  Handler(s):

                  "DEDICATED" established:5 refused:0

                  LOCAL SERVER

                The command completed successfully

                So how were you able to connect to the DB in sqlplus if you weren’t able to in sqldeveloper?

                 

                I didn't connect to anythin. I just run sqlplus and wrote commends there.

                Ok now it works and I get this:

                 

                C:\Users\qwerty>set oracle_sid=xe

                 

                C:\Users\qwerty>sqlplus / As sysdba

                 

                SQL*Plus: Release 11.2.0.2.0 Production on N Gru 17 18:25:51 2017

                 

                Copyright (c) 1982, 2014, Oracle. All rights reserved.

                 

                 

                Connected to:

                Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

                 

                SQL> startup

                ORA-01081: cannot start already-running ORACLE - shut it down first

                • 6. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                  John Thorton

                  post excerpt (10 line above & 10 lines below ) from file below where status=12505 exists in log.xml file

                   

                  >Listener Log File I:\program\oracledb\app\oracle\diag\tnslsnr\qwerty-Komputer\listener\alert\log.xml

                  • 7. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                    ziutek

                    I can't find line "status=12505" in that file.

                    • 8. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                      John Thorton

                      ziutek wrote:

                       

                      I can't find line "status=12505" in that file.

                      EVERY connection request that gets to the listener is logged by the listener.

                      You report that you get/got ORA-12505 error

                      log.xml should contain "12505" string; ideally near the end/bottom of the file & does NOT actually contain the string "status" any where near 12505 value

                      • 9. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor

                        Well - that shows that THERE IS NO XE SERVICE registered with that listener.

                         

                        So either you database isn't running or it hasn't registered.

                        • 10. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                          John Thorton

                          rp0428 wrote:

                           

                          Well - that shows that THERE IS NO XE SERVICE registered with that listener.

                           

                          So either you database isn't running or it hasn't registered.

                          Or there might be another listener.

                          • 11. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                            ziutek

                            John Thorton napisał(-a):

                            log.xml should contain "12505" string; ideally near the end/bottom of the file & does NOT actually contain the string "status" any where near 12505 value

                            Then you should say so. I was looking for "status=12505". There is a lot of 12505 in this file.

                             

                            Here are +-10 lines near the last one:

                            (HOST=__jdbc__)(USER=qwerty))(SID=xe)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=54851)) * establish * xe * 12505

                            </txt>

                            </msg>

                            <msg time='2017-12-17T17:25:14.977+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

                            </txt>

                            </msg>

                            <msg time='2017-12-17T17:32:13.654+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>17-GRU-2017 17:32:13 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=qwerty))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=54892)) * establish * xe * 12505

                            </txt>

                            </msg>

                            <msg time='2017-12-17T17:32:13.655+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

                            </txt>

                            </msg>

                            <msg time='2017-12-17T17:32:13.658+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>17-GRU-2017 17:32:13 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=qwerty))(SID=xe)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=54893)) * establish * xe * 12505

                            </txt>

                            </msg>

                            <msg time='2017-12-17T17:32:13.659+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

                            </txt>

                            </msg>

                            <msg time='2017-12-17T17:42:15.650+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>17-GRU-2017 17:42:15 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=qwerty))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0

                            </txt>

                            </msg>

                            <msg time='2017-12-17T18:23:18.279+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>17-GRU-2017 18:23:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=qwerty))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0

                            </txt>

                            </msg>

                            <msg time='2017-12-17T18:23:28.772+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>17-GRU-2017 18:23:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=qwerty))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * services * 0

                            </txt>

                            </msg>

                            <msg time='2017-12-17T21:13:19.389+01:00' org_id='oracle' comp_id='tnslsnr'

                            type='UNKNOWN' level='16' host_id='QWERTY-KOMPUTER'

                            host_addr='fe80::d515:7bbb:aea3:a772%16'>

                            <txt>System parameter file is I:\program\oracledb\app\oracle\product\11.2.0\server\network\admin\listener.ora

                            </txt>

                            </msg>

                            <msg time='2017-12-17T21:13:19.763+01:00' org_id='oracle'

                            • 12. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                              John Thorton

                              Below show the connection is for SID=xe that run on HOST=127.0.0.1

                               

                              ><txt>17-GRU-2017 17:32:13 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=qwerty))) * (ADDRESS=>(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=54892)) * establish * xe * 12505</txt>

                               

                              The listener correctly report that no such database exists.

                               

                              Do as below

                               

                              ping QWERTY-KOMPUTER

                              lsnrctl stop

                               

                              rename file to a new name such as listener.ora.save

                              I:\program\oracledb\app\oracle\product\11.2.0\server\network\admin\listener.ora

                               

                              lsnrctl start

                               

                              ---- wait 60+ seconds

                               

                              lsnrctl status

                               

                              COPY the results from above then PASTE all back here.

                              • 13. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                                ziutek

                                You forgot to mention that I need to run cmd as admin, but luckily I noticed trhat.

                                 

                                C:\Windows\system32>ping QWERTY-KOMPUTER

                                 

                                Badanie qwerty-Komputer [fe80::d515:7bbb:aea3:a772%16] z 32 bajtami danych:

                                Odpowiedź z fe80::d515:7bbb:aea3:a772%16: czas<1 ms

                                Odpowiedź z fe80::d515:7bbb:aea3:a772%16: czas<1 ms

                                Odpowiedź z fe80::d515:7bbb:aea3:a772%16: czas<1 ms

                                Odpowiedź z fe80::d515:7bbb:aea3:a772%16: czas<1 ms

                                 

                                Statystyka badania ping dla fe80::d515:7bbb:aea3:a772%16:

                                  Pakiety: Wysłane = 4, Odebrane = 4, Utracone = 0

                                  (0% straty),

                                Szacunkowy czas błądzenia pakietów w millisekundach:

                                  Minimum = 0 ms, Maksimum = 0 ms, Czas średni = 0 ms

                                 

                                C:\Windows\system32>lsnrctl stop

                                 

                                LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 17-GRU-2017 23:53

                                :40

                                 

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

                                 

                                Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                                The command completed successfully

                                 

                                C:\Windows\system32>lsnrctl start

                                 

                                LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 17-GRU-2017 23:54

                                :17

                                 

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

                                 

                                Starting tnslsnr: please wait...

                                 

                                TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production

                                Log messages written to I:\program\oracledb\app\oracle\diag\tnslsnr\qwerty-Kompu

                                ter\listener\alert\log.xml

                                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=qwerty-Komputer)(PORT=15

                                21)))

                                 

                                Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                                STATUS of the LISTENER

                                ------------------------

                                Alias LISTENER

                                Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ

                                ction

                                Start Date 17-GRU-2017 23:54:23

                                Uptime 0 days 0 hr. 0 min. 5 sec

                                Trace Level off

                                Security ON: Local OS Authentication

                                SNMP OFF

                                Listener Log File I:\program\oracledb\app\oracle\diag\tnslsnr\qwerty-Kom

                                puter\listener\alert\log.xml

                                Listening Endpoints Summary...

                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=qwerty-Komputer)(PORT=1521)))

                                The listener supports no services

                                The command completed successfully

                                 

                                C:\Windows\system32>lsnrctl status

                                 

                                LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 17-GRU-2017 23:56

                                :07

                                 

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

                                 

                                Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                                STATUS of the LISTENER

                                ------------------------

                                Alias LISTENER

                                Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ

                                ction

                                Start Date 17-GRU-2017 23:54:23

                                Uptime 0 days 0 hr. 1 min. 47 sec

                                Trace Level off

                                Security ON: Local OS Authentication

                                SNMP OFF

                                Listener Log File I:\program\oracledb\app\oracle\diag\tnslsnr\qwerty-Kom

                                puter\listener\alert\log.xml

                                Listening Endpoints Summary...

                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=qwerty-Komputer)(PORT=1521)))

                                The listener supports no services

                                The command completed successfully

                                • 14. Re: ORA-12505, TNS Listener does not currently know of SID given in connect descriptor
                                  John Thorton

                                  lsnrctl status shows NO database is registered with the listener.

                                   

                                  on Windows Oracle requires OS Service to be started & running prior to starting the database.

                                   

                                  post the last 20 - 40 lines from alert_XE.log file; where it may be alert_xe.log alternatively.

                                  1 2 上一个 下一个