Summary
Configure the Shared mode for Oracle connection, it failed and returned ORA-12523, found out it is mandatory to configure local listener.
本地监听的配置是必须的。
Configure Shared server mode
‘listener.ora’ was configured as below,
LISTENER3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocp)(PORT = 1523))
)
LISTENER2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocp)(PORT = 1522))
)
ADR_BASE_LISTENER3 = /u01/app/tom
ADR_BASE_LISTENER2 = /u01/app/tom
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocp)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/tom
SID_LIST_LISTENER =
( SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
SID_LIST_LISTENER2 =
( SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
SID_LIST_LISTENER3 =
( SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
tnsnames.ora was configured as below,
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1521))
LISTENER2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1522))
LISTENER3 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1523))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocp)(PORT = 1522))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
#(SID = orcl)
)
)
ORCL3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1523))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
)
)
Configure the shared servers by commands as below,
SQL> alter system set shared_servers=10;
系统已更改。
SQL>
Configure dispatchers:
SQL> alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=4) (SERVICE=orcl)';
系统已更改。
SQL>
SQL> show parameter share;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 16M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 10
SQL>
SQL> select name, status from v$shared_server;
NAME STATUS
-------------------------------------------------- ------------------------------------------------
S000 WAIT(COMMON)
S001 WAIT(COMMON)
S002 WAIT(COMMON)
S003 WAIT(COMMON)
S004 WAIT(COMMON)
S005 WAIT(COMMON)
S006 WAIT(COMMON)
S007 WAIT(COMMON)
S008 WAIT(COMMON)
S009 WAIT(COMMON)
已选择10行。
Checking the network port status,
SQL>
[root@ocp ~]# netstat -ntulp | grep 152
tcp6 0 0 :::1521 :::* LISTEN 3807/tnslsnr
tcp6 0 0 :::1522 :::* LISTEN 3816/tnslsnr
tcp6 0 0 :::1523 :::* LISTEN 3888/tnslsnr
[root@ocp ~]#
Problem
SQL> conn sys/oracle@ORCL2 as sysdba
ERROR:
ORA-12523: TNS: 监听程序无法找到适用于客户机连接的例程
警告: 您不再连接到 ORACLE。
SQL>
SQL> conn sys/oracle@ORCL3 as sysdba
ERROR:
ORA-12523: TNS: 监听程序无法找到适用于客户机连接的例程
警告: 您不再连接到 ORACLE。
Solution
The problem ORA-12523 was casued by missing out seup the local listener, it can be resolved by below command, setting up the local listener.
SQL> alter system set local_listener=LISTENER3,LISTENER2,LISTENER_ORCL;
系统已更改。
SQL>
The above command is the same as below,
SQL> alter system set local_listener='(address_list=(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1522))(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.26.130)(PORT = 1523)))';
系统已更改。
SQL>
Checking result,
SQL> show parameter local_l;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string LISTENER3, LISTENER2, LISTENER
_ORCL
SQL>
If use 2nd command, the show parameter result is as below,
SQL> show parameter local_l;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string (address_list=(ADDRESS = (PROT
OCOL = TCP)(HOST = 192.168.26.
130)(PORT = 1521))(ADDRESS = (
PROTOCOL = TCP)(HOST = 192.168
.26.130)(PORT = 1522))(ADDRESS
= (PROTOCOL = TCP)(HOST = 192
.168.26.130)(PORT = 1523)))
SQL>
The connection result is as below,
SQL> conn sys/oracle@ORCL as sysdba
已连接。
SQL> conn sys/oracle@ORCL2 as sysdba
已连接。
SQL> conn sys/oracle@ORCL3 as sysdba
已连接。
SQL>
SQL> show parameter share;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 16M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 10
SQL> select name, status from v$shared_server;
NAME STATUS
-------------------------------------------------- ------------------------------------------------
S000 WAIT(COMMON)
S001 EXEC
S002 EXEC
S003 WAIT(COMMON)
S004 WAIT(COMMON)
S005 WAIT(COMMON)
S006 WAIT(COMMON)
S007 WAIT(COMMON)
S008 WAIT(COMMON)
S009 WAIT(COMMON)
已选择10行。
SQL>
In conclustion, in shared mode server configuration, setting local listner is mandatory.
本地监听的配置是必须的。
Reference
oracle共享服务器配置汇总(53天)
【Oracle】静态监听导致的ORA-12523错误