- Purposes:
+ Daily full backups take up a lot of time and load on the database.
+ Reduce daily backup time
+ Reduce IO, CPU, RAM load for server, storage, database
+ Use 1 script for all databases
- Operations:
+ Run level 0 backup on Sunday, or on the day with the least load during the week.
+ Run level 1 backup on the other days of the week.
+ It is possible to implement backup on Physical Standby to reduce the load on the primary database.
- Deployment:
3.1 Script content (as below)
Create 2 files: rman_level0.sh, rman_level1.sh in the folder /home/oracle/scripts
3.2 Schedule in Crontab to run”
############# BACKUP RMAN – ThieuVD ########
0 23 * * 0 /home/oracle/scripts/rman_level0.sh > /dev/null 2>&1
0 23 * * 1,2,3,4,5,6 /home/oracle/scripts/rman_level1.sh > /dev/null 2>&1
—- Script Backup LEVEL0 —–
—- rman_level0.sh —–
#!/bin/bash -login
##########################################################
########## SCRIPT NAME: rman_level0.sh
########## AUTHOR: ThieuVD
##########################################################
#export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
BASE_NAME=$0
BASE_DIR=$(dirname “$0”)
cd $BASE_DIR
BASE_DIR=`pwd`
mkdir -p log_full
### Chose backup dir
### Some databases use 2 backups BACKUP_DIR1, BACKUP_DIR2 for large database capacity
### Each level 0 is stored in a backup BACKUP_DIR1/BACKUP_DIR2
BACKUP_DIR1=/backup/rman
BACKUP_DIR2=/backup/rman
PARALLELISM=4
cd $BASE_DIR/log_full
dir1=`$last ls -1rht *level0*.log | tail -1 | xargs tail -1`
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -2 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -3 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -4 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -5 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
echo “No successful backup! Manual fill backup dir.”
#exit
fi
if [ “$dir1” = “$BACKUP_DIR1” ]
then
BACKUP_DIR=$BACKUP_DIR2
fi
if [ “$dir1” = “$BACKUP_DIR2” ]
then
BACKUP_DIR=$BACKUP_DIR1
fi
if [ “$dir1” = “” ]
then
BACKUP_DIR=$BACKUP_DIR1
fi
echo $BACKUP_DIR
#export BACKUP_DIR=/new_backup1
### Get PMON
PMON_COUNT=`ps -ef |grep pmon |grep -v grep|egrep -v “sed|grep|ASM|APX|MGMT” | sed “s/ora_pmon_//g” |awk ‘{print $8}’|wc -l`
if [ $PMON_COUNT -eq 0 ]; then
echo “Database not running.”
exit
fi
if [ $PMON_COUNT -gt 1 ]; then
echo “Can export ORACLE_SID=?.”
fi
### auto pmon
PMON=`ps -ef |grep pmon |grep -v grep|egrep -v “sed|grep|ASM|APX|MGMT” | sed “s/ora_pmon_//g” |awk ‘{print $8}’`
export ORACLE_SID=${PMON};
### manual pmon
#export ORACLE_SID=ussd1
export DATE=`date +%Y%m%d_%H%M`
export DATA_DIR=$BACKUP_DIR/backup_full
export ARC_DIR=$BACKUP_DIR/backup_full
export BACKUP_DATA=$DATA_DIR/$ORACLE_SID”_data_level0_”$DATE”_f%t_s%s_s%p”
export BACKUP_ARC=$ARC_DIR/$ORACLE_SID”_arc_level0_”$DATE”_f%t_s%s_s%p”
export BACKUP_CTR=$DATA_DIR/controlfile_”$DATE”.ctl
### Get status backup
BK_STATUS=$($ORACLE_HOME/bin/sqlplus -s / as sysdba <<!
SET HEADING OFF FEEDBACK OFF PAGES 0 HEAD OFF
SELECT status
FROM v\$rman_backup_subjob_details
WHERE 1 = 1
AND end_time = (SELECT MAX (end_time)
FROM v\$rman_backup_subjob_details
WHERE end_time > SYSDATE – 30);
exit;
!
)
if [ “$BK_STATUS” != ‘RUNNING’ ]; then
mkdir -p $DATA_DIR
mkdir -p $ARC_DIR
$ORACLE_HOME/bin/rman target / log $BASE_DIR/log_full/rman_backup_level0_$ORACLE_SID”_”$DATE.log <<EOF
RUN
{
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE REDUNDANCY = 1;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 4096 M;
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM BACKUP TYPE TO BACKUPSET;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE FORMAT ‘$BACKUP_DATA’ FILESPERSET 32 TAG LEVEL0;
BACKUP AS COMPRESSED BACKUPSET FORMAT ‘$BACKUP_ARC’ ARCHIVELOG ALL TAG ARCH;
BACKUP CURRENT CONTROLFILE FORMAT ‘$BACKUP_CTR’;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE REDUNDANCY = 1;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME ‘SYSDATE-1’ BACKED UP 1 TIMES TO DISK;
}
exit;
EOF
echo $BACKUP_DIR >> $BASE_DIR/log_full/rman_backup_level0_$ORACLE_SID”_”$DATE.log
else
echo “—-> Backup RMAN running (RUNNING).”
fi
exit;
++++++++++++++++++++++++++++++++++++++++++++++++++++
—- Script Backup LEVEL1 —–
—- rman_level1.sh —–
#!/bin/bash -login
##########################################################
########## SCRIPT NAME: rman_level0.sh
########## AUTHOR: ThieuVD
##########################################################
#export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
BASE_NAME=$0
BASE_DIR=$(dirname “$0”)
cd $BASE_DIR
BASE_DIR=`pwd`
mkdir -p log_full
### Chose backup dir
BACKUP_DIR1=/backup_exagrid/rman
BACKUP_DIR2=/backup_exagrid/rman
PARALLELISM=4
cd $BASE_DIR/log_full
dir1=`$last ls -1rht *level0*.log | tail -1 | xargs tail -1`
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -2 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -3 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -4 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
dir1=`$last ls -1rht *level0*.log | tail -5 | head -1 | xargs tail -1`
fi
if [ “$dir1” != “$BACKUP_DIR1” ] && [ “$dir1” != “$BACKUP_DIR2” ]; then
echo “Khong co backup thanh cong! Manual fill backup dir.”
#exit
fi
if [ “$dir1” = “$BACKUP_DIR1” ]
then
BACKUP_DIR=$BACKUP_DIR1
fi
if [ “$dir1” = “$BACKUP_DIR2” ]
then
BACKUP_DIR=$BACKUP_DIR2
fi
if [ “$dir1” = “” ]
then
BACKUP_DIR=$BACKUP_DIR1
fi
echo $BACKUP_DIR
#export BACKUP_DIR=/backup
### Get PMON
PMON_COUNT=`ps -ef |grep pmon |grep -v grep|egrep -v “sed|grep|ASM|APX|MGMT” | sed “s/ora_pmon_//g” |awk ‘{print $8}’|wc -l`
if [ $PMON_COUNT -eq 0 ]; then
echo “Database not running.”
exit
fi
if [ $PMON_COUNT -gt 1 ]; then
echo “Can export ORACLE_SID=?.”
fi
### auto pmon
PMON=`ps -ef |grep pmon |grep -v grep|egrep -v “sed|grep|ASM|APX|MGMT” | sed “s/ora_pmon_//g” |awk ‘{print $8}’`
export ORACLE_SID=${PMON};
### manual pmon
#export ORACLE_SID=ussd1
export DATE=`date +%Y%m%d_%H%M`
export DATA_DIR=$BACKUP_DIR/backup_full
export ARC_DIR=$BACKUP_DIR/backup_full
export BACKUP_DATA=$DATA_DIR/$ORACLE_SID”_data_level1_”$DATE”_f%t_s%s_s%p”
export BACKUP_ARC=$ARC_DIR/$ORACLE_SID”_arc_level1_”$DATE”_f%t_s%s_s%p”
export BACKUP_CTR=$DATA_DIR/controlfile_”$DATE”.ctl
### Get status backup
BK_STATUS=$($ORACLE_HOME/bin/sqlplus -s / as sysdba <<!
SET HEADING OFF FEEDBACK OFF PAGES 0 HEAD OFF
SELECT status
FROM v\$rman_backup_subjob_details
WHERE 1 = 1
AND end_time = (SELECT MAX (end_time)
FROM v\$rman_backup_subjob_details
WHERE end_time > SYSDATE – 30);
exit;
!
)
if [ “$BK_STATUS” != ‘RUNNING’ ]; then
mkdir -p $DATA_DIR
mkdir -p $ARC_DIR
$ORACLE_HOME/bin/rman target / log $BASE_DIR/log_full/rman_backup_level1_$ORACLE_SID”_”$DATE.log <<EOF
RUN
{
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE REDUNDANCY = 2;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 4096 M;
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM BACKUP TYPE TO BACKUPSET;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE FORMAT ‘$BACKUP_DATA’ FILESPERSET 32 TAG LEVEL1;
BACKUP AS COMPRESSED BACKUPSET FORMAT ‘$BACKUP_ARC’ ARCHIVELOG ALL TAG ARCH;
BACKUP CURRENT CONTROLFILE FORMAT ‘$BACKUP_CTR’;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE REDUNDANCY = 2;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME ‘SYSDATE-1’ BACKED UP 1 TIMES TO DISK;
}
exit;
EOF
echo $BACKUP_DIR >> $BASE_DIR/log_full/rman_backup_level1_$ORACLE_SID”_”$DATE.log
else
echo “—-> Backup RMAN running (RUNNING).”
fi
exit;
