Blocked Connection in listener
Blocked connection occurred when you listener is not registered with database, or when a database is in restricted mode and shutdown of the database is in progress.
If a database is in restricted mode, then LREG Backgroud process block all connections to the instance.
Clients are getting one of the following errors:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
ORA-12527: TNS:listener: all appropriate instances are in restricted mode or blocking new connections
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
The ORA-12528 error occurs when a database instance is not yet registered with the listener.
1. If the database service registration is not listed, then enter the following SQL command:
--1. Check the service is registered with listener if not follow the next step.
--2. Connect with the database
sqlplus sys as sysdba
--3. Fire following command to register the Listener with Database
SQL> ALTER SYSTEM REGISTER;
You can set the listener parameter for register the Server:
-- Mention listener name
ALTER SYSTEM SET LOCAL_LISTENER=listener_sales1;
--In RAC you can also set remote listener
ALTER SYSTEM SET REMOTE_LISTENER=listener_sales2;
cmctl: is used for register the listener on linux or unix system.
CMCTL> ADMINISTER [instance_name]
Check database is not present in mount, nomount & restricted state if not then start the database in open state:
ORA-12528: TNS Listener all instances are blocked in Dataguard Environment
In this case, we are getting the following error when our database is start in mount / nomount / restricted state. Sometime we faced this while configuring the dataguard Environment.
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Oct 1 19:40:51 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> connect sys@dbname as sysdba
ERROR: ORA-12528: TNS:listener: all appropriate instances are blocking new connection
You might get this error if you encounter with Connections via the listener to an instance that is in RESTRICTED status or in NO MOUNT status. The lsnrctl services output will show that the service handler for this instance is in state: BLOCKED or RESTRICTED.
Check the status of services by listener commands:
As example show below the status is blocked for ORCL service:
STATUS of the LISTENER
Version TNSLSNR for Linux: Version 22.214.171.124.0 - Production
Start Date 20-DEC-2014 02:39:22
Uptime 14 days 2 hr. 26 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
Listener Parameter File /u01/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
In Some cases we need to overcome with this problem like dataguard configuration. In which our standby database is in recover mode means it’s in mounted state for apply the redo logs of primary database. In that case listener status for service is Blocked or RESTRICTED. To overcome from it we used the following parameter in tnsnames.ora file:
(UR=A) clause is used for TNS connect strings has been created as an enhancement.*(UR=A)* clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. (ID 362656.1)
Need to modify the tnsnames.ora file for connectivity as shown below:
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
(SERVICE_NAME = hostname.rnhub.com)
(UR = A)