连不上oracle一例

今天，开发人员报连不上oracle，错误如下：

09:58:02,629 WARN  [JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
10.1.0.25:1521:orcl
)

Alert文件中出现大量这种错误：

***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 22-MAY-2012 12:25:04
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.0.1)(PORT=16990))
Tue May 22 12:25:10 2012


***********************************************************************

查错误代码ORA-12519:

ORA-12519: TNS:no appropriate service handler found

 Cause: The listener could not find any available service handlers that are appropriate for the client connection. 
Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener, and are accepting connections.

按照提示运行　lsnrctl services，如下：

[oracle@prdev1 ~]$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-MAY-2012 10:01:45
Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCLXDB.aaxischina.net" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: prdev1.aaxischina.net, pid: 4094>
         (ADDRESS=(PROTOCOL=tcp)(HOST=prdev1.aaxischina.net)(PORT=36477))
Service "orcl.aaxischina.net" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1185317 refused:12 state:blocked
         LOCAL SERVER
The command completed successfully
[oracle@prdev1 ~]$ 

发现监听状态为blocked，于是重启监听，然后再执行：lsnrctl services，出现如下问题：

[oracle@prdev1 111]$ lsnrctl services;
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-MAY-2012 10:16:28
Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

客户端连接时，提示

SQL> conn scott/tiger@orcl
ERROR:
ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的 SID

请输入用户名:

应该是数据库没注册上监听，手动注册：

SQL> alter system register;
System altered.

问题依旧：

[oracle@prdev1 111]$ lsnrctl services;
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-MAY-2012 10:16:28
Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

于是静态注册：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/product/10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = ora10g)
      (ORACLE_HOME = /home/oracle/product/10.2.0)
      (GLOBAL_DBNAME = ora10g)
    )
  )

再看结果：

[oracle@prdev1 admin]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-MAY-2012 12:25:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prdev1.aaxischina.net)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2903 refused:0
         LOCAL SERVER
The command completed successfully

问题解决。