服务器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博客

oracle实验记录（NET）

最新文章

ITPub博客

oracle实验记录 （NET）

最新文章

oracle实验记录（NET）