9. Other Configurations – Mastering Oracle Scheduler in Oracle 11g Databases

Chapter 9. Other Configurations

I hope that the previous chapters gave enough handles to start working with the Scheduler. I am aware that there is a lot more to tell about the Scheduler, and this chapter will shed light on some other configurations where the Scheduler can be used. This time it is about RAC (Real Application Clusters) and the standby databases.

RAC

When the jobs you are running depend on things other than just the database, it might be interesting to know what these other things are, and where they can be found. There are several ways to control which instance a job is going to run, some of which are:

  • Define instance_id in the job definition

  • Use instance_stickiness in the job definition

  • Define service_name in the job class definition for the job

To be able to show what exactly happens, we need an RAC installation. The RAC installation used here runs on two nodes—oralin01 and oralin02—using the shared storage that is exported as iSCSI disks from the of01 server running openfiler. The database called "shields" is using the iSCSI disks through the ASM. The instances of this database are shields1 and shields2. In the default setup, every instance of the cluster supports the service whose name is the same as the database name. For demonstration purposes, the services departures and arrivals have been defined. The preferred instance for the arrivals service is shields2 and the preferred instance for the departures service is shields1. The shields service runs on both the instances.

To find out where a service is currently running, we can use the gv$active_services view. This view lists things such as the instance_id and the services running on that instance. Combine it with instance_name from v$instance to find out on which instance the query runs:

In this output, we can see that we are connected on the shields1 instance. Currently, the departures service is running on shields1, which is the preferred instance of this service. The arrivals service is running on the shields2 instance. The other services are either the default shields services and the shieldsXDB service.

Another view that is interesting in this regard is the v$session view. It lists through which service name what connection was made:

In this output, we can see that the Session ID is 123, the session is connected on the departures service, and the instance is shields1.

In this case, the configuration is made such that the departures and the arrivals service each run on their preferred instance. It could very well be that these services run on more instances. Using service management, we can define on which instances a service is allowed to run and which one is the preferred instance. Enough has been said about RAC. For now, let's go back to the Scheduler jobs.

Job creation/modification

On the most detailed level of the job, the instance can be selected by assigning an instance ID to the instance_id attribute. This might be a little surprising as we are all educated to use service_name. There is a reason for this. Sometimes, a job should not run if the selected instance is not available. In that case, a hardcoded link to the instance_idinstance_id could be very valid. The definition indicates the instance on which the job is to be run. If the instance is not running, the job cannot run. The following job can only run when instance_id 1 is open:

--/
RACjob, modifyingbegin
dbms_scheduler.create_job
(
job_name => 'RAC01',
job_class => 'DEFAULT_JOB_CLASS',
job_type => 'plsql_block',
job_action => 'begin insert into test_rac
(sid,instance_name,service_name,time)
select sid, instance_name, service_name, systimestamp from v$session, v$instance where sid = (select sid from v$mystat where rownum = 1);
end;',
repeat_interval => 'freq=minutely;interval=2'
);
dbms_scheduler.set_attribute
(
name => 'RAC01', attribute => 'instance_id',value => 1
);
dbms_scheduler.enable('RAC01');
end;
/

The output of this job is in the test_rac table, which is defined as:

CREATE
TABLE test_rac
(
sid NUMBER,
service_name VARCHAR2(30),
instance_name VARCHAR2(30),
TIME TIMESTAMP WITH TIME zone
);

The job runs every 2 minutes and inserts a line into the test_rac table. Note that this job has the instance_id hardcoded into it. This means that the job will not run if this instance is not running. It also means that the service_name found in the v$session view for this job is the SYS$USERS, a default service name.

What should we do when we want a job to switch to an available instance automatically after the original instance has terminated for whatever reason? We should tie the job to a service name. The way to do this is by creating a job class that connects to the desired service. In this case, the job class connects to the departures service:

--/
begin
dbms_scheduler.create_job_class
(
job_class_name => 'departures_class',
service => 'departures',
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
comments => 'run on the departures service'
);
end;
/

Now, we will change the previously defined job RAC01 so that it falls in the departures_class by running this piece of code:

--/
begin
dbms_scheduler.set_attribute
(
name => 'RAC01',
attribute => 'job_class',
value => 'departures_class'
);
end;
/

From this moment on, the job output will look a little different as we can see in the following test_rac table:

See how the default service name is changed to the desired service name departures. The departures service currently runs in the shields1 instance.

Let's consider a hypothetical situation where a disaster strikes all of a sudden and the host that serves the shields1 instance is shut down. After a while, we can see that the departures service has moved to the shields2 instance.

Now that the service is running on a different instance, we expect that the job is also going to run. Minutes later, we find that the job is still not running. How can this be? Here we find a nice default value for the instance_stickiness attribute of the job. This tells the Scheduler to try to run the job on the same instance where it started for the first time.

The first time, if no explicit instance selection is made, the job will run on the instance (that runs that service) with the lightest load that is available at that moment. The tricky bit here is in the definition, which tells us that if the interval between the runs is large, instance_stickiness will be ignored and if the instance where the job should run is unable to run the job for a significant period of time, another instance will run the job. Pay attention to this parameter as it might help you. But if it's used in a wrong way, it may have a few surprises up its sleeve. In our hypothetical scenario, we had hoped that the job would fluently switch over to the other instance when the service was relocated to the surviving instance.

As we are not willing to delay the departing passengers any longer, we reset the instance_stickiness parameter to false and the instance_id to NULL.

--/
begin
dbms_scheduler.set_attribute
(
name => 'RAC01',
attribute => 'instance_stickiness',
value => false );
end;
/

It would be better to create the job with instance_stickiness set to false from the beginning and instance_id to null if we want to be flexible.

Here, we clearly see the switch to the other instance. This took a bit of extra time, caused by the default behavior of dbms_scheduler.create_job. The default behavior is to create a job that runs on the instance where the job was created and to keep it running there. So now we change the following attributes:

  • instance_id = NULL

  • instance_stickiness = false

We should be able to get the job quickly running on the other instance again. In the meantime, the shields1 instance has been restarted, so now let's shut down the host of shields2 instance and see what happens.

Almost immediately, the job starts running on the shields1 instance where the departures service was relocated by the cluster software when we crashed the node of the shields2 instance.

The job_class definition

The selection of the instance where a job should run can also be handed over to the job class. In the job_class, we can define a service_name on which the jobs of that class should run. This gives the Scheduler more freedom to select an available instance that supports the defined service. If the specified service is dropped, the jobs automatically fall back to the default service.

Service selection can also be controlled by resource_consumer_group. The resource_consumer_group definition will take precedence if a job class has a service defined, this service is also mapped to a resource consumer group, and at the same time the job class also has a resource_consumer_group defined.

Standby databases

Jobs are mostly defined in a normal primary database. There are reasons for desiring jobs to run in a standby database. Oracle 11g introduces the database roles for this purpose. In the database_role attribute of a job, we can specify when a job should run. Should it run when the database is run in the PRIMARY role, or should it run when the database runs in the LOGICAL STANDBY role? There is no option for the PHYSICAL STANDBY role. It is understandable that the PHYSICAL STANDBY role is not implemented. But at the same time there could be some valid reasons to still want jobs to run with the database in the PHYSICAL STANDBY role. That would, of course, be without the fancy logging, but what about the maintenance of the applied archives? This still has to be scheduled outside the database that needs this maintenance.

Creating jobs in a logical standby database

The job creation in DDL is not propagated to the logical standby database. However, you can create Scheduler jobs in the logical standby database. After the job creation, the role of the Scheduler job is inherited from the database_role where the job was created. This just means that if you create a job in the primary database, the job will run in the primary database. If you create a job in the logical standby database, it will inherit the LOGICAL STANDBY role from the database. It is possible to change the database_role for an existing job by running this:

--/
begin
DBMS_SCHEDULER.SET_ATTRIBUTE('your_job_name','database_role', 'LOGICAL STANDBY');
end;
/

Note

The job role is listed in the DBA_SCHEDULER_JOB_ROLES view, and not in the *_SCHEDULER_JOBS views.

Role transition means the primary database can exchange its role with the standby database and you have jobs that should run not only when the database is in the PRIMARY role, but also when the database is in the LOGICAL STANDBY role. If a role transition is to be expected, you need to create two copies of that job in the same database—one copy that has the PRIMARY role and the other has the LOGICAL STANDBY role.

For this sample job, we need to create a table to hold the job logging in the primary database as follows:

CREATE
TABLE PZ
(
T TIMESTAMP(6) WITH TIME ZONE,
INSTANCE_NAME VARCHAR2(30),
ROLE VARCHAR2(30),
M VARCHAR2(30)
)
/

Do this in the primary database. It is automatically propagated to the logical standby database when the Redo Apply process is running.

In order to be able to create a job, the Redo Apply process must first be stopped and Data Guard must be disabled. If you try to create a job without this preparation, you will get the ORA-01031: insufficient privileges error. The following code stops the Redo Apply process and disables the Data Guard. This enables us to create the job like this:

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER session DISABLE GUARD;
--/
BEGIN
dbms_scheduler.create_job
(
job_name => 'teststby_stby',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'test in logical standby database',
auto_drop => TRUE,
job_type => 'plsql_block',
job_action => 'begin insert into pz (t,instance_name, role, m)
select systimestamp, i.instance_name, d.database_role, ''teststby from stby''
from v$instance i, v$database d;
end;',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'freq=minutely;interval=2;',
end_date => NULL);
END;
/
ALTER session ENABLE GUARD;
ALTER DATABASE START LOGICAL STANDBY APPLY;

This code creates the job. This does not mean that the job is going to run now. The job will run when the guard status is STANDBY.

Running jobs in a logical standby database

Once the job is created in the logical standby database, we can run it. However, first we must switch the logical standby database into the correct mode as shown here:

alter database guard standby;

However, how successful that job will be depends on what it should do. If the job updates a table that is guarded, there will be other errors such as:

PL/SQL: ORA-16224: Database Guard is enabled

It is possible to successfully run such a job by running:

alter database guard none;

Now, tables whose changes are propagated from the primary database are editable for the job. So the job that tries this has more chance for success.

To stop this, we can set it back using:

alter database guard all;

In this screenshot, you can see that a job was running in the primary database all the time. The logical standby database was altered to allow a job to run for a short while, after which the Redo Apply process was restarted.

Summary

In this chapter we have seen:

  • How to tie a job to a specific RAC instance

  • How to tie a job to a specific service

  • How instance_stickiness and instance_id can prevent jobs from running

  • How we can achieve a job to keep running when a service gets relocated

  • How we can create and run jobs in a logical standby database

In the next chapter, we will take a look at the tools that are available for us to increase our productivity when working with the Scheduler.