[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
--这样才能连上数据库。以后要注意这个问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1797866/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1797866/