Using this feature, we can also enable jobs running on primary to failover to standby database in the event of switchover / failover. To implement this, we need to create a job class which maps to a service and we assign job to the job class. On failover/switchover, service will failover to the standby database and along with it all the jobs assigned to the job classes which have been mapped to the service will also failover to standby database.

Job 1 —-|

Job 2 —+—–> Job class —> Service

Job 3 —-|

OVERVIEW:

- Create a service orcls for the database and start it

- Create net service name for the service which points to both the primary and standby database

- Create a trigger which starts the service on the primary database

- Connect to the primary database (orcl) using the service orcls

- Create a job class TEST_CLASS mapping ro service orcls

- Create and start a job belonging to TEST_CLASS

- Check that job is running on primary (orcl)

- Perform switchover

- Check that job has failed over and is now running on new primary (sby)

—- CREATE SERVCE —-

– CHECK THE SERVICE_NAMES PARAMETER ON PRIMARY

PRI>sho parameter service_names

NAME TYPE VALUE

———————————— ———– ——————————

service_names string orcl

– CREATE A SERVICE ORCLS

PRI>exec dbms_service.create_service(-

service_name => ‘orcls’,-

network_name => ‘orcls’,-

failover_method => ‘BASIC’, -

failover_type => ‘SELECT’,-

failover_retries => 180,-

failover_delay => 1);

– CHECK THAT LISTENER DOES NOT KNOW ABOUT THE SERVICE AS IT HAS NOT BEEN STARTED YET

$ lsnrctl services |grep orcls

– START THE SERVICE

PRI>exec dbms_service.start_service(‘orcls’);

– CHECK THAT SERVICE APPEARS IN PARAMETER SERVICE_NAMES

PRI>sho parameter service_names

NAME TYPE VALUE

———————————— ———– ——————————

service_names string orcls

– CHECK THAT SERVICE IS AUTOMATICALLY REGISTERED WITH LISTENER

PRI>ho lsnrctl services |grep orcls

Service “orcls” has 1 instance(s).

– CREATE AFTER STARTUP TRIGGER WHICH STARTS THE SERVICE –

ON THE DATABASE WHICH IS CURRENTLY IN PRIMARY ROLE

PRI>create or replace trigger manage_service after startup on database

declare

role varchar2(30);

begin

select database_role into role from v$database;

IF role = ‘PRIMARY’ THEN

DBMS_SERVICE.START_SERVICE(‘orcls’);

ELSE

DBMS_SERVICE.STOP_SERVICE(‘orcls’);

END IF;

END;

/

- CREATE NET SERVICE NAME FOR SERVICE ORCLS

——————————-

Pls note that both primary and standby databases should be registered statically with listener in database home. Here database home listener is running on port 1521 for both standby and primary databases.