Instance Status BLOCKED in Listener – Oracle Database 11g
Scenarios where you can get Instance Blocked in Listener Status while working with Oracle Environment
This is the first time when I
handled such type of issue where I saw Instance Blocked Status in Listener.
Normally you’ll notice that the status for the added instances is UNKNOWN or
READY but it can also be BLOCKED in some typical scenarios. As per the status,
BLOCKED is simply indicating that you are not allowed to make remote
connections or connection using @ORACLE_SID as it will simply fail the login
with error. Since the instance is blocked, the listener will not allow
connections to the database. This is how you’ll see the BLOCKED status for the
instance,
C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 04-MAY-201817:23:38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbacareer.practice.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 04-MAY-2018 16:46:12
Uptime 0 days 0 hr. 37 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File F:\app\Administrator\diag\tnslsnr\dbacareer\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbacareer.practice.com)(PORT=1521))
)
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "DBCAREER" has 2 instance(s).
Instance "DBCAREER", status UNKNOWN, has 1 handler(s) for this service...
Instance "DBCAREER", status BLOCKED, has 1 handler(s) for this service...
Service "DBCAREERXDB" has 1 instance(s).
Instance "DBCAREER", status BLOCKED, has 1 handler(s) for this service...
Service "demodb" has 1 instance(s).
Instance "demodb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
C:\Windows\system32>
After some digging I found that
the issue was because of Shutdown operation which hadn’t fully completed. The
person who ran the command used SHUTDOWN (normal) instead of SHUTDOWN IMMEDIATE
/ SHUTDOWN TRANSACTIONAL or in rare cases SHUTDOWN ABORT. Since in this scenario,
as the SHUTDOWN NORMAL was used, the database was waiting for the sessions to
release and hence the operation was stuck in the middle.
C:\Windows\system32>set ORACLE_SID=DBCAREER
C:\Windows\system32>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 4 17:39:00 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected.
SQL> select name, open_mode from V$database;
select name, open_mode from V$database
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
In such cases, you can’t stop the
Shutdown operation since it has already started but you can run one more
shutdown command in parallel. But this time run SHUTDOWN IMMEDIATE or if this
doesn’t work then SHUTDOWN ABORT. See below,
C:\Windows\system32>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 4 17:42:31 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 5311549440 bytes
Fixed Size 2290440 bytes
Variable Size 4227861752 bytes
Database Buffers 1056964608 bytes
Redo Buffers 24432640 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name, open_mode from V$database;
NAME OPEN_MODE
--------- --------------------
DBCAREER READ WRITE
SQL>
SQL>
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application testing options
C:\Windows\system32>
C:\Windows\system32>
Once your database is up, again check for the status of Listener now, it will be showing the status ready for the INSTANCE which you just brought up.
C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 04-MAY-2018 17:43:18
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbacareer.practice.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 04-MAY-2018 17:33:23
Uptime 0 days 0 hr. 9 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File F:\app\Administrator\diag\tnslsnr\dbacareer\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbacareer.practice.com)(PORT=1521))
)
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "DBCAREERXDB" has 1 instance(s).
Instance "DBCAREER", status READY, has 1 handler(s) for this service...
Service "demodb" has 1 instance(s).
Instance "demodb", status UNKNOWN, has 1 handler(s) for this service...
Service "DBCAREER" has 2 instance(s).
Instance "DBCAREER", status UNKNOWN, has 1 handler(s) for this service...
Instance "DBCAREER", status READY, has 1 handler(s) for this service...
The command completed successfully
thank you for sharing!
ReplyDelete