最近遇到的问题也越来越妖了,在linux环境中使用sqlplus / as sysdba无法连接到oracle实例,如下:

[dsg@cnsvwshs0438 config]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 22 22:05:31 2019

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

SQL>Connected to an idle instance.
 

一般这个都是环境变量不对引起的,所以查看当前的环境变量:

[dsg@cnsvwshs0438 config]$ env |grep ORACLE
ORACLE_SID=ncc
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/db10g
[dsg@cnsvwshs0438 config]$ ps -ef |grep pmon
dsg       8171  7788  0 22:04 pts/1    00:00:00 grep pmon
oracle   19042     1  0  2016 ?        00:39:14 ora_pmon_ncc
 

怎么看也没什么问题啊.由于我的这个用户是客户帮我新建的用户,客户没有帮我配置环境变量.oracle的换变量是我自己根据系统查的,其中ORACLE_SID根据pmon的进程号取出来的.ORACLE_HOME是根据/etc/oratab取出来的.而且我到相对于的ORACLE_HOME目录下查看也是正确的啊.

一脸懵逼中...

求救于MOS,在MOS中找到一篇文档:

Connecting AS SYSDBA results in "Connected to idle instance", yet the database is OPEN (文档 ID 728895.1)

SYMPTOMS

-- Problem Statement:
On 10.2.0.1 in Production:
When attempting to connect AS SYSDBA to an open Oracle database,
the following message is received:

MESSAGE
-------------------
Connected to an idle instance.

However, the database was indeed up, running, and open.

CAUSE

The session's $ORACLE_HOME was not the same as that the instance was started with.

The Oracle instance was started with a trailing slash in $ORACLE_HOME: /opt/oracle/10gR2/

The session's $ORACLE_HOME was: /opt/oracle/10gR2 (without the trailing slash).

SOLUTION

From Note 373303.1, "How to Check the Environment Variables for an Oracle Process":


1. Determine the pid of the process at OS level, eg for the smon process:

ps -ef | grep smon

2. Get the environment of the process:

SOLARIS:
pargs -e <pid from above> | grep ORACLE

(See Note 373303.1 for syntax on other platforms.)

3. Modify ORACLE_HOME in the session environment to match this string.

Or, restart the instance using the ORACLE_HOME that matches that of the session environment.

 

如上,MOS很肯定的告诉我们,你的ORACLE_HOME环境变量不对,而且告诉了我们怎么查看ORACLE运行的环境变量,见文档:

How to Check the Environment Variables for an Oracle Process (文档 ID 373303.1)

我就直接贴过来了:

SOLUTION

 

1. Determine the pid of the process at OS level, eg for the smon process:
ps -ef | grep smon

2. Get the environment of the process:

SOLARIS:
pargs -e <pid from above> | grep ORACLE

LINUX:
cat /proc/<pid from above>/environ

AIX:
ps eauwww <pid from above>

HP-UX:
On this Unix flavor there is no command to grasp the process environment directly. This can only be extracted using a debugger from the _environ structure. This procedure can be used on the other Unix flavors, as follows:
gdb smon <pid from above>
This attaches gdb to the pid mentioned above. The smon name is just an indication that the process we attach to is smon, but the only parameter that matters is the pid.
After attaching to the process, the following command extracts the information from the _environ list:
p ((char**)_environ)[0]@30
which would list the first 30 environment variables. If more are defined, just increase the parameter after @.
As well, the list can be extracted one item from the list at a time, using an iterator like:
p ((char**)_environ)[i]
which would extract element #i+1.

 

Alternatively you can do this :

1) Create the following script called print_environment.gdb:

set $v = (char**)environ
while $v[0]
  print $v[0]
  set $v = $v+1
end
detach
quit

2) Get the PID of one background server process :

ps -ef | grep smon

 

3) Call print_environment.gdb to display the variable ( SHLIB_PATH in this case ) :

 

gdb -q -x print_environment.gdb  a.out <pid from above> | grep SHLIB_PATH

 

Windows:
To get the information on Windows, 2 things are needed:
1. check the registry for the ORACLE_* keys used to start the Oracle process. These keys are in:
HKEY_LOCAL_MACHINE/Software/Oracle/HOME<x>
(before 10g)
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_<home name>
from 10g on.
2. check the environment variables that were used by the oracle process at startup.
For this, one would need the process explorer utility from sysinternals, which can be found at:
www.sysinternals.com
(http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx)
After starting the procexp utility, find the oracle process you want to check in the process list, right click on it, then select Properties. The Environment tab should indicate all the environment variables used when the process was started (even if dynamically in command line).
The utility also displays the key values from registry, but being so many it's difficult to look for them.

 

最后发现客户的ORACLE_HOME环境变量为:

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1//db10g
而我写的ORACLE_HOME环境变量为:

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/db10g
 

 注意这里我的环境变量少了一个/  但是我查他真实的ORACLE_HOME安装目录应该就是我这个目录才对,不清楚为何客户oracle的环境变量为什么多了一个/

坑是真的深....