stick the job to the specific instance in Oracle RAC
First method ( and easy one too ) :-
note:- must perform this activity on each instance one by one.
......stop jobs which is assigning to the specific instance.....
$ export ORACLE_SID=SID
connect database using job owner ....
$ sqlplus username/password
list of jobs and its instance....
sql> select JOB,INSTANCE from user_jobs;
JOB INSTANCE
---------- ----------
2510 0
2951 0
2992 0
3091 0
3111 0
1570 0
1567 0
1568 0
1569 0
1571 0
1572 0
stick the job to the
specific instance.....
Syntax
sql>execute
dbms_job.instance(job=>1571, instance=>2,force=>true);
verify from the instance number........
sql>select JOB,INSTANCE from user_jobs;
JOB INSTANCE
---------- ----------
2510 0
2951 0
2992 0
3091 0
3111 0
1570 0
1567 0
1568 0
1569 0
1571 2
sql> exit
....start jobs........
======================================================================
Second Method ( the long one ) :-
BEGIN
-- Create a new service associated with the specified TSN service name.
DBMS_SERVICE.create_service (
service_name => '<service name>',
network_name => '<db name>.<domain name>');
-- Start the specified service.
DBMS_SERVICE.start_service (
service_name => ' <service name>');
-- Disconnects all sessions associated with the specified service.
DBMS_SERVICE.disconnect_session (
service_name => '<service name>');
-- Stop the specified service.
DBMS_SERVICE.stop_service (
service_name => '<service name>');
-- Delete the specified service.
DBMS_SERVICE.delete_service (
service_name => '<service name>');
END;
/
Some examples of using the srvctl utility to do similar actions are listed below.
# Create the service on two nodes.
srvctl add service -d <db name> -s <service name> -r <instance name>,<instance name>
# Stop and start the service on a single or multiple nodes.
srvctl stop service -d <db name> -s <service name> -i <instance name>,<instance name>
srvctl start service -d <db name> -s <service name> -i <instance name>
# Disable and enable the service on a single or multiple nodes.
srvctl disable service -d <db name> -s <service name> -i <instance name>,<instance name>
srvctl enable service -d <db name> -s <service name> -i <instance name>
# Display the current status of the service.
srvctl status service -d <db name> -s <service name> -v
# Remove the service from both nodes.
srvctl remove service -d <db name> -s <service name> -i <instance name>,<instance name>
Once a service is present, it can be assigned to a job class during creation or subsequently using the set_attribute procedure, as shown below.
BEGIN
DBMS_SCHEDULER.create_job_class (
job_class_name => '<job class name>',
service => ‘<service name>’);
DBMS_SCHEDULER.set_attribute (
name => '<job class name>',
attribute => 'instance_stickiness',
value => ‘true’);
END;
/
The following scenario will explain more specifically how services can be used to partition applications in a three-node RAC environment.
For services to function correctly, the Global Services Daemon (GSD) must be running on each node in the cluster. The GSDs are started using the gsdctl utility, which is part of the Cluster Ready Services (CRS) installation, so they must be started from that environment.
# Set environment.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/crs
export PATH=$ORACLE_HOME/bin:$PATH
# Start GSD daemon.
gsdctl start
Once the GSDs are running, the user must check that the cluster configuration is correct. The following command and output show the expected configuration for a three-node database called <db name>.
srvctl config database -d <db name>
This configuration is typically performed during the cluster database creation, but it can be performed subsequently using the following commands.
srvctl add database -d <db name> -o /u01/app/oracle/product/11.2.0/db_1
Assume that two applications should run in the following way:
OLTP - Should run on nodes one and two of the RAC, but is able to run on node three if nodes one and two are not available.
BATCH - Should run on node three, but is able to run on nodes one and two if node three is not available.
To meet this requirement, the following services can be created:
# Set environment.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
# Create services.
srvctl add service -d <db name> -s OLTP_SERVICE -r <instance 1>,<instance 2> -a <instance 1>,<instance 2>,<instance 3>
srvctl add service -d <db name> -s BATCH_SERVICE -r <instance 3> -a <instance 1>,<instance 2>,<instance 3>
The OLTP_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on nodes one and two, indicated by the -r option. The BATCH_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on node three, indicated by the -r option.
The services can be started and stopped using the following commands:
srvctl start service -d <db name> -s OLTP_SERVICE
srvctl start service -d <db name> -s BATCH_SERVICE
srvctl stop service -d <db name> -s OLTP_SERVICE
srvctl stop service -d <db name> -s BATCH_SERVICE
Comments
Post a Comment