ORACLE FOR WINDOWS 监听配置之 ORA-12514 TNS能ping通不能登陆数据库

    环境介绍：
    操作系统：windows server2008 R2 
    数据库版本：oracle 11.2.0.3
    问题描述：windwos 服务器上刚安装完oracle 数据库，服务器本地或使用TNS远程连接数据库报错ORA-12514，具体报错如下：
c:\OGG>sqlplus zhul/zhul@192.168.3.13:1521/orcl
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 27 16:06:09 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>
    问题处理过程
    1、查看数据库状态（由于使用操作系统认证能正常登陆数据库，可知不是操作系统环境变量导致的）
c:\OGG>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 27 16:04:03 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> select instance_name from v$instance;
INSTANCE_NAME
----------------
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:\OGG>
    2、查看监听状态：
c:\OGG>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 27 16:08:50 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> alter system register ;
系统已更改。
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:\OGG>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-2月 -2018 16:09:02
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
启动日期                  26-2月 -2018 16:08:57
正常运行时间              1 天 0 小时 0 分 9 秒
跟踪级别                  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 个处理程序...
命令执行成功
c:\OGG>
    3、测试数据库服务连接
c:\OGG>tnsping orcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-2月 -2018 16:05:49
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
已使用的参数文件:
C:\oracle\app\oracle\db\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.13)(PORT =
1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))OK (0 毫秒)
c:\OGG>

    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>

C:\Users\Administrator>sqlplus zhul/zhul@192.168.3.13:1521/orcl
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 27 16:23:51 2018
Copyright (c) 1982, 2010, 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>