ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (NET)

oracle实验记录 (NET)

原创 Linux操作系统 作者:fufuh2o 时间:2009-08-12 16:04:51 0 删除 编辑

 

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

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

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

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

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

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

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427015