ORA-12523: TNS: Listeners cannot find routines for client connection, ora-12523tns

ORA-12523: TNS: Listeners cannot find routines for client connection, ora-12523tns

Today, when you connect to a new test server using PL/SQL Developer, you encounter a ORA error: ORA-12523: TNS: The Listener cannot find a routine for client connection. The error in the corresponding listening log file is TNS-12523: TNS: listener cocould not find instance appropriate for the client connection

First use the oerr command to View Details of the ORA-12523 error prompt

[oracle@DB-Server admin]$ oerr ora 12523

12523, 00000, "TNS:listener could not find instance appropriate for the client connection"

// *Cause:  The listener could not find any available (database) instances, 

// that are appropriate for the client connection.

// *Action: Run "lsnrctl services" to ensure that the instance(s) are

// registered with the listener, and have status READY.

As prompted, I first run "lsnrctl services" to check whether the database instance has registered the listening service and whether the listening service is ready.

[oracle@DB-Server admin]$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 06-JAN-2015 14:24:23

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.22)(PORT=1521)))

Services Summary...

Service "EPPS" has 1 instance(s).

  Instance "EPPS", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

The command completed successfully

Check the configuration of the local tnsnames. ora and find that it is connected to the database using the SHARED Server mode (SHARED ).

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.16.22) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = epps)
)

Check to check the test Server and check whether the Shared Server mode is enabled for its configuration. As shown in the following figure, the database is in the Shared Server mode.

SQL> show parameter shared_server

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers                   integer     40

shared_server_sessions               integer

shared_servers                       integer     1

SQL> 

SQL> show parameter dispatchers

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

dispatchers                          string                           (protocol=TCP)

max_dispatchers                      integer

SQL> 

But why does the client fail to log on to the database in SHARED mode when the SHARED Server mode is enabled for the database? Then I first modify the client connection mode to the DEDICATED (DEDICATED) mode.

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.16.22) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = epps)
)
)

At this point in the use of PL/SQL Developer login database, found OK, no ORA-12523 error Oh. View the current session. You can use the following SQL statements to find a dedicated connection to the database.

SQL> select server from v$session where sid = (select sid from v$mystat where rownum < 2);

SERVER

---------

DEDICATED

Of course, you can view it using the following SQL statement, where GET253194 is the name of your computer.

COL USERNAME FOR A20

COL OSUSER FOR A10

COL MACHINE FOR A20

COL TERMINAL FOR A20;

SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER 

FROM V$SESSION

WHERE TERMINAL='GET253194'; 

 

Modify tnsnames. ora and delete (SERVER = DEDICATED) (as shown below). You can still connect to the database, view the connection method, and find that it will select its own DEDICATED connection method. That is to say, the connection is in dedicated mode by default, unless it is in Shared Server mode.

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.16.22) (PORT = 1521 ))
(CONNECT_DATA =
(SERVICE_NAME = epps)
)
)

SQL> select server from v$session where sid = (select sid from v$mystat where rownum < 2);

SERVER

---------

DEDICATED

So where is the problem? I am wondering that the server is in the shared server mode and you can connect to the database through a shared service connection. In the end, I found that this server was originally a production server, and I was taken back as a test server, so I changed the IP address to 192.168.16.22, but only modified the/etc/sysconfig/network-scripts/ifcfg-eth0 system file. you forgot to modify the IP address of/etc/hosts. After I modified the IP address in/etc/hosts, I found that the ORA-12523 error disappeared and the problem was solved. But why? It seems that you still need to have an in-depth understanding of the monitoring service principles. Learn more and learn more about related materials. To be added later.