Sunday, July 8

Using SERVICE_NAMES in Oracle

The use of "SERVICE_NAMES" in Oracle is quite an old and probably well known feature but perhaps not everyone is familiar with it yet.
Got asked today about a recovery scenario where the administrator had a failed instance (broken data files, no logs, no backups, just a nightly exp), a new database was created with 'dbca', but with a new name to test importing the exp file.
All worked fine, but there was a problem with the database name. The application had the service name set in a number of config files and there was also a number of ETL scripts with service names hardcoded. The thinking at the time was to delete the old instance, remove all traces of it (oratab etc.) and then create it *again* with the same name.
Now hold on here, we have tested the imp in a new database, all is fine and all we want to do is allow connections to the old database instance name?
That's pretty much a one-liner, not a new database.
We can simply add the new name we want to "listen on" to the SERVICE_NAMES parameter.
Easy peasy.

Ok, here is what we should do. Quite a long example for something simple.
But hey, just want to make it clear.
oracle@htpc:admin$ lsnrctl status
-- What's the current db_name and service_names?
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUL-2007 18:31:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 08-JUL-2007 18:23:39
Uptime 0 days 0 hr. 7 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/10g/network/admin/listener.ora
Listener Log File /u01/oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htpc)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "peggy" has 2 instance(s).
Instance "peggy", status UNKNOWN, has 1 handler(s) for this service...
Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggyXDB" has 1 instance(s).
Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggy_XPT" has 1 instance(s).
Instance "peggy", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@htpc:admin$ rsqlplus hlinden/hlinden as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:24 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string peggy
SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string peggy
SQL>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- What can we connect to?
oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/peggy

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:53 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/dog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:58 2007

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

-- Ouch, that's the one we want!

oracle@htpc:admin$ rsqlplus hlinden/hlinden as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:32:01 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Here is the 'one-liner'
SQL> alter system set service_names='peggy,dog' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 2022600 bytes
Variable Size 167772984 bytes
Database Buffers 423624704 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.
SQL>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Let's see what changed. What can we connect to now?
oracle@htpc:admin$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUL-2007 18:33:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 08-JUL-2007 18:23:39
Uptime 0 days 0 hr. 10 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/10g/network/admin/listener.ora
Listener Log File /u01/oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htpc)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dog" has 1 instance(s).
Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggy" has 2 instance(s).
Instance "peggy", status UNKNOWN, has 1 handler(s) for this service...
Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggyXDB" has 1 instance(s).
Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggy_XPT" has 1 instance(s).
Instance "peggy", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/dog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:34:18 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- It works, but where are we?

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
------------------------------------------------------------------------------------------------------------------------
dog

SQL> select sys_context('userenv','DB_NAME') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
------------------------------------------------------------------------------------------------------------------------
peggy

7 comments:

HFAJ said...

Yes, this is very useful. I wonder if we can apply the same approach when using ORACLE_SID.

潇洒光头、 said...

●●百度类●●:
代孕 淘宝刷信用
北京发票 代开发票
餐饮发票 住宿发票
广告发票 对讲机
传世私服 传奇世界私服
新开传世私服 传奇私服
天龙八部私服 天龙私服
苏州办证
手机窃听器 手机窃听器
手机窃听器
手机监听器
手机监听器
手机窃听器
代写论文 代写论文
北京办证 办证
代孕 代孕网
代孕 代孕
代孕 试管婴儿
代写论文 代写论文
代写代发 论文代写 dhl

●●Google类●●:
modern abstract art sofa manufacturer
净水器 开水器 净水机 净水 软水机 软水 直饮机 家用净水 家用净水器 家用净水机 中央净水 中央净水器 水家装 水家电 水卫士 混合机
过滤机 DHL快递 俄罗斯签证
保险箱 法兰 法兰标准
polycarbonate sheet 回流焊 波峰焊
免烧砖机 注册上海公司 儿童摄影
牛皮癣 皮肤病 制氮机
食堂售餐机 校园一卡通
学校一卡通 ic卡售饭机
食堂售饭机 深圳一卡通
广东售饭机 机电设备安装
北京发票 代开发票
餐饮发票 住宿发票
广告发票
网络电话 免费网络电话
假发 补发
织发 植发
压滤机 板框压滤机
蒸馏水机 纯蒸气发生器
上海搬家公司 上海搬场公司
大众搬家 大众搬场
张家界旅游 香港旅游
深圳旅行社 打包机
收缩机 对讲机 电源模块
血管栓塞剂
售饭机 水控机 水控器
萎缩性胃炎 neoprene laptop bags
SEO优化
SEO优化 计量泵
胃炎 胃病
冷水机 冰水机
工业冷水机
北京特价机票 北京打折计票 北京国际机票
北京机票预定 北京飞机票
北京订机票 北京机票查询 饮料机械
血糖仪 血糖仪
银杏 水培花卉 企业宣传片 空分设备
化工泵 离心机
电话交换机 程控交换机 集团电话 集装袋
混合机 混合机
混合机捏合机 捏合机
捏合机导热油炉 导热油炉
导热油炉 反应釜 反应釜
反应釜 spherical roller bearing
搬运车 搬运车 电动搬运车 油桶搬运车 堆高车 电动堆高车 半电动堆高车 堆垛车
高空作业平台车 电动叉车 平衡重叉车 前移叉车 电瓶叉车
韩国饰品批发 模块电源
X架 超薄灯箱> 易拉宝 展柜制作
代理服务器 游戏加速器 网络加速器
网通加速器 电信加速器 电信网通转换器
电信网通加速器 网通电信互转
网通电信互通 网络游戏加速器
美国VPN代理 美国独享VPN 美国独享IP
pvc ceiling panel Spherical roller bearings
SEO优化
安全鞋 劳保鞋 防砸鞋 电绝缘鞋 上海安全鞋 上海劳保鞋 江苏劳保鞋
服装软件 服装管理软件 进销存软件
进销存管理软件 服装管理系统 服装进销存软件
进销存系统 进销存管理系统 免费进销存软件
吉林中医 东北特产
打包机
阳痿 阴茎短小 阴茎增大
早泄 前列腺炎 阴茎增粗 阴茎延长
国际机票 上海国际机票
国际特价机票 国际打折机票
CRM 客户管理软件 客户关系管理
免费客户管理软件 客户管理软件下载 客户信息管理系统 销售管理系统 销售管理
CRM系统 CRM软件 客户关系管理系统
客户关系管理软件 客户管理 客户管理系统 营销管理系统 客户资源管理 销售管理软件 客户资料管理软件 客户资源管理软件
客户信息管理软件 客户资料管理 客户资源管理 客户信息管理 客户资料管理系统
客户资源管理系统 客户管理软件免费版
砂磨机 砂磨机
砂磨机 卧式砂磨机
卧式砂磨机 卧式砂磨机
三辊研磨机 三辊研磨机
三辊研磨机 混合机 混合机
混合机 锥形混合机 锥形混合机 锥形混合机 行星动力混合机 行星动力混合机 行星动力混合机 无重力混合机 无重力混合机 无重力混合机
干粉砂浆设备 干粉砂浆设备
干粉砂浆设备 捏合机 捏合机 捏合机 导热油炉 导热油炉 导热油炉 反应釜 反应釜 反应釜 搪玻璃反应釜 搪玻璃反应釜 搪玻璃反应釜
乳化机 涂料设备 干混砂浆设备 无重力混合机 胶体磨 涂料成套设备 双螺旋混合机
北京婚庆 北京婚庆公司
400电话
办证 呼吸机 制氧机
亚都 亚都加湿器 亚都净化器
亚都装修卫士
饰品批发 小饰品批发 韩国饰品 韩国饰品批发 premature ejaculation penis enlargement
破碎机 制砂机 球磨机 雷蒙磨 雷蒙磨粉机 鄂式破碎机 鄂式破碎机 免烧砖机 加气混凝土设备
反击式破碎机 选矿设备
安利产品 马来西亚留学
网站优化 网站推广
衬布
代写论文
代写论文
论文代写 代写论文
磁力泵
离心泵
化工泵
隔膜泵
螺杆泵
潜水泵
油泵
耐腐蚀泵
水泵
拖链 防护罩 排屑机 塑料拖链 钢铝拖链
化工离心泵
计量加油泵
自吸式离心泵
管道油泵
自吸式排污泵
潜水排污泵
自吸式磁力泵
耐高温磁力泵
不锈钢多级离心泵
多级离心泵
耐腐蚀自吸泵
自吸化工泵
玻璃钢液下泵
液下式排污泵
卧式离心清水泵
氟塑料磁力泵
磁力驱动循环泵
耐腐蚀污水泵
卧式化工离心泵
玻璃钢耐酸泵
防爆管道油泵
不锈钢多级泵
立式多级离心泵
塑料磁力泵
水泵厂
手摇油泵
上海水泵厂
上海水泵
离心泵厂家
热水泵
清水泵
气动隔膜泵
深圳装饰 深圳装饰公司 深圳装修公司
特价机票 打折机票 国际机票
机票
新风换气机 换气机 立式新风换气机 风机箱 新风系统 能量回收机
搅拌机 混合机 乳化机
分散机
毛刷 毛刷辊 工业毛刷 刷子 钢丝刷
涂层测厚仪 硬度计
兆欧表 激光测距仪
测振仪 转速表
温湿度计 风速仪
超声波测厚仪
粗糙度仪
噪音计 红外测温仪
万用表
硬度计 万用表
美容院 美容加盟
澳洲留学 澳大利亚留学
什么是法兰
电烤箱
酒店预定 北京酒店预定 北京酒店
离心机
nail equipment nail products nail product nail uv lamp nail uv lamp nail uv lamps uv nail lamp nail brush
nail file nail tool nail tip nail gel curing uv lamps lights
万用表 风速仪
红外测温仪 噪音计
苗木价格 苗木信息 标牌制作 深圳标牌 北京儿童摄影 防静电鞋 淘宝刷信誉
威海凤凰湖 威海海景房 大庆密封件
打标机 淘宝刷信誉 TESOL/TEFL国际英语教师证书 英语教师进修及培训 北京快递公司 北京国际快递

Anonymous said...

It is the goonzu gold which make me very happy these days, my brother says goonzu money is his favorite games gold he likes, he usually buy some goonzu online gold to start his game and most of the time he will win the buy goonzu gold back and give me some cheap goonzu gold to play the game.
I always heard something from my neighbor that he sometimes goes to the internet bar to play the game which will use him some gw gold,he usually can win a lot of GuildWars Gold,then he let his friends all have some Guild Wars Gold,his friends thank him very much for introducing them the GuildWars money,they usually cheap gw gold together.

aion china server kina said...

aion china, aion china gold,
aion cn goldaion chinese gold,
aion gold chinaaion gold chinese,
china aion goldchinese aion gold,
aion china kinaaion chinese kina,
aion kina chinachina aion kina,
aion china buybuy aion china,
aion chinese server goldaion cn server gold,
aion china server goldchina aion server gold,
chinese aion server goldaion chinese server gold,
aion cn server kinaaion china server kina,
china aion server kinachinese aion server kina

requiem online gold said...

Now do you worried about that in the game do not had enough second life linden to play the game, now you can not worried, my friend told me a website, in here you can buy a lot linden dollars and only spend a little money, do not hesitate, it was really, in here we had much secondlife money, we can sure that you will get the cheap linden, quick to come here to buy lindens.

Now do you worried about that in the game do not had enough requiem gold to play the game, now you can not worried, my friend told me a website, in here you can buy a lot requiem lant and only spend a little money, do not hesitate, it was really, in here we had much requiem money, we can sure that you will get the cheap requiem lant, quick to come here to buy requiem online gold.

Roxy said...

Costa rica tours, shopping online, costa rica fishing, jaco fishing charters, costa rica whale watching, costa rica dolphin watching, costa rica party boat, panama fishing, fishing costa rica los suenos, los suenos fishing charters, costa rica fishing los suenos, los suenos costa rica fishing, costa rica los suenos fishing, fishing costa rica los suenos, los suenos fishing charters, costa rica fishing los suenos, los suenos costa rica fishing charters, costa rica los suenos fishing, Los suenos fishing, Fishing costa rica los suenos, Costa Rica Rafting,
http://www.kingtours.com/.

Extreme News said...

What youre saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I also love the images you put in here. They fit so well with what youre trying to say. Im sure youll reach so many people with what youve got to say. chinese new year 2012 mid autumn festival 2011 hungry ghost festival 2011 Indonesia holiday holiday forum new year 2012