☰

为什么从命令行指定 ORACLE SID 和密码时无法连接到 sqlplus？

tag-icon • tag-icon command-line redhat-enterprise-linux database
为什么从命令行指定 ORACLE SID 和密码时无法连接到 sqlplus？

我有 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命令可以正常工作。

相关内容