Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |
APPLIES TO:
Oracle Net Services - Version 11.1.0.6 to 11.2.0.4 [Release 11.1 to 11.2]Oracle Net Services - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 14-OCT-2013***
SYMPTOMS
Connecting to the database via the TNS Listener fails with ORA-1033, ORA-12528, ORA-12526, ORA-12527.
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 19 11:54:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 19 12:44:49 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
CHANGES
The database was started using the nomount, mount or restricted mode. (e.g. the DB admin executed "STARTUP NOMOUNT" or "STARTUP MOUNT" or "STARTUP RESTRICT" in an idle instance)
CAUSE
The listener service handler for an instance that is in either nomount,mount or restricted mode will be blocked or restricted, for connections for any non-privileged account.
Database Nomount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1302432 bytes
Variable Size 157818976 bytes
Database Buffers 134217728 bytes
Redo Buffers 262144 bytes
Listener services show status blocked
LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 19-FEB-2008 11:35:05
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hal)(PORT=1525)))
Services Summary...
Service "v10g" has 1 instance(s).
Instance "v10g", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "v10g.uk.oracle.com" has 1 instance(s).
Instance "v10g", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Database Mounted
Database altered.
Listener services shows status ready
LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 19-FEB-2008 11:39:26
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hal)(PORT=1525)))
Services Summary...
Service "v10g" has 1 instance(s).
Instance "v10g", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0
LOCAL SERVER
Service "v10g.uk.oracle.com" has 1 instance(s).
Instance "v10g", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Database Restricted mode
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1302432 bytes
Variable Size 157818976 bytes
Database Buffers 134217728 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
Listener service show status restricted
LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 19-FEB-2008 11:28:01
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hal)(PORT=1525)))
Services Summary...
Service "v10g" has 1 instance(s).
Instance "v10g", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "v10g.uk.oracle.com" has 1 instance(s).
Instance "v10g", status RESTRICTED, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
SOLUTION
Connecting to Nomounted database
1. Net service name using sysdba with (CONNECT_DATA=(SID=<SID>))
Possible due to SID_LIST entry in the LISTENER.ORA for the v10g database.
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 19 12:31:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> exit
2. Use (UR=A) with (CONNECT_DATA=(SERVICE_NAME=SERVICE))
Connection using SERVICE_NAME without (UR=A) would error with ORA-12528: TNS:listener: all appropriate instances are blocking new connections
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 19 12:37:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> exit
Connecting to Mounted Database
Net service names using sysdba with either (CONNECT_DATA=(SID)) or (CONNECT_DATA=(SERVICE_NAME=(SERVICE)) can be used.
Connection to Restricted Database
Without sysdba, Net service name using SID or Net service name using SERVICE_NAME with UR=A
TNSNAMES.ORA file used
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hal)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = v10g.uk.oracle.com)
)
)
SID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hal)(PORT = 1525))
)
(CONNECT_DATA =
(SID = v10g)
(SERVER = DEDICATED)
)
)
TESTUA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hal)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = v10g.uk.oracle.com)
(UR = A)
)
)
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.
REFERENCES
NOTE:1022414.6 - ORA-01033 DATABASE INITIALIZATION OR SHUTDOWN IN PROGRESSNOTE:444120.1 - Connections Via the Listener to the Database Fail with TNS-12526, TNS-12527 or TNS-12528
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20747382/viewspace-2130845/ ，如需转载，请注明出处，否则将追究法律责任。
