[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
--這樣才能連上資料庫。以後要注意這個問題。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1797866/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190930]關於資料結構設計問題.txt資料結構
- 3.1.5.1 關於啟動資料庫例項資料庫
- 3.1.3 關於資料庫服務自動啟動資料庫
- [20200102]資料庫安裝問題.txt資料庫
- Oracle資料庫啟動問題彙總(一)Oracle資料庫
- [20191202]關於hugepages相關問題.txt
- 關於oracle資料庫訊號量的問題Oracle資料庫
- MySQL資料庫遠端連線開啟方法MySql資料庫
- [20191129]關於hugepages的問題.txt
- [20181123]關於降序索引問題.txt索引
- [20180403]關於時區問題.txt
- 關於go和資料庫連線，客戶端以及驅動的疑問？Go資料庫客戶端
- 遠端服務不能啟動問題的解決方法
- 關於 SAP HANA 資料庫的死鎖問題(deadlock)資料庫
- [20221128]dg資料庫最佳化問題.txt資料庫
- [20181128]toad連線資料庫的問題.txt資料庫
- 資料庫事物相關問題資料庫
- [20230317]關於TIME_WAIT問題.txtAI
- [20210520]關於主鍵索引問題.txt索引
- [20190918]關於函式索引問題.txt函式索引
- [20181229]關於字串的分配問題.txt字串
- Oracle日常問題-資料庫無法啟動(案例二)Oracle資料庫
- Oracle日常問題處理-資料庫無法啟動Oracle資料庫
- Oracle如何診斷遠端訪問資料庫慢/超時等問題小結Oracle資料庫
- 寶塔部署 寶塔遠端連線資料庫出現1045問題資料庫
- 關於wake on lan遠端喚醒主機的問題，長時間關機無法遠端喚醒
- [20201106]瞭解oracle資料庫啟動時間.txtOracle資料庫
- [20230306]os認證連線資料庫問題.txt資料庫
- 關於移動端元件庫元件
- CentOS 7遠端連線相關問題CentOS
- [20190910]關於降序索引問題5.txt索引
- [20211220]關於標量子查詢問題.txt
- [20200711]關於左右連線的問題.txt
- [20200416]關於軟軟解析的問題.txt
- [20181124]關於降序索引問題4.txt索引
- [20181124]關於降序索引問題3.txt索引
- [20181124]關於降序索引問題2.txt索引
- 基於mysql資料庫 關於sql優化的一些問題MySql資料庫優化
- 關於 App 啟動時間測試的問題APP