Scenario:

  1. PrimeDG is generating too many archivelogs.
  2. Server doesn’t have enough space.
  3. Developement team wants to backup archivelog and purge archivelog every 3 hours.
  4. Backup location is already set → /u99/PrimeDG/Backup
  5. Archivelog location is already set → /u99/PrimeDG/Archive

Solution:

1.        Lets get OS and kernel info.

Linux-223:(none)> uname -a

Linux Linux-223 2.6.18-371.9.1.el5 #1 SMP Tue May 13 06:52:49 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux

Linux-223:(none)> cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

2.        Let’s create two directories called scripts (to keep rman scripts) and log (to keep rman’s daily backup logs)

Linux-223:(none)>  mkdir -p ~/scripts/log

3.        Let’s create script that we will use to schedule the backups.

Linux-223:(none)> vi  ~/scripts/rman_purge_PrimeDG_arc.sh

i → to go to insert mode

#!/bin/bash

#mmuhammad Export Script

#Version1.0

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=PrimeDG

/u01/app/oracle/product/11.2.0/db_1/bin/rman target / @/home/oracle/scripts/rman_purge_PrimeDG_arc.rcv

exit

ESC → to go to escape mode

:wq! → to save and exit out

4.        Let’s create rman script which will be called by the above script.

Linux-223:(none)> vi ~/scripts/rman_purge_PrimeDG_arc.rcv

i → to go to insert mode

run

{

backup archivelog all not backed up 1 times delete all input;

}

ESC → to go to escape mode

:wq! → to save and exit out

5.        Change the permission on both rman scripts.

Linux-223:(none)> chmod 755 ~/scripts/rman_purge_PrimeDG_arc.*

6.         Login as sys

. oraenv → PrimeDG

sqlplus / as sysdba

show user

7.        Schedule job from dbms schedular

1) Create Schedule

begin

        dbms_scheduler.create_schedule

        (

        schedule_name => 'ARCHIVELOG_BACKUP_HOURLY',

        repeat_interval => 'FREQ=DAILY;

        INTERVAL=1;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=1',

        comments => 'schedule backup of archivelog to run hourly'

        );

end;

/

2) Create Program

begin

        dbms_scheduler.create_program

        (

        program_name => 'BACKUP_ARCHIVELOGS',

        program_type => 'EXECUTABLE',

        program_action => '/home/oracle/scripts/rman_purge_PrimeDG_arc.sh',

        enabled => TRUE,

        comments => 'Backup Archivelogs Using RMAN'

        );

END;

/

3) Create Job

begin

        dbms_scheduler.create_job

        (

        job_name=>'HOURLY_BACKUP_OF_ARCHIVELOG',

        program_name =>'backup_archivelogs',

        schedule_name=> 'Archivelog_Backup_Hourly',

        enabled => true,

        comments => 'backs up databases daily at 4am'

        );

END;

/

Note: In case you need to delete the schedule, you will need to drop the job, then program and finally schedule as shown below.

1

Drop_job    

BEGIN
 DBMS_SCHEDULER.drop_job (job_name => '
HOURLY_BACKUP_OF_ARCHIVELOG');
END;
/

2

Drop_program (and)

BEGIN
 DBMS_SCHEDULER.drop_program (program_name =>
'BACKUP_ARCHIVELOGS');
END;
/

3

Drop_schedule

BEGIN
 DBMS_SCHEDULER.drop_schedule (schedule_name => 'ARCHIVELOG_BACKUP_HOURLY');
END;
/

8.        Enable Job

exec dbms_scheduler.enable('HOURLY_BACKUP_OF_ARCHIVELOG');

9.        Verify Job is submitted

SELECT

        JOB_NAME,

        STATE

FROM

        DBA_SCHEDULER_JOBS

WHERE

        JOB_NAME = 'HOURLY_BACKUP_OF_ARCHIVELOG';

10.        Run a one time job

EXEC dbms_scheduler.run_job('HOURLY_BACKUP_OF_ARCHIVELOG');

11.        Verify the logs are being generated.

Linux-223:(none)> cd ~/scripts/log

Linux-223:(none)> ls -ltrh ~/scripts/log

That is it. Hope this helps.

--Moid