Oracle Database 10g introduced an automatic workload management facility, called services. A service represents the workload of applications with common attributes, performance thresholds, and priorities. A single service can represent an application, multiple applications or a subset of a single application. A single service can be associated with one or more instances of an Oracle RAC database, and a single instance can support multiple services. Services provide the following benefits:
A single entity for managing applications that compete for the same resources
Allow each workload to be managed as a single unit
Hide the complexity of the cluster from the client
To manage workloads, you can define services that you assign to a particular application or to a subset of an application's operations. You can also use services to manage the workload for different types of work. For example, online users can use one service while batch processing can use a different service and reporting can use yet another service type.
Traditionally an Oracle database provided a single service and all users connected to the same service. A database will always have this default database service that is the database name. This service cannot be modified and will always allow you to connect to the database.
Note: Do not use the default database service for application workloads. Create at least one service as described in "Creating Services".
When a user or application connects to a database, Oracle recommends that you use a service for the connection.Oracle Database automatically creates one database service when the database is created. For many installations, this may be all you need. For more flexibility in the management of the workload using the database, Oracle Database enables you to create multiple services and specify which database instances offer the services.
A single entity for managing applications that compete for the same resources
Allow each workload to be managed as a single unit
Hide the complexity of the cluster from the client
To manage workloads, you can define services that you assign to a particular application or to a subset of an application's operations. You can also use services to manage the workload for different types of work. For example, online users can use one service while batch processing can use a different service and reporting can use yet another service type.
Traditionally an Oracle database provided a single service and all users connected to the same service. A database will always have this default database service that is the database name. This service cannot be modified and will always allow you to connect to the database.
Note: Do not use the default database service for application workloads. Create at least one service as described in "Creating Services".
When a user or application connects to a database, Oracle recommends that you use a service for the connection.Oracle Database automatically creates one database service when the database is created. For many installations, this may be all you need. For more flexibility in the management of the workload using the database, Oracle Database enables you to create multiple services and specify which database instances offer the services.
srvctl add service Syntax and Options
Oracle Real Application Clusters Administration and Deployment Guide 11g Release 2 (11.2)
- [oracle@node1 ~]$ 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
Use this example syntax to add the gl.example.com service to the my_rac database with AQ HA notifications enabled, a failover method of BASIC, a Connection Load Balancing Goal of LONG, a failover type of SELECT, and 180 failover retries with a delay of 5:
srvctl add service -d my_rac -s gl.example.com -q TRUE -m BASIC -e SELECT -z 180 -w 5 -j LONG
Use this example syntax to add a named service to a database with preferred instances in list one and available instances in list two, using preconnect failover for the available instances:
srvctl add service -d crm -s sales -r crm01,crm02 -a crm03 -P PRECONNECT
srvctl add service -d my_rac -s gl.example.com -q TRUE -m BASIC -e SELECT -z 180 -w 5 -j LONG
Use this example syntax to add a named service to a database with preferred instances in list one and available instances in list two, using preconnect failover for the available instances:
srvctl add service -d crm -s sales -r crm01,crm02 -a crm03 -P PRECONNECT
Add services to support the load balancing and TAF
Add a name for "ZWC" service, instance node1 as the first choice for instance node, instance node2 as standby node, TAF strategy use "PRECONNECT" preliminary connection, fault switching type to "SELECT" enable transaction support, switch delay set to 0, use the following order:
- [oracle@node1 ~]$ srvctl add service -d zhongwc -s ZWC -r 'zhongwc1' -a 'zhongwc2' -P PRECONNECT -e SELECT -x TRUE -w 0
- [oracle@node1 ~]$
- [oracle@node1 ~]$
- [oracle@node1 ~]$ srvctl config service -d zhongwc -s ZWC -a
- Warning:-a option has been deprecated and will be ignored.
- Service name: ZWC
- Service is enabled
- Server pool: zhongwc_ZWC
- Cardinality: 1
- Disconnect: false
- Service role: PRIMARY
- Management policy: AUTOMATIC
- DTP transaction: true
- AQ HA notifications: false
- Failover type: SELECT
- Failover method: NONE
- TAF failover retries: 0
- TAF failover delay: 0
- Connection Load Balancing Goal: LONG
- Runtime Load Balancing Goal: NONE
- TAF policy specification: PRECONNECT
- Edition:
- Preferred instances: zhongwc1
- Available instances: zhongwc2
- [oracle@node1 ~]$
- [oracle@node1 ~]$ srvctl modify service -d zhongwc -s ZWC -z 180 -w 5 -m BASIC
- [oracle@node1 ~]$ srvctl modify service -d zhongwc -s ZWC -B SERVICE_TIME
- [oracle@node1 ~]$ srvctl modify service -d zhongwc -s ZWC -q TRUE
- [oracle@node1 ~]$ srvctl modify service -d zhongwc -s ZWC -j SHORT
- [oracle@node1 ~]$
- [oracle@node1 ~]$
- [oracle@node1 ~]$ srvctl config service -d zhongwc -s ZWC
- Service name: ZWC
- Service is enabled
- Server pool: zhongwc_ZWC
- Cardinality: 1
- Disconnect: false
- Service role: PRIMARY
- Management policy: AUTOMATIC
- DTP transaction: true
- AQ HA notifications: true
- Failover type: SELECT
- Failover method: BASIC
- TAF failover retries: 180
- TAF failover delay: 5
- Connection Load Balancing Goal: SHORT
- Runtime Load Balancing Goal: SERVICE_TIME
- TAF policy specification: PRECONNECT
- Edition:
- Preferred instances: zhongwc1
- Available instances: zhongwc2
Start "ZWC" service
- [oracle@node1 ~]$ srvctl start service -d zhongwc -s ZWC
- [oracle@node1 ~]$ su - grid -c "crsctl stat res -t"
- Password:
- --------------------------------------------------------------------------------
- NAME TARGET STATE SERVER STATE_DETAILS
- --------------------------------------------------------------------------------
- Local Resources
- --------------------------------------------------------------------------------
- ora.CRS.dg
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.DATADG.dg
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.FRADG.dg
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.LISTENER.lsnr
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.asm
- ONLINE ONLINE node1 Started
- ONLINE ONLINE node2 Started
- ora.gsd
- OFFLINE OFFLINE node1
- OFFLINE OFFLINE node2
- ora.net1.network
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.ons
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- --------------------------------------------------------------------------------
- Cluster Resources
- --------------------------------------------------------------------------------
- ora.LISTENER_SCAN1.lsnr
- 1 ONLINE ONLINE node2
- ora.cvu
- 1 ONLINE ONLINE node1
- ora.node1.vip
- 1 ONLINE ONLINE node1
- ora.node2.vip
- 1 ONLINE ONLINE node2
- ora.oc4j
- 1 ONLINE ONLINE node1
- ora.scan1.vip
- 1 ONLINE ONLINE node2
- ora.zhongwc.db
- 1 ONLINE ONLINE node1 Open
- 2 ONLINE ONLINE node2 Open
- ora.zhongwc.zwc.svc
- 1 ONLINE ONLINE node1
- ora.zhongwc.zwc_preconnect.svc
- 1 ONLINE ONLINE node2
- [oracle@node1 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 6 16:12:08 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> show parameter service_name
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string ZWC
- [oracle@node2 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 6 16:12:33 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> show parameter service_name
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string zwc_preconnect
node1,node2 tnsnames
- [oracle@node2 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
- # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
- # Generated by Oracle configuration tools.
- ZHONGWC =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = node-cluster-scan)(PORT = 1521))
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = zhongwc)
- )
- )
- ZWC = (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = node-cluster-scan)(PORT = 1521))
- (LOAD_BALANCE = YES)
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = ZWC)
- (FAILOVER_MODE =
- (BACKUP = ZWC_PRECONNECT)
- (TYPE = SELECT)
- (METHOD = PRECONNECT)
- (RETRIES = 180)
- (DELAY = 5)
- )
- )
- )
- ZWC_PRECONNECT = (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = node-cluster-scan)(PORT = 1521))
- (LOAD_BALANCE = YES)
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = ZWC_PRECONNECT)
- (FAILOVER_MODE =
- (TYPE = SELECT)
- (METHOD = BASIC)
- (RETRIES = 180)
- (DELAY = 5)
- )
- )
- )
parameter interpretation
test
node1
- [oracle@node1 dbhome_1]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 7 09:23:31 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL>
- SQL>
- SQL>
- SQL> show parameter service_names
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string ZWC
node2
- [oracle@node2 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 7 09:23:38 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> show parameter service_na
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string zwc_preconnect
client
- C:\>sqlplus scott/tiger@node-cluster-scan/zwc
- SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 1月 7 09:25:12 2013
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- 连接到:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
- 会话已更改。
- SQL> set line 300
- SQL> col machine format a22
- SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;
- MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
- ---------------------- ------------- ---------- --- ----------
- DINA\PC-201204200916 SELECT BASIC NO 1
- node1 NONE NONE NO 43
abort node1 instance
- SQL> shutdown abort
- ORACLE instance shut down.
- [grid@node1 ~]$ crsctl stat res -t
- --------------------------------------------------------------------------------
- NAME TARGET STATE SERVER STATE_DETAILS
- --------------------------------------------------------------------------------
- Local Resources
- --------------------------------------------------------------------------------
- ora.CRS.dg
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.DATADG.dg
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.FRADG.dg
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.LISTENER.lsnr
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.asm
- ONLINE ONLINE node1 Started
- ONLINE ONLINE node2 Started
- ora.gsd
- OFFLINE OFFLINE node1
- OFFLINE OFFLINE node2
- ora.net1.network
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- ora.ons
- ONLINE ONLINE node1
- ONLINE ONLINE node2
- --------------------------------------------------------------------------------
- Cluster Resources
- --------------------------------------------------------------------------------
- ora.LISTENER_SCAN1.lsnr
- 1 ONLINE ONLINE node2
- ora.cvu
- 1 ONLINE ONLINE node1
- ora.node1.vip
- 1 ONLINE ONLINE node1
- ora.node2.vip
- 1 ONLINE ONLINE node2
- ora.oc4j
- 1 ONLINE ONLINE node1
- ora.scan1.vip
- 1 ONLINE ONLINE node2
- ora.zhongwc.db
- 1 OFFLINE OFFLINE Instance Shutdown
- 2 ONLINE ONLINE node2 Open
- ora.zhongwc.zwc.svc
- 1 ONLINE ONLINE node2
- ora.zhongwc.zwc_preconnect.svc
- 1 ONLINE OFFLINE
client
- C:\>sqlplus scott/tiger@node-cluster-scan/zwc
- SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 1月 7 09:25:12 2013
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
- SQL> set line 300
- SQL> col machine format a22
- SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;
- MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
- ---------------------- ------------- ---------- --- ----------
- DINA\PC-201204200916 SELECT BASIC NO 1
- node1 NONE NONE NO 43
- SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;
- MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
- ---------------------- ------------- ---------- --- ----------
- node2 NONE NONE NO 51
- DINA\PC-201204200916 SELECT BASIC YES 1
- SQL>
node2
- [oracle@node2 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 7 09:23:38 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> show parameter service_na
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string zwc_preconnect
- SQL>
- SQL>
- SQL> show parameter service_na
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string ZWC
- [oracle@node1 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 7 09:38:58 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to an idle instance.
- SQL> startup
- ORACLE instance started.
- Total System Global Area 839282688 bytes
- Fixed Size 2233000 bytes
- Variable Size 624954712 bytes
- Database Buffers 209715200 bytes
- Redo Buffers 2379776 bytes
- Database mounted.
- Database opened.
- SQL> !srvctl relocate service -d zhongwc -s ZWC -i zhongwc2 -t zhongwc1
- SQL> show parameter service_names
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string ZWC
node2
- [oracle@node2 ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 7 09:23:38 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
- Data Mining and Real Application Testing options
- SQL> show parameter service_na
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string zwc_preconnect
- SQL>
- SQL>
- SQL> show parameter service_na
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string ZWC
- SQL> show parameter service_na
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- service_names string zwc_preconnect
No comments:
Post a Comment