Modify Oracle 11gR2 RAC SCAN listening and local listening ports

Modify the port SCAN listens on

Modify the port SCAN listens on to 3521, and the port local listens on remains the same, at 1521:

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

View the listening status after modification:

[grid@web2 ~]$ lsnrctl status LISTENER_SCAN1

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

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

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER_SCAN1))
STATUS of LISTENER
------------------------
Alias LISTENER_SCAN1
 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 Start date 06-May-2016 01:31:28
 Normal operating time 0 days, 0 hours, 1 minute, 23 seconds
 Tracking 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/11.2.0/grid/product/db_1/log/diag/tnslsnr/web2/listener_scan1/alert/log.xml
 Listening Endpoint Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.15)(PORT=1521)))
Service Summary..
The service "prod" contains two instances.
  Instance "prod1", status READY, contains a handler for this service...
  Instance "prod2", status READY, contains a handler for this service...
The service "prodXDB" contains two instances.
  Instance "prod1", status READY, contains a handler for this service...
  Instance "prod2", status READY, contains a handler for this service...
Command executed successfully
[grid@web2 ~]$

SCAN listen port number or 1521 was found because there was no reason to restart listening, and then restart listening:

[root@web1 ~]# srvctl stop scan_listener
[root@web1 ~]# srvctl start scan_listener

Change Confirmation:

[root@web1 ~]# srvctl config scan_listener
SCAN listener LISTENER_SCAN1 Already exists.port: TCP:3521
[root@web1 ~]#

Review the status again after restarting SCAN listening:

[grid@web2 ~]$ lsnrctl status LISTENER_SCAN1

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

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

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER_SCAN1))
STATUS of LISTENER
------------------------
Alias LISTENER_SCAN1
 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 Start date 06-May-2016 01:34:42
 Normal operating time 0 days, 0 hours, 0 minutes, 4 seconds
 Tracking 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/11.2.0/grid/product/db_1/log/diag/tnslsnr/web2/listener_scan1/alert/log.xml
 Listening Endpoint Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.15)(PORT=3521)))
Listener does not support services
 Command executed successfully
[grid@web2 ~]$

The port has been modified to 3521, but no instances on the listener have been registered and the database cannot be accessed remotely by 192.168.1.15:3521/prod at this time. The following is to register an instance with the SCAN listener by modifying the remote_listener parameter of the instance:

[root@web1 ~]# su - oracle
[oracle@web1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Friday 5month 6 01:20:17 2016

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


//Connect to: 
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';

//The system has changed.

SQL>

After execution, look at the SCAN listener and the instance is registered:

[grid@web2 ~]$ lsnrctl status LISTENER_SCAN1

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

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

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER_SCAN1))
STATUS of LISTENER
------------------------
Alias LISTENER_SCAN1
 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 Start date 06-May-2016 01:34:42
 Normal operating time 0 days, 0 hours, 10 minutes, 54 seconds
 Tracking 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/11.2.0/grid/product/db_1/log/diag/tnslsnr/web2/listener_scan1/alert/log.xml
 Listening Endpoint Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.15)(PORT=3521)))
Service Summary..
The service "prod" contains two instances.
  Instance "prod1", status READY, contains a handler for this service...
  Instance "prod2", status READY, contains a handler for this service...
The service "prodXDB" contains two instances.
  Instance "prod1", status READY, contains a handler for this service...
  Instance "prod2", status READY, contains a handler for this service...
Command executed successfully
[grid@web2 ~]$

The following are accessed successfully through remote access:

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

SQL*Plus: Release 11.2.0.4.0 Production on Friday 5month 6 01:47:27 2016

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


//Connect to:
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>

The next four ip addresses are public ip or vip, and they are not accessible because they are being listened on by the local listener and the port is still 1521 and has not been changed to 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

The following errors were reported above:

SQL*Plus: Release 11.2.0.4.0 Production on Friday May 6 01:49:31 2016

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

ERROR:
ORA-12541: TNS: No listener

Modify local listening port

Get the configuration information for the current listener:

[root@web1 ~]# srvctl config listener
 Name: LISTENER
 Network: 1, Owner: grid
 Home directory: <CRS home>
Endpoint: TCP:1521
[root@web1 ~]#

Modify the local listening port of 1 node below to 3521:

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

View status after modification:

[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 ~]$

Not modified yet, need to restart listening:

[root@web1 ~]# srvctl stop listener
[root@web1 ~]# srvctl start listener
[root@web1 ~]#

Review the status after restart:

[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 ~]$

The port has been modified to 3521, but the instance has not been registered. Modify the parameter local_listener below to have the PMON process of the instance register the instance to the listener:

[oracle@web1 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Friday 5month 6 02:03:50 2016

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


//Connect to: 
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';

//The system has changed.

SQL>

View the status of the monitoring as soon as the modification is complete:

[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 ~]$

The instance found has already been registered.(
In the same way, modify the port on which node 2 listens locally to 3521, and register the instance to listen locally, since the steps are the same as above, they will not be repeated here.

By modifying the port of the SCAN listener and the port of the local listener, the following five IP addresses can access the database normally:

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 Friday May 6 02:17:22 2016

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


//Connect to:
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>

summary

1. If you only modify the port of the SCAN listener but not the port of the local listener, SCAN IP can access the database using the modified port, while PUBLIC IP and VIP cannot access the database using the new port.(
2. If the ports of both the SCAN listener and the local listener are modified, all IP addresses can access the database using the new port.

Reference

How to Modify SCAN Setting or SCAN Listener Port after Installation (Document ID 972500.1)
Changing Listener Ports On RAC/EXADATA (Document ID 1473035.1)

Keywords: Oracle SQL sqlplus Linux

Added by shinstar on Sat, 25 May 2019 22:58:12 +0300

Programming VIP

Modify Oracle 11gR2 RAC SCAN listening and local listening ports

Modify the port SCAN listens on

Modify local listening port

summary

Reference

Popular Keywords