orarac2poc:DGS:/u01/app/oracle/product/11.2.0/db_2/dbs> sqlplus sys/oracle123@DGS as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 11:00:41 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:
Tried restart LISTENER, but still fail.
Here is the listener.ora. （was working fine with previous standby database, now re-create it)
[grid@orarac2poc admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY=LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DGS_DGMGRL.POC)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2)
(SID_NAME =DGS)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
#and tnsnames.ora
DGS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGS.POC)
)
)
and inside PFILE , db_domain is set to 'POC'.
2. I removed the db_domain , GLOBAL_DBNAME info in PFILE , listener.ora and tnsnames.ora. As they were added for database guard broker, and remain static registration. After which it works.
The followings are new setting.
[grid@orarac2poc admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY=LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2)
(SID_NAME =DGS)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
[grid@orarac2poc admin]$ lsnrctl service LISTENER
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-JAN-2013 11:02:41
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "DGS" has 1 instance(s).
Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[grid@orarac2poc admin]$ lsnrctl service LISTENER
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-JAN-2013 11:05:03
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "DGS" has 2 instance(s).
Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0
LOCAL SERVER
Instance "DGS", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
...
The command completed successfully
DGS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DGS)
# (SERVICE_NAME = DGS.POC)
)
)
orarac2poc:DGS:/u01/app/oracle/product/11.2.0/db_2/dbs> tnsping DGS
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 22-JAN-2013 11:13:50
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGS)))
OK (0 msec)
orarac2poc:DGS:/u01/app/oracle/product/11.2.0/db_2/dbs> sqlplus sys/oracle123@DGS as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 11:10:43 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SYS@DGS> startup nomount
ORACLE instance started.
Total System Global Area 680607744 bytes
Fixed Size 2229600 bytes
Variable Size 197135008 bytes
Database Buffers 473956352 bytes
Redo Buffers 7286784 bytes
SYS@DGS> exit
4. More research was done against metalink, and below docs provide more details.
Connection to Auxilary using connect string failed with ORA-12528 [ID 419440.1]
ORA-12528: TNS:listener: all appropriate instances are blocking new connections [ID
301099.1]
"Cause
The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it's not registered with listener. PMON process registers the instances with listener when they are in MOUNT status, while instances are in NOMOUNT status appear in listener as BLOCKED."
options to solve this problem:
a.) Start rman from Auxiliary environment.
b.) Create a static entry for the auxiliary database in the listener.ora file of the auxiliary database Oracle_home and restart the listener. (This is what I used.)
c.) "Use the Oracle10G feature ( specify (UR=A) in connect data )".
Some info about URA=A . This is a new feature in 10g, but I can't find relevant info in both 10g and 11g Oracle® Database Net Services Reference
d.) From my testing , it seems DB_DOMAIN also can cause the error.
d.) From my testing , it seems DB_DOMAIN also can cause the error.
Manually create a standby database
"Notice the value (UR=A) in the CONNECT_DATA of the tns entry THTESTDG.WORLD.
This will allow the primary database to connect
remotely to a database in nomount or mount mode. It’s a feature
introduced in oracle 10g."
Thanks for the question regarding "Connecting sys as sysdba to database in restricted mode", version 10.2.0.1
Please note that the (UR=A) clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. SID may connect to a statically configured handler."
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
"The (UR=A) clause for TNS connect strings in TNSNAMES.ORA file. This clause insert into:
(CONNECT_DATA =
(UR=A)It allows a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users. "
