Tag Archives: Check Listener Status

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

Start and Stop Listener in Oracle

Oracle Listener Information

1. Check the listener running on Operating System.

-- On Linux
ps -ef|grep tns
-- On windows
Open the services.msc and check the name with OracleTNS
For default Listener name: OracleTNSListener

2. Check the listener status with following command.
For status the Listener run following command from command prompt:

lsnrctl status
Note: If listener name is default LISTENER then no need to specify

3. Stop the listener
For start the Listener run following command from command prompt:

lsnrclt stop
Note: If listener name is default LISTENER then no need to specify

4. Start the listener
For start the Listener run following command from command prompt:

lsnrctl start
Note: If listener name is default LISTENER then no need to specify

Reload the listener
If you made any changes in listener.ora file. For effect the changes you need to reload the Listener configuration.

lsnrctl reload
Note: If listener name is default LISTENER then no need to specify

 
Check the Listener Status
lsnrctl status command tell the listener configuration or status :
Ready – Means listener is register dynamically
unknown – lsnrctl having entries in listener.ora file
Blocked – database is open in restricted mode or DR (dataguard) is configured

Stop or Disable the listener log

set log_status off
rename listener.log listener.old
set log_status on