sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

案例:Oracle无法本地认证登录 ORACLE_SID正确 但是本地登录提示idle instance

时间:2016-06-20 15:01   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA反映,其数据库本地认证登录时提示idle instance,查看ORACLE_SID正确,TNS可以登录,分析原因为Oracle_home异常导致

1.查看Oracle pmon进程

[oracle@oracleplus ~]$ ps -ef|grep pmon
oracle   26295     1  0 04:11 ?        00:00:01 ora_pmon_XFF
oracle   27997 27966  0 05:48 pts/0    00:00:00 grep pmon
[oracle@oracleplus ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1
[oracle@oracleplus ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 05:48:30 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL>
[oracle@oracleplus ~]$ sqlplus sys/oracleplus@XFF as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 05:54:49 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

对于这样的现象,通过试验重现,并且通过oradebug ipc进行说明。
补充知识点:oracle本地认证是通过ipc进行的,而ipc是直接访问共享内存段的

2.分析Oracle系统当前状态

系统未启动然后数据库情况

[oracle@oracleplus ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
[oracle@oracleplus ~]$ ps -ef|grep pmon|grep -v grep
[oracle@oracleplus ~]$ 

启动数据库
确定环境变量ORACLE_SID,ORACLE_HOME

[oracle@oracleplus ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1
[oracle@oracleplus ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 04:10:22 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266896 bytes
Variable Size              83888944 bytes
Database Buffers          167772160 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

--做debug ipc
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/oracle/admin/XFF/udump/xff_ora_26852.trc


*** SESSION ID:(152.25) 2013-04-20 04:43:00.983
Dump of unix-generic skgm context
areaflags            000000e7
realmflags           0000000f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00200000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdb87e6c
stackdir                   -1
mode                      640
magic                acc01ade
Handle:             0xe781de0 `/u01/oracle/oracle/product/10.2.0/db_1XFF' --->注意($ORACLE_HOME$ORACLE_SID)
Dump of unix-generic realm handle `/u01/oracle/oracle/product/10.2.0/db_1XFF', flags = 00000000
 Area #0 `Fixed Size' containing Subareas 0-0
  Total size 00000000001354d0 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr   --->主要Shmid
      0        0  3080192 0x00000020000000 0x00000020000000
                              Subarea size     Segment size
                          0000000000136000 000000000fa00000
 Area #1 `Variable Size' containing Subareas 2-2
  Total size 000000000f000000 Minimum Subarea size 00400000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      1        2  3080192 0x00000020800000 0x00000020800000
                              Subarea size     Segment size
                          000000000f000000 000000000fa00000
 Area #2 `Redo Buffers' containing Subareas 1-1
  Total size 00000000006ca000 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
   Area  Subarea    Shmid      Stable Addr      Actual Addr

[oracle@oracleplus ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x6fd58208 3080192    oracle    640        262144000  17                      

这里证明ipc的处理是通过$ORACLE_HOME$ORACLE_SID进行的,修改ORACLE_HOME,进一步验证

修改ORACLE_HOME

[oracle@oracleplus ~]$ export ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1/
[oracle@oracleplus ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1/

[oracle@oracleplus ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 04:11:46 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266896 bytes
Variable Size              83888944 bytes
Database Buffers          167772160 bytes
Redo Buffers                7118848 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

[oracle@oracleplus ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x6fd58208 3080192    oracle    640        262144000  18                      
0x079d1b38 3112964    oracle    640        262144000  13              

--启动两个同样的sid实例
[oracle@oracleplus ~]$ ps -ef|grep pmon|grep -v grep
oracle   26211     1  0 04:10 ?        00:00:00 ora_pmon_XFF
oracle   26295     1  0 04:11 ?        00:00:00 ora_pmon_XFF        

SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/oracle/admin/XFF/udump/xff_ora_27708.trc

*** SESSION ID:(159.5) 2013-04-20 05:32:00.969
Dump of unix-generic skgm context
areaflags            000000e7
realmflags           0000000f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00200000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdb5979c
stackdir                   -1
mode                      640
magic                acc01ade
Handle:             0xd99ede0 `/u01/oracle/oracle/product/10.2.0/db_1/XFF'--->注意
Dump of unix-generic realm handle `/u01/oracle/oracle/product/10.2.0/db_1/XFF', flags = 00000000

进一步证明在linux/unix系统,oracle数据库的内存段是通过ORACLE_HOME和ORACLE_SID结合起来识别的,只要ORACLE_HOME或者ORACLE_SID不一样就不能通过IPC访问实例的内存段,也就不能登录数据库


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle无法本地认证登录 ORACLE_SID正确 但是本地登录提示idle instance

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/450.html

Oracle研究中心

关键词:

Oracle无法本地认证登录

ORACLE_SID正确 但是本地登录提示idle instance