81 积分	0 好友	4 主题

发消息

生产库出现ora-12518

1^#

发表于 2012-3-29 10:39:15 | 查看: 6010| 回复: 5

oracle: 10.2.0.5 3节点RAC
今早生产库无法远程连接，使用的是sql developer+ service name登陆，报告ora-12518错误；但是如果换成SID，则可以正常登陆；
登陆server采用sqlplus [email=***/***@dbname]***/***@dbname[/email]的方式可以正常登陆；
数据库采用的是dedicate连接，且process值设置为1000，远远高于现有连接会话数
alert log没有错误信息，listener log也没有错误信息；
查看listener service发现有会话可以陆续连接进入数据库

请Maclean帮忙诊断一下
谢谢

分享0

收藏0 回复只看该作者道具举报

ylw6006

2^#

发表于 2012-3-29 15:03:41

是不是相关的service没启动？

回复只看该作者道具举报

myownstars

3^#

发表于 2012-3-29 16:16:06

oracle:  10.2.0.5  3节点RAC
OS： AIX 6
症状：客户早上报告连接数据库总是报告ora-12518，因为是生产库所以十分紧急；经过一番尝试发现以下情况：
1 客户使用如下tnsname连接
justin=
  (DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = justin98-vip..aaaa.net)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = justin99-vip..aaaa.net)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = justin100-vip..aaaa.net)(PORT = 1521))
)
(CONNECT_DATA =
      (SERVICE_NAME = justin)
)
)

2 自己使用sql developer尝试连接
当使用service_names连接时，总是抱错ora-12518；使用sid则能正常连接

3 登陆server
运行sqlplus ***/***@justin.world总是能够成功登陆

4 登陆justin98上的数据库
查看gv$session_wait没有发现严重latch，且各节点的pga都有一定空余(三节点都是使用dedicated server)
该节点上的listener.log没有任何错误信息

5 查看 listener service
结果显示3节点都有session陆续连接进来
Service "justin.world" has 3 instance(s).
  Instance "justin1", status READY, has 2 handler(s) for this service...
Handler(s):
   "DEDICATED" established:0 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=justin98-vip..aaaa.net)(PORT=1521))
   "DEDICATED" established:133 refused:0 state:ready
      LOCAL SERVER
  Instance "justin2", status READY, has 1 handler(s) for this service...
Handler(s):
   "DEDICATED" established:153 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=justin99-vip..aaaa.net)(PORT=1521))
  Instance "justin3", status READY, has 1 handler(s) for this service...
Handler(s):
   "DEDICATED" established:58 refused:0 state:ready
      REMOTE SERVER
      (ADDRESS=(PROTOCOL=TCP)(HOST=aaa100-vip..aaaa.net)(PORT=1521))

最后经过一番尝试，发现justin99无法ssh登陆，初步怀疑是该节点无法接受客户端请求导致的ora-12518

验证
配置Sqlnet进行客户端跟踪
TRACE_LEVEL_CLIENT =16
TRACE_FILE_CLIENT=CLIENT
trace_directory_client=/orasw/dba/tep123

然后尝试不同tnsname配置下登陆
1
justin.world=
  (DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = justin98-vip..aaaa.net)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = justin99-vip..aaaa.net)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = aaa100-vip..aaaa.net)(PORT = 1521))
)
(CONNECT_DATA =
#       (SERVICE_NAME = justin.world)
      (sid = justin1)
)
)

连接正常，登陆了节点1
coa1[test]:/etc>sqlplus tep123/tep123@justin.world

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 29 03:20:32 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter instance

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
active_instance_count             integer
cluster_database_instances          integer    3
instance_groups                   string
instance_name                      string    justin1
instance_number                   integer    1
instance_type                      string    RDBMS
open_links_per_instance             integer    4
parallel_instance_group             string
parallel_server_instances          integer    3

2
justin.world=
  (DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = justin98-vip..aaaa.net)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = justin99-vip..aaaa.net)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = justin100-vip..aaaa.net)(PORT = 1521))
)
(CONNECT_DATA =
      (SERVICE_NAME = justin.world)
)
)
连接出错
coa1[test]:/etc>sqlplus tep123/tep123@justin.world

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 29 03:21:48 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12518: TNS:listener could not hand off client connection

Enter user-name:

查看跟踪日志，居然尝试连接node2
coa1[test]:/orasw/dba/tep123>tail -500 client_8392.trc
[29-MAR-2012 03:21:48:470] nspsend: 01 17 00 00 01 04 00 00  |........|
[29-MAR-2012 03:21:48:470] nspsend: 01 39 01 2C 0C 01 08 00  |.9.,....|
[29-MAR-2012 03:21:48:470] nspsend: 7F FF 7F 08 00 00 00 01  |........|
[29-MAR-2012 03:21:48:470] nspsend: 00 DD 00 3A 00 00 02 00  |...:....|
[29-MAR-2012 03:21:48:470] nspsend: 41 41 00 00 00 00 00 00  |AA......|
[29-MAR-2012 03:21:48:470] nspsend: 00 00 00 00 00 00 00 00  |........|
[29-MAR-2012 03:21:48:470] nspsend: 00 00 00 00 00 00 00 00  |........|
[29-MAR-2012 03:21:48:470] nspsend: 00 00 28 44 45 53 43 52  |..(DESCR|
[29-MAR-2012 03:21:48:470] nspsend: 49 50 54 49 4F 4E 3D 28  |IPTION=(|
[29-MAR-2012 03:21:48:470] nspsend: 41 44 44 52 45 53 53 3D  |ADDRESS=|
[29-MAR-2012 03:21:48:470] nspsend: 28 50 52 4F 54 4F 43 4F  |(PROTOCO|
[29-MAR-2012 03:21:48:470] nspsend: 4C 3D 54 43 50 29 28 48  |L=TCP)(H|
[29-MAR-2012 03:21:48:470] nspsend: 4F 53 54 3D 75 73 73 62  |OST=|
[29-MAR-2012 03:21:48:470] nspsend: 61 7A 75 64 62 30 39 38  |aaa098|
[29-MAR-2012 03:21:48:470] nspsend: 2D 76 69 70 2E 75 73 73  |-vip.aaa|
[29-MAR-2012 03:21:48:470] nspsend: 62 2E 61 73 74 72 61 7A  |b.aaaa|
[29-MAR-2012 03:21:48:471] nspsend: 65 6E 65 63 61 2E 6E 65  |eneca.ne|
[29-MAR-2012 03:21:48:471] nspsend: 74 29 28 50 4F 52 54 3D  |t)(PORT=|
[29-MAR-2012 03:21:48:471] nspsend: 31 35 32 31 29 29 28 43  |1521))(C|
[29-MAR-2012 03:21:48:471] nspsend: 4F 4E 4E 45 43 54 5F 44  |ONNECT_D|
[29-MAR-2012 03:21:48:471] nspsend: 41 54 41 3D 28 53 45 52  |ATA=(SER|
[29-MAR-2012 03:21:48:471] nspsend: 56 49 43 45 5F 4E 41 4D  |VICE_NAM|
[29-MAR-2012 03:21:48:471] nspsend: 45 3D 73 63 70 2E 77 6F  |E=justin.wo|
[29-MAR-2012 03:21:48:471] nspsend: 72 6C 64 29 28 43 49 44  |rld)(CID|
[29-MAR-2012 03:21:48:471] nspsend: 3D 28 50 52 4F 47 52 41  |=(PROGRA|
[29-MAR-2012 03:21:48:471] nspsend: 4D 3D 73 71 6C 70 6C 75  |M=sqlplu|
[29-MAR-2012 03:21:48:471] nspsend: 73 29 28 48 4F 53 54 3D  |s)(HOST=|
[29-MAR-2012 03:21:48:471] nspsend: 75 73 73 62 63 6F 61 31  |coa1|
[29-MAR-2012 03:21:48:471] nspsend: 29 28 55 53 45 52 3D 6F  |)(USER=o|
[29-MAR-2012 03:21:48:471] nspsend: 72 61 63 6C 65 29 29 28  |racle))(|
[29-MAR-2012 03:21:48:471] nspsend: 53 45 52 56 45 52 3D 64  |SERVER=d|
[29-MAR-2012 03:21:48:471] nspsend: 65 64 69 63 61 74 65 64  |edicated|
[29-MAR-2012 03:21:48:471] nspsend: 29 28 49 4E 53 54 41 4E  |)(INSTAN|
[29-MAR-2012 03:21:48:471] nspsend: 43 45 5F 4E 41 4D 45 3D  |CE_NAME=|
[29-MAR-2012 03:21:48:471] nspsend: 73 63 70 32 29 29 29    |justin2))) |
…….
[29-MAR-2012 03:21:48:974] nsprecv: 00 FB 00 00 06 04 00 00  |........|
[29-MAR-2012 03:21:48:974] nsprecv: 00 40 28 44 45 53 43 52  |.@(DESCR|
[29-MAR-2012 03:21:48:974] nsprecv: 49 50 54 49 4F 4E 3D 28  |IPTION=(|
[29-MAR-2012 03:21:48:974] nsprecv: 54 4D 50 3D 29 28 56 53  |TMP=)(VS|
[29-MAR-2012 03:21:48:974] nsprecv: 4E 4E 55 4D 3D 31 38 35  |NNUM=185|
[29-MAR-2012 03:21:48:974] nsprecv: 35 39 39 37 34 34 29 28  |599744)(|
[29-MAR-2012 03:21:48:974] nsprecv: 45 52 52 3D 31 32 35 31  |ERR=1251|
[29-MAR-2012 03:21:48:974] nsprecv: 38 29 28 45 52 52 4F 52  |8)(ERROR|
[29-MAR-2012 03:21:48:974] nsprecv: 5F 53 54 41 43 4B 3D 28  |_STACK=(|
[29-MAR-2012 03:21:48:974] nsprecv: 45 52 52 4F 52 3D 28 43  |ERROR=(C|
[29-MAR-2012 03:21:48:974] nsprecv: 4F 44 45 3D 31 32 35 31  |ODE=1251|
[29-MAR-2012 03:21:48:974] nsprecv: 38 29 28 45 4D 46 49 3D  |8)(EMFI=|
[29-MAR-2012 03:21:48:974] nsprecv: 34 29 29 28 45 52 52 4F  |4))(ERRO|
[29-MAR-2012 03:21:48:974] nsprecv: 52 3D 28 43 4F 44 45 3D  |R=(CODE=|
[29-MAR-2012 03:21:48:974] nsprecv: 31 32 35 34 39 29 28 45  |12549)(E|
[29-MAR-2012 03:21:48:975] nsprecv: 4D 46 49 3D 34 29 29 28  |MFI=4))(|
[29-MAR-2012 03:21:48:975] nsprecv: 45 52 52 4F 52 3D 28 43  |ERROR=(C|
[29-MAR-2012 03:21:48:975] nsprecv: 4F 44 45 3D 31 32 35 36  |ODE=1256|
[29-MAR-2012 03:21:48:975] nsprecv: 30 29 28 45 4D 46 49 3D  |0)(EMFI=|
[29-MAR-2012 03:21:48:975] nsprecv: 34 29 29 28 45 52 52 4F  |4))(ERRO|
[29-MAR-2012 03:21:48:975] nsprecv: 52 3D 28 43 4F 44 45 3D  |R=(CODE=|
[29-MAR-2012 03:21:48:975] nsprecv: 35 31 39 29 28 45 4D 46  |519)(EMF|
[29-MAR-2012 03:21:48:975] nsprecv: 49 3D 34 29 29 28 45 52  |I=4))(ER|
[29-MAR-2012 03:21:48:975] nsprecv: 52 4F 52 3D 28 42 55 46  |ROR=(BUF|
[29-MAR-2012 03:21:48:975] nsprecv: 3D 27 49 42 4D 2F 41 49  |='IBM/AI|
[29-MAR-2012 03:21:48:975] nsprecv: 58 20 52 49 53 43 20 53  |X.RISC.S|
[29-MAR-2012 03:21:48:975] nsprecv: 79 73 74 65 6D 2F 36 30  |ystem/60|
[29-MAR-2012 03:21:48:975] nsprecv: 30 30 20 45 72 72 6F 72  |00.Error|
[29-MAR-2012 03:21:48:975] nsprecv: 3A 20 31 32 3A 20 4E 6F  |:.12:.No|
[29-MAR-2012 03:21:48:975] nsprecv: 74 20 65 6E 6F 75 67 68  |t.enough|
[29-MAR-2012 03:21:48:975] nsprecv: 20 73 70 61 63 65 27 29  |.space')|
[29-MAR-2012 03:21:48:975] nsprecv: 29 29 29                |)))    |
[29-MAR-2012 03:21:48:975] nsprecv: normal exit
并且跟踪日志报告ROR=(BUF='IBM/AIX.RISC.System/6000.Error:.12:.Not.enough.space')

回复只看该作者道具举报