ORACLE FOR WINDOWS 監聽配置之 ORA-12514 TNS能ping通不能登陸資料庫

    4、再次嘗試TNS本地遠端登陸
c:\OGG>sqlplus zhul/zhul@ORCL
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 27 16:12:24 2018
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS: 監聽程式當前無法識別連線描述符中請求的服務
請輸入使用者名稱:
ERROR:
ORA-01017: 使用者名稱/口令無效; 登入被拒絕
請輸入使用者名稱:
ERROR:
ORA-01017: 使用者名稱/口令無效; 登入被拒絕
SP2-0157: 在 3 次嘗試之後無法連線到 ORACLE, 退出 SQL*Plus
c:\OGG>
    5、由2判斷監聽雖然註冊orcl服務，但是沒有向外提供服務，檢視監聽配置檔案
# listener.ora Network Configuration File: C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\app\oracle\db\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.13)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = C:\oracle\app\oracle\db
    6、由5監聽配置檔案內容可知，監聽不提供orcl服務，修改監聽配置檔案（新增綠色加粗部分）：
# listener.ora Network Configuration File: C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = C:\oracle\app\oracle\db\product\11.2.0\dbhome_1) 
      (SID_NAME = ORCL)
     )       
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\app\oracle\db\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.13)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\oracle\app\oracle\db
    7、重啟監聽
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 27 16:19:57 2018
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl
SQL> quit
從 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開
C:\Users\Administrator>lsnrctl stop
LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-2月 -2018 16:22:32
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
正在連線到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.13)(PORT=1521)))命令執行成功
C:\Users\Administrator>lsnrctl start
LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-2月 -2018 16:22:37
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
啟動tnslsnr: 請稍候...
TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
系統引數檔案為C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\network\admin\listener.ora
寫入C:\oracle\app\oracle\db\diag\tnslsnr\WIN-6ABO3IQOF3U\listener\alert\log.xml
的日誌資訊
監聽: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.13)(PORT=1521)))
監聽: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
正在連線到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.13)(PORT=1521)))
LISTENER 的 STATUS
------------------------
別名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Produ
ction
啟動日期                  27-2月 -2018 16:22:42
正常執行時間              0 天 0 小時 0 分 5 秒
跟蹤級別                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
監聽程式引數檔案          C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\networ
k\admin\listener.ora
監聽程式日誌檔案          C:\oracle\app\oracle\db\diag\tnslsnr\WIN-6ABO3IQOF3U\l
istener\alert\log.xml
監聽端點概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.13)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
服務摘要..
服務 "CLRExtProc" 包含 1 個例項。
  例項 "CLRExtProc", 狀態 UNKNOWN, 包含此服務的 1 個處理程式...
服務 "orcl" 包含 1 個例項。
  例項 "ORCL", 狀態 UNKNOWN, 包含此服務的 1 個處理程式...
命令執行成功
C:\Users\Administrator>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-2月 -2018 16:22:51
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
正在連線到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.13)(PORT=1521)))
LISTENER 的 STATUS
------------------------
別名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Produ
ction
啟動日期                  27-2月 -2018 16:22:42
正常執行時間              0 天 0 小時 0 分 12 秒
跟蹤級別                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
監聽程式引數檔案          C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\networ
k\admin\listener.ora
監聽程式日誌檔案          C:\oracle\app\oracle\db\diag\tnslsnr\WIN-6ABO3IQOF3U\l
istener\alert\log.xml
監聽端點概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.13)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
服務摘要..
服務 "CLRExtProc" 包含 1 個例項。
  例項 "CLRExtProc", 狀態 UNKNOWN, 包含此服務的 1 個處理程式...
服務 "orcl" 包含 1 個例項。
  例項 "ORCL", 狀態 UNKNOWN, 包含此服務的 1 個處理程式...
命令執行成功
C:\Users\Administrator>
    8、再次遠端登陸成功
C:\Users\Administrator>sqlplus zhul/zhul@ORCL
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 27 16:23:11 2018
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>