14 Replies Latest reply: May 26, 2008 4:27 AM by fsze88hoho RSS

    Why I can't connect to oracle Server

    fsze88hoho
      Dear sir/madam

      Is somebody can help me that why I can't connect to oracle Server from client or server itself?

      At client, the content of tnsnames.ora file is
      STOCK =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = stock)
      )
      )

      KOONHEY =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = koonhey)
      )
      )

      KENNAM =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = kennam)
      )
      )

      the content of sqlnet.ora is

      SQLNET.AUTHENTICATION_SERVICES= (NTS)

      NAMES.DIRECTORY_PATH= (TNSNAMES)

      At server named oracleserver

      the content of tnsnames.ora is

      STOCK =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = stock)
      )
      )

      KOONHEY =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = koonhey)
      )
      )

      KENNAM =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = kennam)
      )
      )

      ORCL =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      )
      )

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )

      the content of sqlnet.ora is
      SQLNET.AUTHENTICATION_SERVICES= (NTS)

      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

      the content of listener.ora is

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
      )

      is somebody can help me to solve this out?

      many thanks

      Francis SZE
        • 1. Re: Why I can't connect to oracle Server
          Pavan DBA
          Hi its fine that you posted the entries. but it would be better if you provide the oracle version and OS. also please post how you are connecting to the server from client as well as server and post the error messages you are getting while trying to connect.
          • 2. Re: Why I can't connect to oracle Server
            585179
            Your listener doesn't contain Oracle services


            Cheers
            • 3. Re: Why I can't connect to oracle Server
              599375
              Check Listener is Runnig or Not

              LSNRCTL status <listener_name>

              if its not then LSNRCTL start <listener_name>

              Give a try now
              • 4. Re: Why I can't connect to oracle Server
                fsze88hoho
                The Server is windows 2003 enterprise english edition
                I am using Oracle 10g version 10.2.0.10

                I have tried connect from client to oracle server oracleserver using sqlplus kennam/kennam007@kennam, it doesn't work
                The errror message is ORA-12514:

                I also tried to connect from server to server itself oracleserver by
                sqlplus kennam/kennam007@kennam
                It give same error ORA-12514

                please help
                • 5. Re: Why I can't connect to oracle Server
                  612926
                  Could you please paste exact oracle error message you are getting. ?
                  • 6. Re: Why I can't connect to oracle Server
                    fsze88hoho
                    C:\Documents and Settings\Administrator>lsnrctl status listener

                    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-5月 -2008
                    13:5
                    8:26

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

                    連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver)(PORT=1521)))
                    監聽器的狀態
                    ------------------------
                    別名 LISTENER
                    版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 -
                    Produc
                    tion
                    啟動日期 26-5月 -2008 13:44:01
                    正常運作時間 0 days 0 hr. 14 min. 24 sec
                    追蹤層次 off
                    安管 ON: Local OS Authentication
                    SNMP OFF
                    監聽器參數檔案
                    C:\oracle\product\10.2.0\db_1\network\admin\listener.or
                    a
                    監聽器日誌檔
                    C:\oracle\product\10.2.0\db_1\network\log\listener.log
                    監聽終止點摘要...
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))
                    服務摘要...
                    服務 "PLSExtProc" 有 1 個執行處理.
                    執行處理 "PLSExtProc", 狀態 UNKNOWN, 有 1 個此服務的處理程式...
                    命令順利完成

                    C:\Documents and Settings\Administrator>sqlplus kennam/kennam007@kennam

                    SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 5月 26 13:58:35 2008

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

                    ERROR:
                    ORA-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
                    • 7. Re: Why I can't connect to oracle Server
                      585179
                      Add Oracle service to your listener. your listener doesn't contain oracle services


                      Cheers
                      • 8. Re: Why I can't connect to oracle Server
                        Pavan DBA
                        Hi check your listener status as specified by other experts
                        also see the following
                        ORA-12514: TNS:listener does not currently know of service requested 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 service name for a service (usually a database service) 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 services are currently known by the listener by executing: lsnrctl services <listener name>

                        - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

                        - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

                        - Check for an event in the listener.log file.
                        • 9. Re: Why I can't connect to oracle Server
                          fsze88hoho
                          Dear fzheng

                          I would like to know how to Add Oracle service to your listener

                          many thanks

                          Francis SZE
                          • 10. Re: Why I can't connect to oracle Server
                            599375
                            In the Listener.ora replace "PLSExtProc" with your SID.

                            Stop and Start the Listener.

                            check status

                            Paste Log here
                            • 11. Re: Why I can't connect to oracle Server
                              585179
                              Try add this in your listener.ora

                              (SID_DESC =
                              (GLOBAL_DBNAME = KENNAM)
                              (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                              (SID_NAME = KENNAM)


                              Then stop and restart again the listener

                              Cheers
                              • 12. Re: Why I can't connect to oracle Server
                                fsze88hoho
                                many thanks

                                But it still not work, I don't know why.

                                content of listener.ora is

                                SID_LIST_LISTENER =
                                (SID_LIST =
                                (SID_DESC =
                                (SID_NAME = PLSExtProc)
                                (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                                (PROGRAM = extproc)
                                )
                                )

                                LISTENER =
                                (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
                                )


                                (SID_DESC =
                                (GLOBAL_DBNAME = KENNAM)
                                (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                                (SID_NAME = KENNAM)


                                But the result of lsnrctl status listener is

                                C:\Documents and Settings\Administrator>lsnrctl status listener

                                LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-5月 -2008 15:3
                                0:22

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

                                連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver)(PORT=1521)))
                                監聽器的狀態
                                ------------------------
                                別名 listener
                                版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produc
                                tion
                                啟動日期 26-5月 -2008 15:27:44
                                正常運作時間 0 days 0 hr. 2 min. 39 sec
                                追蹤層次 off
                                安管 ON: Local OS Authentication
                                SNMP OFF
                                監聽器參數檔案 C:\oracle\product\10.2.0\db_1\network\admin\listener.or
                                a
                                監聽器日誌檔 C:\oracle\product\10.2.0\db_1\network\log\listener.log
                                監聽終止點摘要...
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))
                                服務摘要...
                                服務 "PLSExtProc" 有 1 個執行處理.
                                執行處理 "PLSExtProc", 狀態 UNKNOWN, 有 1 個此服務的處理程式...
                                命令順利完成

                                the content of listener.log is
                                26-5月 -2008 15:34:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.107)(PORT=3493)) * establish * orcl * 12514
                                TNS-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
                                26-5月 -2008 15:34:07 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=listener)(VERSION=169869568)) * status * 0
                                26-5月 -2008 15:34:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=kennam)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.107)(PORT=3497)) * establish * kennam * 12514
                                TNS-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
                                26-5月 -2008 15:34:11 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=kennam)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.107)(PORT=3500)) * establish * kennam * 12514
                                TNS-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
                                26-5月 -2008 15:34:11 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=kennam)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.107)(PORT=3501)) * establish * kennam * 12514
                                TNS-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
                                26-5月 -2008 15:34:11 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=kennam)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.107)(PORT=3503)) * establish * kennam * 12514
                                TNS-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
                                26-5月 -2008 15:34:11 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=kennam)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.107)(PORT=3504)) * establish * kennam * 12514
                                TNS-12514: TNS: 監聽器目前不知道連線描述區中要求的服務

                                C:\Documents and Settings\Administrator>

                                please help, Thanks for all of you.
                                • 13. Re: Why I can't connect to oracle Server
                                  585179
                                  It is not like that, it should be like this

                                  SID_LIST_LISTENER =
                                  (SID_LIST =
                                  (SID_DESC =
                                  (SID_NAME = PLSExtProc)
                                  (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                                  (PROGRAM = extproc)
                                  )
                                  (SID_DESC =
                                  (GLOBAL_DBNAME = KENNAM)
                                  (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                                  (SID_NAME = KENNAM)
                                  )


                                  Try again

                                  Cheers
                                  • 14. Re: Why I can't connect to oracle Server
                                    fsze88hoho
                                    Dear fzheng

                                    now the content of listener.ora is

                                    SID_LIST_LISTENER =
                                    (SID_LIST =
                                    (SID_DESC =
                                    (SID_NAME = PLSExtProc)
                                    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                                    (PROGRAM = extproc)
                                    )
                                    (SID_DESC =
                                    (GLOBAL_DBNAME = kennam)
                                    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                                    (SID_NAME = kennam)
                                    )
                                    )

                                    LISTENER =
                                    (DESCRIPTION =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521))
                                    )

                                    the output of lsnrctl status listener is

                                    C:\Documents and Settings\Administrator>lsnrctl status listener

                                    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-5月 -2008 17:2
                                    1:54

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

                                    連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver)(PORT=1521)))
                                    監聽器的狀態
                                    ------------------------
                                    別名 LISTENER
                                    版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produc
                                    tion
                                    啟動日期 26-5月 -2008 17:19:59
                                    正常運作時間 0 days 0 hr. 1 min. 55 sec
                                    追蹤層次 off
                                    安管 ON: Local OS Authentication
                                    SNMP OFF
                                    監聽器參數檔案 C:\oracle\product\10.2.0\db_1\network\admin\listener.or
                                    a
                                    監聽器日誌檔 C:\oracle\product\10.2.0\db_1\network\log\listener.log
                                    監聽終止點摘要...
                                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))
                                    服務摘要...
                                    服務 "PLSExtProc" 有 1 個執行處理.
                                    執行處理 "PLSExtProc", 狀態 UNKNOWN, 有 1 個此服務的處理程式...
                                    服務 "kennam" 有 1 個執行處理.
                                    執行處理 "kennam", 狀態 UNKNOWN, 有 1 個此服務的處理程式...
                                    命令順利完成

                                    C:\Documents and Settings\Administrator>

                                    it still not after restart the listener
                                    lsnrctl stop listener
                                    lsnrctl start listener
                                    even reboot the server, it still get the same error

                                    C:\Documents and Settings\Administrator>sqlplus kennam/kennam007@kennam

                                    SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 5月 26 17:26:16 2008

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

                                    ERROR:
                                    ORA-12523: TNS:監聽器找不到適合從屬端連線的執行處理

                                    Thanks