关于SERVICE的问题

武汉-SSH

怎么在数据库中查一个服务的preferred instances ，available instances？
dbms_service.create_service 时，为什么不能选择 preferred instances ，available instances ？那么SRVCTL 又是在哪里标记 preferred instances ，available instances 的呢？

[ 本帖最后由 武汉-SSH 于 2012-2-29 10:34 编辑 ]

Maclean Liu(刘相兵

ODM finding:

The DBMS_SERVICE package supports the management of services in the database for the purposes of workload measurement, management, prioritization, and distributed transaction management. This package allows the creation, deletion, starting, and stopping of services in both RAC and a single instance. Additionally, it provides the ability to disconnect all sessions that connect to the instance with a service name when RAC removes that service name from the instance. Although the preferred method to create a service in a RAC environment is to use the DBCA, SRVCTL, or Enterprise Manager, you can use the DBMS_SERVICE.CREATE_SERVICE procedure to create a service in a single-instance environment. This is because the DBMS_SERVICE package is not integrated with Oracle Clusterware to define preferred and available instances for the service.
However, you can use the DBMS_SERVICE.MODIFY_SERVICE procedure to modify some of the service’s attributes in a RAC environment that cannot be modified using either the DBCA or Enterprise Manager—for example, the FAILOVER_RETRIES parameter.
The example in the slide shows you how to use DBMS_SERVICE.MODIFY_SERVICE to set the Load Balancing Advisory goal for SELF-SERVICE. Refer to the section “Service Attributes” in this lesson for more information about these attributes.
Note: For more information about the DBMS_SERVICE package, refer to the PL/SQL Packages and Types Reference.




you can use the DBMS_SERVICE.CREATE_SERVICE procedure to create a service in a single-instance environment. This is because the DBMS_SERVICE package is not integrated with Oracle Clusterware to define preferred and available instances for the service.

武汉-SSH

ML的资料库索引真牛啊！我记得RAC的课程里讲过，。。。。。。。。。

第一个问题和第三个问题有解？

Maclean Liu(刘相兵

For Question 3:
[grid@vrh2 ~]$ srvctl  add service -h

Adds a service configuration to the Oracle Clusterware.

Usage: srvctl add service -d <db_unique_name> -s <service_name> {-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}] | -g <pool_name> [-c {UNIFORM | SINGLETON}] } [-k   <net_num>] [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y {AUTOMATIC | MANUAL}] [-q {TRUE|FALSE}] [-x {TRUE|FALSE}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z <failover_retries>] [-w <failover_delay>] [-t <edition>] [-f]
    -d <db_unique_name>      Unique name for the database
    -s <service>             Service name
    -r "<preferred_list>"    Comma separated list of preferred instances
    -a "<available_list>"    Comma separated list of available instances
    -g <pool_name>           Server pool name
    -c {UNIFORM | SINGLETON} Service runs on every active server in the server pool hosting this service (UNIFORM) or just one server (SINGLETON)
    -k <net_num>             network number (default number is 1)
    -P {NONE | BASIC | PRECONNECT}        TAF policy specification
    -l <role>                Role of the service (primary, physical_standby, logical_standby, snapshot_standby)
    -y <policy>              Management policy for the service (AUTOMATIC or MANUAL)
    -e <Failover type>       Failover type (NONE, SESSION, or SELECT)
    -m <Failover method>     Failover method (NONE or BASIC)
    -w <integer>             Failover delay
    -z <integer>             Failover retries
    -t <edition>             Edition (or "" for empty edition value)
    -j <clb_goal>  Connection Load Balancing Goal (SHORT or LONG). Default is LONG.
    -B <Runtime Load Balancing Goal>     Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)
    -x <Distributed Transaction Processing>  Distributed Transaction Processing (TRUE or FALSE)
    -q <AQ HA notifications> AQ HA notifications (TRUE or FALSE)
Usage: srvctl add service -d <db_unique_name> -s <service_name> -u {-r "<new_pref_inst>" | -a "<new_avail_inst>"} [-f]
    -d <db_unique_name>      Unique name for the database
    -s <service>             Service name
    -u                       Add a new instance to service configuration
    -r <new_pref_inst>       Name of new preferred instance
    -a <new_avail_inst>      Name of new available instance
    -f                       Force the add operation even though a listener is not configured for a network
    -h                       Print usage
       
    -r "<preferred_list>"    Comma separated list of preferred instances
    -a "<available_list>"    Comma separated list of available instances       
       
       
       
       
       
       
[oracle@vrh2 ~]$  srvctl add service -d VPROD -s maclean_pres -r "VPROD2"  -a "VPROD1"


[oracle@vrh2 ~]$ su - grid
Password:
[grid@vrh2 ~]$ ocrdump ocr


[SYSTEM.CRSD.RESOURCES.ora!vprod!maclean_pre!svc]
UNDEF :
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

[SYSTEM.CRSD.RESOURCES.ora!vprod!maclean_pre!svc.CONFIG]
ORATEXT : ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=~ACTIVE_PLACEMENT=1~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_S
UFFIX%~AGENT_PARAMETERS=~AQ_HA_NOTIFICATION=0~AUTO_START=restore~BASE_TYPE=ora.cluster_resource.type~CARDINALITY=1~CHECK_INTERVAL=600~CHECK_TIMEOUT=30~CLB_GOAL=LONG~DE
FAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=service) PROPERTY(SERVICE_NAME=%GEN_SERVICE_NAME%) PROPERTY(DB_UNIQUE_NAME=CONCAT(PARSE(%NAME%, ., 2), STAT(ora.vprod.db, USR_OR
A_DOMAIN), .)) ELEMENT(INSTANCE_NAME=STAT(ora.vprod.db, GEN_USR_ORA_INST_NAME))~DEGREE=1~DESCRIPTION=Oracle Service resource~DTP=0~EDITION=~ENABLED=1~FAILOVER_DELAY=0~
FAILOVER_METHOD=NONE~FAILOVER_RETRIES=0~FAILOVER_TYPE=NONE~FAILURE_INTERVAL=0~FAILURE_THRESHOLD=0~GEN_SERVICE_NAME=~HOSTING_MEMBERS=~LOAD=1~LOGGING_LEVEL=1~MANAGEMENT_
POLICY=AUTOMATIC~NAME=ora.vprod.maclean_pre.svc~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~PLACEMENT=restricted~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEM
PTS=0~RLB_GOAL=NONE~ROLE=PRIMARY~SCRIPT_TIMEOUT=60~SERVER_POOLS=ora.VPROD_maclean_pre~SERVICE_NAME=maclean_pre~START_DEPENDENCIES=hard(ora.vprod.db,type:ora.cluster_vi
p_net1.type) weak(type:ora.listener.type) pullup(type:ora.cluster_vip_net1.type) pullup:always(ora.vprod.db)~START_TIMEOUT=600~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES
=hard(intermediate:ora.vprod.db,type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=600~TAF_POLICY=NONE~TYPE=ora.service.type~TYPE_ACL=owner:oracle:rwx,pgrp:oinstall:rwx,othe
r::r--~TYPE_NAME=ora.service.type~TYPE_VERSION=2.2~UPTIME_THRESHOLD=1h~USR_ORA_DISCONNECT=false~USR_ORA_ENV=~USR_ORA_FLAGS=~USR_ORA_OPEN_MODE=~USR_ORA_OPI=false~USR_OR
A_STOP_MODE=~VERSION=11.2.0.3.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

[SYSTEM.CRSD.RESOURCES.ora!vprod!maclean_pre!svc.INTERNAL]
ORATEXT : CARDINALITY_ID=0~CARDINALITY_ID@CARDINALITYID(1)=1~CREATION_SEED=33546~DEGREE_ID=0~DEGREE_ID@CARDINALITYID(1)=1~ID=ora.vprod.maclean_pre.svc~ID@CARDINALITYID
(1)=ora.vprod.maclean_pre.svc 1 1~LAST_SERVER=~LAST_SERVER@CARDINALITYID(1)=~TARGET=8~TARGET@CARDINALITYID(1)=8~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

[SYSTEM.CRSD.RESOURCES.ora!vprod!maclean_pres!svc]
UNDEF :
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

[SYSTEM.CRSD.RESOURCES.ora!vprod!maclean_pres!svc.CONFIG]
ORATEXT : ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--~ACTION_FAILURE_TEMPLATE=~ACTION_SCRIPT=~ACTIVE_PLACEMENT=1~AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_S
UFFIX%~AGENT_PARAMETERS=~AQ_HA_NOTIFICATION=0~AUTO_START=restore~BASE_TYPE=ora.cluster_resource.type~CARDINALITY=1~CHECK_INTERVAL=600~CHECK_TIMEOUT=30~CLB_GOAL=LONG~DE
FAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=service) PROPERTY(SERVICE_NAME=%GEN_SERVICE_NAME%) PROPERTY(DB_UNIQUE_NAME=CONCAT(PARSE(%NAME%, ., 2), STAT(ora.vprod.db, USR_OR
A_DOMAIN), .)) ELEMENT(INSTANCE_NAME=STAT(ora.vprod.db, GEN_USR_ORA_INST_NAME))~DEGREE=1~DESCRIPTION=Oracle Service resource~DTP=0~EDITION=~ENABLED=1~FAILOVER_DELAY=0~
FAILOVER_METHOD=NONE~FAILOVER_RETRIES=0~FAILOVER_TYPE=NONE~FAILURE_INTERVAL=0~FAILURE_THRESHOLD=0~GEN_SERVICE_NAME=~HOSTING_MEMBERS=~LOAD=1~LOGGING_LEVEL=1~MANAGEMENT_
POLICY=AUTOMATIC~NAME=ora.vprod.maclean_pres.svc~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~PLACEMENT=restricted~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTE
MPTS=0~RLB_GOAL=NONE~ROLE=PRIMARY~SCRIPT_TIMEOUT=60~SERVER_POOLS=ora.VPROD_maclean_pres~SERVICE_NAME=maclean_pres~START_DEPENDENCIES=hard(ora.vprod.db,type:ora.cluster
_vip_net1.type) weak(type:ora.listener.type) pullup(type:ora.cluster_vip_net1.type) pullup:always(ora.vprod.db)~START_TIMEOUT=600~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENC
IES=hard(intermediate:ora.vprod.db,type:ora.cluster_vip_net1.type)~STOP_TIMEOUT=600~TAF_POLICY=NONE~TYPE=ora.service.type~TYPE_ACL=owner:oracle:rwx,pgrp:oinstall:rwx,o
ther::r--~TYPE_NAME=ora.service.type~TYPE_VERSION=2.2~UPTIME_THRESHOLD=1h~USR_ORA_DISCONNECT=false~USR_ORA_ENV=~USR_ORA_FLAGS=~USR_ORA_OPEN_MODE=~USR_ORA_OPI=false~USR
_ORA_STOP_MODE=~VERSION=11.2.0.3.0~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}

[SYSTEM.CRSD.RESOURCES.ora!vprod!maclean_pres!svc.INTERNAL]
ORATEXT : CARDINALITY_ID=0~CARDINALITY_ID@CARDINALITYID(1)=1~CREATION_SEED=33547~DEGREE_ID=0~DEGREE_ID@CARDINALITYID(1)=1~ID=ora.vprod.maclean_pres.svc~ID@CARDINALITYI
D(1)=ora.vprod.maclean_pres.svc 1 1~LAST_SERVER=~LAST_SERVER@CARDINALITYID(1)=~TARGET=8~TARGET@CARDINALITYID(1)=8~
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_NONE, OTHER_PERMISSION : PROCR_NONE, USER_NAME : root, GROUP_NAME : root}
............

Maclean Liu(刘相兵

For Question1:

preferred instances ，available instances 这2个属性似乎并不记录在DB中， 包括 dba_services、v$service、 v$active_service均不包括这2个字段。

as maclean understand ,因为dbms_service这个package is not integrated with Oracle Clusterware 。 所以从数据字典或动态视图的角度 也没有为这2个属性考虑， 所以无从 从DB角度得知以上属性。

需要通过Clusterware的命令获取。