开发者社区> lfreeali> 正文

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

简介: [20150911]关于远程启动数据库问题.txt --上午同事遇到一个本地访问数据库的问题,我看了一下,很快定位问题,就是因为.bash_profile中定义环境变量ORACLE_HOME后面有1 --个斜杠。
+关注继续查看

[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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
[20121108]关于克隆数据库的问题.txt
[20121108]关于克隆数据库的问题.txt前几天在克隆数据库生产系统数据库时,遇到一个非常奇怪的问题,记录一下:克隆的机器口令文件,以及spfile以前都存在,实际上很简单,先停止旧数据库,然后改安装目录名(害怕还要使用!),再安装数据库:startup ...
712 0
[20170111]设置无需口令登录数据库2.txt
[20170111]设置无需口令登录数据库2.txt --//上午测试无需口令登录数据库,这样连接数据库使用sqlplus /@book ,这样实际上通过网络连接数据库,哪怕是在本机也是这样。
815 0
[20150508]列顺序问题.txt
[20150508]列顺序问题.txt --链接: https://viveklsharma.wordpress.com/2015/04/30/cpu-cycles-for-column-skipping/ --测试列顺序对CPU cost的影响: S...
636 0
[20121214]数据库错误记录.txt
[20121214]数据库错误记录.txt--自己参照许多文档,写了一个检测数据库错误的例子:--以sys用户登录,建立表CREATE TABLE SYS.ERROR_LOG(  IP_ADDRESS       VARCHAR2(30 BYTE),  USER...
665 0
[20121126]backup as copy datafile 1的问题.txt
[20121126]backup as copy datafile 1的问题.txtSQL> select * from v$version where rownumBANNER------------------------------------------...
726 0
[20180224]expdp query 写法问题.txt
[20180224]expdp query 写法问题.txt --//如果使用expdp/impdp导入导出,如果参数复杂,最好的建议使用参数文件.避免各种问题.通过简单的例子说明问题.
1074 0
[20150430]同义词使用问题.txt
[20150430]同义词使用问题.txt SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -...
737 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
2121
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载