If your database was started in nomount mode you can encounter following error
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:39:28 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: sys@ora12c as sysdba Enter password: ERROR: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
To solve this problem you need to make static registration of your service in listener.ora.
Settings before solving the error
tnsnames.ora
ORA12C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA12C) ) )
listener.ora
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:apporacleproduct12.1.0dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:apporacleproduct12.1.0dbhome_1binoraclr12.dll") ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521)) ) )
New settings
listener.ora
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:apporacleproduct12.1.0dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:apporacleproduct12.1.0dbhome_1binoraclr12.dll") ) (SID_DESC = (GLOBAL_DBNAME = ORA12C) (ORACLE_HOME = D:apporacleproduct12.1.0dbhome_1) (SID_NAME = ORA12C) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521)) ) ) ADR_BASE_LISTENER = D:apporacleproduct12.1.0dbhome_1log
You need to restart listener to make it effective. Once it’s done you should be able to connect to your database which is working in nomount.
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:50:15 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: sys@ora12c as sysdba Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
Have a fun 
Tomasz
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:Windowssystem32>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 get this error if you encounter with Connections via the listener to an instance that is in RESTRICTED status or in NO MOUNT status. The lsnrctl services output will show that the service handler for this instance is in state: 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:oracle12.1.0dbhome_1networkadminlistener.ora Listener Log File D:oraclediagtnslsnrtestlisteneralertlog.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. Check the listener is working on Dynamic registration for the Service. If you are again and again getting this error. You can need to make manual entry of SID in listener.ora file or add with help of NETCA utility.
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:oracle12.1.0dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:oracle12.1.0dbhome_1binoraclr12.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = D:oracle12.1.0dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
If its a Dataguard Environment or you used Auxiliary channel for RMAN
In Some cases we need to overcome with this problem like dataguard configuration. In which our standby database is in recover mode means it’s in mounted state for apply the redo logs of primary database. In that case listener status for service is Blocked or RESTRICTED. To overcome from it we used the following parameter in 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)
)
)
Cause: All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.
Action: Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.
Typically (in Oracle 9i and above), when you «shutdown» an Oracle database, that process «unregisters» the database with the LISTENER. Then when you «startup» the database, it «registers» with the LISTENER.
If the «unregister» did not complete properly, then when you go to «startup» the next time, since the LISTENER did not unregister the instance, you receive the error, «ORA-12528: TNS:listener: all appropriate instances are blocking new connections» since an «appropriate» instance is already running.
Check the status of the listener by
# lsnrctl
LSNRCTL> status
One method that should resolve this problem is to restart (bounce) the LISTENER:
LSNRCTL> stop
LSNRCTL> start
Then restart your instance: sqlplus with sysdba privileges
SQL> startup
Make sure all parameters are set.
Example
Adding some information to the listener.ora file will let the listener know where to find the database even when it’s down. Here’s basically what can be added under the
SID_LIST_LISTENER:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = or1)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = STAN)
(ORACLE_HOME =C:appInamproduct11.2.0dbhome_1)
)
)
Note: If you started your instance with nomount and try to connect using tns entry, you will get the same error.PMON
process registers the instances with listener when they are in MOUNT
status, while instances are in NOMOUNT status appear in listener as
BLOCKED. Create a static entry for the database in the listener.ora,adding the SID_NAME in SID_DESC in listener.ora will resolve the issue.
C:Windowssystem32>sqlplus sys/oracle123@stan as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 20 13:13:42 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
OR
Use the Oracle10G feature ( specify (UR=A) in connect data )
Make below entry in the tnsnames.ora file of the database Oracle_home
STAN=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = or1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME= STAN) (UR=A)
)
)
Вернули копию за вчера, проделали все операции с самого начала — та же ситуация
1.Вин Сервер 2003 р2, оракл 11.2.0.3
2.листенер.лог 222М
Вот информация с файла listener.log (концовка)
Wed Feb 24 09:31:03 2021
24-ФЕВ-2021 09:31:03 * service_update * merid04 * 0
24-ФЕВ-2021 09:31:04 * service_died * merid04 * 12547
TNS-12547: TNS:контакт потерян
Wed Feb 24 09:35:15 2021
сообщения протокола записаны в D:oraclediagtnslsnrserver-merid04listeneralertlog.xml
информация протокола записана в D:oraclediagtnslsnrserver-merid04listenertraceora_12776_12800.trc
текущим уровнем протокола является 0
Запущен с pid=12776
Прослушивается: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-merid04)(PORT=1521)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
Wed Feb 24 09:35:34 2021
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-merid04)(PORT=1521)))
24-ФЕВ-2021 09:35:34 * service_register * merid04 * 0
24-ФЕВ-2021 09:35:39 * service_update * merid04 * 0
Wed Feb 24 09:35:59 2021
24-ФЕВ-2021 09:35:59 * (CONNECT_DATA=(SID=merid04)(CID=(PROGRAM=C:SM2000BinSm.DbInit.exe)(HOST=server-merid04)(USER=admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.1)(PORT=2753)) * establish * merid04 * 12528
TNS-12528: TNS:прослушиватель: все соответствующие экземпляры блокируют новые соединения
Wed Feb 24 09:37:00 2021
24-ФЕВ-2021 09:37:00 * (CONNECT_DATA=(SID=merid04)(CID=(PROGRAM=D:oracleproduct11.2.0dbhome_1binsqlplus.exe)(HOST=SERVER-MERID04)(USER=admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.1)(PORT=2771)) * establish * merid04 * 12528
TNS-12528: TNS:прослушиватель: все соответствующие экземпляры блокируют новые соединения
Концовка alert.log концовка
ORACLE_BASE from environment = D:oracle
Wed Feb 24 09:18:27 2021
alter database mount exclusive
ORA-214 signalled during: alter database mount exclusive…
Wed Feb 24 09:18:28 2021
Checker run found 1 new persistent data failures
Wed Feb 24 09:20:40 2021
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 1
alter database close normal
ORA-1507 signalled during: alter database close normal…
Wed Feb 24 09:28:04 2021
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.3.0 — Production.
Windows NT Version V5.2 Service Pack 2
CPU : 2 — type 586, 2 Physical Cores
Process Affinity : 0x0x00000000
Memory (Avail/Total): Ph:2520M/4094M, Ph+PgF:3787M/5973M, VA:715M/2047M
Using parameter settings in server-side spfile D:ORACLEPRODUCT11.2.0DBHOME_1DATABASESPFILEMERID04.ORA
System parameters with non-default values:
processes = 1500
sessions = 2272
memory_target = 1848M
memory_max_target = 1848M
control_files = «D:ORACLEORADATAMERID04CONTROL01.CTL»
control_files = «D:ORACLEFRAMERID04CONTROL02.CTL»
db_block_size = 8192
compatible = «11.2.0.0.0»
db_recovery_file_dest = «oraclefra»
db_recovery_file_dest_size= 4977M
undo_tablespace = «UNDOTBS1»
O7_DICTIONARY_ACCESSIBILITY= TRUE
remote_login_passwordfile= «EXCLUSIVE»
db_domain = «»
audit_file_dest = «D:ORACLEADMINMERID04ADUMP»
audit_trail = «NONE»
db_name = «merid04»
open_cursors = 300
diagnostic_dest = «D:ORACLE»
Wed Feb 24 09:28:14 2021
PMON started with pid=2, OS id=8708
Wed Feb 24 09:28:14 2021
PSP0 started with pid=3, OS id=9908
Wed Feb 24 09:28:15 2021
VKTM started with pid=4, OS id=8904 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Feb 24 09:28:15 2021
GEN0 started with pid=5, OS id=8688
Wed Feb 24 09:28:15 2021
DIAG started with pid=6, OS id=9092
Wed Feb 24 09:28:15 2021
DBRM started with pid=7, OS id=8596
Wed Feb 24 09:28:15 2021
DIA0 started with pid=8, OS id=9132
Wed Feb 24 09:28:15 2021
MMAN started with pid=9, OS id=8868
Wed Feb 24 09:28:15 2021
DBW0 started with pid=10, OS id=9260
Wed Feb 24 09:28:15 2021
LGWR started with pid=11, OS id=8540
Wed Feb 24 09:28:15 2021
CKPT started with pid=12, OS id=3512
Wed Feb 24 09:28:15 2021
SMON started with pid=13, OS id=8668
Wed Feb 24 09:28:15 2021
RECO started with pid=14, OS id=8924
Wed Feb 24 09:28:15 2021
MMON started with pid=15, OS id=8996
Wed Feb 24 09:28:15 2021
MMNL started with pid=16, OS id=9884
Wed Feb 24 09:28:15 2021
ORACLE_BASE from environment = D:oracle
Wed Feb 24 09:28:15 2021
alter database mount exclusive
ORA-214 signalled during: alter database mount exclusive…
Wed Feb 24 09:31:02 2021
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 1
alter database close normal
ORA-1507 signalled during: alter database close normal…
Wed Feb 24 09:35:20 2021
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.3.0 — Production.
Windows NT Version V5.2 Service Pack 2
CPU : 2 — type 586, 2 Physical Cores
Process Affinity : 0x0x00000000
Memory (Avail/Total): Ph:2418M/4094M, Ph+PgF:3769M/5973M, VA:715M/2047M
Using parameter settings in server-side spfile D:ORACLEPRODUCT11.2.0DBHOME_1DATABASESPFILEMERID04.ORA
System parameters with non-default values:
processes = 1500
sessions = 2272
memory_target = 1848M
memory_max_target = 1848M
control_files = «D:ORACLEORADATAMERID04CONTROL01.CTL»
control_files = «D:ORACLEFRAMERID04CONTROL02.CTL»
db_block_size = 8192
compatible = «11.2.0.0.0»
db_recovery_file_dest = «oraclefra»
db_recovery_file_dest_size= 4977M
undo_tablespace = «UNDOTBS1»
O7_DICTIONARY_ACCESSIBILITY= TRUE
remote_login_passwordfile= «EXCLUSIVE»
db_domain = «»
audit_file_dest = «D:ORACLEADMINMERID04ADUMP»
audit_trail = «NONE»
db_name = «merid04»
open_cursors = 300
diagnostic_dest = «D:ORACLE»
Wed Feb 24 09:35:34 2021
PMON started with pid=2, OS id=11944
Wed Feb 24 09:35:34 2021
PSP0 started with pid=3, OS id=12988
Wed Feb 24 09:35:35 2021
VKTM started with pid=4, OS id=13028 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Feb 24 09:35:35 2021
GEN0 started with pid=5, OS id=11976
Wed Feb 24 09:35:35 2021
DIAG started with pid=6, OS id=13892
Wed Feb 24 09:35:35 2021
DBRM started with pid=7, OS id=12300
Wed Feb 24 09:35:35 2021
DIA0 started with pid=8, OS id=11536
Wed Feb 24 09:35:35 2021
MMAN started with pid=9, OS id=11340
Wed Feb 24 09:35:35 2021
DBW0 started with pid=10, OS id=13124
Wed Feb 24 09:35:35 2021
LGWR started with pid=11, OS id=12344
Wed Feb 24 09:35:35 2021
CKPT started with pid=12, OS id=10304
Wed Feb 24 09:35:35 2021
SMON started with pid=13, OS id=12108
Wed Feb 24 09:35:35 2021
RECO started with pid=14, OS id=11820
Wed Feb 24 09:35:35 2021
MMON started with pid=15, OS id=11664
Wed Feb 24 09:35:35 2021
MMNL started with pid=16, OS id=11160
Wed Feb 24 09:35:35 2021
ORACLE_BASE from environment = D:oracle
Wed Feb 24 09:35:36 2021
alter database mount exclusive
ORA-214 signalled during: alter database mount exclusive..
вроде сервер чистый — ничего дополнительного типа антивируса не стоит
что можно сделать?