公司最近要求新部署一台oracle10g的数据库,整个部署过程比较顺利,但是在新建数据库实例后发现新建的用户没法直接通过sqlplus访问。
问题描述:
在新建的数据库实例中sitedb3中创建用户,通过
sqlplus tester/tester@sitedb3登录访问失败
ORA-12537,需要手动再次输入用户密码才能访问。
执行过程信息:
oracle@host-10-19-11-22:~> sqlplus tester/tester@sitedb3
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 15 16:11:14 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name: tester -----这里输入用户密码可以正常访问
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 15 16:11:14 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name: tester -----这里输入用户密码可以正常访问
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
listener.log 日志报错信息:
15-AUG-2016 16:13:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sitedb3)(CID=(PROGRAM=D:\app\hyholine\product\11.2.0\cl
ient_1\bin\sqlplus.exe)(HOST=DESKTOP-5E51B3B)(USER=hyholine))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.0.38)(PORT=13461)) * esta
blish * sitedb3 * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 13: Permission denied
ient_1\bin\sqlplus.exe)(HOST=DESKTOP-5E51B3B)(USER=hyholine))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.0.38)(PORT=13461)) * esta
blish * sitedb3 * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 13: Permission denied
tnsnames.ora 配置:
sitedb3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sitedb3)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sitedb3)
)
)
listener.ora 配置:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = sitedb1)
(ORACLE_HOME =/data/oracle/product/10.2)
(PROGRAM = sitedb1)
)
(SID_DESC =
(SID_NAME = sitedb2)
(ORACLE_HOME =/data/oracle/product/10.2)
(PROGRAM = sitedb2)
)
(SID_DESC =
(SID_NAME = sitedb3)
(ORACLE_HOME =/data/oracle/product/10.2)
(PROGRAM = sitedb3)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.11.22)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = sitedb1)
(ORACLE_HOME =/data/oracle/product/10.2)
(PROGRAM = sitedb1)
)
(SID_DESC =
(SID_NAME = sitedb2)
(ORACLE_HOME =/data/oracle/product/10.2)
(PROGRAM = sitedb2)
)
(SID_DESC =
(SID_NAME = sitedb3)
(ORACLE_HOME =/data/oracle/product/10.2)
(PROGRAM = sitedb3)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.11.22)(PORT = 1521))
)
)
解决办法:
把 listener.ora 中(PROGRAM = sitedb3) 这部分都删除掉,因为监听在访问时会先查找PROGRAM的配置,配置的实例在当前文件无法获取到导致异常。