ORA-01304错误一例

Oracle10g装在VMware中的redhat4中，想让过plsql develop在windows中连接到redhat4中的Oracle上。

1. 下载安装plsql develop

2. 在Oracle官网上下载到instantclient-basic-win32-10.2.0.5.zip文件，解压到目录E:\instantclient_10_2 下，然后将redhat中的/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora 复制到该目录下：

TNSNAMES.ORA不需要进行修改，它的内容如下：
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JIAGULUN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = JIAGULUN)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PL***tProc)
      (PRESENTATION = RO)
    )
  )

3.配置环境变量
在windows中增加环境变量：
1）TNS_ADMIN 值为： E:\instantclient_10_2
2）NLS_LANG 值为：AMERICAN_AMERICA.ZHS16GBK （Oracle选择的字符集为ZHS16GBK ）

4. 配置plsql develop
打开plsql develop，在登陆界面选择“取消”，然后在菜单上选择“工具”==>> “首先项”，在其中的设
置如下：

主要是设置 Oracle主目录和OCI库。

5.启动Oracle的监听器：
[oracle@localhost ~]$ lsnrctl start;
查看监听器的状态：
[oracle@localhost ~]$ lsnrctl status;
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-OCT-2012 21:37:06

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                05-OCT-2012 21:35:32
Uptime                    0 days 0 hr. 1 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PL***tProc" has 1 instance(s).
  Instance "PL***tProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

我们发现Oracle服务无法正确的注册到监听器中，即使使用了命令行也无法注册：
SQL> alter system register;

于是我们采用了静态注册，修改文件/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora如下（红色的为增加的内容）：
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PL***tProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = JIAGULUN)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = JIAGULUN)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = redhat4)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@localhost ~]$ echo $ORACLE_SID
jiagulun
[oracle@localhost ~]$ echo $GLOBAL_DBNAME

[oracle@localhost ~]$

然后我们重启监听器：
[oracle@localhost ~]$ lsnrctl stop;
[oracle@localhost ~]$ lsnrctl start;
[oracle@localhost udump]$ lsnrctl start;

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-OCT-2012 21:48:32

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                05-OCT-2012 21:48:32
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "JIAGULUN" has 1 instance(s).
  Instance "JIAGULUN", status UNKNOWN, has 1 handler(s) for this service...
Service "PL***tProc" has 1 instance(s).
  Instance "PL***tProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost udump]$
这次我们发现Oracle serviece正确的像监听器注册成功了。

6. ORA-01304错误
启动plsql develop，用system用户已sysdba身份登录，发生了ORA-01304错误：


当以用户system以Normal身份登录时：


报错：ORA-01304，ORACLE不可用。
但是我们通过putty用sqlplus却可以正常地登录到 ORACLE 上，显然ORACLE已经启动。

想到前面我们出采用静态注册，修改文件/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
所以我们怀疑是修改文件造成了错误。我们查看listener.ora：
    (SID_DESC =
      (SID_NAME = JIAGULUN)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = JIAGULUN)
    )
我们发现ORACLE_SID的值为：
[oracle@localhost ~]$ echo $ORACLE_SID
jiagulun
我们试着将SID_NAME和GLOBAL_DBNAME的值修改为小写的：jiagulun
    (SID_DESC =
      (SID_NAME = jiagulun)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (GLOBAL_DBNAME = jiagulun)
    )

然后重新启动监听器，再用plsql develop登录，成功登录。

所以本次plsql develop登录oracle产生ORA-01304错误的原因是没有注意listener.ora文件中SID_NAME和GLOBAL_DBNAME值得大小写问题。