1 2 Previous Next 16 Replies Latest reply on Dec 4, 2017 2:24 AM by Hemant K Chitale

    TNS:no listener when connecting to pluggable database

    user4197738

      I have installed 12c R2 on Windows 10 machine with one pluggable database. I had to reboot the machine due to windows update and since then I am having problems connecting to an existing account in pluggable database. In SQL Developer the error I get is "Status: Failure -Test failed: IO Error: The Network Adapter could not establish the connection." when trying to Test the connection. I can connect to the SYS or SYSTEM accounts in the root database (corcl). Windows services TNSListener is successfully running. I have restarted it too, but still I am not able to connect. The pluggable database (CORCLPDB) is open in read write mode.

       

      The tnsnames.ora file contents are as follows:

       

      # tnsnames.ora Network Configuration File: C:\app\rak549\virtual\product\12.2.0\dbhome_1\network\admin\tnsnames.ora

      # Generated by Oracle configuration tools.

       

      CORCL =

        (DESCRIPTION =

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

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = corcl.SGF.EDUBEAR.NET)

          )

        )

       

      CORCLPDB =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = gl385-oracle)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = corclpdb.SGF.EDUBEAR.NET)

          )

        )

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      LISTENER_CORCL =

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

       

      When trying to connect through SQL Plus I get the following error:

       

      SQL> connect cis528inst2/welcome@corclpdb

      ERROR:

      ORA-12541: TNS:no listener

       

      When tnsping to pluggable database pluggable database corclpdb it gives error. However it does tnsping the root database corcl. See below:

      C:\Users\rak549>tnsping corcl

       

      TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 27-NOV-2017 20:50:57

       

      Copyright (c) 1997, 2016, Oracle.  All rights reserved.

       

      Used parameter files:

      C:\app\rak549\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = corcl.SGF.EDUBEAR.NET)))

      OK (0 msec)

       

      C:\Users\rak549>tnsping corclpdb

       

      TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 27-NOV-2017 20:51:25

       

      Copyright (c) 1997, 2016, Oracle.  All rights reserved.

       

      Used parameter files:

      C:\app\rak549\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = gl385-oracle)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = corclpdb.SGF.EDUBEAR.NET)))

      TNS-12541: TNS:no listener

       

      C:\Users\rak549>

       

      I don't understand what could have changed during the rebooting process for listener to deny access to pluggable database. Any workaround?

       

      Thanks.

        • 1. Re: TNS:no listener when connecting to pluggable database
          rchem

          Used TNSNAMES adapter to resolve the alias

          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = corcl.SGF.EDUBEAR.NET)))

          OK (0 msec)

          You have hostname as localhost for corcl , whereas for PDB you have specified it as gl385-oracle , are they both resolve to same server? replace gl385-oracle with localhost in pdb tns and try the connection to see if the issue is with the DNS name gl385-oracle.

          Used TNSNAMES adapter to resolve the alias

          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = gl385-oracle)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = corclpdb.SGF.EDUBEAR.NET)))

          TNS-12541: TNS:no listener

          • 2. Re: TNS:no listener when connecting to pluggable database

            In SQL Developer the error I get is "Status: Failure -Test failed: IO Error: The Network Adapter could not establish the connection." when trying to Test the connection.

            The 'basic' connection does NOT use TNSNAMES.ORA

             

            That exception means you are calling a WRONG NUMBER - the host or port you are using doesn't exist or can't be reached.

            • 3. Re: TNS:no listener when connecting to pluggable database
              vanpupi

              Can you show the output from

               

              lsnrctl status

              • 4. Re: TNS:no listener when connecting to pluggable database
                Franck Pachot

                Hi,

                As you can connect to the root, probably the PDB is not open.

                You see that with "show pdbs" in sqlplus

                Then you can open it with "alter pluggable database corclpdb open;"

                And if you want to avoid the same issue and have it opened automatically when the CDB is opened, you can: "alter pluggable database corclpdb save state;"

                Regards,

                Franck

                • 5. Re: TNS:no listener when connecting to pluggable database
                  user4197738

                  Changing to localhost instead of gl385-oracle for PDB in tnsnames.ora worked!!

                   

                  But now I have a related problem that when I try to access the server accounts from another machine in SQL Developer it gives the same error "Status: Failure -Test failed: IO Error: The Network Adapter could not establish the connection." When I try to tnsping the server from the other machine it now gives the error TNS-12535: TNS: operation timed out. I have allowed access to port 1521 through server firewall already. 

                  • 6. Re: TNS:no listener when connecting to pluggable database
                    rchem

                    localhost will only work from the host server (database server), you can't use it to connect as a client from other machines. You should either use the IP address of the server or the DNS hostname of the DB server.

                    • 7. Re: TNS:no listener when connecting to pluggable database
                      user4197738

                      On the second PC I have Oracle 11g XE and Oracle 11g Client installed. I can ping the server PC hostname successfully from second PC. After changing the hostname in tnsnames.ora file to IP address I can tnsping also successfully. But when I try to connect to the server through SQL Developer on the second machine I get the error "Status: Failure -Test failed: Listener refused connection with the following error ORA-12514, TNS: Listener does not currently know of service requested in connect descriptor." Again before the windows update on the server PC I was able to access server accounts through SQL Developer on the second PC with hostname.

                       

                      tnsping output below:

                       

                      C:\Users\rak549>tnsping corclpdb

                       

                      TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 29-NOV-2017 20:37:15

                       

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

                       

                      Used parameter files:

                      C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

                       

                      Used TNSNAMES adapter to resolve the alias

                      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 146.7.120.104)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = corclpdb.SGF.EDUBEAR.NET)))

                      OK (0 msec)

                       

                       

                      The sqlnet.ora file contents:

                       

                      # This file is actually generated by netca. But if customers choose to

                      # install "Software Only", this file wont exist and without the native

                      # authentication, they will not be able to connect to the database on NT.

                       

                      SQLNET.AUTHENTICATION_SERVICES = (NONE)

                       

                      Listener.ora file content:

                       

                      SID_LIST_LISTENER =

                        (SID_LIST =

                          (SID_DESC =

                            (SID_NAME = PLSExtProc)

                            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                            (PROGRAM = extproc)

                          )

                          (SID_DESC =

                            (SID_NAME = CLRExtProc)

                            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                            (PROGRAM = extproc)

                          )

                        )

                       

                      LISTENER =

                        (DESCRIPTION_LIST =

                          (DESCRIPTION =

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

                            (ADDRESS = (PROTOCOL = TCP)(HOST = GL385-Kaula.SGF.EDUBEAR.NET)(PORT = 1521))

                            (ADDRESS = (PROTOCOL = TCP)(HOST = 146.7.120.104)(PORT = 1521))

                          )

                        )

                       

                      DEFAULT_SERVICE_LISTENER = (XE)

                       

                      Appreciate help. Thanks.

                      • 8. Re: TNS:no listener when connecting to pluggable database
                        Hemant K Chitale

                        tnsping only tests connectivity to a listener.  It doesn't verify if the remote database exists or is open.

                        corclpdb would be a 12c Pluggable Database that cannot be running in an 11g XE environment !!!!

                         

                        Hemant K Chitale

                        • 9. Re: TNS:no listener when connecting to pluggable database
                          user4197738

                          It is not running in the XE environment. I have two machines side by side. One has 12c server installed. The second machine has 11g client and XE installed. I am trying to access pluggable database corclpdb through SQL Developer on the second machine. I have the appropriate tnsnames entries in both XE and 11g client tnsnames.ora files. SQL Developer on the second machine (having XE and 11g client) was allowing me access before with no problems till the windows update on the server machine changed something. Even the Net Configuration Assistant of 11g client on second machine cannot access the server database.  

                          • 10. Re: TNS:no listener when connecting to pluggable database
                            Hemant K Chitale

                            Why present the listener.ora of the 11g XE database if you are attempting to connect to the 12c PDB ?  How will that help ?

                             

                            You need to know the configuration and running status of the 12c Listener !  Whether the 12c server listener has started.  Whether the 12c database instance has registered.  Whether the PDB is open.  Whether the PDB service has registered with the listener.

                            (LSNRCTL STATUS and LSNRCTL SERVICES would show the database and service registration status on the 12c listener)

                             

                            Hemant K Chitale

                            • 11. Re: TNS:no listener when connecting to pluggable database
                              user4197738

                              12c listener status and service output:

                               

                              C:\Users\rak549>lsnrctl services

                               

                              LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 01-DEC-2017 10:08:18

                               

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

                               

                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

                              Services Summary...

                              Service "8b58d063249b4f6b943a4efad979f417.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                                  Handler(s):

                                    "DEDICATED" established:0 refused:0 state:ready

                                       LOCAL SERVER

                              Service "CLRExtProc" has 1 instance(s).

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

                                  Handler(s):

                                    "DEDICATED" established:0 refused:0

                                       LOCAL SERVER

                              Service "corcl.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                                  Handler(s):

                                    "DEDICATED" established:0 refused:0 state:ready

                                       LOCAL SERVER

                              Service "corclXDB.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                                  Handler(s):

                                    "D000" established:0 refused:0 current:0 max:1022 state:ready

                                       DISPATCHER <machine: GL385-ORACLE, pid: 3784>

                                       (ADDRESS=(PROTOCOL=tcp)(HOST=GL385-oracle.SGF.EDUBEAR.NET)(PORT=49744))

                              Service "corclpdb.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                                  Handler(s):

                                    "DEDICATED" established:0 refused:0 state:ready

                                       LOCAL SERVER

                              The command completed successfully

                               

                              C:\Users\rak549>lsnrctl status

                               

                              LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 01-DEC-2017 10:11:24

                               

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

                               

                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

                              STATUS of the LISTENER

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

                              Alias                     LISTENER

                              Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production

                              Start Date                01-DEC-2017 10:06:44

                              Uptime                    0 days 0 hr. 4 min. 40 sec

                              Trace Level               off

                              Security                  ON: Local OS Authentication

                              SNMP                      OFF

                              Listener Parameter File   C:\app\rak549\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

                              Listener Log File         C:\app\rak549\virtual\diag\tnslsnr\GL385-oracle\listener\alert\log.xml

                              Listening Endpoints Summary...

                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

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

                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=GL385-oracle.SGF.EDUBEAR.NET)(PORT=5501))(Security=(my_wallet_directory=C:\APP\RAK549\VIRTUAL\admin\corcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))

                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GL385-oracle.SGF.EDUBEAR.NET)(PORT=7778))(Presentation=HTTP)(Session=RAW))

                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=GL385-oracle.SGF.EDUBEAR.NET)(PORT=5500))(Security=(my_wallet_directory=C:\APP\RAK549\VIRTUAL\admin\corcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))

                              Services Summary...

                              Service "8b58d063249b4f6b943a4efad979f417.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                              Service "CLRExtProc" has 1 instance(s).

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

                              Service "corcl.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                              Service "corclXDB.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                              Service "corclpdb.SGF.EDUBEAR.NET" has 1 instance(s).

                                Instance "corcl", status READY, has 1 handler(s) for this service...

                              The command completed successfully

                               

                              C:\Users\rak549>

                              • 12. Re: TNS:no listener when connecting to pluggable database

                                Ok - and did you see this?

                                Service "corclXDB.SGF.EDUBEAR.NET" has 1 instance(s).

                                THERE IS NO service named 'corclpdb' that the listener knows about.

                                 

                                The above is the service_name.

                                 

                                Perhaps you are confusing SERVICE_NAME with the ALIAS you might use in a TNSNAMES.ORA file?

                                 

                                A 'basic' connection in Sql Developer DOES NOT use TNSNAMES.ORA

                                 

                                For 12c you use the SERVICE_NAME - and that would be what the listener shows as I quoted from your post above.

                                • 13. Re: TNS:no listener when connecting to pluggable database
                                  user4197738

                                  So for basic connection in SQL Developer the Service name entry would be "corcl.SGF.EDUBEAR.NET" for connecting to the root and "corclpdb.SGF.EDUBEAR.NET" for connecting to the pluggable database. I still get errors.

                                  • 14. Re: TNS:no listener when connecting to pluggable database

                                    I still get errors.

                                    Just saying you get 'errors' is nothing but whining. How is ANYONE supposed to know what errors you got when you tried the basic connection?

                                     

                                    If you want help you have to SHOW US:

                                     

                                    1. WHAT you do

                                    2. HOW you do it

                                    3. WHAT results you get

                                     

                                    People are trying to help you but you aren't making it easy.

                                    1 2 Previous Next