in Oracle

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


Write a Comment

Comment