Tried to connect a NOMOUNT database, but it failed with ORA-12528.
C:Usersed>sqlplus sys/password@orcl as sysdba
...
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
ORA-12528 means that the database is at NOMOUNT state, there’s no way to connect to a NOMOUNT database for normal users at client side. On the other side, there’s a way that can allow SYS to connect to a NOMOUNT database externally.
In such moment, the connection to the service of database is BLOCKED in the listener, which means, normal connections will be rejected. For example:
SQL> conn hr/hr@orcl
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Let’s check the service status of database in listener.
[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
Solution
For normal users who want to connect to the database, you have to open the database to able them to access. For connections by SYS, the solution is to add a special parameter UR=A in connect descriptor to lift off the restriction. More specifically, we added (UR=A) for a connect identifier ORCL.
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)(UR=A)
)
)
Let’s see how we connect the NOMOUNT database.
C:Usersed>sqlplus sys/password@ORCL as sysdba
...
Connected.
We solved it.
For connecting idle, nomount or restricted database externally, you need some skills to do it.
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.5k1 gold badge37 silver badges55 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,6577 gold badges34 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,9754 gold badges43 silver badges62 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
I am getting this error when I try to connect to my database:
ora-12528: TNS:Listener: All Appropriate instances are blocking new connections
I tried the following, with no success:
- Stop and Start the Listener.
- Shutdown and Startup database.
- Restart the oracle services.
How might I resolve this?
asked Apr 3, 2012 at 4:37
You might have a problem with either the network and/or the archive logs — the above usually happens when the area/disk where the archive logs are stored is full, Oracle then just refuses new connections.
Another possibility is that you maxed out the number of allowed connections — this should usually be warning sign that you might have an application which leaks connections.
If you are 100% sure that you are not leaking connections then you could configure Oracle to accept more connections (BEWARE of licensing, RAM etc.!).
answered Apr 3, 2012 at 4:42
YahiaYahia
69.3k9 gold badges114 silver badges144 bronze badges
1
Вернули копию за вчера, проделали все операции с самого начала — та же ситуация
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..
вроде сервер чистый — ничего дополнительного типа антивируса не стоит
что можно сделать?
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..
вроде сервер чистый — ничего дополнительного типа антивируса не стоит
что можно сделать?