I’m getting this error if i try to login as db user. If lsnrctl status is run i get the below error.
DB was working fine all these years and stopped working suddenly.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ABC.LOCAL)(PORT=1521)
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 19-MAY-2014 12:18:17
Uptime 0 days 0 hr. 22 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:OracleAdministratorproduct11.2.0dbhome_1networkadminlistener.ora
Listener Log File d:oracleadministratordiagtnslsnrabclisteneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ABC.LOCAL)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "devdb" has 1 instance(s).
Instance "devdb", status BLOCKED, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
asked May 19, 2014 at 17:39
1
set ORACLE_SID=<YOUR_SID>
sqlplus "/as sysdba"
alter system disable restricted session;
or maybe
shutdown abort;
or maybe
lsnrctl stop
lsnrctl start
answered May 19, 2014 at 18:12
RandyRandy
16.4k1 gold badge35 silver badges54 bronze badges
2
You have to know if the problem come from the listener or from the database.
-
So first, restart the listener, it could solve the problem.
-
Second, it could come from the db if it’s not in open mode (nomount, mount, restrict). To check it, connect locally and do the following query:
sqlplus /nolog
connect / as sysdba
SQL> select instance_name, status, database_status from v$instance;
answered May 19, 2014 at 18:14
eliatoueliatou
7445 silver badges12 bronze badges
5
I had this error message with boot2docker on windows with the docker-oracle-xe-11g image (https://registry.hub.docker.com/u/wnameless/oracle-xe-11g/).
The reason was that the virtual box disk was full (check with boot2docker.exe ssh df). Deleting old images and restarting the container solved the problem.
answered May 21, 2015 at 12:56
leoleo
3,5977 gold badges33 silver badges46 bronze badges
I had this problem on my developent environment with Visual Studio.
What helped me was to Clean Solution in Visual Studio and then do a rebuild.
answered Mar 2, 2016 at 11:27
Martin StaufcikMartin Staufcik
7,6204 gold badges40 silver badges61 bronze badges
0
If you are using 11G XE with Windows, along with tns listener restart, make sure Windows Event Log service is started.
answered Oct 6, 2014 at 4:39
Chandan CChandan C
1381 silver badge11 bronze badges
I tried restarting my computer and that fixed it for me.
answered Feb 19, 2022 at 22:19
February 26, 2019
ORACLE
When trying to connect to an open intance via the listener, you may receive the error “ORA-12528: TNS: listener: all appropriate instances are blocking new connections”. The current instance is in RESTRICTED or NOMOUNT mode. In this case, you need to add the line (UR = A) to the corresponding tns record in the tnsnames.ora file.
When connected with Rman, an error will be received as follows.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
The same error is also seen when connected with Sqlplus.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
In this case, the corresponding TNS record in the tnsnames.ora file under the corresponding $ ORACLE_HOME/network/admin directory should be similar to the following.
|
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl—scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) (UR = A) ) ) |
Then, you can connect successfully.
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
Вернули копию за вчера, проделали все операции с самого начала — та же ситуация
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..
вроде сервер чистый — ничего дополнительного типа антивируса не стоит
что можно сделать?
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)
)
)
May 6, 2020
Hi,
Sometimes You can get “ORA-12528: TNS:listener: all appropriate instances are blocking new connections ” error.
Details of error are as follows.
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
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.
To solve this error, you can create a static listener and start it as follows.
Add the following listener descriptions to the listener.ora file under $ORACLE_HOME/network/admin.
Check your Hostname, Port, DB Name and fill them correctly.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.63.34)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = MSDB) (ORACLE_HOME = /u01/app/db_home_18c ) (SID_NAME = MSDB1 ) ) )
[[email protected] ~]$ lsnrctl start LISTENER
Or Restart database and listener.
LSNRCTL> stop LSNRCTL> start SQL> shutdown immediate SQL> startup
Do you want to learn more details about RMAN, then read the following articles.
https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/
2,343 views last month, 2 views today
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)
)
)
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
oracle version : 11.2.0.3
OS :AIX
db_name=ATSBAUY
db_unique_name=ATSB_ST
instance_name=ATSB_N
db_domain=”
tns entry in tnsnames.ora;
ATSB_ST_TNS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ATSB_ST) ) )
I am creating standby db instance started with nomount mode.
there is just one service created automatically from db_unique_name
SQL> show parameter service NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string ATSB_ST
listener.ora:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /oracle/product/11.2.0.3/db) (SID_NAME = ATSB_N) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521)) ) ) )
lets start listener
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 13:55:13
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 13:55:13
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
listener is not listening for ATSB_ST service despite listenig for instance_name (ATSB_N)
lest open instance (nomount database)
SQL> startup nomount ORACLE instance started. Total System Global Area 1.0689E+10 bytes Fixed Size 2230912 bytes Variable Size 5435819392 bytes Database Buffers 5234491392 bytes Redo Buffers 16932864 bytes SQL>
now lets check for listener status
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 13:59:36
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 13:55:13
Uptime 0 days 0 hr. 4 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Service "ATSB_ST" has 1 instance(s).
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
the right service(ATSB_ST) is blocked. when tring to connect with TNS ORA-12528 error occured.
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>sqlplus /@ATSB_ST_TNS as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:00:55 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:
try connect via sqlplus after change tnsnames.ora as;
ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_N)
)
)
{arcl_host:oracle}/oracle/product/11.2.0.3/db/dbs/>sqlplus sys/syspass@ATSB_ST_TNS as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:04:32 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
we can coonnect to db that listner service is unknown. if we add GLOBAL_DBNAME to listener.ora, listner will listen on ATSB_ST service.
in listner.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ATSB_ST)
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)
change tnsnames.ora for right service name:
ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)
now stop and start listener:
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl stop
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:11:39
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
The command completed successfully
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:11:48
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
after a while the listner start to listen on ATSB_ST service;
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:13:02
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 0 days 0 hr. 1 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 2 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
now we can connect with new TNS;
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>sqlplus sys/syspass@ATSB_ST_TNS as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:14:30 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>