【一台ORACLE数据库服务器两个监听的同时使用】

最新推荐文章于 2021-04-08 06:00:43 发布

骑着蜗牛追汤圆

最新推荐文章于 2021-04-08 06:00:43 发布

阅读量796

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_40003309/article/details/106438301

版权

数据库专栏收录该内容

20 篇文章 2 订阅

订阅专栏

Oracle的监听默认端口是1521，现要使用两个监听端口，首先需要去建立监听

1.建立监听：在listener.ora创建监听

#在listener.ora文件下建立1526端口监听
[oracle@o12c ~]$ cd $ORACLE_HOME/network/admin
[oracle@o12c admin]$ ll
total 16
-rw-r--r-- 1 oracle oinstall  332 Apr 20 00:25 listener.ora
drwxrwxrwx 2 oracle oinstall   64 Apr 19 23:41 samples
-rwxrwxrwx 1 oracle oinstall 1441 Aug 28  2015 shrept.lst
-rw-r--r-- 1 oracle oinstall  149 Apr 25 18:29 sqlnet.ora
-rw-r----- 1 oracle oinstall  643 Apr 26 02:46 tnsnames.ora
[oracle@o12c admin]$ vim listener.ora 
[oracle@o12c admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
LISTENER2=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1526))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
    )
  )
#开启1526端口监听LISTENER2
[oracle@o12c admin]$ lsnrctl start LISTENER2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 06-JUN-2020 21:12:41

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

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/o12c/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o12c)(PORT=1526)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o12c)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                06-JUN-2020 21:12:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o12c/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o12c)(PORT=1526)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
The listener supports no services
The command completed successfully
[oracle@o12c admin]$ lsnrctl status LISTENER2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 06-JUN-2020 21:12:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o12c)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                06-JUN-2020 21:12:41
Uptime                    0 days 0 hr. 0 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o12c/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o12c)(PORT=1526)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
The listener supports no services
The command completed successfully

2.数据库同时注册两个监听：修改local_listener参数，两种方法：

#查看默认的监听注册端口：
SQL> show parameter local;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 LISTENER_TEST
parallel_force_local		     boolean	 FALSE
#修改local_listener参数值：
第一种方法：直接修改参数
SQL>alter system set local_listener="(address = (protocol = tcp)(host = rhel6)(port = 1521))，(address = (protocol = tcp)(host = rhel6)(port = 1526))" scope=both;

System altered.
第二种方法：使用别名文件给监听起别名，然后修改local_listener参数
[oracle@o12c admin]$ vim tnsnames.ora 
[oracle@o12c admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_TEST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
LISTENER2_TEST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1526))


LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))


TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HXPDB)
    )
  )
SQL> alter system set local_listener='LISTENER_TEST,LISTENER2_TEST' scope=both;

System altered.
#修改之后再查看监听注册端口
SQL> show parameter local;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 LISTENER_TEST,LISTENER2_TEST
parallel_force_local		     boolean	 FALSE

3.测试

[c:\~]$ sqlplus sys/pineyang000@192.168.131.200:1526/test

SQL*Plus: Release 12.1.0.1.0 Production on 星期六 1月 7 20:40:03 2017

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


连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

注：一个监听坏了还可以使用另一个监听。

注：上面的使用的是动态注册(隔段时间给监听注册数据库服务信息)，这样的话，速度比较慢，此外还有静态注册。
动态注册：依赖于数据库的启动与关闭。静态注册：在监听文件里配置(标志：监听状态变为UNKNOWN)
静态注册特点：加快连接，用于远程启动数据库（因为动态远程监听可以关库但是启不开库，而静态注册可以关库也可以启库）
注：静态监听+远程密码文件（用于校验远程密码orapw名）=远程启动数据库

将上面的1526动态注册改成静态注册：

[oracle@o12c ~]$ cd $ORACLE_HOME/network/admin
[oracle@o12c admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
LISTENER2=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = o12c)(PORT = 1526))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
    )
  )
#静态注册：SID_LIST_监听名
SID_LIST_LISTENER2=       
  (SID_LIST=
      (SID_DESC=
          (GLOBAL_DNAME=test)
              (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
               (SID_NAME=test)))
               
#先停止1526端口监听LISTENER2  或 进行监听重载 lsnrctl reload
[oracle@o12c admin]$ lsnrctl stop LISTENER2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-JUN-2020 01:30:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o12c)(PORT=1526)))
The command completed successfully

#再开启1526监听端口LISTENER2，这时的状态变为UNKNOWN
[oracle@o12c admin]$ lsnrctl start LISTENER2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-JUN-2020 01:30:43

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

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/o12c/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o12c)(PORT=1526)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o12c)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                07-JUN-2020 01:30:43
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o12c/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o12c)(PORT=1526)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1526)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully