Friday, 4 May 2018

Instance Status BLOCKED in Listener – Oracle Database

Instance Status BLOCKED in Listener – Oracle Database 11g

Scenarios where you can get Instance Blocked in Listener Status while working with Oracle Environment

Instance Status BLOCKED in Listener



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

I hope this helps !!

2 comments:

  1. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. Quincy Locksmith & Supply

    ReplyDelete