CODEWITHSUNDEEP
databaseoracle-databasedatabase-administration
Shashi
Shashi

Reputation: 315

ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance "CLRExtProc", status UNKNOWN

I'm getting this error if i try to login as db user. If lsnrctl status is run i get the below error.
DB was working fine all these years and stopped working suddenly.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ABC.LOCAL)(PORT=1521)
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                19-MAY-2014 12:18:17
Uptime                    0 days 0 hr. 22 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\Oracle\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         d:\oracle\administrator\diag\tnslsnr\abc\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ABC.LOCAL)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "devdb" has 1 instance(s).
  Instance "devdb", status BLOCKED, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

Upvotes: 8

Views: 186165

Answers (8)

lumizic
lumizic

Reputation: 1

I had this error when trying to connect an API container to an oracle DB container which were both started by docker compose. The solution was to add "condition: service_healthy" in the docker compose so that the API would only start running when the DB oracle finished initializing. See my docker-compose below:

version: '3.9'
services:
   api:
      build:
         context: . 
      depends_on:
         db:
            condition: service_healthy
      environment:
         - SPRING_DATASOURCE_URL=jdbc:oracle:thin:@//db:1521/xe
         - SPRING_DATASOURCE_USERNAME=system
         - SPRING_DATASOURCE_PASSWORD=oracle
      ports:
         - 8888:8080
      networks:
         spring-net:
           aliases:
             - spring-host
   db:
      image: oracleinanutshell/oracle-xe-11g:latest
      healthcheck:
         test: "exit 0"
      ports:
         - 49161:1521
         - 5500:5500
      environment:
         - ORACLE_ALLOW_REMOTE=true
      networks:
         spring-net:
           aliases:
             - db-host
networks:
  spring-net:
    driver: bridge
    ipam:
      driver: default

Upvotes: 0

David Lipschitz
David Lipschitz

Reputation: 152

Thanks for the above. I had to do a combination of things. Here are my notes:

$ or c:\ > sqlplus "/as sysdba"
SQL> shutdown abort;
SQL> startup mount;

This error happened: ora-00214: more here: https://knowledge.exlibrisgroup.com/Aleph/Knowledge_Articles/Oracle_database_fails_to_start%2C_error_message%3A_ORA-00214%3A_control_file_control02.ctl_%5B...%5D_inconsistent_with_file_control01.ctl

Go to

C:\app\david\virtual\oradata\orcl

backup ctl files. Copy CONTROL01.CTL to CONTROL02.CTL

See if startup mount works; it does!

select instance_name, status, database_status from v$instance;

instance name; status; database_status
orcl; MOUNTED; ACTIVE

Listener Stop and Start in Windows Services : didn’t help

$ sqlplus nolog
SQL> connect / as sysdba
Connected.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> elect instance_name, status, database_status from v$instance;
instance name; status; database_status
orcl; MOUNTED; ACTIVE

Listener Stop and Start in Windows Services : now worked. (note that in both cases the listener showed as running).

$ tnsping orcle
Last line should say OK (0 msec)

Mine Dev Sandbox environment is localhost port 1521 service_name = orcl

Now I can connect from Toad, etc. firstly as SYSTEM

select * from davidl.dlfirst;

Upvotes: 0

scvblwxq
scvblwxq

Reputation: 51

I tried restarting my computer and that fixed it for me.

Upvotes: 1

Martin Staufcik
Martin Staufcik

Reputation: 9502

I had this problem on my developent environment with Visual Studio.

What helped me was to Clean Solution in Visual Studio and then do a rebuild.

Upvotes: 3

leo
leo

Reputation: 3749

I had this error message with boot2docker on windows with the docker-oracle-xe-11g image (https://registry.hub.docker.com/u/wnameless/oracle-xe-11g/).

The reason was that the virtual box disk was full (check with boot2docker.exe ssh df). Deleting old images and restarting the container solved the problem.

Upvotes: 4

Chandan C
Chandan C

Reputation: 138

If you are using 11G XE with Windows, along with tns listener restart, make sure Windows Event Log service is started.

Upvotes: 1

eliatou
eliatou

Reputation: 744

You have to know if the problem come from the listener or from the database.

  • So first, restart the listener, it could solve the problem.

  • Second, it could come from the db if it's not in open mode (nomount, mount, restrict). To check it, connect locally and do the following query:

    sqlplus /nolog

    connect / as sysdba

    SQL> select instance_name, status, database_status from v$instance;

Upvotes: 11

Randy
Randy

Reputation: 16673

set ORACLE_SID=<YOUR_SID>
sqlplus "/as sysdba"
alter system disable restricted session;

or maybe

shutdown abort;

or maybe

lsnrctl stop

lsnrctl start

Upvotes: 18

Related Questions