[20150911]关于远程启动数据库问题.txt

[20150911]关于远程启动数据库问题.txt

--上午同事遇到一个本地访问数据库的问题，我看了一下，很快定位问题，就是因为.bash_profile中定义环境变量ORACLE_HOME后面有1
--个斜杠。

--我以前写过一个关于启动的问题，链接如下，可以我接着测试，无论如何都不能再现当时的情况：
--http://blog.itpub.net/267265/viewspace-1443469/

--我记得当时测试许多次，当时没有分析为什么？看来以后一定要认真分析原因，而不是仅仅解决问题。

--"最终"定位了问题，不知道是否存在其它情况，是因为服务的监听配置里面配置的ORACLE_HOME最后有1个斜线。

--[后记:这个带引号的最终,主要是我现在看当时出问题的机器,在服务器监听里面确实存在斜线(ORACLE_HOME参数最后),但是不大可能我
--的测试环境也存在这个问题,当时写那篇blog的时间是2015.02.28,春节前也许出问题,开发重启了数据库,因为开发人员没有oracle用户
--的密码,无法登陆服务器重启数据库,而是通过windows的机器以sys用户登陆关闭与重启了数据库.而我拿测试环境测试进行同样的测试,
--也出现同样的问题,想当然认为远程连接启动数据库都存在这个问题.犯了一个非常低级的错误!]

--出现问题的监听配置如下:
$  cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/rac_db/network/admin/listener.ora
# Generated by Oracle configuration tools.

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 =
      (SDU=32767)
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)
      (SID_NAME = test)
      )

  )

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

--首先1点要远程启动数据库，一定要配置静态监听。
--这样当远程启动数据库时，ORALCE_HOME作为其中1部分。这样本地的配置ORACLE_HOME环境变量不带斜线,通过本地访问就无法连接数据库。

--去掉这个斜线远程启动，本地就不存在前面描述的问题。
--看来以后出现问题，不仅要解决问题，还要分析问题的原因。这样自己的能力才能提高。下面是一些补充(有点乱)

1.检查环境:
$ echo $ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2

--监听设置的静态监听：
SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = test.com)
      (ORACLE_HOME = /u01/app/oracle11g/product/11.2.0/db_2/)
      (SID_NAME = test)
      )
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = test_DGMGRL.com)
      (ORACLE_HOME = /u01/app/oracle11g/product/11.2.0/db_2)
      (SID_NAME = test)
      )
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = test_DGB.com)
      (ORACLE_HOME = /u01/app/oracle11g/product/11.2.0/db_2)
      (SID_NAME = test)
      )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle11g

--注意后面的斜线.

2.以上环境启动数据库：

--这个时候远程执行，一点问题都没有。
sqlplus scott/btbtms@192.168.100.40:1521/test.com

$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-SEP-2015 17:05:46
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.40)(PORT=1521)))
The command completed successfully

$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-SEP-2015 17:05:48
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Starting /u01/app/oracle11g/product/11.2.0/db_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle11g/product/11.2.0/db_2/network/admin/listener.ora
Log messages written to /u01/app/oracle11g/diag/tnslsnr/hisdg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.40)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.40)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.40)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                11-SEP-2015 17:05:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle11g/product/11.2.0/db_2/network/admin/listener.ora
Listener Log File         /u01/app/oracle11g/diag/tnslsnr/hisdg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.40)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.40)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "test.com" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test_DGB.com" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test_DGMGRL.com" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

d:\tools\sqltemp>sqlplus scott/btbtms@192.168.100.40:1521/test.com
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 11 17:05:48 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

--仔细查看service=test.com，status=UNKNOWN,表示静态监听。因为里面的斜线，导致通过这个服务无法连上。
--如果动态监听注册后，一般等几分钟就注册了或者执行alter system register手工注册。

$ lsnrctl status
...
Services Summary...
Service "b.com" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test.com" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
  Instance "test", status READY, has 1 handler(s) for this service...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Service "testXDB.com" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_DGB.com" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_DGMGRL.com" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

--这个时候远程就可以连上。

SCOTT@192.168.100.40:1521/test.com> @spid
       SID    SERIAL# SPID   C50
---------- ---------- ------ --------------------------------------------------
        11          5 12308  alter system kill session '11,5' immediate;

# cat /proc/12308/environ |tr '=' '\n' | strings | grep -A1 ORACLE_HOME
ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2

--可以发现使用动态监听。通过lsnrctl service也可以确定.

$ lsnrctl service
...
Service "test.com" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0
         LOCAL SERVER
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER

--注意看静态监听也有2次，实际上那2次我都没连上。

3.修改监听配置文件。[注:删除最后的斜线]

$ lsnrctl stop
$ lsnrctl start

--马上在远程执行登陆一点问题都没有。因为静态监听配置正确。

SCOTT@192.168.100.40:1521/test.com> @spid
       SID    SERIAL# SPID   C50
---------- ---------- ------ --------------------------------------------------
        11          7 12344  alter system kill session '11,7' immediate;

# cat /proc/12344/environ |tr '=' '\n' | strings | grep -A1 ORACLE_HOME
ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2

--如何知道是通过静态服务连接数据库呢？通过lsnrctl service可以确定。

$ lsnrctl service
...

Service "test.com" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

--可以从上面看出来。另外我的测试如果动态监听注册，再远程连接数据库使用的是动态监听。

$ lsnrctl service
...
Service "test.com" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER

4.最后修改监听配置文件,恢复删除最后的斜线,重新再现问题.
d:\tools\sqltemp>sqlplus sys/btbtms@192.168.100.40:1521/test.com as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 12 11:04:16 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@192.168.100.40:1521/test.com> @spid
       SID    SERIAL# SPID   C50
---------- ---------- ------ --------------------------------------------------
       201          3 683    alter system kill session '201,3' immediate;

# cat /proc/683/environ | tr '=' '\n'|strings | grep -A1 ORACLE_HOME
ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2

SYS@192.168.100.40:1521/test.com> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

# cat /proc/683/environ | tr '=' '\n'|strings | grep -A1 ORACLE_HOME
cat: /proc/683/environ: No such file or directory

--注意看这个进程号683已经不存在。
# lsof -i :1521 -P -n
COMMAND   PID      USER   FD   TYPE    DEVICE SIZE NODE NAME
oracle    711 oracle11g   14u  IPv6 145765121       TCP 192.168.100.40:1521->192.168.101.6:50033 (ESTABLISHED)
tnslsnr 24419 oracle11g    8u  IPv6 145344652       TCP *:1521 (LISTEN)

# ps -ef | grep 2441[9]
503      24419     1  0 Sep11 ?        00:00:00 /u01/app/oracle11g/product/11.2.0/db_2/bin/tnslsnr LISTENER -inherit

# ps -ef | grep 71[1]
503        711     1  0 11:06 ?        00:00:00 oracletest (DESCRIPTION=(LOCAL=NO)(SDU=32767))

--连上进程号实际上是711.

# cat /proc/24419/environ | tr '=' '\n'|strings | grep -A1 ORACLE_HOME
ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2

# cat /proc/711/environ | tr '=' '\n'|strings | grep -A1 ORACLE_HOME
ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2/

--注意看这个时候进程号711的环境变量ORACLE_HOME=/u01/app/oracle11g/product/11.2.0/db_2/,是带斜线的。这个时候远程启动数据库：

SYS@192.168.100.40:1521/test.com> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes
Database mounted.
Database opened.

--回到本地机器看看：

$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 12 11:15:52 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
Connected to an idle instance.

--如果重新设置环境变量
$ export ORACLE_HOME=/u01/app/oracle11g/product/11.2.0/db_2/
$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 12 11:18:04 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--这样才能连上数据库。以后要注意这个问题。