In this post, I will demonstrate automatic failover of a job to standby which can be implemented as a result of the automatic client failover introduced in 11g dataguard.
Now, we can create service for the database which can run on both primary and standby database and
following a switchover / failover, dataguard broker can automatically
- start the appropriate services on the new primary database,
- disconnect clients from the failed database and
- redirect them to the new primary database — no manual intervention is required.
For demonstration of automatic client failover please click here:
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.
IF BOTH PRIMARY AND STANDBY DATABASES ARE ON SAME MACHINE
. create net service name orcls
– service name = orcls
– add address on host1 port 1521 (representing both primary database orcl and standby database sby)
– NOW WHEN SERVICE IS AVAILABLE ON ORCL (PRIMARY DATABASE), LISTENER WILL CONNECT TO ORCL DATABASE
– AND WHEN SERVICE IS AVAILABLE ON SBY (STANDBY DATABASE FOLLWING FAILOVER/SWITCHOVER), LISTENER WILL CONNECT TO SBY DATABASE
———————————–
IF PRIMARY AND STANDBY DATABASES ARE ON DIFFERENT MACHINES
– USING NETMGR
. create net service name orcls
– service name = orcls
– add address on host1 port 1521 (representing primary database orcl)
– add address on host2 port 1521 (representing standby database sby)
– NOW WHEN SERVICE CONNECTS TO ORCL PRIMARY DATABASE, IT WILL BE REGISTERED AUTOMATICALLY WITH LISTENER ON PORT 1521 ON HOST1 AND APPLICATION WILL GET CONNECTED TO ORCL DATABASE.
– NOW WHEN SERVICE CONNECTS TO SBY STANDBY DATABASE, IT WILL BE REGISTERED AUTOMATICALLY WITH LISTENER ON PORT 1521 ON HOST2 AND APPLICATION WILL GET CONNECTED TO SBY DATABASE.
———————————
——— JOB FAILOVER ——————-
– check current configuration
DGMGRL> show configuration;
Protection Mode: MaxPerformance
Databases:
orcl – Primary database
sby – Physical standby database
– create test table on PRIMARY (ORCL)
ORCL>drop table hr.test purge;
create table hr.test(id number, tim timestamp);
— create job class TEST_CLASS which is associated with service orcls
ORCL>exec dbms_scheduler.drop_job_class (‘TEST_CLASS’);
BEGIN
sys.dbms_scheduler.create_job_class(
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
service => ‘orcls’,
job_class_name => ‘”TEST_CLASS”‘);
END;
/
– create a job which inserts records in table HR.TEST every 3 seconds
and belongs to job_class TEST_CLASS
ORCL>exec sys.dbms_scheduler.drop_job(job_name=>’sys.test_job’);
BEGIN
sys.dbms_scheduler.create_job(
job_name => ‘”SYS”.”TEST_JOB”‘,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘begin
insert into hr.test values (1, systimestamp);
commit;
end;’,
repeat_interval => ‘FREQ=SECONDLY;INTERVAL=3′,
start_date => systimestamp at time zone ‘Asia/Calcutta’,
job_class => ‘”TEST_CLASS”‘,
auto_drop => FALSE,
enabled => TRUE);
END;
/
– check that job executes every 3 seconds
ORCL>select * from hr.test
order by tim;
– check that job is currently running on primary
ORCL>select job_name
from dba_scheduler_jobs
where job_name=’TEST_JOB';
JOB_NAME
——————————
TEST_JOB
– check that job is currently not running on standby
SBY>select job_name
from dba_scheduler_jobs
where job_name=’TEST_JOB';
no rows selected
– Perform switchover so that
SBY becomes new primary and
ORCL becomes new physical standby
DGMGRL>switchover to sby;
– Startup new standby (orcl)
ORCL>conn / as sysdba
startup
-
– check that switchover successful
DGMGRL>connect sys/sys@sby
show configuration;
Protection Mode: MaxPerformance
Databases:
sby – Primary database
orcl – Physical standby database
– check that job is still running by checking the records in hr.TEST table
ORCL>select * from hr.test
order by tim;
– check that the job is now running on new primary i.e. SBY and not on eralier primary (orcl)
as it has failed over along with service orcls
– check that job is currently running on new primary(SBY)
SBY>select job_name
from dba_scheduler_jobs
where job_name=’TEST_JOB';
JOB_NAME
——————————
TEST_JOB
– check that job is currently not running on new standby (ORCL)
ORCL>select job_name
from dba_scheduler_jobs
where job_name=’TEST_JOB';
no rows selected
– RESTORE EARLIER STATUS
DGMGRL>switchover to orcl;
ORCL>exec dbms_scheduler.drop_job(‘TEST_JOB’);
drop table hr.test purge;
I hope you found this demonstration interesting. Pls give your valuable comments/suggestions
——————————————————————
Related links:
——————–