Oracle Rac database Rman Backup using multiple Instances -- Node affinity

Recently we had requirement to share rman backup across  nodes .   Came across Oracle article that facilities requirement using  Node affinity

In some cluster database configurations, some nodes of the cluster have faster access to some datafiles than to other datafiles. RMAN automatically detects this affinity, which is known as node affinity awareness.

To use node affinity, configure RMAN channels on the nodes of the cluster that have affinity to the datafiles you want to back up. For example, use the syntax:

-- setup for a parallel backup

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';

RMAN> CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';

RMAN> CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2' ;

CONFIGURE DEVICE TYPE sbt PARALLELISM 3;

CONFIGURE DEFAULT DEVICE TYPE TO sbt; 

CONFIGURE CHANNEL 1 DEVICE TYPE sbt CONNECT 'user1/password1@node1';

CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT 'user2/password2@node2';

CONFIGURE CHANNEL 3 DEVICE TYPE sbt CONNECT 'user3/password3@node3';

You can manually override the automatic node affinity by specifying which channels should back up which datafiles. For example:

BACKUP

  # channel 1 gets datafile 1

  (DATAFILE 1 CHANNEL ORA_SBT_TAPE_1)

  # channel 2 gets datafiles 2-4

  (DATAFILE 2,3,4 CHANNEL ORA_SBT_TAPE_2)

  # channel 3 gets datafiles 5-10

  (DATAFILE 5,6,7,8,9,10 CHANNEL ORA_SBT_TAPE_3); 

References : 

https://docs.oracle.com/cd/B10500_01/rac.920/a96596/backup.htm

Oracle Database DBMS_Scheduler job to run on preferred node in RAC -- instance_stickiness

 

We observed  that Rac 1st  node seems highly loaded  though we had database service spread across  instances . Checking further  came across dba_scheduler_jobs feature   instance_stickiness   introduced back in 11g but not known much . 

If instance_stickiness is set to FALSE, each instance of the job runs on the first instance available. For environments other than Oracle RAC, this attribute is not useful because there is only one instance

dba_scheduler_jobs.instance_id will be NULL unless you have explicitly set it with set_attribute.

Instance Stickiness value true means job will run on same node until node is extremely overloaded or not available. False means job will run on any available node. Its default value is true. Job will continue on that on which its first run or started in RAC case. No use in Standalone server.

BEGIN

  dbms_scheduler.create_job(

     job_name => 'TEST_JOB'

    ,job_type => 'PLSQL_BLOCK'

    ,job_action => 'begin null; end; '

    ,start_date => TIMESTAMP'2020-07-24 04:15:01 US/Eastern' 

     ,repeat_interval => 'FREQ=DAILY'

    ,enabled => TRUE

    ,comments => 'scheduler for LATE FEE CALULATION FOR LOANS');

  dbms_scheduler.set_attribute(

                              'TEST_JOB',

                              'INSTANCE_ID',

                              '1'

                             );

END;

/

--Change the attribute value with DBMS set attribute procedure

--select Preferred instance run the job is 1

exec dbms_scheduler.set_attribute(name => 'TEST_JOB' ,attribute=>'INSTANCE_ID', value=>'1');

--Disable the instance stickness attribute.

exec dbms_scheduler.set_attribute(name => 'TEST_JOB' ,attribute=>'INSTANCE_STICKINESS', value=>FALSE);

--Check the job condition and status with following query

 select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like 'TEST_JOB';

Using Job Class and Database Service : 

This can also be achieved using   service defined in Job class 

The Oracle 10g scheduler allows jobs to be linked with job classes, which in turn can be linked to services to allows jobs to run on specific nodes in a RAC environment. To support our requirements we might create a job classes as follows.

--Create the service on two nodes.

srvctl add service -d ORCL -s BATCH_SERVICE -r RAC1,RAC2

srvctl add service -d ORCL -s BATCH_SERVICE -preferred RAC1 -available RAC2

SQL> BEGIN

DBMS_SCHEDULER.create_job_class(

job_class_name => 'BATCH_JOB_CLASS',

service        => 'BATCH_SERVICE');

END;

/

-- Create job by using that job class

Begin

DBMS_SCHEDULER.CREATE_JOB (

   job_name  => 'TEST_JOB',

   job_type  => 'PLSQL_BLOCK',

   job_action => 'Begin dbms_output.put_line(''Hello'') end;',

   number_of_arguments  => 0,

   start_date      => Sysdate,

   repeat_interval  => 'freq=daily;byhour=9,21;byminute=0;bysecond=0;',

   job_class      => 'BATCH_JOB_CLASS',

   enabled     => 'TRUE'

   );

END;

/

Reference : 

How To Run A Job On Specific Node Using DBMS_SCHEDULER (Doc ID 472535.1)

Doc ID 2214476.1