![为什么从命令行指定 ORACLE SID 和密码时无法连接到 sqlplus?](https://linux22.com/image/1556736/%E4%B8%BA%E4%BB%80%E4%B9%88%E4%BB%8E%E5%91%BD%E4%BB%A4%E8%A1%8C%E6%8C%87%E5%AE%9A%20ORACLE%20SID%20%E5%92%8C%E5%AF%86%E7%A0%81%E6%97%B6%E6%97%A0%E6%B3%95%E8%BF%9E%E6%8E%A5%E5%88%B0%20sqlplus%EF%BC%9F.png)
我有 2 台不同的 Red Hat 服务器,其中装有 Oracle 12c。在其中一台服务器上,我可以通过从命令行指定 ORACLE SID 和密码来登录 sqlplus:
[root@server1 ~]# $ORACLE_HOME/bin/sqlplus ourdbuser@$ORACLE_SID/ourpassword
SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 20:16:01 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Mar 23 2018 20:14:29 +00:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
另一方面,当我从命令行指定 ORACLE SID 和密码时,它不起作用:
[root@server2 ~]# $ORACLE_HOME/bin/sqlplus ourdbuser@$ORACLE_SID/ourpassword
SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 20:16:53 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
但是如果我没有从命令行指定 ORACLE SID 和密码,而是在提示符中输入密码,那么它就可以起作用:
[root@server2 ~]# $ORACLE_HOME/bin/sqlplus ourdbuser
SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 20:17:17 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri Mar 23 2018 20:12:25 +00:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
我尝试查看 server1 和 server2 之间是否存在差异,但没有发现任何差异。Server1:
[root@server1 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[root@server1 ~]# env | grep "ORACLE"
ORACLE_SID=ourdb
ORACLE_HOME=/opt/oracle/product/12.2.0/db
[root@server1 ~]# ls -lrt /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
-rw-r-----. 1 oracle oinstall 416 Aug 3 2017 /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
[root@server1 ~]# cat /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_OURDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
OURDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ourdb)
)
)
服务器2:
[root@server2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[root@server2 ~]# env | grep "ORACLE"
ORACLE_SID=ourdb
ORACLE_HOME=/opt/oracle/product/12.2.0/db
[root@server2 ~]# ls -lrt /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
-rw-r-----. 1 oracle oinstall 466 Aug 22 2017 /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
[root@server2 ~]# cat /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_OURDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
OURDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ourdb)
)
)
我还应该检查什么?
答案1
我采纳了 Pimp Juice IT 的建议,使用了不同的 sqlplus 命令(sqlplus ourdbuser/ourpassword@localhost:1521/$ORACLE_SID
),并且它起作用了。
但那只是一种权宜之计,真正的问题在服务器重启后 Oracle 监听器无法启动时才被发现,真正的问题是在安装 Oracle 后服务器的域被更改了。
该hostname
命令显示了服务器的新域:
server1.zzz.bbb.ccc.com
但是 $ORACLE_HOME/network/admin/listener.ora 文件包含旧域:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.aaa.bbb.ccc.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
$ORACLE_HOME/network/admin/tnsnames.ora 文件也同样如此:
LISTENER_OURSID =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.aaa.bbb.ccc.com)(PORT = 1521))
OURSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.aaa.bbb.ccc.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oursid)
)
)
更改这两个文件中的域后,Oracle 侦听器就可以启动,并且原始sqlplus ourdbuser@$ORACLE_SID/ourpassword
命令可以正常工作。