1.問題描述

在linux服務(wù)器centos7上新安裝的oracle 19c，結(jié)果發(fā)現(xiàn)客戶端怎么都連不上。檢查了下，發(fā)現(xiàn)原來是監(jiān)聽的端口采用默認(rèn)配置為了LOCALHOST：

[oracle@mv161p120 bin]$ netstat -an |grep 1521
tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:56449         127.0.0.1:1521          ESTABLISHED
tcp        0      0 127.0.0.1:1521          127.0.0.1:56449         ESTABLISHED
unix  2      [ ACC ]     STREAM     LISTENING     108564   /var/tmp/.oracle/sEXTPROC1521
[oracle@mv161p120 bin]$ 

可以看到監(jiān)聽端口的ip為127.0.0.1。是本地回環(huán)路由。其他服務(wù)器無法訪問這個(gè)地址。
現(xiàn)在對(duì)這個(gè)問題進(jìn)行修改：

2.修改listener.ora 文件

首先要修改listener.ora 文件。
該文件位于：

/opt/oracle/product/19c/dbhome_1/network/admin/listener.ora

vim這個(gè)文件：

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

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

將上述文件的LOCALHOST修改為IP地址。

3.修改tnsnames.ora

同理，tnsnames.ora文件也需要修改：

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

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.161.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )

LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.161.120)(PORT = 1521))

3.重啟oracle

切換到oracle用戶：

[root@mv161p120 admin]# sudo su - oracle
Last login: Thu Jul 21 16:50:06 CST 2022 on pts/0

查看監(jiān)聽狀態(tài)：

[oracle@mv161p120 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUL-2022 17:19:59

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.161.120)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-JUL-2022 16:21:09
Uptime                    0 days 0 hr. 58 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/mv161p120/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=mv161p120)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "e44dc6e87c454000e05378a1a8c0f895" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@mv161p120 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUL-2022 17:20:25

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.161.120)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

之后用sqlplus連接：

[oracle@mv161p120 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 17:20:32 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.

執(zhí)行shutdown：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

啟動(dòng)監(jiān)聽：

[oracle@mv161p120 ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUL-2022 17:21:39

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/mv161p120/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.161.120)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.161.120)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-JUL-2022 17:21:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/mv161p120/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.161.120)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@mv161p120 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-JUL-2022 17:21:46

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.161.120)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                21-JUL-2022 17:21:39
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/mv161p120/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.161.120)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

sqlplus連接并啟動(dòng)：

[oracle@mv161p120 ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 17:21:53 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 2466250400 bytes
Fixed Size		    9137824 bytes
Variable Size		  570425344 bytes
Database Buffers	 1879048192 bytes
Redo Buffers		    7639040 bytes
Database mounted.
Database opened.

4.測試

netstat驗(yàn)證：

[root@mv161p120 admin]# netstat -an |grep 1521
tcp        0      0 192.168.161.120:1521    0.0.0.0:*               LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     126161   /var/tmp/.oracle/sEXTPROC1521
[root@mv161p120 admin]# 

客戶端配置：

之后客戶端連接測試：