Thứ Sáu, 25 tháng 12, 2020

Tự động Backup Oracle Database hàng ngày qua Crontab_New

Mục đích: Tự động Backup Oracle Database hàng ngày qua Crontab
1. Tạo script backup:
1.1. Tạo scritp backup level 0
$ cd ~/backup (hoặc cd /home/oracle/backup)
mkdir  logs
mkdir  level0
mkdir  level1

oracle@dbaviet-db02 # vi /home/oracle/backup/level0.rman
#################################
# Script for backup full database dbaviet
# Created Date 13/10/2020
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/level0/auto_dbaviet_ctl%F';
run {
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt expired archivelog all;
BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK INCREMENTAL LEVEL 0 DATABASE FORMAT '/home/oracle/backup/level0/dbaviet_%T_%d_%u_%s' FILESPERSET 10 MAXSETSIZE 80G TAG LEVEL0; 
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK ARCHIVELOG ALL FORMAT '/home/oracle/backup/level0/arc_%T_%d_%u_%s_%U' FILESPERSET 10 TAG ARCH;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';
#sql ' BEGIN TEST_SEND_MAIL_BACKUP; END; ';
}
EXIT;
oracle@dbaviet-db02 # vi /home/oracle/backup/level0.sh 
#################################
# Script for backup full database dbaviet
# Created Date 13/10/2020
#su - oracle
logfile=`date +%Y%m%d`_level0.log 
export ORACLE_SID=db12c
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
rman target / nocatalog cmdfile /home/oracle/backup/level0.rman  log /home/oracle/backup/logs/$logfile
exit


1.2. Tạo script backup level 1

oracle@dbaviet-db02 # vi /home/oracle/backup/level1.rman
#################################
# Script for backup incremental database dbaviet
# Created Date 13/10/2020
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/level1/auto_dbaviet_ctl%F';
run {
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt expired archivelog all;
BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK INCREMENTAL LEVEL 1 DATABASE FORMAT '/home/oracle/backup/level1/dbaviet_%T_%d_%u_%s' FILESPERSET 10 MAXSETSIZE 80G TAG LEVEL1; 
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK ARCHIVELOG ALL FORMAT '/home/oracle/backup/level1/arc_%T_%d_%u_%s_%U' FILESPERSET 10 TAG ARCH;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';
#sql ' BEGIN TEST_SEND_MAIL_BACKUP; END; ';
}
EXIT;

oracle@dbaviet-db02 # vi  /home/oracle/backup/level1.sh
#################################
# Script for backup incremental database dbaviet
# Created Date 13/10/2020
logfile=`date +%Y%m%d`_level1.log 
export ORACLE_SID=db12c
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
rman target / nocatalog cmdfile /home/oracle/backup/level1.rman  log /home/oracle/backup/logs/$logfile
exit

2. Phân quyền chạy 
$ cd /home/oracle/backup/

$ chmod +x *.sh

Yêu cầu phải có đầy đru các file sau:

(file swp bỏ qua)

3. Tạo crontab backup tự động trên OS, user oracle
#########backupdb_dbaviet########
Vào bằng user oracle

(oracle)$ crontab -e

20 22 * * 0,3 /home/oracle/backup/level0.sh             #22h ngày thứ 4 và chủ nhật backup level 0
20 22 * * 1,2,4,5,6 /home/oracle/backup/level1.sh    #Các ngày còn lại backup level 1

Sau đó lưu lại bằng phím Esc --> :wq (write and quite)

Kiểm tra lại nếu có 2 dòng như trên là thành công:

(oracle)$ crontab -l 

Kiểm tra log lịch chạy bằng crontab sinh ra:

oot@ol7 log]# tail -1000f /var/log/cron
Oct  1 21:22:03 ol7 run-parts(/etc/cron.daily)[9660]: finished logrotate
Oct  1 21:22:03 ol7 run-parts(/etc/cron.daily)[9648]: starting man-db.cron
Oct  1 21:22:07 ol7 run-parts(/etc/cron.daily)[9720]: finished man-db.cron
Oct  1 21:22:07 ol7 run-parts(/etc/cron.daily)[9648]: starting mlocate
Oct  1 21:22:09 ol7 run-parts(/etc/cron.daily)[9733]: finished mlocate
Oct  1 21:22:09 ol7 anacron[4831]: Job `cron.daily' terminated (produced output)
Oct  1 21:30:01 ol7 CROND[11327]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Oct  1 21:40:01 ol7 CROND[13559]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Oct  1 21:42:03 ol7 anacron[4831]: Job `cron.weekly' started
Oct  1 21:42:03 ol7 anacron[4831]: Job `cron.weekly' terminated
Oct  1 21:42:03 ol7 anacron[4831]: Normal exit (2 jobs run)
Oct  1 21:50:01 ol7 CROND[15624]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Oct  1 22:00:01 ol7 CROND[17775]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Oct  1 22:01:01 ol7 CROND[17980]: (root) CMD (run-parts /etc/cron.hourly)
Oct  1 22:01:01 ol7 run-parts(/etc/cron.hourly)[17980]: starting 0anacron
Oct  1 22:01:01 ol7 run-parts(/etc/cron.hourly)[17996]: finished 0anacron
Oct  1 22:01:01 ol7 run-parts(/etc/cron.hourly)[17980]: starting mcelog.cron
Oct  1 22:01:01 ol7 run-parts(/etc/cron.hourly)[18002]: finished mcelog.cron
Oct  1 22:10:01 ol7 CROND[19960]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Oct  1 22:15:36 ol7 crontab[21130]: (oracle) BEGIN EDIT (oracle)
Oct  1 22:15:48 ol7 crontab[21130]: (oracle) REPLACE (oracle)
Oct  1 22:15:48 ol7 crontab[21130]: (oracle) END EDIT (oracle)
Oct  1 22:15:52 ol7 crontab[21188]: (oracle) LIST (oracle)
Oct  1 22:17:08 ol7 crontab[21516]: (oracle) BEGIN EDIT (oracle)
Oct  1 22:17:18 ol7 crontab[21516]: (oracle) REPLACE (oracle)
Oct  1 22:17:18 ol7 crontab[21516]: (oracle) END EDIT (oracle)
Oct  1 22:18:01 ol7 crond[3184]: (oracle) RELOAD (/var/spool/cron/oracle)
Oct  1 22:20:01 ol7 CROND[22178]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Oct  1 22:20:01 ol7 CROND[22179]: (oracle) CMD (/home/oracle/backup/level1.sh    #C\303\241ng\303\240c\303\262?i backup level 1)
Oct  1 22:20:47 ol7 crontab[22437]: (oracle) LIST (oracle)
Oct  1 22:20:58 ol7 crontab[22469]: (oracle) BEGIN EDIT (oracle)
Oct  1 22:21:14 ol7 crontab[22469]: (oracle) REPLACE (oracle)
Oct  1 22:21:14 ol7 crontab[22469]: (oracle) END EDIT (oracle)
Oct  1 22:21:22 ol7 crontab[22533]: (oracle) LIST (oracle)
Oct  1 22:21:36 ol7 crontab[22583]: (oracle) LIST (oracle)
Oct  1 22:22:01 ol7 crond[3184]: (oracle) RELOAD (/var/spool/cron/oracle)


4. Kiểm tra lại bản backup

RMAN > list backup;

RMAN> list backup summary;

RMAN> list backup of datafile 1;

SQL> select command_id, start_time, end_time, status,INPUT_TYPE, input_bytes_display, output_bytes_display, time_taken_display, round(compression_ratio,2) RATIO , input_bytes_per_sec_display, output_bytes_per_sec_display
from v$rman_backup_job_details 
where trunc(end_time)>=trunc(sysdate-120)
order by end_time desc; 


Chúc các bạn thành công.
==================================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
#OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master