数据库服务(Database Services)
数据库服务是表示一个或多个数据库实例的命名方式。服务能让你组合数据库工作负载并将一个特定的工作请求分发到一个合适的实例。一个数据库服务代表了一个单独的数据库。这个数据库可以是单实例数据库或者RAC数据库。一个全局数据库服务通过数据复制的多数据库同步机制来提供服务。
数据库服务将单数据库的工作负载分成多个互不相交的工作组。每个数据库服务使用常见的属性,服务级别阈值与优先级来代表一个工作负载。分组是基于工作属性的,它可能包含被使用的应用功能,执行应用功能的优先级,被管理的job类型或者应用功能中使用的数据范围或job类型。
数据库连接请求可以包含一个数据库服务名。因此中间层应用与C/S应用可以通过在TNS连接数据中指定数据库服务作为连接的一部分来使用服务。如果没有包含数据为服务名并且Net服务文件listener.ora指定了一个缺省数据库服务,那么连接将会使用这个缺省的数据库服务。
数据库服务可以对单个数据库配置工作负载,管理,启用与禁用,并且可以作为单个实体来检测工作负载。可以使用标准工具比如DBCA,NETCA和Cloud Control来进行操作。Cloud Control支持查看与操作服务。
在RAC环境中,数据库服务可以跨一个或多个实例并且基于事务性能来达到工作负载平衡。这种功能提供了无人值守的恢复,回滚与完全的位置透明。RAC也能让你使用Cloud Control,DBCA与SRVCLT来管理多个数据库服务功能。
数据库服务描述了应用程序,应用程序功能和功能服务或数据依赖服务的数据范围。功能服务通常映射到工作负载。会话使用特定功能被分组在一起。相反,数据领带根据数据键值将会话路由到数据服务。工作请求映射会在应用程序服务与TP监控的对象关系映射层中出现。例如,在RAC中,因为数据库是共享的,这些范围可能基于需要动态的完成。
另外数据库服务可以通过应用程序来使用,Oracle数据库也支持两种内部数据库服务:SYS$BACKGROUND只能由后台进程使用,SYS$USERS是用户会话的缺省数据库服务,它不与服务相关联。
使用数据库服务请求不需要改变应用等程序代码。客户端可以连接到一个命名的数据服务进行操作。服务端,比如Oracle调度,并行执行与Oracle数据库高级队列,将数据库服务名设置为工作负载定义的一部分。使用数据库服务执行的工作请求继承了服务的性能阈值与作为服务一部分的测量。
数据库服务与性能
数据库服务在性能调整中也提供了一种额外的维度。在大多数系统中所有会话都是匿名与共享的,可以使用”服务与SQL”调整来替代”会话与SQL”调整。使用数据库服务,工作负载可见且可以被检测。通过应用程序的资源消耗与等待属性来实现。另外,指定到数据库服务的资源当负载增加或减少时可以扩展。动态资源分配对于满足你出现的需求是最具成本效益的解决方案。例如,数据库服务会自动被
检测并与服务级别阈值进行比较。性能问题会报告给Cloun Control,并执行自动或调度解决方案。
使用数据库服务的数据库功能
有些Oracle数据库功能支持数据库服务。AWR管理服务的性能。AWR记录了数据库服务的性能,包含执行时间,等待类型与服务的资源消耗。当数据库服务响应时间超过了阈值AWR会发出警告。动态性能视图使用一小时历史数据来报告当前服务的性能度量。每个数据库服务对于响应时间与CPU消耗都有服务质量阈值。
数据库资源客理器可以将数据库服务映射到用户组。因此,可以自动管理数据库服务的优先级。可以使用用户组来定义相对优先级或资源消耗。
可以为数据库服务指定一个编辑属性。编辑可以使数据库中的相同对象有两个或多个版本。当你对数据库服务指定编辑属性时,所有后续的连接将使用这个编辑属性来初始化会话。
对数据库服务指定一个编辑属性可以更容易的管理资源使用。例如,使用编辑属性的数据库服务可以被置于RAC环境中的单独实例中,数据库资源管理器通过使用不同编辑属性的相关数据库服务所关联的资源计划来管理资源的使用。
对于Oracle调度,可以选择在创建job类型时指定数据库服务。在执行时间,job被指派到job类型,并且job类型可以使用数据库服务来运行。指定job类型的数据库服务可以确保通过job调度的执行被工作负载管理所识别与执行调整。
对于并行查询与并行DML,查询协调者连接到数据库服务就像其它客户端连接数据一样。对于执行时间并行查询进程继承数据库服务。在查询执行结束后,并行执行进程会归还给缺省的数据库服务。
创建数据库服务
依赖于数据库的配置有几种创建数据库服务的方式
1.如果是单实例数据库且由Oracle Restart管理,可以使用srvctl工具来创建数据库服务
srvctl add service -db db_unique_name -service service_name
[oracle@oracle12c admin]$ srvctl add service -db jycs -service jycs_service
检查服务状态
[oracle@oracle12c admin]$ srvctl status service -db jycs Service jycs_service is not running.
查看服务配置信息
[oracle@oracle12c admin]$ srvctl config service -db jycs Service name: jycs_service Cardinality: SINGLETON Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Pluggable database name: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled
启动服务
[oracle@oracle12c admin]$ srvctl start service -db jycs -service jycs_service
查看监听是否注册了服务
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 11:56:23 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 08:41:25 Uptime 0 days 3 hr. 14 min. 58 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service...
可以看到jycs_service服务已经被监听注册,在客户端配置tns并验证通过服务jycs_service能否登录数据库
C:\Users\Administrator>tnsping 12c_jycs_service TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 05-5月 - 2016 13:21:38 Copyright (c) 1997, 2013, Oracle. All rights reserved. 已使用的参数文件: D:\oracle\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jycs_service))) OK (20 毫秒)
C:\Users\Administrator>sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on 星期四 5月 5 14:44:06 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn sys/system@12c_jycs_service as sysdba 已连接。 SQL>
从执行命令的结果来看通过服务jycs_service可以登录数据库
2.如果是单实例且没有使用Oracle Restart来管理数据库,可以通过以下一种方式来创建数据库服务
SQL> show parameter service_names NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string jycs
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 14:59:06 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 1 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service...
增加一个名为jycs_service_2的服务名
SQL> alter system set service_names='jycs,jycs_service_2' scope=both; System altered.
检查监听是否注册了服务jycs_service_2
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:02:08 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 4 min. 15 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1/db/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
从这里可以看到jycs_service_2服务已被监听所注册,但是通过这种方式创建服务后,原来PDB的服务从监听中消失了,这可能是12.1.0.2的bug(猜测),如是重启数据库就可以解决这个问题。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 377487464 bytes Database Buffers 687865856 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:02:58 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 5 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
3.执行dbms_service.create_service过程来创建数据库服务名
SQL>dbms_service.create_service(service_name => 'jycs_service_3',network_name => 'jycs_service_3');
执行dbms_service.create_service过程来创建数据库服务名jycs_service_3后,在v$services视图中是没有该服务的行记录,但在dba_services视图中有该服务的行记录,需要调整数据库参数service_names,并重启。
SQL> select * from v$services; SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH GOAL DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTCOME RETENTION_TIME REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY GLOBAL PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME CON_ID ---------- -------------------------- ---------- --------------- ------------- ------------------ ------------ --- ------------------ -------- -------------- -------------- ------------------------- ------------------------------ ------ ------------ ------------------------ -------------- ---------- 0 jycsp10 2786476993 jycsp10 0 NONE N NO SHORT NO 86400 300 NO JYCSP10 12 0 jycsp9 3094752551 jycsp9 0 NONE N NO SHORT NO 86400 300 NO JYCSP9 11 0 jycsp8 2804702749 jycsp8 0 NONE N NO SHORT NO 86400 300 NO JYCSP8 10 0 jycsp7 651053443 jycsp7 0 NONE N NO SHORT NO 86400 300 NO JYCSP7 9 0 jycsp6 2502944067 jycsp6 0 NONE N NO SHORT NO 86400 300 NO JYCSP6 8 0 jycsp5 1822500990 jycsp5 0 NONE N NO SHORT NO 86400 300 NO JYCSP5 7 0 jycsp4 1917126355 jycsp4 0 NONE N NO SHORT NO 86400 300 NO JYCSP4 6 0 jycsp3 2193443928 jycsp3 0 NONE N NO SHORT NO 86400 300 NO JYCSP3 5 0 jycsp2 3609153374 jycsp2 0 NONE N NO SHORT NO 86400 300 NO JYCSP2 4 0 jycsp1 1271175711 jycsp1 0 NONE N NO SHORT NO 86400 300 NO JYCSP1 3 3 jycs_service 1423956612 jycs_service 2016/5/5 11:5 668160606 NONE N NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 1 7 jycs_service_2 2320947470 jycs_service_2 2016/5/5 15:0 4274618966 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1 5 jycsXDB 1180545090 jycsXDB 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1 6 jycs 1105513663 jycs 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1 1 SYS$BACKGROUND 165959219 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1 2 SYS$USERS 3427055676 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1 16 rows selected
SQL> select * from dba_services; SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL EDITION COMMIT_OUTCOME RETENTION_TIMEOUT REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY GLOBAL_SERVICE PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME GSM_FLAGS ---------- -------------------------- ---------- --------------- ------------- ------------------ ------------------ ---------------- ---------------- -------------- --------------- --------------- ------------ --- ------- ------------------- -------- ----------- -------------- ----------------- ------------------------- ----------------------------- -------------- ----------- ------------------------- -------------- ---------- 1 SYS$BACKGROUND 165959219 2014/7/7 5:39 977152970 N NO NO LONG NO NO CDB$ROOT 2 SYS$USERS 3427055676 2014/7/7 5:39 977152970 N NO NO LONG NO NO CDB$ROOT 3 jycs_service 1423956612 jycs_service 2016/5/5 11:5 668160606 0 0 NONE N NO NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 0 4 jycs_service1 3627910471 jycs_service1 2016/5/5 14:5 153848850 N NO NO LONG NO NO CDB$ROOT 5 jycsXDB 1180545090 jycsXDB 2016/3/31 20: 3827618340 N NO NO LONG NO NO CDB$ROOT 6 jycs 1105513663 jycs 2016/3/31 20: 3827618340 N NO NO LONG NO NO CDB$ROOT 7 jycs_service_2 2320947470 jycs_service_2 2016/5/5 15:0 4274618966 N NO NO LONG NO NO CDB$ROOT 8 jycs_service_3 1197864246 jycs_service_3 2016/5/5 15:0 2019662977 NONE N NO NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 0 8 rows selected
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:18:05 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 20 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully
设置参数service_names并重启数据库
SQL> alter system set service_names='jycs,jycs_service_2,jycs_service_3' scope=both; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 377487464 bytes Database Buffers 687865856 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
SQL> select * from v$services; SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH GOAL DTP AQ_HA_NOTIFICATION CLB_GOAL COMMIT_OUTCOME RETENTION_TIME REPLAY_INITIATION_TIMEOUT SESSION_STATE_CONSISTENCY GLOBAL PDB SQL_TRANSLATION_PROFILE MAX_LAG_TIME CON_ID ---------- ------------------- ---------- --------------- ------------- ------------------ ------------ --- ------------------ -------- -------------- -------------- ------------------------- ------------------------------ ------ ------------- ------------------------- ------------- ---------- 3 jycs_service 1423956612 jycs_service 2016/5/5 11:5 668160606 NONE N NO LONG NO 86400 300 DYNAMIC NO CDB$ROOT ANY 1 0 jycsp10 2786476993 jycsp10 0 NONE N NO SHORT NO 86400 300 NO JYCSP10 12 0 jycsp9 3094752551 jycsp9 0 NONE N NO SHORT NO 86400 300 NO JYCSP9 11 0 jycsp8 2804702749 jycsp8 0 NONE N NO SHORT NO 86400 300 NO JYCSP8 10 0 jycsp7 651053443 jycsp7 0 NONE N NO SHORT NO 86400 300 NO JYCSP7 9 0 jycsp6 2502944067 jycsp6 0 NONE N NO SHORT NO 86400 300 NO JYCSP6 8 0 jycsp5 1822500990 jycsp5 0 NONE N NO SHORT NO 86400 300 NO JYCSP5 7 0 jycsp4 1917126355 jycsp4 0 NONE N NO SHORT NO 86400 300 NO JYCSP4 6 0 jycsp3 2193443928 jycsp3 0 NONE N NO SHORT NO 86400 300 NO JYCSP3 5 0 jycsp2 3609153374 jycsp2 0 NONE N NO SHORT NO 86400 300 NO JYCSP2 4 0 jycsp1 1271175711 jycsp1 0 NONE N NO SHORT NO 86400 300 NO JYCSP1 3 8 jycs_service_3 1197864246 jycs_service_3 2016/5/5 15:0 2019662977 NONE N NO LONG NO 86400 300 NO CDB$ROOT ANY 1 7 jycs_service_2 2320947470 jycs_service_2 2016/5/5 15:0 4274618966 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1 5 jycsXDB 1180545090 jycsXDB 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1 6 jycs 1105513663 jycs 2016/3/31 20: 3827618340 NONE N NO LONG NO 86400 300 NO CDB$ROOT 1 1 SYS$BACKGROUND 165959219 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1 2 SYS$USERS 3427055676 2014/7/7 5:39 977152970 NONE N NO SHORT NO 86400 300 NO CDB$ROOT 1
查看监听是否注册了jycs_service_3,可以看到在设置service_names参数并重启数据库后监听注册了该服务
[grid@oracle12c ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2016 15:21:10 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 05-MAY-2016 14:57:53 Uptime 0 days 0 hr. 23 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/oracle12c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/jycs/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "jycs" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsXDB" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycs_service_3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp1" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp10" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp2" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp3" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp4" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp5" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp6" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp7" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp8" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... Service "jycsp9" has 1 instance(s). Instance "jycs", status READY, has 1 handler(s) for this service... The command completed successfully