1.問題描述

在linux服務器centos7上新安裝的oracle 19c，結果發現客戶端怎么都連不上。檢查了下，發現原來是監聽的端口采用默認配置為了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]$ 

可以看到監聽端口的ip為127.0.0.1。是本地回環路由。其他服務器無法訪問這個地址。
現在對這個問題進行修改：

2.修改listener.ora 文件

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

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

vim這個文件：

# 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

查看監聽狀態：

[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.

執行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

啟動監聽：

[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連接并啟動：

[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驗證：

[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]# 

客戶端配置：

之后客戶端連接測試：