修改oracle12c监听端口,oracle12c 多租户管理三（监听）

一、local_listener参数说明

1、By default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521

2、To have PMON register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

二、动态注册

2.1、使用默认端口,非默认监听名

1、监听文件内容如下

[oracle@oracle18c1 admin]$ cat listener.ora

mycdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.40)(PORT = 1521))

)

[oracle@oracle18c1 admin]$ /sbin/ifconfig -a

ens33: flags=4163 mtu 1500

inet 192.168.5.40 netmask 255.255.255.0 broadcast 192.168.5.255

inet6 fe80::20c:29ff:fe3b:ab57 prefixlen 64 scopeid 0x20

ether 00:0c:29:3b:ab:57 txqueuelen 1000 (Ethernet)

RX packets 1341 bytes 125876 (122.9 KiB)

RX errors 0 dropped 0 overruns 0 frame 0

TX packets 833 bytes 112696 (110.0 KiB)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

2、启动监听mycdb

[oracle@oracle18c1 admin]$ lsnrctl start mycdb

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 16-MAY-2020 20:56:21

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

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

TNSLSNR for Linux: Version 18.0.0.0.0 - Production

System parameter file is /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.40)(PORT=1521)))

STATUS of the LISTENER

Alias mycdb

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 16-MAY-2020 20:56:21

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/18c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening Endpoints Summary...

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

The listener supports no services

The command completed successfully

3、修改local_listener

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1521)))';

System altered.

3、发起快速注册命令

SQL> alter system register;

System altered.

4、查看监听状态

[oracle@oracle18c1 admin]$ lsnrctl status mycdb

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 16-MAY-2020 21:01:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.40)(PORT=1521)))

STATUS of the LISTENER

Alias mycdb

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 16-MAY-2020 21:00:43

Uptime 0 days 0 hr. 1 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "64a52f53a7683286e053cda9e80aed76" has 1 instance(s).

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

Service "9fb8b7c48142c9d1e0532805a8c0ebe2" has 1 instance(s).

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

Service "9fb8b9abf261cad7e0532805a8c0d1a0" has 1 instance(s).

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

Service "9fb97b02bd6ae395e0532805a8c045d9" has 1 instance(s).

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

Service "mycdb" has 1 instance(s).

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

Service "mycdbXDB" has 1 instance(s).

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

Service "mypdb1" has 1 instance(s).

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

Service "mypdb2" has 1 instance(s).

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

Service "youpdb" has 1 instance(s).

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

The command completed successfully

5、连接测试

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40/mycdb as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:06:48 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED READ ONLY NO

3 MYPDB1 READ WRITE NO

4 MYPDB2 READ WRITE NO

5 YOUPDB MOUNTED

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40/mypdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:07:04 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

3 MYPDB1 READ WRITE NO

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40/mypdb2 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:07:14 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

4 MYPDB2 READ WRITE NO

2.2、非默认端口的动态监听

1、监听配置文件如下：

[oracle@oracle18c1 admin]$ cat listener.ora

listener.ora Network Configuration File: /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Generated by Oracle configuration tools.

mycdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.40)(PORT = 1522))

)

2、启动监听mycdb

[oracle@oracle18c1 admin]$ lsnrctl start mycdb

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 16-MAY-2020 21:09:25

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

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

TNSLSNR for Linux: Version 18.0.0.0.0 - Production

System parameter file is /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.40)(PORT=1522)))

STATUS of the LISTENER

Alias mycdb

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 16-MAY-2020 21:09:25

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/18c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1522)))

The listener supports no services

The command completed successfully

3、修改local_listener参数

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1522)))';

System altered.

4、快速注册

SQL> alter system register;

System altered.

5、查看监听状态

[oracle@oracle18c1 admin]$ lsnrctl status mycdb

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 16-MAY-2020 21:10:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.40)(PORT=1522)))

STATUS of the LISTENER

Alias mycdb

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 16-MAY-2020 21:09:25

Uptime 0 days 0 hr. 1 min. 23 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1522)))

Services Summary...

Service "64a52f53a7683286e053cda9e80aed76" has 1 instance(s).

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

Service "9fb8b7c48142c9d1e0532805a8c0ebe2" has 1 instance(s).

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

Service "9fb8b9abf261cad7e0532805a8c0d1a0" has 1 instance(s).

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

Service "9fb97b02bd6ae395e0532805a8c045d9" has 1 instance(s).

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

Service "mycdb" has 1 instance(s).

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

Service "mycdbXDB" has 1 instance(s).

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

Service "mypdb1" has 1 instance(s).

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

Service "mypdb2" has 1 instance(s).

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

Service "youpdb" has 1 instance(s).

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

The command completed successfully

6、连接测试

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40:1522/mycdb as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:12:50 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED READ ONLY NO

3 MYPDB1 READ WRITE NO

4 MYPDB2 READ WRITE NO

5 YOUPDB MOUNTED

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40:1522/mypdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:14:02 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

3 MYPDB1 READ WRITE NO

三、静态注册

1、注意事项

GLOBAL_DBNAME=容器的名字

Sid 输入容器的实例名字

2、监听文件内容如下

[oracle@oracle18c1 admin]$ cat listener.ora

SID_LIST_mycdb =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = mycdb)

(ORACLE_HOME = /opt/oracle/product/18c/dbhome_1)

(SID_NAME = mycdb)

)

(SID_DESC =

(GLOBAL_DBNAME = mypdb1)

(ORACLE_HOME = /opt/oracle/product/18c/dbhome_1)

(SID_NAME = mycdb)

)

(SID_DESC =

(GLOBAL_DBNAME = mypdb2)

(ORACLE_HOME = /opt/oracle/product/18c/dbhome_1)

(SID_NAME = mycdb)

)

)

ADR_BASE_CDB = /oracle/app

mycdb =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.40)(PORT = 1521))

)

3、启动监听

[oracle@oracle18c1 admin]$ lsnrctl start mycdb

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 16-MAY-2020 21:28:42

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

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

TNSLSNR for Linux: Version 18.0.0.0.0 - Production

System parameter file is /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.40)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.40)(PORT=1521)))

STATUS of the LISTENER

Alias mycdb

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 16-MAY-2020 21:28:42

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/18c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "mycdb" has 1 instance(s).

Instance "mycdb", status UNKNOWN, has 1 handler(s) for this service...

Service "mypdb1" has 1 instance(s).

Instance "mycdb", status UNKNOWN, has 1 handler(s) for this service...

Service "mypdb2" has 1 instance(s).

Instance "mycdb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

4、查看监听文件状态

[oracle@oracle18c1 admin]$ lsnrctl status mycdb

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 16-MAY-2020 21:28:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.40)(PORT=1521)))

STATUS of the LISTENER

Alias mycdb

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 16-MAY-2020 21:28:42

Uptime 0 days 0 hr. 0 min. 11 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/oracle18c1/mycdb/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "mycdb" has 1 instance(s).

Instance "mycdb", status UNKNOWN, has 1 handler(s) for this service...

Service "mypdb1" has 1 instance(s).

Instance "mycdb", status UNKNOWN, has 1 handler(s) for this service...

Service "mypdb2" has 1 instance(s).

Instance "mycdb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

5、登陆验证

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40/mycdb as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:35:03 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED READ ONLY NO

3 MYPDB1 READ WRITE NO

4 MYPDB2 READ WRITE NO

5 YOUPDB MOUNTED

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40:1521/mypdb1 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:35:16 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

3 MYPDB1 READ WRITE NO

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

[oracle@oracle18c1 admin]$ sqlplus sys/Pa44w0rd@192.168.5.40:1521/mypdb2 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 16 21:35:23 2020

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

4 MYPDB2 READ WRITE NO

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0