Oracle DBA: 2017

How to schedule cron job

Any Value *

value list separator ,

range of values -

step values /

uses :

1 2 3 4 5 <command or script with path>

Example

<MIN> Minute 0 to 59

<HRS> Hours 0 to 24

<DOM> Day of Month 1 to 31 as per month days

<MON> Month 1 to 12

<DOW> Day of Week 1 to 7

1. Execute a cron job every 5 Minutes

*/5 * * * * /home/ramesh/backup.sh

Note: In the same way, use */10 for every 10 minutes, */15 for every 15 minutes, */30 for every 30 minutes, etc.

2. Execute a cron job every 5 Hours

0 */5 * * * /home/ramesh/backup.sh

Note: In the same way, use */2 for every 2 hours, */3 for every 3 hours, */4 for every 4 hours, etc.

3. Execute a job every 5 Seconds

Cron job cannot be used to schedule a job in seconds interval. i.e You cannot schedule a cron job to run every 5 seconds. The alternative is to write a shell script that uses ‘sleep 5’ command in it.

$ cat every-5-seconds.sh
#!/bin/bash
while true
  do
   /home/ramesh/backup.sh
    sleep 5
done

$ nohup ./every-5-seconds.sh &

4. Execute a job every 5th weekday

This example is not about scheduling “every 5 days”. But this is for scheduling “every 5th weekday”.

The 5th field is DOW (day of the week). If you specify * in this field, it runs every day. To run every Friday, specify either 5 of Fri in this field.

The following example runs the backup.sh every Friday at midnight.

        0 0 * * 5 /home/ramesh/backup.sh

        (or)

        0 0 * * Fri /home/ramesh/backup.sh

5.  Execute a job every 5 months

There is no direct way of saying ‘every 5 months’, instead you have to specify what specific months you want to run the job. Probably you may want to run the job on 5th month (May), and 10th month (Oct).

The fourth field is for Months. If you specify * in this field, it runs every month. To run for the specific month, you have to specify the number that corresponds to the month. For example, to run the job on May and Oct, you should specify 5,10 (or) you can simply use the 3 letter acronym of the month and specify May,Oct.

The third field is for DOM (Day of the Month). If you specify * in this field, it runs every day of the month. If you specify 1 in this month, it runs 1st of the month.

The following example runs the backup.sh twice a year. i.e 1st May at midnight, and 1st Oct at midnight.

0 0 1 5,10 * /home/ramesh/backup.sh

(or)

0 0 1 May,Oct * /home/ramesh/backup.sh

############################ RMAN Database FULL Backup #######################

00 00 * * * /home/oracle/RMANBACKUP_SCRIPTS/RMAN_FULL.sh > /home/oracle/RMANBACKUP_SCRIPTS/ORACLE_RMAN_FULL_backup_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

############################ RMAN Database INCREMENTAL Backup #############

#Backup every 2 hours

00 02,04,06,08,10,12,14,16,18,20,22 * * * /home/oracle/RMANBACKUP_SCRIPTS/RMAN_INCREMETAL.sh > /home/oracle/RMANBACKUP_SCRIPTS/ORACLE_RMAN_INC_BACKUP_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

############################ RMAN ARCHIVE Backup #############

#Archive backup in every 30 Minutes

30 * * * * /home/oracle/RMANBACKUP_SCRIPTS/RMAN_ARCHIVE.sh > /home/oracle/RMANBACKUP_SCRIPTS/ORACLE_RMAN_ARCHIVE_backup_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

RMAN Backup Scripting

vi /home/oracle/RMANBACKUP_SCRIPTS/RMAN_FULL.sh

######### RMAN FULL backup Scripts ###################################

#!/bin/ksh
ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
mkdir /home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"
test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"
rman target=/ << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '$test/%d_DB_FULL_%u_%s_%p';
sql 'ALTER SYSTEM SWITCH LOGFILE ';
sql 'ALTER SYSTEM SWITCH LOGFILE ';
sql 'ALTER SYSTEM SWITCH LOGFILE ';
sql 'ALTER SYSTEM CHECKPOINT';
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP SPFILE;
BACKUP CURRENT CONTROLFILE;
delete force noprompt archivelog all backed up 2 times to disk;
DELETE FORCE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
DELETE FORCE NOPROMPT OBSOLETE;
RELEASE CHANNEL ch1;
}
EXIT;$HOME/`date +\%Y\%m\%d\%H\%M\%S`-cron.log
EOF

------------------------------------------------------------------------------------------------------------------------------

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RMAN_FULL.sh

vi /home/oracle/RMANBACKUP_SCRIPTS/RMAN_INCREMETAL.sh

########RMAN INCREMENTAL backup Scripts########################################

rman target=sys/Oracle_4U@emrep << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '$test/%d_DB_INC_%u_%s_%p';
sql 'ALTER SYSTEM SWITCH LOGFILE ';
sql 'ALTER SYSTEM SWITCH LOGFILE ';
sql 'ALTER SYSTEM SWITCH LOGFILE ';
sql 'ALTER SYSTEM CHECKPOINT';
BACKUP INCREMENTAL LEVEL 1 DATABASE;
RELEASE CHANNEL ch1;
}
EXIT;

EOF

----------------------------------------------------------------------------------------------------------------------------------

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RMAN_INCREMETAL.sh

vi /home/oracle/RMANBACKUP_SCRIPTS/RMAN_ARCHIVE.sh

##################### RMAN ARCHIVE backup Scripts##########################
ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"
rman target=/ << EOF
RUN {
ALLOCATE CHANNEL ch1 TYPE
DISK FORMAT '$test/%d_ARCHIVE_%u_%s_%p';
BACKUP ARCHIVELOG ALL delete input;
RELEASE CHANNEL ch1;
}
EXIT;
EOF

-------------------------------------------------------------------------------------------------------------------------------------

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RMAN_ARCHIVE.sh

1. Every day RMAN Full Backup

2. Every 2 hours RMAN Incremental Backup

3. Every Hours RMAN Archive Backup

4. Every Day Gather stats of schema

5. Every week crontab backup

6. Every Month reducing alert logfile size

7. Every Day copy RMAN backup of shared storage

8. Every week purging database recycle bin.

9. Record every minute active inactive session and Events in log file daily basis.

10. Record Database health in text file daily basis and Zip health report file monthly basis.

Copy all file and folder created today under directory /home/oracle/TEST_2016/

vi /home/oracle/RMANBACKUP_SCRIPTS/CP_RMAN_FILES.sh

find /home/oracle/TEST_2016/ORACLE_`date +"%d-%m-%Y"` -ctime -1 -type d -exec cp -avr "{}" /home/oracle/TEST_2016/SHARED_STORAGE/ORACLE_RMAN_BACKUP/ \;

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/CP_RMAN_FILES.sh

#################### crontab entry ##############################

00 23 * * * /home/oracle/RMANBACKUP_SCRIPTS/CP_RMAN_FILES.sh > /home/oracle/RMANBACKUP_SCRIPTS/BACKUP_COPY_LOG/RMAN_BACKUP_COPY_LOG_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

echo `date +"%d-%m-%Y" --date="-7 day"`

vi /home/oracle/RMANBACKUP_SCRIPTS/RM_RMAN_BACKUP_FOLDER_SHARED_LOCATION.sh

find /home/oracle/TEST_2016/SHARED_STORAGE/ORACLE_RMAN_BACKUP/ORACLE_`date +"%d-%m-%Y" --date=" 0 day"` -ctime 0 -type d -exec rm -rf "{}" \;

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RM_RMAN_BACKUP_FOLDER_SHARED_LOCATION.sh

=============crontab entry==========================================

30 00 * * * /home/oracle/RMANBACKUP_SCRIPTS/RM_RMAN_BACKUP_FOLDER_SHARED_LOCATION.sh > /home/oracle/RMANBACKUP_SCRIPTS/BACKUP_COPY_LOG/RMAN_BACKUP_REMOVE_LOG_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

Oracle DBA

ORACLE

Total Pageviews

Saturday, 11 February 2017

crontab entry

Followers