文档中对错误的说明
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.
查看standby listener的状态，发现状态为blocked：
[
oracle @ora10g_dg ~]$ lsnrctl service
LSNRCTL for
Linux: Version 10.2.0.4.0 - Production on 03-JUL-2009 11:16:28
Copyright (c) 1991, 2007, oracle . All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary…
Service “demo” has 1 instance(s).
Instance “demo”, status BLOCKED, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL
SERVER Service “demo_XPT” has 1 instance(s).
Instance “demo”, status BLOCKED, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
从上面可以看出服务是动态注册的，我们知道 oracle 动态注册是通过PMON来实现的，而启动PMON注册需到mount状态，因此如果instance处于nomount状态就会出现blocked的情况，解决的办法是改成静态注册。更详细的介绍可参考：419440.1
Refer to metalink Note:419440.1
During duplicate database creation, connection to the auxiliary database from target database server fails with ORA-12528: TNS:listener: all appropriate instances are blocking new connections.
E:oracleproduct10.2.0db_1BIN>rman target / auxiliary sys/sys@duplicate
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Mar 16 00:11:05 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORIGINAL (DBID=1451288337)
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 appropri
ate instances are blocking new connections
When it fails , add manual entry for sid in listener.ora as per Note 301099.1
However, still you receive the same error on connect in some cases.
Cause
Its because only the Auxiliary Instance is running .The instance registers with the listener by the PMON process. TO start the PMON process the database need to be in MOUNT status. Before there is nothing to register the database to the listerner so the instance is BLOCKED.
Solution
There are 2 options to solve this problem
Option 1
Connect to the auxiliary instance using OS authentication (as /) and use password file authentication for target database and connect via Listener.For an example:
E:>rman target sys/sys@original auxiliary /
Recovery Manager: Release 10.2.0.1.0 – Production on Thu Mar 15 23:57:08 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORIGINAL (DBID=1451288337)
connected to auxiliary database: DUPLICATE (not mounted)
Option 2
Create a static entry for the auxiliary database in the listener.ora file and restart the listener. For example
Listener.ora
————-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SAIRAM)
(ORACLE_HOME = G:oracleproduct10.2.0db_1)
(SID_NAME = SAIRAM)
)
)
Or
Use the Oracle10G feature :
tnsnames.ora
————
SAIRAM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apadhi-idc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SAIRAM)(UR=A)
)
)
This fix is required for administrative connections where we specify (UR=A) in connect data.
