配置静态监听时SID_NAME名字需要注意大小写,同样的名字不一样的大小写表示完全不同的数据库实例。一旦静态监听的实例名字与对应的数据库实例不一致时,便会出现使用连接串无法连接数据库的问题。
将这个问题记录在这里,供大家参考。
1.查看与静态监听相关的配置信息
PROD@secdb1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cat listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = prod)
)
)
特别注意,此处SID_NAME内容是小写的“prod”,而数据库实例的名字是大写的“PROD”。
2.查看tnsnames.ora中记录的连接串
PROD@secdb1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cat tnsnames.ora
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = secdb1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)
3.重启监听使上面错误的配置信息生效
PROD@secdb1 /home/oracle$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2011 21:44:43
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 11-AUG-2011 21:47:42
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
特别注意这里的提示信息“Instance "prod"”,这里清楚地显示出是引号括起来的小写prod实例名。
4. 测试 使用连接串连接数据库时的故障现象
1)尝试用SYS用户以特权身份使用连接串登录数据库
PROD@secdb1 /home/oracle$ sqlplus sys/oracle@prod as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 11 21:53:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
这里提示的错误是“权限不足”,潜在的含义便是没有办法登录数据库。
2)尝试用system普通用户使用连接串登录数据库
PROD@secdb1 /home/oracle$ sqlplus system/oracle@prod
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 11 21:48:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
这里提示的错误是“Oracle数据库不可用”,这个错误提示很直观,因为静态监听中配置的是小写的“prod”实例,而实际上数据库实例名字是大写的“PROD”,因此连接过程中无法找到对应的实例!
5.小结
本文给出故障现象的最根本的原因是:数据库实例名本身是区分大小写的,因此在配置静态监听配置SID_NAME时一定要注意大小写。
当遇到数据库无法连接的时候我们应该从多个角度去分析,不放过任何一个细节。
Good luck.
secooler
11.08.11
-- The End --
将这个问题记录在这里,供大家参考。
1.查看与静态监听相关的配置信息
PROD@secdb1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cat listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = prod)
)
)
特别注意,此处SID_NAME内容是小写的“prod”,而数据库实例的名字是大写的“PROD”。
2.查看tnsnames.ora中记录的连接串
PROD@secdb1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cat tnsnames.ora
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = secdb1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)
3.重启监听使上面错误的配置信息生效
PROD@secdb1 /home/oracle$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2011 21:44:43
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 11-AUG-2011 21:47:42
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
特别注意这里的提示信息“Instance "prod"”,这里清楚地显示出是引号括起来的小写prod实例名。
4. 测试 使用连接串连接数据库时的故障现象
1)尝试用SYS用户以特权身份使用连接串登录数据库
PROD@secdb1 /home/oracle$ sqlplus sys/oracle@prod as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 11 21:53:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
这里提示的错误是“权限不足”,潜在的含义便是没有办法登录数据库。
2)尝试用system普通用户使用连接串登录数据库
PROD@secdb1 /home/oracle$ sqlplus system/oracle@prod
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 11 21:48:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
这里提示的错误是“Oracle数据库不可用”,这个错误提示很直观,因为静态监听中配置的是小写的“prod”实例,而实际上数据库实例名字是大写的“PROD”,因此连接过程中无法找到对应的实例!
5.小结
本文给出故障现象的最根本的原因是:数据库实例名本身是区分大小写的,因此在配置静态监听配置SID_NAME时一定要注意大小写。
当遇到数据库无法连接的时候我们应该从多个角度去分析,不放过任何一个细节。
Good luck.
secooler
11.08.11
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-704704/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-704704/