文章目录

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)

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>

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,

> 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错误​​