oracle 11g scan 监听,修改Oracle 11gR2 RAC SCAN监听跟本地监听端口

修改SCAN监听的端口

将SCAN监听的端口修改为3521，本地监听的端口不变，依然为1521:

[root@web1 ~]# srvctl modify scan_listener -p 3521

修改后查看监听状态：

[grid@web2 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-5月 -2016 01:32:52

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

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

LISTENER 的 STATUS

------------------------

别名 LISTENER_SCAN1

版本 TNSLSNR for Linux: Version 11.2.0.3.0 - Production

启动日期 06-5月 -2016 01:31:28

正常运行时间 0 天 0 小时 1 分 23 秒

跟踪级别 off

安全性 ON: Local OS Authentication

SNMP OFF

监听程序参数文件 /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

监听程序日志文件 /u01/app/11.2.0/grid/product/db_1/log/diag/tnslsnr/web2/listener_scan1/alert/log.xml

监听端点概要...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.15)(PORT=1521)))

服务摘要..

服务 "prod" 包含 2 个实例。

实例 "prod1", 状态 READY, 包含此服务的 1 个处理程序...

实例 "prod2", 状态 READY, 包含此服务的 1 个处理程序...

服务 "prodXDB" 包含 2 个实例。

实例 "prod1", 状态 READY, 包含此服务的 1 个处理程序...

实例 "prod2", 状态 READY, 包含此服务的 1 个处理程序...

命令执行成功

[grid@web2 ~]$

发现SCAN监听端口号还是1521，是因为没有重启监听原因，接下来重启监听：

[root@web1 ~]# srvctl stop scan_listener

[root@web1 ~]# srvctl start scan_listener

更改确认：

[root@web1 ~]# srvctl config scan_listener

SCAN 监听程序 LISTENER_SCAN1 已存在。端口: TCP:3521

[root@web1 ~]#

重启SCAN监听后再次查看状态：

[grid@web2 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-5月 -2016 01:34:47

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

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

LISTENER 的 STATUS

------------------------

别名 LISTENER_SCAN1

版本 TNSLSNR for Linux: Version 11.2.0.3.0 - Production

启动日期 06-5月 -2016 01:34:42

正常运行时间 0 天 0 小时 0 分 4 秒

跟踪级别 off

安全性 ON: Local OS Authentication

SNMP OFF

监听程序参数文件 /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

监听程序日志文件 /u01/app/11.2.0/grid/product/db_1/log/diag/tnslsnr/web2/listener_scan1/alert/log.xml

监听端点概要...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.15)(PORT=3521)))

监听程序不支持服务

命令执行成功

[grid@web2 ~]$

发现端口已经修改为3521，只是监听器上没有实例被注册进去，此时也无法通过192.168.1.15:3521/prod这种远程方式访问数据库，下面通过修改实例的remote_listener参数让实例向SCAN监听器进行注册：

[root@web1 ~]# su - oracle

[oracle@web1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期五 5月 6 01:20:17 2016

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> show parameter remote_listener

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

remote_listener string web-cluster-scan:1521

SQL>

SQL> alter system set remote_listener='web-cluster-scan:3521';

系统已更改。

SQL>

执行完成后，查看SCAN监听器，实例已经注册进去：

[grid@web2 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-5月 -2016 01:45:37

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

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

LISTENER 的 STATUS

------------------------

别名 LISTENER_SCAN1

版本 TNSLSNR for Linux: Version 11.2.0.3.0 - Production

启动日期 06-5月 -2016 01:34:42

正常运行时间 0 天 0 小时 10 分 54 秒

跟踪级别 off

安全性 ON: Local OS Authentication

SNMP OFF

监听程序参数文件 /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

监听程序日志文件 /u01/app/11.2.0/grid/product/db_1/log/diag/tnslsnr/web2/listener_scan1/alert/log.xml

监听端点概要...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.15)(PORT=3521)))

服务摘要..

服务 "prod" 包含 2 个实例。

实例 "prod1", 状态 READY, 包含此服务的 1 个处理程序...

实例 "prod2", 状态 READY, 包含此服务的 1 个处理程序...

服务 "prodXDB" 包含 2 个实例。

实例 "prod1", 状态 READY, 包含此服务的 1 个处理程序...

实例 "prod2", 状态 READY, 包含此服务的 1 个处理程序...

命令执行成功

[grid@web2 ~]$

下面通过远程访问，可以访问成功：

C:\Users\Bill>sqlplus sys/oracle@192.168.1.15:3521/prod as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 5月 6 01:47:27 2016

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

下面4个ip地址为public ip或vip，他们均无法访问，因为它们都由本地监听器进行监听，且端口仍为1521，还未改为3521：

C:\Users\Bill>sqlplus sys/oracle@192.168.1.11:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.12:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.13:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.14:3521/prod as SYSDBA

上面均报如下错误：

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 5月 6 01:49:31 2016

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

ERROR:

ORA-12541: TNS: 无监听程序

修改本地监听的端口

获取当前监听器的配置信息：

[root@web1 ~]# srvctl config listener

名称: LISTENER

网络: 1, 所有者: grid

主目录:

端点: TCP:1521

[root@web1 ~]#

下面修改1节点的本地监听端口为3521：

[root@web1 ~]# srvctl modify listener -l LISTENER -p "TCP:3521"

修改后查看状态：

[grid@web1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-MAY-2016 02:01:43

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 06-MAY-2016 01:15:17

Uptime 0 days 0 hr. 46 min. 25 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/web1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.13)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "prod" has 1 instance(s).

Instance "prod1", status READY, has 1 handler(s) for this service...

Service "prodXDB" has 1 instance(s).

Instance "prod1", status READY, has 1 handler(s) for this service...

The command completed successfully

[grid@web1 ~]$

还没有修改过来，需要重启监听：

[root@web1 ~]# srvctl stop listener

[root@web1 ~]# srvctl start listener

[root@web1 ~]#

重启后再查看状态：

[grid@web1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-MAY-2016 02:02:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 06-MAY-2016 02:02:30

Uptime 0 days 0 hr. 0 min. 27 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/web1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=3521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.13)(PORT=3521)))

The listener supports no services

The command completed successfully

[grid@web1 ~]$

端口已经修改为3521，但实例没有被注册进去，下面修改参数local_listener让实例的PMON进程将实例注册到监听器：

[oracle@web1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期五 5月 6 02:03:50 2016

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.13)(PORT=3521))))' sid='prod1';

系统已更改。

SQL>

修改完成后马上查看监听的状态：

[grid@web1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-MAY-2016 02:10:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 06-MAY-2016 02:02:30

Uptime 0 days 0 hr. 7 min. 48 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/product/db_1/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/web1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=3521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.13)(PORT=3521)))

Services Summary...

Service "prod" has 1 instance(s).

Instance "prod1", status READY, has 1 handler(s) for this service...

Service "prodXDB" has 1 instance(s).

Instance "prod1", status READY, has 1 handler(s) for this service...

The command completed successfully

[grid@web1 ~]$

发现实例已经注册进去。

用同样的方法将节点2的本地监听的端口也修改为3521，并将实例注册到本地监听，因步骤和上面相同，这里就不再赘述。

通过修改SCAN监听器的端口及本地监听器的端口，下面5个IP地址均能正常访问数据库：

C:\Users\Bill>sqlplus sys/oracle@192.168.1.11:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.12:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.13:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.14:3521/prod as sysdba

C:\Users\Bill>sqlplus sys/oracle@192.168.1.15:3521/prod as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 5月 6 02:17:22 2016

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

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

HELLO

-------------------------------------------------------------------------------------

The print set file:D:\u01\app\oracle\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql

SQL>

总结

1.如果只修改SCAN监听器的端口而没修改本地监听器的端口，则SCAN IP可以使用修改后的端口访问数据库，而PUBLIC IP和VIP是无法使用新端口访问数据库的。

2.如果SCAN监听器和本地监听器的端口都修改了，则所有IP地址都可以使用新端口进行访问数据库。

Reference

How to Modify SCAN Setting or SCAN Listener Port after Installation (文档 ID 972500.1)

Changing Listener Ports On RAC/EXADATA (文档 ID 1473035.1)