RMAN backup jobs through DBMS_SCHEDULER

It is very easy to create the RMAN backup jobs through DBMS_SCHEDULER.

Benefits of using DBMS_SCHEDLER for RMAN jobs:

  • For multinode RAC configuration, executing the RMAN jobs through OS cronjobs will fail when the node is not available. While using DBMS_SCHEDULER , the job can be run from serviving instance.
  • It is easy to maintain the logs at OS level as well as in the database dictionary.
  • By default execution of the job can be fixed for specific instance using service and can be changed at any time.


Below is the example of creating RMAN backup job for 2 -node RAC.

Step 1: Create the OS level credential from SYS user.


SQL> exec dbms_scheduler.create_credential(credential_name => ‘oracle’,username => ‘oracle’, password => ‘xyz’);

Here username => ‘oracle’,password => ‘xyz’ are the OS level user through which we want to run the RMAN backup job.

Step 2:  Create the JOB_CLASS that will use the service to run the RMAN job. 


SQL> exec dbms_scheduler.create_job_class(job_class_name=> ‘RUN_RMAN_JOB’, service=> ‘rmansec’);

Here, rmansec is the service that was created with below specifications.

[oracle@rh-test-db-01 ~]$ srvctl add service -d testdb -s rmansec -r testdb2 -a testdb1

[oracle@rh-test-db-01 ~]$ srvctl config service -d testdb -s rmansec
Service name: rmansec
Service is enabled
Server pool: testdb_rmansec
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: testdb2
Available instances: testdb1

 

Step 3: Create database JOB for RMAN backup.


begin
dbms_scheduler.create_job(
job_name => ‘RMAN_incr1_BACKUP’,
job_type => ‘EXECUTABLE’,
job_action => ‘/bin/ksh’,
NUMBER_OF_ARGUMENTS => 1,
start_date => SYSTIMESTAMP,
credential_name => ‘oracle’,
job_class => ‘RUN_RMAN_JOB’,
auto_drop => FALSE,
enabled => FALSE);
end;
/
commit;

Note: -> Do not schedule the job at the time of creation. Please do not forget to mention the NUMBER_OF_ARGUMENTS. The number of arguments are the one which we need to pass as a parameter when job will run.I am having only .sh file name as a input so I have only 1 argument.

 

Step 4: Set the argument VALUE of the above created JOB. 


begin
dbms_scheduler.set_job_argument_value(
job_name => ‘RMAN_INCR0_BACKUP’,
ARGUMENT_POSITION => 1,
argument_value => ‘/oracle/backup/rman_incr0_backup.sh’);
end;
/
commit;

Note: Here the argument position is the script name which we want to run for RMAN backup. Give the absolute path of the RMAN script.

 

Step 5: Schedule the JOB for weekly backup.


begin
dbms_scheduler.set_attribute(
name => ‘RMAN_INCR0_BACKUP’,
attribute => ‘repeat_interval’,
value => ‘freq=weekly; byday=wed,sun; byhour=5; byminute=0; bysecond=0;’);
dbms_scheduler.enable( ‘RMAN_INCR0_BACKUP’ );
end;
/
commit;

Note: The job has been scheduled to run on every wednesday and sunday at 5 AM.

 

Additional Information:

  • You can purge the job run details as below :

— Only purge the log
begin
dbms_scheduler.purge_log(job_name=>’RMAN_INCR0_BACKUP’);
end;
/

  • You can run the job manually as below :

— Only run the job
begin
dbms_scheduler.run_job(‘RMAN_INCR0_BACKUP’);
end;
/

 

Below script have been deployed on both the nodes under same location i.e. /oracle/backup/rman_incr0_backup.sh

[oracle@rh-test-db-01 backup]$ cat rman_incr0_backup.sh
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=testdb1
DTTS=`date ‘+%d%m%Y_%H%M%S’`
RMANLOG=/oracle/backup/RMAN/log
echo start >>/oracle/backup/RMAN/RMAN_incr0_BACKUP_SCRIPT.out
echo `date`>>/oracle/backup/RMAN/RMAN_incr0_BACKUP_SCRIPT.out
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target \/ log=$RMANLOG\/RMAN_incr0_BKP_LOG_$DTTS.out<<EOF
run
{
backup incremental level 0 database;
crosscheck archivelog all;
backup archivelog all delete input;
backup current controlfile;
}
EOF
echo end >>/oracle/backup/RMAN/RMAN_incr0_BACKUP_SCRIPT.out
echo `date`>>/oracle/backup/RMAN/RMAN_incr0_BACKUP_SCRIPT.out

 

=> You can query the job execution details as below:

set lines 180 pages 300
col RUN_DURATION for a20
col JOB_NAME for a20
col STATUS for a10
col ACTUAL_START_DATE for a35
col CPU_USED for a20
col LOG_DATE for a35
SELECT instance_id,job_name,log_date,actual_start_date, run_duration, cpu_used ,status FROM dba_scheduler_job_run_details where job_name like ‘RMAN_%’ order by 4 desc;

=> You can query the job details as below:

set lines 180 pages 300
col SCHEDULE_NAME for a20
col START_DATE for a40
col REPEAT_INTERVAL for a40
col OWNER for a5
col JOB_NAME for a10
col NEXT_RUN_DATE for a30
select OWNER , JOB_NAME , JOB_TYPE , START_DATE , REPEAT_INTERVAL, ENABLED , NEXT_RUN_DATE , INSTANCE_ID , job_class, instance_stickiness from DBA_SCHEDULER_JOBS where JOB_NAME like ‘RMAN_%’;

Advertisements

One thought on “RMAN backup jobs through DBMS_SCHEDULER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s