专题实验第一篇：ORACLE网络

来源：互联网发布：windows xp镜像编辑：程序博客网时间：2024/05/16 12:40

lurou从3月份开始，开始在思考，如何在生产系统不出故障的情况下，依然可以不落下数据库实践操作的机会，依然保持动手能力逐步加强的步伐。lurou曾经对自己的学生开玩笑：“你不懂oracle，你可以吹牛忽悠，可是如果在现场，你不懂，你如何动手，你敢动手吗？”。而这个“懂”，我以为应该有坚实的实践基础，当然理论是前提。于是我打算再次对oracle进行整体性梳理，进行分类专题实验，比如分为oracle网络、oracle数据空间管理、oracle内存调整、oracle监控等专题。

一、理论参考

1、oracle监听和tns

a.什么叫注册，静态注册和动态注册的区别

b.手工编写和netca配置，netmgr配置

c.listener，tnsname.ora，sqlnet.ora各自的作用

d.不建议用户在win7下实验oracle软件，建议使用虚拟机与客户端的win7进行完美分离。因为win7不被oracle支持，有很多问题，比如：

案例一：

在win7如果使用netmgr来配置listener时不会报错，但是lsnrctl start时会报错，服务不能成功添加到注册表中，不过可以使用netca来成功配置。所以在win 7使用oracle配置监听可以使用netca。

案例二：

oracle客户端在win7下进行安装之后，进行ODBC配置时会出问题，最后需要修改注册表进行修正。

理论参考链接：

http://blog.csdn.net/tianlesoftware/article/details/5543166

http://hi.baidu.com/edeed/item/c895500c2cf1caf3a0103489

http://space.itpub.net/165278/viewspace-607236

2、数据库专有模式和共享模式

理论参考链接:

http://blog.csdn.net/tianlesoftware/article/details/5695784

3、dblink

知识点：

理论参考链接:

张烈数据库讲义

http://www.cnblogs.com/xinyuxin912/archive/2008/01/09/1032261.html

http://cheneyph.iteye.com/blog/480462

二、实验列表

实验1：静态注册和动态注册

LSNRCTL> services

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.128.21)(PORT=1521)))

Services Summary...

Service "share" has 1 instance(s).

Instance "share", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:4561 refused:0

LOCAL SERVER

The command completed successfully

status UNKNOWN的是静态注册，如果现实状态为READY的为动态注册。

手工注册：

在动态注册的情况中，如果监听在数据库实例启动的状态下被停止之后再启动，监听需要在大概1分钟之后进行数据库注册，如果你等不及或者出现异常的时候，你可以手工进行注册,命令很简单：

ALTER SYSTEM REGISTER

在10g、11g版本中，很多人还是偏爱动态监听的，数据库被创建之后，其实不用netca进行任何配置也是可以从外界连接到数据库的，我测试过，em管理器也可以正常使用。

需要注意的是，在操作系统级别的HA双机中，由于浮动IP地址需要配置到监听中，这个时候就必须使用静态监听。

实验2：如何配置sqlnet.ora

WINDOW环境下修改sqlnet.ora时，使用editplus或ue工具，否则会出现乱码问题。

# sqlnet.ora Network Configuration File: d:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)

位置在：

D:\app_files\oracle\product\10.2.0\client_1\network\ADMIN

sqlnet.ora的作用为：

举例如下：当你执行如下命令连接远端数据库AAA时，

C:\Users >SQLPLUS SYSTEM/STRONGS@AAA

本地需要去解析并连接AAA字符串对应的主机，解析的范围有本地的hosts文件、tnsnames.ora等多种方式，如果你未配置sqlnet.ora或根本没有这个配置文件，那就会按照oracle软件默认的顺序去搜索并解析。

实验3：netca配置listener，netmgr配置tns，手工配置listener和tns

netca

配置的注意事项：

1、端口1521，内网无所谓，外网需要使用非默认端口

2、默认的监听注册信息，当前互联网已经有很多针对他得攻击手段，有被攻击的风险，应该在第一次创建监听的时候就立即删除掉：

第一次创建配置的默认监听配置信息：

sharedbpro:/oracle/product/10.2/db/network/admin> more listener.ora20121119am

# listener.ora Network Configuration File: /oracle/product/10.2/db/network/admin

/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/product/10.2/db)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.128.21)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

修改整理之后的监听配置信息：

sharedbpro:/oracle/product/10.2/db/network/admin> more listener.ora

# listener.ora Network Configuration File: /oracle/product/10.2/db/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = share)

(ORACLE_HOME = /oracle/product/10.2/db)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.128.21)(PORT = 1521))

)

当某些倒霉或极端情况下，你不能使用图形界面去配置listener和tnsnames.ora的时候，你只能手工编写配置文件，这个时候最好的方法是从别的主机上找一份正确的配置文件，ftp到需要进行配置的目标主机。（或者你直接查看并模仿oracle软件自带的示例文件即可，再或者随身带一份linux字符集环境下的文件，然后上传到目的主机并进行适当修改即可。）

oracle自带的示例文件的位置：

sharedbpro:/oracle/product/10.2/db/network/admin/samples> ls

listener.ora sqlnet.ora tnsnames.ora

实验4：监听加密的配置

1、查看原始状态：

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 13-APR-2013 12:18:35

Uptime 0 days 1 hr. 55 min. 44 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/10.2/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.123.188)(PORT=1521)))

Services Summary...

Service "oratest" has 1 instance(s).

Instance "oratest", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL>

oracle@test:/oracle/product/10.2/db_1/network/admin> more listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = oratest)

(ORACLE_HOME = /oracle/product/10.2/db_1/)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.123.188)(PORT = 1521))

)

2、修改本地系统认证参数

添加参数，使得本地系统验证被禁用。

oracle@test:/oracle/product/10.2/db_1/network/admin> more listener.ora

## close the os yanzheng

LOCAL_OS_AUTHENTICATION_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = oratest)

(ORACLE_HOME = /oracle/product/10.2/db_1/)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.123.188)(PORT = 1521))

)

先手执行reload和status命令，知道你可以看到如下红字的样子：

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521) ))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 13-APR-2013 12:18:35

Uptime 0 days 2 hr. 8 min. 18 sec

Trace Level off

Security OFF

SNMP OFF

Listener Parameter File /oracle/product/10.2/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.123.188)(PORT=1521)))

Services Summary...

Service "oratest" has 1 instance(s).

Instance "oratest", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

3、设置监听密码

LSNRCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

start stop status

services version reload

save_config trace spawn

change_password quit exit

set* show*

LSNRCTL> change_password

Old password:

New password:

Reenter new password:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

Password changed for LISTENER

The command completed successfully

LSNRCTL> save_config

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

TNS-01169: The listener has not recognized the password

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

TNS-01169: The listener has not recognized the password

LSNRCTL> set password

Password:

The command completed successfully

LSNRCTL>

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 13-APR-2013 12:18:35

Uptime 0 days 2 hr. 11 min. 35 sec

Trace Level off

Security ON: Password

SNMP OFF

Listener Parameter File /oracle/product/10.2/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.123.188)(PORT=1521)))

Services Summary...

Service "oratest" has 1 instance(s).

Instance "oratest", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL> save_config

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

Saved LISTENER configuration parameters.

Listener Parameter File /oracle/product/10.2/db_1/network/admin/listener.ora

Old Parameter File /oracle/product/10.2/db_1/network/admin/listener.bak

The command completed successfully

检查监听器里的密码设置相关内容：

oracle@test:/oracle/product/10.2/db_1/network/admin> more listener.ora

## close the os yanzheng

LOCAL_OS_AUTHENTICATION_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = oratest)

(ORACLE_HOME = /oracle/product/10.2/db_1/)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.123.188)(PORT = 1521))

)

#----ADDED BY TNSLSNR 13-APR-2013 14:30:47---

PASSWORDS_LISTENER = ADD733DA61CD19A5

#--------------------------------------------

4.验证监听密码的设置是否生效

LSNRCTL> exit

oracle@test:~> lsnrctl

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 13-APR-2013 14:33:26

Welcome to LSNRCTL, type "help" for information.

LSNRCTL>

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

TNS-01169: The listener has not recognized the password

LSNRCTL> stop

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

TNS-01169: The listener has not recognized the password

LSNRCTL> start

TNS-01106: Listener using listener name LISTENER has already been started

LSNRCTL>

用set password关键字输入密码，并且进行正常操作：

LSNRCTL> set password

Password:

The command completed successfully

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 13-APR-2013 12:18:35

Uptime 0 days 2 hr. 15 min. 54 sec

Trace Level off

Security ON: Password

SNMP OFF

Listener Parameter File /oracle/product/10.2/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.123.188)(PORT=1521)))

Services Summary...

Service "oratest" has 1 instance(s).

Instance "oratest", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL> stop

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.123.188)(PORT=1521)))

The command completed successfully

总结：

set password命令，有2个作用，一个是设置密码，另外一个作用就是每次需要验证密码的时候需要输入该命令，就比如你第一次设置密码之后，在保存设置得时候系统会提示监听程序不认识该密码，其实这个时候就是提示你监听需要你输入密码进行初次验证，这个时候你输入set password进行验证之后，就可以保存配置且再到listener.ora中看就能看到新添加的跟密码设置有关的记录了。也就是说save_config至少需要操作两次才能成功。

实验5：判断oracle是共享模式还是专用模式的方法，共享模式下的缺点

SQL> show parameter shared_server

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers integer

shared_server_sessions integer

shared_servers integer 1

SQL>

max_shared_servers和shared_server_sessions对应的值都为空时表示数据库为专用服务器模式，非空时则表示为共享服务器模式。专用服务器模式常常用于OLTP系统中，共享服务器模式有时候会用于OLAP系统中。

共享模式下的缺点是：该模式的串行特点容易造成任务的阻塞。

实验6： dblink，使得北京的数据库可以访问新疆的数据库内的内容；

为什么我们需要dblink？

物理上存放于同一网络但是存在于不同地点（比如不同省份）的多个ORACLE数据库，逻辑上可以看成一个单一的大型数据库，用户可以通过网络对异地数据库中的数据进行存取，而服务器之间的协同处理对于工作站用户及应用程序而言是完全透明的，开发人员无需关心网络的链接细节、数据在网络节点中的具体分布情况和服务器间的协调工作过程。

1.条件规划

192.168.74.10 GZX

192.168.74.200 ZHANGWEI

2、数据库一，创建用户，创建数据表，插入数据

create user dblinktest identified by tiger (on 74.10)

grant connect,resource todblinktest;

conn dblinktest/tiger

create table t(id varchar2(10));

insert into t values(1);

insert into t values(2);

commit;

3、数据库二，配置tns

PACS2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.10)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = pacs2)

)

4.数据库二，创建dblink

create database link gzx_to_zw
connect to dblinktest identified by tiger
using 'pacs2';

1)dblink名必须与远程数据库的全局数据库名（global_name）相同；
2)用户名，口令为远程数据库用户名，口令;
3)主机字符串为本机（数据库二）tnsnames.ora中定义的串;
4)两个同名的数据库间不得建立dblink;
然后，你就可以通过dblink访问远程数据库了。

5、测试dblink是否创建成功

SELECT * FROM t@ gzx_to_zw;

实验7：public dblink，创建同义词和视图，使得同一数据库的其他用户也可以访问

create public database link p_gzx_to_zw
connect to dblinktest identified by tiger
using 'pacs2';

CREATE VIEW t_view AS SELECT * FROMt@p_gzx_to_zw;

CREATE VIEW t_view2 AS SELECT * FROMt@ gzx_to_zw;

create user pblink identified by pblink (on 74.200)

grant connect,resource to pblink;

conn pblink/pblink

select * from dblinktest.t_viewt@p_gzx_to_zw;

select * from t_viewt@p_gzx_to_zw;

___________________________________________________________________________________

Author: laven54 (lurou)

Email: laven54@163.com

Blog: http://blog.csdn.net/laven54