ora-12514

　

ora-12514

ora-12514

一、问题缘由

1）建立DBA用户jack，但是登陆的时候一直提示：“ora-12541”

[oracle@ocmserver ~]$ sqlplus jack/oracle@ocm
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jun 29 13:05:37 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12541: TNS:no listener

Enter user-name: 
[oracle@ocmserver ~]

2）发现是没有启动监听，于是启动监听再登陆

[oracle@ocmserver ~]$lsnrct start

LSNRCTL for linux: Version 10.2.0.1.0 - Production on 29-JUN-2013 13:05:43
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting /opt/oracle/product/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/oracle/product/network/admin/listener.ora
Log messages written to /opt/oracle/product/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocmserver.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmserver.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                29-JUN-2013 13:05:43
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/network/admin/listener.ora
Listener Log File         /opt/oracle/product/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocmserver.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3）启动之后登陆继续提示监听不清楚当前的请求

[oracle@ocmserver ~]$ sqlplus jack/oracle@ocm
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jun 29 13:05:45 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name: 

二、问题解决

1、检查监听文件$ORACLE_HOME/product/network/admin/listener.ora

# listener.ora Network configuration File: /opt/oracle/product/network/admin/listener.ora
# generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmserver.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

2）备份listener.ora文件并添加ocm数据库的监听服务

# listener.ora Network Configuration File: /opt/oracle/product/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = ocm)
      (ORACLE_HOME = /opt/oracle/product)
      (SID_NAME =ocm)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocmserver.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

说明：

红色字体为新添加，注意GLOBAL_DBNAME和SID_NAME是小写的。

3）重启监听服务

[oracle@ocmserver admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JUN-2013 13:12:56

Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@ocmserver admin]$ ls
ls           lsattr       lsb_release  lsdiff       lshal        lsmod        lsnrctl      lsnrctl0     lspci        lspcmcia     lspgpot      lss16toppm   lsUSB
[oracle@ocmserver admin]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JUN-2013 13:13:14
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting /opt/oracle/product/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/oracle/product/network/admin/listener.ora
Log messages written to /opt/oracle/product/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocmserver.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
TNS-01155: incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
 NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...
[oracle@ocmserver admin]$ 

[oracle@ocmserver admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 29-JUN-2013 13:17:08

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

Starting /opt/oracle/product/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is /opt/oracle/product/network/admin/listener.ora

Log messages written to /opt/oracle/product/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocmserver.com)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmserver.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 29-JUN-2013 13:17:08

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/network/admin/listener.ora

Listener Log File /opt/oracle/product/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocmserver.com)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "ocm" has 1 instance(s).

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

The command completed successfully

[oracle@ocmserver admin]$

4）启动数据库并用jack登陆

SQL> startup
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size            1218604 bytes
Variable Size           67110868 bytes
Database Buffers      146800640 bytes
Redo Buffers            2973696 bytes
Database mounted.
Database opened.
SQL> conn jack/oracle
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> 

说明：提示用户名密码错误，检查dba_user表发现jack之前实验删除了，重建之。

5）重建用户jack

SQL> create user jack identified by oracle ;
User created.

SQL> grant dba to jack;
Grant succeeded.

SQL> commit;
Commit complete.
SQL> 

6）再次用jack登陆后，问题解决

[oracle@ocmserver ~]$ alias |grep j

alias j='sqlplus jack/oracle@ocm'

alias jack='cd /opt/oracle/jack'

[oracle@ocmserver ~]$ j

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jun 29 13:37:11 2013

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> show user;

USER is "JACK"

SQL>

三、小结

listener.ora问题很明显，每次都懒得去理会。就都是通过sqlplus / as sysdba登陆后，再conn jack/oracle 可以跳转登陆成功，但问题今天算是基本解决。listener.ora文件需要详细的解读和理解，后续加强学习并汇总在此。

posted on 2013-06-29 13:40 Alexy Young 阅读(...) 评论(...) 编辑 收藏

转载于:https://www.cnblogs.com/alexy/p/ora-12514.html

文章最后发布于: 2019-08-09 19:36:18

相关阅读

ORA-12514的解决方法

我本机安装的数据库版本是Oracle 11G R2，用plsql连接时候，报ora-12514如下错误：但是在cmd里用sqlplus连接已经创建的用户时候，报如下

ORA-12514

在使用PLSQL Developer登录时出现ora-12514错误。 在安装完成oracel之后数据库一直在启动状态，并没有关闭过，所以一直没问题， 当关闭

ORA-12154:TNS:无法解析指定的连接标识符

ORA-12154:TNS:无法解析指定的连接标识符 根本的解决方法是需要增加系统的环境变量:

“ora-12154无法解析指定的连接标识符”问题解决

环境：win10, oracle10.2.1, plsql 7.4问题：安装完oracle后，使用plsql连接数据库，发现总是报“ora-12154无法解析指定的连接标识符”错

Oracle错误 ora-12514 解决方法

成功连到数据库上之后，查看listener状态：lsnrctl status status READY 状态，需要由非归档转为归档模式，故操作如下： 1.关闭数据库shu