Ora 12528 ошибка как исправить

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

Maheswaran Ravisankar's user avatar

asked May 19, 2014 at 17:39

Shashi's user avatar

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

Randy's user avatar

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

eliatou's user avatar

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

leo's user avatar

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 Staufcik's user avatar

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 C's user avatar

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

scvblwxq's user avatar

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:

  1. Stop and Start the Listener.
  2. Shutdown and Startup database.
  3. Restart the oracle services.

How might I resolve this?

Michael Petrotta's user avatar

asked Apr 3, 2012 at 4:37

Geet's user avatar

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.!).

Sathyajith Bhat's user avatar

answered Apr 3, 2012 at 4:42

Yahia's user avatar

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..

вроде сервер чистый — ничего дополнительного типа антивируса не стоит

что можно сделать?

Понравилась статья? Поделить с друзьями:
  • Как найти котенка когда он потерялся
  • Как найти удельную теплоемкость в физике формула
  • Как найти цвет мебели
  • Как исправить ошибку в фортнайт 20006
  • Как найти редкие руды