oracle 网络配置(1)

网络：

服务器端：一定要启动监听程序
无配置启动监听程序：rm -f $ORACLE_HOME/network/admin/listener.ora
查看监听状态：lsnrctl status
启动监听：lsnrctl start

客户端：使用服务命名（网络连接字符串）

cat/etc/hosts
------------------------------------------------------------
#Donotremovethefollowingline,orvariousprograms
#thatrequirenetworkfunctionalitywillfail.
127.0.0.1localhost.localdomainlocalhost
::1localhost6.localdomain6localhost6
172.25.254.250foundation0.ilt.example.com

------------------------------------------------------------
客户端使用简单连接：
sqlplus scott/tiger@foundation0.ilt.example.com:1521/primary
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
使用监听配置文件：可以控制IP、端口、监听名字
vi $ORACLE_HOME/network/admin/listener.ora
-------------------------------------------------------------
LISTENER=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=1521))
)
-------------------------------------------------------------

使用客户端配置文件：
vi $ORACLE_HOME/network/admin/tnsnames.ora
-------------------------------------------------------------
250=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME = primary)
)
)
-------------------------------------------------------------
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
修改监听的端口号：
vi $ORACLE_HOME/network/admin/listener.ora
-------------------------------------------------------------
LISTENER=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
)
-------------------------------------------------------------

pmon默认情况只能向1521注册实例！
使用local_listener指明注册实例的详细信息：
alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788)))';

使用remote_listener将实例注册到远程监听
alter system set remote_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.0.10)(PORT=1521)))';

客户端的端口号需要同步修改：
vi $ORACLE_HOME/network/admin/tnsnames.ora
-------------------------------------------------------------
250=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
(CONNECT_DATA=
(SERVICE_NAME = primary)
)
)
-------------------------------------------------------------

修改监听的名字：对客户端没有任何影响
vi $ORACLE_HOME/network/admin/listener.ora
-------------------------------------------------------------
LISTENER_DB01 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
)
-------------------------------------------------------------
操作非默认命名的监听：
lsnrctl status LISTENER_DB01
lsnrctl status LISTENER_DB01
lsnrctl status LISTENER_DB01

激活pmon注册实例信息到监听程序

SQL>altersystemregister;

使用pmon注册实例信息到监听程序，属于动态注册，没有实例的情况下是不能使用oracle的网络！！
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
在数据库停止的状态如果向使用oracle的网络，需要静态注册
vi $ORACLE_HOME/network/admin/listener.ora
-------------------------------------------------------------
LISTENER=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary) #数据库唯一名+域名,db_unique_name+db_domain
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = db01) # $ORACLE_SID,决定寻找口令文件和参数文件的后缀
)
)
-------------------------------------------------------------

客户端的故障转移配置：服务器异常时客户端的连接不中断
vi $ORACLE_HOME/network/admin/tnsnames.ora
-------------------------------------------------------------
250=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
(RETRIES = 180)
(DELAY = 5)
)
)
)
-------------------------------------------------------------

查看会话是否支持故障转移：
select USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER
from v$session where username='SCOTT';

预衍生服务进程：
vi $ORACLE_HOME/network/admin/listener.ora
-------------------------------------------------------------
LISTENER=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = db01)
(PRESPAWN_MAX = 10)
(PRESPAWN_LIST =
(PRESPAWN_DESC =
(PROTOCOL = tcp)
(POOL_SIZE = 10)
(TIMEOUT = 1)
)
)
)
)
-------------------------------------------------------------

配置共享连接：
1.允许服务器启动共享服务进程

altersystemsetshared_servers=5;

2.允许client端以共享方式连接服务器

altersystemsetshared_server_sessions=2000;

3.允许服务器启动调度器
SQL> select * from v$dispatcher;

altersystemsetdispatchers='(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=56352)(DISPATCHERS=5))';

vi $ORACLE_HOME/network/admin/tnsnames.ora
-------------------------------------------------------------
test =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.254.250)(PORT=7788))
(CONNECT_DATA=
#(server = DEDICATED)
#(server = SHARED)
(SERVICE_NAME = primary)
)
)
-------------------------------------------------------------

数据库链接：可以在sql语句级别建立网络连接同时获得多个数据库中的数据

SQL>grantcreatedatabaselinktoscott;

使用本地户名口令到远程进行安全审核：动态安全审核

SQL>createdatabaselinklink_254_scottusing'250';

静态安全审核
SQL> create database link link_254_scott
connect to scott identified by lion
using '250';

共有数据库链接：
SQL> conn / as sysdba
SQL> create public database link link_254_scott using '250';

配置oracle的网络连接：

配置服务器端：需要配置监听程序 $ORACLE_HOME/bin/tnslsnr

查看监听是否工作：
使用主机命令

ps-ef|greptnslsnr

使用监听控制台lsnrctl statuslsnrctl stop使用无配置启动监听：rm -f $ORACLE_HOME/network/admin/listener.oralsnrctl startListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle0.example.com)(PORT=1521)))Services Summary...Service "orcl.example.com" has 1 instance(s). Instance "orcl",status READY,has 1 handler(s) for this service...在客户端不配置网络，使用简单连接访问服务器：sqlplus scott/tiger@oracle0.example.com:1521/orcl.example.comSQL> select sid,serial#,username,machine from v$session where username='SCOTT'; SID SERIAL# USERNAME---------- ---------- ------------------------------MACHINE---------------------------------------------------------------- 142 119 SCOTToracle31.example.com使用sys用户连接服务器：sqlplus sys/uplooking@oracle0.example.com:1521/orcl.example.com as sysdba增加客户端的网络配置（服务命名）sqlplus scott/tiger@teachvi $ORACLE_HOME/networ/admin/tnsnames.ora--------------------------------------------------------------------TEACH = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com) ) )--------------------------------------------------------------------打开普通用户远程操作系统审核：alter system set remote_os_authent=true scope=spfile;服务器端增加监听的配置：vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora--------------------------------------------------------------------LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1521)) )--------------------------------------------------------------------修改监听的端口号：vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora--------------------------------------------------------------------LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1111)) )--------------------------------------------------------------------vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora--------------------------------------------------------------------LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1111))--------------------------------------------------------------------监听的静态注册：vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora--------------------------------------------------------------------#数据库服务SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl.example.com) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl) ) )#监听的位置LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1111)) )--------------------------------------------------------------------将实例注册到远程监听程序：alter system set remote_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle31.example.com)(PORT=1521)))';vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora--------------------------------------------------------------------LISTENER_ALONG=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle31.example.com)(PORT=1521)))--------------------------------------------------------------------alter system set remote_listener='LISTENER_ALONG';172.25.0.10 --> pmon --> remote_listener --> tnsnames.ora --> ip : 1521客户端故障转移：vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora--------------------------------------------------------------------TEACH = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com) (FAILOVER_MODE = (TYPE = select) (METHOD = basic) (RETRIES = 180) (DELAY = 5) ) ) )--------------------------------------------------------------------select USERNAME,FAILED_OVER from v$session where username='SCOTT';共享服务器：网络连接有两种模式,专用和共享，指的是user process和server process的关系shared_servers --> 允许启动的共享服务进程的数量shared_server_sessions --> 允许多少个用户进程连接调度器dispatchers --> 启动调度器允许服务器启动5个共享的服务进程alter system set shared_servers=5；允许服务器启动5个调度器：select * from v$dispatcher;alter system set dispatchers='(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.0.10)(PORT=46993)(DISPATCHERS=5))';允许5个user process连接调度器alter system set shared_server_sessions=5;select sid,server from v$session where username='SCOTT';SID USERNAME SERVER--- ------------------------------ ---------12 SCOTT NONE16 SCOTT DEDICATED数据库链接：conn / as sysdbagrant create database link to scott;create database link link_teach using '服务命名';select * from "172.25.0.10"@link_teach;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!