[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

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.

--回到本地機器看看：

--如果重新設定環境變數
$ 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

--這樣才能連上資料庫。以後要注意這個問題。

[20150911]關於遠端啟動資料庫問題.txt

相關文章