Oracle Database Service

数据库服务(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

发表评论

电子邮件地址不会被公开。