oracle實驗記錄（NET）

伺服器listener
C:\>sqlplus xh/a831115

SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 12 13:17:32 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
這種是IPC 連線（程式間連線） client與SERVER 在一臺機器上

C:\>sqlplus xh/a831115@xh

SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 12 13:18:46 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
這種TCP IP連線 CLIENT 用TNSNAMES.ora server用 listener.ora

基本的TCP IP 過程是 CLIENT 發請求 SERVER 的LISTENER 程式監聽請求然後LISTENER 通過PMON 註冊到LISTENER的資訊檢查下，然後LISTENER 衍生出一個SERVER HANDLER 新程式（理論上為LISTENER程式的子程式）並且將連線描述符也交給這個子程式，這樣子程式與CLIENT 通訊沿用LISTENER的IP&PORT ，CLIENT 屬於一次TCP/IP連線，若OS 不允許直接傳送（WIN NT上就如此）CLIENT 請求 LISTENER ，LISTENER 衍生出SERVER HANDLER ，LISTENER 與衍生出的新程式IPC連線
新程式將PORT 告訴LISTENER ，LISTENER將PORT 告訴 CLIENT，CLIENT 用新PORT 與新程式連線（IP 還是用LISTENER的因為只發了PORT）這樣CLIENT 就是2次TCP連線，另外若是SHARED MODE那麼 LISTENER不會衍生SERVER HANDLER 而是提前配置好DISPATCHER程式並且註冊到LISTENER中

關於配置 LISTENER
靜態配置就是將資訊明確的寫到 LISTENER。ORA中

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)~~~~~~~~~為外部儲存過程配置的本地監聽
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = xh)~~~~~~~service_name
(ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = orcl)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    )
)
SNRCTL> status

Services Summary...
Service "PLSExtProc" has 1 instance(s).~~~~~~~~~為外部儲存過程配置的本地監聽
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xh" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully~~~~~~~~~~~~~此時DATABASE DOWN
LSNRCTL>
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                   788672 bytes
Variable Size             145487680 bytes
Database Buffers          167772160 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SLSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

LSNRCTL> status~~~~~~~~~~~~~~~~~可以看到出現動態註冊
Service "ORCL" has 1 instance(s).~~~~~~~~~~~~~~~~~~~~~動態
Instance "orcl", status READY, has 7 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XH" has 2 instance(s).~~~~~~~~~~~~靜態註冊
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...~~~~~~~~~~~~靜態註冊
Instance "orcl", status READY, has 7 handler(s) for this service...~~動態
The command completed successfully

以上看出如果LISTENER 先啟動 DATABASE後那麼 ORACLE 使用動態註冊優先於靜態註冊

例先啟動DATABASE 在啟動LISTENER 這樣 PMON 是60秒才註冊這時候只能用靜態註冊
    (SID_DESC =
      (GLOBAL_DBNAME = xh)
       (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = orcl)
    )
)

Connected.
LSNRCTL> start
Starting tnslsnr: please wait...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service
Service "xh" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...~~靜態
The command completed successfully
SQL> conn xh/a831115@xh~~~~~~~~~

LSNRCTL> stop
    (SID_DESC =
      (GLOBAL_DBNAME = xh)
       (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)~~~~~~~~~去掉這部分資訊
      (SID_NAME = orcl)
    )
)

LSNRCTL> start
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

沒有靜態資訊

>status
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> conn xh/a831115@xh
ERROR:
ORA-12514: TNS:listener does not currently know of service re
descriptor

Warning: You are no longer connected to ORACLE.
此時我們可以等待PMON 也可以手動註冊

SQL> alter system register;

System altered.
>status
Service "ORCL" has 1 instance(s).~~~~~~~都註冊進去了
Instance "orcl", status READY, has 7 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XH" has 1 instance(s).
Instance "orcl", status READY, has 7 handler(s) for this service...
The command completed successfully
LSNRCTL>
SQL> conn xh/a831115@xh
Connected.

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_name string xhtest
SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_domain string
SQL> show parameter service_name

NAME TYPE VALUE
------------------------------------ ----------- ---------------
service_names string ORCL, XH
SQL> show parameter global_name

NAME TYPE VALUE
------------------------------------ ----------- ---------------
global_names boolean FALSE
SQL>

default service_name=global_name=db_name.db_domain
*************************
先啟動DATABASE 後啟動LISTENER~（此時還未動態註冊只能先用靜態的）

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = xh)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = orcl)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    )
)

tnsnames.ora中
AA =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

LSNRCTL> start
Starting tnslsnr: please wait...
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xh" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL> conn xh/a831115@xh
Connected.
SQL> conn xh/a831115@aa~~~~~~~~~LISTENER。ORA global_name與TNSNAME。ORA service_name不匹配
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Warning: You are no longer connected to ORACLE.
>STATUS
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xh" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

過了一會
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "orcl", status READY, has 7 handler(s) for this service...~~~~~~~~~~~~動態註冊了
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xh" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 7 handler(s) for this service...~~~~~~~~~~動態註冊了
The command completed successfully
過了會PMON 將所有SERVICE_name動態註冊到LISTENER了這樣與TNSNAMES中匹配了

SQL> conn xh/a831115@aa~可以連線
Connected.
SQL>
靜態時候 LISTENER 將 LISTENER。ORA中 GLOBAL_NAME 與CLIENT TNSNAMES。ORA中 SERVIEC_NAME 匹配若CILENT TNSNAME.ORA中用的 SID 那麼 listener use LISTENER。ORA中SID_name 匹配就行了

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = xh)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)~~~~~~~~LISTENER.ORA中加上
      (SID_NAME = ORCL)
    )
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
)

>start
Services Summary...
Service "orcl" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "xh" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
>status
Service "orcl" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "xh" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> conn xh/a831115@aa
Connected.
SQL> conn xh/a831115@xh
Connected.

或TNSNAME。ORA中用SID

xh =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (oracle_sid = orcl)
    )
)
AA =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (oracle_sid = orcl)
    )
)

listener.ora中 global_name隨便改
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = AAAAAAA)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = BBBBBB)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = ORCL)
    )
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
)
LSNRCTL> STAR
Starting tnslsnr: please wait...
Services Summary...
Service "AAAAAAA" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "BBBBBB" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

LSNRCTL> status
Service "AAAAAAA" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "BBBBBB" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> conn xh/a831115@xh
Connected.
SQL> conn xh/a831115@aa
Connected.
*****************************************
~動態註冊~~不需要LISTENER。ORA
SQL> host move E:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\listener.ora d:\list
ener.ora;

LSNRCTL> star
Starting tnslsnr: please wait...

The listener supports no services
The command completed successfully 等會或手動註冊（因為先DATABASE 後LSTENER 啟動的，PMON 60秒才註冊）

Services Summary...~過會兒後
Service "ORCL" has 1 instance(s).
Instance "orcl", status READY, has 7 handler(s) for this service...
Service "XH" has 1 instance(s).
Instance "orcl", status READY, has 7 handler(s) for this service...
The command completed successfully~~~~~~~~~~~~~~~~~動態註冊進來

******************
關於tnsnames.ora
這個是CLIENT 要配的~~~CLIENT 總要知道些資訊才能才能連線吧包括使用者名密碼，IP，PORT，SERVICE_name
xh =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (oracle_sid = orcl)
    )
)
AA =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (oracle_sid = orcl)
    )
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

也可以不用TNSNAME。ORA直接連

IP Address. . . . . . . . . . . . : 192.168.1.126

SQL> conn xh/a831115@192.168.1.126:1521/xh 屬於EASY NAMING
Connected.
SQL> conn xh/a831115@192.168.1.126:1521/orcl
Connected.
SQL>

LOCAL NAMING 就是都放入 TNSNAME。ORA

SQLNET。ORA

SQLNET.AUTHENTICATION_SERVICES= (NTS)~~可以控制/ AS SYSDBA（會有單獨實驗詳細介紹 AS SYSDBA）

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) 這個是允許CLIENT連線的方式

若 TNSNAMES去掉
C:\>sqlplus xh/a831115@xh

SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 12 15:56:32 2009

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~幾個檢查
C:\>PING 192.168.1.126~~~~~~~~~~~~網路

Pinging 192.168.1.126 with 32 bytes of data:

Reply from 192.168.1.126: bytes=32 time<1ms TTL=128
Reply from 192.168.1.126: bytes=32 time<1ms TTL=128
Reply from 192.168.1.126: bytes=32 time<1ms TTL=128
Reply from 192.168.1.126: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.1.126:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\>tnsping xh~~~~~~~~~oracle層的檢查

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 12-8月 -
2009 15:58:55

Used parameter files:
E:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 6688-621
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (oracle_sid = orcl)))
OK (50 msec)

若PING通但TNSPING不通那麼檢查TNSNAMES.ora or sqlnet.ora中是否禁用TNSNAMES

C:\>lsnrctl~~~~控制LISTENER

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 12-8月 -2009 16:0
1:17

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               change_password
quit                exit                set*~~~~~~~~~~~~~~還有許多可設定的引數
show*

LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:

rawmode                     displaymode
rules                       trc_file
trc_directory               trc_level
log_file                    log_directory
log_status                  current_listener
inbound_connect_timeout     startup_waittime
snmp_visible                save_config_on_stop 使用查聯機文件就行了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-611997/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄 （NET）

相關文章

oracle實驗記錄（NET）