Efficiency By Design when Creating Backup Strategies

Posted in: Technical Track

Most backup implementations to not take log management into consideration.

Example: Have you seen thousands of backup logs or backup log dating back to 2017?
Is it necessary? Worse, paged in the middle of the night for disk filling up.

I will share backup implementation and log management for database emrep.

Recovery Point Objective (RPO) is RECOVERY WINDOW OF 1 DAYS.
RMAN catalog is being used.

Spool error to a different file for crontab.


$ crontab -l
# Backup database daily at 1am
0 1 * * * /u01/scripts/rman/rman_db.ksh emrep 0 100 > /tmp/emrep_rman_db.out 2>/tmp/emrep_rman_db.err
# Backup archivelog every 4 hour
0 */4 * * * /u01/scripts/rman/rman_arch.ksh env_emrep > /tmp/emrep_rman_arch.out 2>/tmp/emrep_rman_arch.err

Zero byte from .err file indicates there are no errors.


$ ll /tmp/*rman*
-rw-r--r-- 1 oracle oinstall 0 Sep 17 09:39 /tmp/emrep_rman_arch.err
-rw-r--r-- 1 oracle oinstall 897 Sep 17 09:39 /tmp/emrep_rman_arch.out
-rw-r--r-- 1 oracle oinstall 0 Sep 17 01:00 /tmp/emrep_rman_db.err
-rw-r--r-- 1 oracle oinstall 640 Sep 17 01:00 /tmp/emrep_rman_db.out

Manage log by overwriting.

Log indicates exact script ran prefixed with ORACLE_SID (passed to script)

$ ls -lrt /u01/app/oracle/admin/emrep/scripts/log
total 292
-rw-r--r-- 1 oracle oinstall 16039 Sep 12 12:44 emrep.rman_db.ksh.log.Wed
-rw-r--r-- 1 oracle oinstall 9248 Sep 12 13:18 emrep.rman_arch.ksh.log.Wed13
-rw-r--r-- 1 oracle oinstall 13609 Sep 14 10:41 emrep.rman_arch.ksh.log.Fri10
-rw-r--r-- 1 oracle oinstall 8033 Sep 14 16:00 emrep.rman_arch.ksh.log.Fri16
-rw-r--r-- 1 oracle oinstall 7891 Sep 14 20:00 emrep.rman_arch.ksh.log.Fri20
-rw-r--r-- 1 oracle oinstall 8321 Sep 15 00:00 emrep.rman_arch.ksh.log.Sat00
-rw-r--r-- 1 oracle oinstall 11280 Sep 15 01:01 emrep.rman_db.ksh.log.Sat
-rw-r--r-- 1 oracle oinstall 10525 Sep 15 04:00 emrep.rman_arch.ksh.log.Sat04
-rw-r--r-- 1 oracle oinstall 10738 Sep 15 08:00 emrep.rman_arch.ksh.log.Sat08
-rw-r--r-- 1 oracle oinstall 11217 Sep 15 12:00 emrep.rman_arch.ksh.log.Sat12
-rw-r--r-- 1 oracle oinstall 11437 Sep 15 16:00 emrep.rman_arch.ksh.log.Sat16
-rw-r--r-- 1 oracle oinstall 11656 Sep 15 20:00 emrep.rman_arch.ksh.log.Sat20
-rw-r--r-- 1 oracle oinstall 11813 Sep 16 00:00 emrep.rman_arch.ksh.log.Sun00
-rw-r--r-- 1 oracle oinstall 19085 Sep 16 01:01 emrep.rman_db.ksh.log.Sun
-rw-r--r-- 1 oracle oinstall 11284 Sep 16 04:00 emrep.rman_arch.ksh.log.Sun04
-rw-r--r-- 1 oracle oinstall 11285 Sep 16 08:00 emrep.rman_arch.ksh.log.Sun08
-rw-r--r-- 1 oracle oinstall 10937 Sep 16 12:00 emrep.rman_arch.ksh.log.Sun12
-rw-r--r-- 1 oracle oinstall 11224 Sep 16 16:00 emrep.rman_arch.ksh.log.Sun16
-rw-r--r-- 1 oracle oinstall 11440 Sep 16 20:00 emrep.rman_arch.ksh.log.Sun20
-rw-r--r-- 1 oracle oinstall 11597 Sep 17 00:00 emrep.rman_arch.ksh.log.Mon00
-rw-r--r-- 1 oracle oinstall 18469 Sep 17 01:01 emrep.rman_db.ksh.log.Mon
-rw-r--r-- 1 oracle oinstall 11284 Sep 17 04:00 emrep.rman_arch.ksh.log.Mon04
-rw-r--r-- 1 oracle oinstall 11289 Sep 17 08:00 emrep.rman_arch.ksh.log.Mon08

Based on RPO:


Keep 7 logs for daily database backup.
Keep 42 logs for achivelog backup (6 daily x 7 days)

Echo RMAN Backup Log to output as this will make troubleshooting more efficient.

Have you ever had to dig through a backup script to file logfile?

$ cat /tmp/emrep_rman_arch.out
The Oracle base remains unchanged with value /u01/app/oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/lib:/lib:/usr/lib
NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
ORACLE_SID=emrep
PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/usr/local/bin:/home/oracle/bin:/usr/local/bin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin
Oracle Instance alive for sid "emrep"

==============================
RMAN Backup Log
/u01/app/oracle/admin/emrep/scripts/log/emrep.rman_arch.ksh.log.Mon09
==============================

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34>

In conclusion, when designing backup strategies, be sure to take into consideration log usability, scalability, and efficiency.

email

Interested in working with Michael? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *