Tag Archives: ORA-12526

Blocked Connection in listener

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.

Solution 1
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.
lsnrclt services

--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]

Solution 2
Check database is not present in mount, nomount & restricted state if not then start the database in open state:

Startup

Advertisements