ORA-12528: TNS: listeners: all applicable routines cannot create new connections

ORA-12528: TNS: listeners: all applicable routines cannot create new connections

Solution reference http://blog.sina.com.cn/s/blog_4b7325080100bm46.html

First, run the lsnrctl status Command in cmd.
C: \ Documents ents and Settings \ Administrator> LSNRCTL status

The following information is displayed: (Note that you may be in English)

Listener endpoint overview...
(Description = (address = (Protocol = TCP) (host = svctag-5dk652x) (Port = 1521 )))
(Description = (address = (Protocol = IPC) (pipename = \. \ PIPE \ extproc0ipc )))
Service summary ..
The Service "plsextproc" contains one routine.
Routine "plsextproc", status unknown, contains one handler of this service...
The Service "orcl" contains one routine.
Routine "orcl", status blocked, contains 1 handler of this service...
The Service "orcl_xpt" contains one routine.
Routine "orcl", status blocked, contains 1 handler of this service...
Command executed successfully

If you see blocked
This problem indicates that the database does not mount
Connect to the database and find:
ORA-12528: TNS: Listener: all appropriate instances are blocking new connections
Use sqlplus/nolog
SQL> connect [email = sys/ammic @ amicly] sys/ammic @ amicly [/Email] As sysdba
ORA-12528: TNS: Listener: all appropriate instances are blocking new connections

For connection problems, you can modify the parameters of listener. ora to set dynamic parameters to static parameters, marked in red, and then restart the listener.

Location D: \ oracle \ product \ 10.2.0 \ db_1 \ Network \ admin

Sid_list_listener =
  (Sid_list =
 (Sid_desc =
  (Sid_name = plsextproc)
  (ORACLE_HOME = D: \ oracle \ product \ 10.1.0 \ db_1)
  (Program = EXTPROC)
 )
 (Sid_desc =
  (Global_dbname = orcl) // if this orcl already exists, this file does not need to be modified.
  (ORACLE_HOME = D: \ oracle \ product \ 10.1.0 \ db_1) // depending on your actual path
  (Sid_name = orcl)
 )
  )
Listener =
  (Description_list =
 (Description =
  (Address_list =
    (Address = (Protocol = TCP) (host = lypch) (Port = 1521 ))
  )
  (Address_list =
    (Address = (Protocol = IPC) (Key = EXTPROC ))
  )
 )
  )

Modify the tnsnames. ora parameter.
# Tnsnames. ora network configuration file: D: \ oracle \ product \ 10.1.0 \ db_1
\ Network \ admin \ tnsnames. ora
# Generated by Oracle configuration tools.
Ammicly =
  (Description =
 (Address = (Protocol = TCP) (host = lypch) (Port = 1521 ))
 (CONNECT_DATA =
  (Server = dedicated)
  (SERVICE_NAME = orcl)
  (UR = A) // Add this. I don't know what it means.
 )
  )
Extproc_connection_data =
  (Description =
 (Address_list =
  (Address = (Protocol = IPC) (Key = EXTPROC ))
 )
 (CONNECT_DATA =
  (SID = plsextproc)
  (Presentation = Ro)
 )
  )

I restarted the machine at this step, and then it was done, and there was no test in the future.

Then sqlplus/nolog
SQL> alter database Mount;
SQL> alter database open;

In this way, you can start the database;

Or use oradim-shutdown-Sid ammicly-shuttype srvc, inst-shutmode immediate-syspwd ammic; to close the database
Use oradim-startup-Sid ammicly-starttype srvc, inst-syspwd ammic; to start the database solution; or you can start the Database Service on the cross section;

To sum up the RA-12528 problem, because the service in the listener uses the dynamic service, after the system is started, the database does not have the normal mount, so in the dynamic mode, this problem will occur, the above method is to set the listener to static or in tnsnames. add (UR = A) in ora or start the service to solve the problem;