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
[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

独学无友,孤陋寡闻;学习记录,欢迎拍砖!