image.png

大概意思就是TNS监听器程序当前无法识别连接描述符中请求的服务。

问题分析:

1.首先默认的oracle配置文件中tnsnames.ora和listener.ora中默认监听的地址是localhost,如果删除了/etc/hosts中localhost的映射也会出现这个问题。
2.手动的修改listener.org和tnsnames.org文件
日志：/opt/oracle/product/11.2.0/dbhome_1/listener.log

解决方案

首先确定Oracle的安装位置： /opt/oracle/product/11.2.0/dbhome_1/

1修改listener.ora
vim /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
修改前

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /opt/oracle

修改后:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = orcl)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.23.1.192)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /opt/oracle

image.png

说明: 也可以使用IP为HOST = 0.0.0.0

2. 修改tnsnames.org
   vim /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
   修改前

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

修改后

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.23.1.192)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcl)
    )
  )

image.png

3.重启监听器服务

$ lsnrctl stop
$ lsnrctl start

image.png
image.png

4.重启数据库
以sqlplus / as sysdba登录

$ sqlplus / as sysdba
SQL>shutdown immediate  #立即关闭数据库
SQL>startup   #开启数据库
SQL>alter system register;  #注册

到此，问题解决。