南山草舍

0%

Oracle单实例配置多个监听

发表于 更新于 分类于

监听注册

Oracle的注册就是将数据库作为一个服务注册到监听程序，而客户端不需要知道数据库名和实例名，只需要知道改数据库对外提供的服务名就可以申请连接到数据库。

动态注册：由PMON进程动态注册至监听中
静态注册：通过解析listene.ora文件

在没有listener.ora配置文件的情况下，如果启动监听，则监听为动态注册。用图形化netca创建的监听，默认也为动态注册；监听的配置文件都在$ORACLE_HOME/network/admin目录下，默认监听名为LISTENER，端口为1521
05

动态监听

在动态注册监听的环境中，listener.ora文件可以不包括当前数据库的实例信息，所以这个文件不必要。
实例启动时，会由Oracle PMON进程将数据库实例信息动态注册至监听上。当Oracle实例关闭时，会再次由PMON进程自动从监听里面撤销当前实例信息。
动态注册成功大概需要一分钟时间，注册成功状态为READY；

一般有3种状态：READY、BLOCKED和RESTRICED
READY：表示数据库实例已经处于mount或者open状态，可以接受客户端连接
BLOCKED：表示数据库实例还处于nomount状态或者该实例类型为ASM实例，不接受客户端连接，如果这时候客户端去连接数据库会报ora-12528错误
RESTRICED：表示数据库处于RESTRICED模式，不接受普通权限的远程客户端连接，如果这时候客户端去连接数据库会报ora-12526错误

编辑监听文件listener.ora

[oracle@dbserver admin]$ cat listener.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    )
  )

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1522))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

编辑tnsnames.ora

[oracle@dbserver admin]$ cat tnsnames.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

启动新创建的监听LISTENER2

[oracle@dbserver admin]$ lsnrctl start LISTENER2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-SEP-2019 21:01:25

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dbserver/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                05-SEP-2019 21:01:25
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/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbserver/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1522)))
The listener supports no services
The command completed successfully

修改实例注册到新创建的监听LISTENER2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[oracle@dbserver admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 5 21:05:56 2019

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


Connected to:
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> show parameter local_listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string
SQL> alter system set local_listener='TEST';

System altered.

SQL> show parameter local_listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 TEST
SQL>

05
05

修改之后，我们查看监听状态，可以发现orcl实例已经从默认的监听LISTENER修改为LISTENER2了。

注册多个端口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>  ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.132)(PORT=1521))','(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.132)(PORT=1522))';

System altered.

SQL> show parameter local_listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 (ADDRESS=(PROTOCOL=TCP)(HOST=1
						 92.168.100.132)(PORT=1521)), (
						 ADDRESS=(PROTOCOL=TCP)(HOST=19
						 2.168.100.132)(PORT=1522))
SQL> alter system register;

System altered.

SQL>

05
05

静态监听

静态注册不管实例是否启动，在启动监听的时候都会将实例名称注册到监听中，主要用于DBA远程启动数据库实例。

编辑监听配置文件listener.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
[oracle@dbserver admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    )
  )

SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_DBNAME = orcl) 
      (SID_NAME = orcl) 
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 ) 
    ) 
  )


LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1522))
    )
  )
SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

启动服务

05
05