oracle 实例 idle,一则异常的没法链接到oracle实例Connected to an idle instance问题.

最近遇到的问题也愈来愈妖了,在linux环境中使用sqlplus / as sysdba没法链接到oracle实例,以下:linux

[dsg@cnsvwshs0438 config]$ sqlplus / as sysdbasql

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

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

SQL>Connected to an idle instance.

app

通常这个都是环境变量不对引发的,因此查看当前的环境变量:oop

[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

ui

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

一脸懵逼中...spa

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

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 openOracle 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 | 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 | grep ORACLE

LINUX:

cat /proc//environ

AIX:

ps eauwww

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

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

(before 10g)

HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_

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的环境变量为何多了一个/

坑是真的深....