[20180222]改變oracle執行時的引數0.txt

[20180222]改變oracle執行時的引數0.txt

--//春節放假,看了連結https://blog.dbi-services.com/server-process-name-in-postgres-and-oracle/
--//裡面提到postgres資料庫可以顯示執行時引數,作者想到oracle如何修改引數0.真心佩服這傢伙Franck Pachot 的技術功底.
--//裡面提到使用top,ps 顯示引數的方法:例子:
# top -c -U oracle
$ ps -u oracle -o pid,comm,cmd,args

--//但是我一直認為oracle連線無法改變的引數顯示,實際上作者給出了例子,我僅僅重複測試:
sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=book)))
sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=book)(SERVICE_NAME=book)))

--//以上兩個方式都可以連線資料庫,但是加入(ARGV0=aaaa)無效.

1.環境:
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試bed連線方式:
sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=book)))
sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=book)(SERVICE_NAME=book)))

sqlplus scott/book@"(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oraclebook)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1')))"
sqlplus scott/book@"(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oraclebook)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=$ORACLE_HOME'))"

--//4個都可以,另外說明一下使用bed連結不用引號出現如下錯誤ora-12547:
$ rlwrap sqlplus   scott/book@(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=testtest)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=$ORACLE_HOME'))
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 22 09:06:14 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

$ oerr ora 12547
12547, 00000, "TNS:lost contact"
// *Cause: Partner has unexpectedly gone away, usually during process
// startup.
// *Action: Investigate partner application for abnormal termination. On an
// Interchange, this can happen if the machine is overloaded.

--//不知道為什麼?

$ rlwrap sqlplus scott/book@"(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oraclebook)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=$ORACLE_HOME'))"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 22 08:54:21 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

string beginning "'SCOTT@(AD..." is too long. maximum size is 50 characters.
SQL> show user;
USER is "SCOTT"

--//實際上已經連上,至於報這個錯誤,我在連結blog.itpub.net/267265/viewspace-2140401/上有說明,不過這裡沒有服務名大小寫問題.不知道那裡有出現錯誤.

$ rlwrap sqlplus   "scott/book@(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=testtest)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=$ORACLE_HOME'))"

SQL> host ps -u oracle -o pid,comm,cmd,args | grep testtes[t]
33906 rlwrap          rlwrap sqlplus -L scott/boo rlwrap sqlplus -L scott/book@(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=testtest)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1'))
33909 oracle          testtest (DESCRIPTION=(LOCA testtest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))

---//修改引數開頭testtest.

$ rlwrap sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)(ARGV0=testtest))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=book)))
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 22 09:10:30 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@book> host ps -u oracle -o pid,comm,cmd,args | grep testtes[t]
33971 rlwrap          rlwrap sqlplus scott/book@( rlwrap sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)(ARGV0=testtest))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=book)))

--//這樣不行.

3.測試遠端連線如何修改:

--//The remote connection can have the name changed from the static registration, adding an ARVG0 value on the listener side:
--//修改監聽配置:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
#      (ADDRESS = (PROTOCOL = TCP)(HOST = gxqyydg4)(PORT = 1521))
#      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
#      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
)

--//再次佩服這傢伙技術功底,主機IP寫入0.0.0.0就是表示在全部介面上接聽.
--//再加入服務如下:
SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = book)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME=book)
      )
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = book123)
      (ARGV0=myapp0)
~~~~~~~~~~~~~~~~~~     
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME=book)
      )
    )

$ sqlplus scott/book@192.168.100.78:1521/book123

SCOTT@192.168.100.78:1521/book123> host ps -u oracle -o pid,comm,cmd,args | grep myap[p]
34274 oracle          myapp0 (DESCRIPTION=(LOCAL= myapp0 (DESCRIPTION=(LOCAL=NO)(SDU=32767))

--//我的測試好像僅僅配置靜態監聽可以實現.