Oracle SHARED mode server confiuguration error ORA-12523

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

---