oracle的service name,Oracle中Service Name、Database name、Instance Name的区别

1,数据库名是Oracle数据库的内部标识,通常在安装完后不该该改变,数据库的不少物理存储目录都用到了数据库名。sql

2,Instance_Name,ORACLE_SID,数据库实例名,是数据库和操做系统交互时用到的名称。数据库

若是在一台机器上建立了多个数据库,经过Sqlplus想链接到其中的一个数据库,就须要指明ORACLE_SID:服务器

set ORACLE_SID=SIDNAME网络

sqlplus / as sysdba(这种连法只能在本机用,会根据ORACLE_SID链接到对应的实例)session

一般碰到的12560错误通常就是由于实例名被错误修改或者服务没有被启动。 oracle

Instance_Name则是数据库的一个参数.this

3,Service Name:服务名,若是数据库有域名则等同于Global DB Name、没有的话则等同于数据库名,这是由于数据库启动后会自动把数据库名注册到监听成为服务名,同时数据库参数service_names也会注册为服务名,因此一个数据库能够有多个服务名.spa

当在Oracle8i,9i,10g的客户端链接Oracle8i,9i,10g服务器的时候,主机字符串应该使用服务名。操作系统

4,Net Service Name:网络服务名,也有人叫TNS别名、网络链接串(connect string),在tnsnames.ora中配置的名称,以下面例子的DBTNS.

sqlplus sys/sys@orcl as sysdba(这种连法会根据tnsnames.ora中配置的网络服务名链接到本机或者远程的oracle,走的是网络通讯)

看看数据库参数设置:

SQL> show parameter instance_name

NAME                                 TYPE        VALUE

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

instance_name                        string      orcl

SQL> show parameter service_names

NAME                                 TYPE        VALUE

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

service_names                        string      orcla,orclb

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      orcl

service_names也能够经过alter system set service_names=orclA,orclB;修改成对应多个值。blog

listener启动时候根据listener.ora配置的信息静态注册可用的服务,同时数据库实例启动之后(PMON)会把

service_names,db_name的值动态的注册到Listener。

例如,若是service_names的值为orcla,orclb,db_name的值为orcl,在listener.ora里有以下配置:

(SID_DESC =

(GLOBAL_DBNAME = orclst)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(SID_NAME = orcl)

)

则在Tnsname.ora中能够做为SERVICE_NAME的值为orcla,orclb,orcl,orclst

经过lsnrct status 能够看到这些服务的信息: status READY的为动态注册的(由于是在实例启动以后才注册到listener的,因此状态为ready),status UNKNOWN的为静态注册的(由于是listener启动的时候根据配置启动的,这是实例是否启动是不知道的,因此为unknown)。

经过链接后看v$session中的service_name也能够判断用的服务名是静态注册的仍是动态注册的.SYS$USERS表示为静态的.

其中orclst为静态的注册,查看session能够看到以下

SQL> select service_name from v$session where sid=(select distinct(sid) from v$mystat);

SERVICE_NAME

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

SYS$USERS

其中经过orclsa为动态的注册,查看session能够看到以下

SQL> select service_name from v$session where sid=(select distinct(sid) from v$mystat);

SERVICE_NAME

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

orcla

Services Summary...

Service "ORCLA" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "ORCLB" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

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

Service "orcl" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orcl_XPT" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orclst" has 1 instance(s).

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

The command completed successfully

动态注册默认只能注册1521的listener,若是监听端口不是1521,须要:

1,在服务器端的Tnsname.ora中配置一个tns指明端口号,只有ADDRESS配置没有CONNECT_DATA配置项:

lclsn=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = PC-6753184)(PORT = 1522))

)

2,将数据库的参数 local_listener的值改成服务器端的Tnsname.ora配置的lclsn:

alter system set local_listener=lclsn;

Tnsname.ora中配置: 经过lsnrct status获得能够用的SERVICE_NAME ,如orcla,orclb,orcl...

DBTNS=

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = teldb)

)

)

上面例子中的SERVICE_NAME = teldb也能够换成SID=orcl.

一个数据库集群的例子:

DBCLUSTER=

(DESCRIPTION_LIST =

(LOAD_BALANCE = OFF)

(FAILOVER = ON)

(DESCRIPTION =

(ADDRESS_LIST =

(LOAD_BALANCE = ON)

(FAILOVER = ON)

(ADDRESS = (PROTOCOL = TCP)(HOST = rdl701d001-oravip.test.com)(PORT = 6191))

(ADDRESS = (PROTOCOL = TCP)(HOST = rdl701d002-oravip.test.com)(PORT = 6191))

(ADDRESS = (PROTOCOL = TCP)(HOST = rdl701d003-oravip.test.com)(PORT = 6191))

(ADDRESS = (PROTOCOL = TCP)(HOST = rdl701d004-oravip.test.com)(PORT = 6191))

(ADDRESS = (PROTOCOL = TCP)(HOST = rdl701d005-oravip.test.com)(PORT = 6191))

(ADDRESS = (PROTOCOL = TCP)(HOST = rdl701d006-oravip.test.com)(PORT = 6191))

)

(CONNECT_DATA =

(SERVICE_NAME = opofs_rd)

(FAILOVER_MODE =

(TYPE = SESSION)

(METHOD = BASIC)

(RETRIES = 120)

(DELAY = 5)

)

)

)

)

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值