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
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 should show 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 !!
thank you for sharing!
ReplyDelete