Dataguard configuration listener status for service is Blocked or RESTRICTED for Standby solution

In this case, we are getting the following error when our database is start in mount / nomount / restricted state. Sometime we faced this while configuring the dataguard Environment.

Error:

C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Oct 1 19:40:51 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect sys@dbname as sysdba
Enter password:
ERROR: ORA-12528: TNS:listener: all appropriate instances are blocking new connection

You might see this error if you connect to an instance that is in RESTRICTED or NO MOUNT status. The lsnrctl services output will indicate that the service handler is BLOCKED or RESTRICTED.

Check the status of services by listener commands:

lsnrctl status

lsnrctl services

As example show below the status is blocked for ORCL service:

lsnrctl status
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for windows: Version 11.2.0.1.0 - Production
Start Date                20-DEC-2014 02:39:22
Uptime                    14 days 2 hr. 26 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File D:\oracle\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\oracle\diag\tnslsnr\test\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...

Solution

For Standalone database
1. Restart the Oracle services.(first step will fixed issue).

SQLPLUS / as sysdba
-- Shutdown the DB Server
Shutdown immediate
-- Start the DB server
Startup

2. Ensure that the listener is correctly set up for dynamic registration of the service. If you continue to encounter this error, manually add the SID to the listener.ora file or use the NETCA utility for assistance.
Entry as shown in bold: (SID_DESC= (GLOBAL_DBNAME=ORCL ….
` Listener.ora file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
 (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = D:\oracle\12.1.0\dbhome_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )

If it’s a Data Guard Environment or you used an Auxiliary channel for RMAN
Sometimes we encounter issues with Data Guard configurations, especially when the standby database is in recovery mode and mounted for applying redo logs from the primary database. In such cases, the listener status for the service may show as Blocked or RESTRICTED. To resolve this, we can use the following parameter in the tnsnames.ora file:

(UR=A) clause is used for TNS connect strings has been created as an enhancement.*(UR=A)* clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. (ID 362656.1)

Need to modify the tnsnames.ora file for connectivity as shown below:

DBNAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hostname.rnhub.com)
            (UR = A)
    )
  )