【已解决】Failed to connect to database instance: ORA-12528

fertiland

listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = MYDATA)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = MYDATA)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = zhang.sirius.com)(PORT = 1521))
    )
  )

-----------------------------------------------------------------------------
sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

-----------------------------------------------------------------------------
tnsnames.ora

MYDATA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zhang.sirius.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MyData)
      (UR=A)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

[ 本帖最后由 fertiland 于 2007-7-12 21:21 编辑 ]

rollingpig

用命令行看看吧……

感觉上 instance可能是在mount 或者 nomount状态

fertiland

首先谢谢rollingpig的回复

刚接触oracle，以下是我知道的查看方法，还请详细指教。

1. 
   
2. [oracle@zhanglinux admin]$ tnsping Mydata
   
3. 
   
4. TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2007 11:01:28
   
5. 
   
6. Copyright (c) 1997, 2005, Oracle.  All rights reserved.
   
7. 
   
8. Used parameter files:
   
9. /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
   
10. 
    
11. 
    
12. Used TNSNAMES adapter to resolve the alias
    
13. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zhang.sirius.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MyData) (UR=A)))
    
14. OK (10 msec)
    

复制代码

1. [oracle@zhanglinux admin]$ lsnrctl status
   
2. LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2007 10:58:40
   
3. 
   
4. Copyright (c) 1991, 2005, Oracle.  All rights reserved.
   
5. 
   
6. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
   
7. STATUS of the LISTENER
   
8. ------------------------
   
9. Alias                     LISTENER
   
10. Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    
11. Start Date                17-MAY-2007 10:29:09
    
12. Uptime                    0 days 0 hr. 29 min. 31 sec
    
13. Trace Level               off
    
14. Security                  ON: Local OS Authentication
    
15. SNMP                      OFF
    
16. Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
    
17. Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    
18. Listening Endpoints Summary...
    
19.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
    
20.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zhanglinux.org)(PORT=1521)))
    
21. Services Summary...
    
22. Service "MYDATA" has 2 instance(s).
    
23.   Instance "MYDATA", status UNKNOWN, has 1 handler(s) for this service...
    
24.   Instance "MyData", status READY, has 1 handler(s) for this service...
    
25. Service "MyDataXDB" has 1 instance(s).
    
26.   Instance "MyData", status READY, has 1 handler(s) for this service...
    
27. Service "MyData_XPT" has 1 instance(s).
    
28.   Instance "MyData", status READY, has 1 handler(s) for this service...
    
29. Service "PLSExtProc" has 1 instance(s).
    
30.   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    
31. The command completed successfully
    
32. 
    

复制代码

listener.log

WARNING: Subscription for node down event still pending

sqlnet.log
没有报错

[ 本帖最后由 fertiland 于 2007-5-17 11:09 编辑 ]

numenhuang

sqlplus '/ as sysdba'
select instance_name,status,shutdown_pending,database_status from v$Instance

fertiland

INSTANCE_NAME    STATUS       SHU DATABASE_STATUS
---------------- ------------ --- -----------------
MyData           OPEN         NO  ACTIVE

用lsnrctl status也可以看出来啊

numenhuang

把em重启一下

fertiland

已经重启过多回了。症状变了，如图

[ 本帖最后由 fertiland 于 2007-5-17 14:27 编辑 ]

fertiland

# Set this to bring up Oracle Net Listener
#ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
ORACLE_HOME_LISTNER=$ORACLE_HOME
-------------------------------------------------------
原来是系统这里没有修改。修改之后，就可以正常工作了。
由于我用的是域名解析，那么主机名也要与域名一致
主机名修改 /etc/sysconfig/network 文件 把里面的HOSTNAME＝newhostname

我是通过域名访问的，而http://hostname:1158/em则似乎是通过主机名获取的。
修改上述两个方面，问题解决了。至于第2个是否相关，我没有测试。