Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail

xychong123發表於2016-12-17
RESTDatabaseAI

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.

C:\>sqlplus system/oracle@net-service-name
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

 

C:\>sqlplus system/oracle@net-service-name
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

SQL> startup 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

uks714[v10g](37)% lsnrctl services   

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

SQL> alter database mount; 

Database altered.

Listener services shows status ready

uks714[v10g](39)% lsnrctl services  

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

SQL> startup restrict  
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

uks714[v10g](29)% lsnrctl services 
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.

C:\sqlplus system/oracle@sid as sysdba 
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

C:\sqlplus system/oracle@testua as sysdba 
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

V10G = 
  (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 PROGRESS
NOTE:444120.1 - Connections Via the Listener to the Database Fail with TNS-12526, TNS-12527 or TNS-12528

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2130845/，如需轉載，請註明出處，否則將追究法律責任。

相關文章