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

If your database was started in nomount mode you can encounter following error

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

Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Ora 12505 как исправить
  • Ora 12154 ошибка
  • Ora 12154 как исправить
  • Ora 12048 error encountered while refreshing materialized view
  • Ora 00600 internal error code arguments rwoirw check ret val

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии