oracle，RMAN定時備份shell腳本，恢復步驟及恢復示例
一、環境配置
- 開啟歸檔日志
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
#查看歸檔日志是否開啟 Automatic archival Enabled
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 119
Next log sequence to archive 121
Current log sequence 121
- 配置rman備份引數
su - oracle
rman target/
#開啟控制檔案自動備份（當出現資料庫結構改變時，RMAN會自動備份控制檔案，如創建表空間，洗掉表空間等，注，控制檔案有延遲自動備份，默認300s）
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
#控制檔案備份路徑，格式
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rmanbak/controlfile/%F';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/rmanbak/controlfile/backup%U';
- 查看所有設定
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rmanbak/controlfile/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/rmanbak/controlfile/backup%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
二、備份腳本
#/bin/bash
rmanbakDir=/u01/rmanbak
#資料檔案備份目錄
rmanbakDatafile=$rmanbakDir/datafile
#控制檔案備份目錄
rmanbakControlfile=$rmanbakDir/controlfile
#日志輸出路徑
logDir=$rmanbakDir/log
backtime=`date +%Y%m%d`
isDirectory(){
if test -d $1
then
echo "$1目錄存在!"
else
echo "$1目錄不存在!,創建目錄!"
mkdir -p $1
fi
}
rmanbak(){
echo "rman備份開始!"
su - oracle -c "
rman target / <<EOF
run{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
#手動備份當前控制檔案
backup current controlfile;
#資料檔案備份路徑，格式
backup database format '$1/orcl_%d_%T_%U';
#歸檔日志檔案備份路徑，格式
backup archivelog all format '$1/orcl_arch_%t_%s_%c' delete input;
report obsolete;
delete noprompt obsolete;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
release channel ch1;
release channel ch2;
}
exit
EOF"
echo "rman備份完成!"
}
removeLog(){
echo "洗掉過期日志！"
find $1 -type f -name "*.log" -mtime +7 -exec rm -rf {} \;
}
log(){
tee -a $logDir/rmanBackupall_$backtime.log
}
chown(){
echo "更改目錄用戶名用戶組！"
su root -c "chown -R oracle:oinstall $1"
}
startRmanbak(){
removeLog $logDir | log
isDirectory $rmanbakDatafile | log
isDirectory $rmanbakControlfile | log
isDirectory $logDir | log
rmanbak $rmanbakDatafile | log
chown $rmanbakDir/ | log
}
startRmanbak
- 查看剛剛完成的備份情況
RMAN> list backupset summary;
RMAN> list backupset ;
RMAN> list backup of controlfile ;
RMAN> list backup of archivelog all;
RMAN> list backup of database;
RMAN> list backup of datafile n ;
三、rman資料恢復
- 表空間恢復步驟
#注:RMAN命令執行報錯，可以先exit，再重新登陸rman
su - oracle
rman target/
RMAN>restore database;
RMAN>recover database;
- 控制檔案恢復步驟
SQL>shutdown abort;
SQL>startup force nomount;
RMAN>list backup of controlfile;
RMAN>restore controlfile to 'xxx.ctl' from 'xxx/c-xx-xx-xx';
SQL>alter database mount;
RMAN> recover database;
SQL> alter database open resetlogs;
四、舉個栗子
- 恢復表空間dbf檔案
#查看資料檔案存放位置
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/oracle/tools/oracle11g/oradata/orcl/imagedb.dbf
#將imagedb.dbf檔案改名 imagedbbak.dbf
-rw-r----- 1 oracle oinstall 9846784 Nov 12 16:41 control01.ctl
-rw-r----- 1 oracle oinstall 209723392 Nov 12 14:48 imagedbbak.dbf
-rw-r----- 1 oracle oinstall 52436992 Nov 2 17:46 imagedbtemp.dbf
-rw-r----- 1 oracle oinstall 52429312 Nov 12 16:40 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Nov 12 16:28 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 12 16:28 redo03.log
-rw-r----- 1 oracle oinstall 723525632 Nov 12 16:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 734011392 Nov 12 16:38 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Nov 11 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 78651392 Nov 12 16:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Nov 12 16:28 users01.dbf
#關閉資料庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#啟動資料庫報錯
SQL> startup;
ORACLE instance started.
Total System Global Area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 3959424976 bytes
Database Buffers 2684354560 bytes
Redo Buffers 34922496 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oracle/tools/oracle11g/oradata/orcl/imagedb.dbf'
RMAN> restore database;
Starting restore at 12-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3966 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/tools/oracle11g/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/tools/oracle11g/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/tools/oracle11g/oradata/orcl/imagedb.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/datafile/orcl_ORCL_20201112_0gvfblen_1_1
channel ORA_DISK_1: piece handle=/u01/rmanbak/datafile/orcl_ORCL_20201112_0gvfblen_1_1 tag=TAG20201112T161415
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/tools/oracle11g/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/tools/oracle11g/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/datafile/orcl_ORCL_20201112_0hvfblen_1_1
channel ORA_DISK_1: piece handle=/u01/rmanbak/datafile/orcl_ORCL_20201112_0hvfblen_1_1 tag=TAG20201112T161415
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 12-NOV-20
RMAN> recover database;
Starting recover at 12-NOV-20
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-NOV-20
SQL>alter database open;
Database altered.
imagedb.dbf檔案恢復情況查看
- 恢復控制檔案
su - oracle
sqlplus / as sysdba
#1.強制關閉資料庫
SQL> shutdown abort;
#2.強制啟動資料庫到nomount狀態，
SQL>startup force nomount;
#3.另開一個視窗,查看已備份的控制檔案
su - oracle
rman target/
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 9.45M DISK 00:00:00 12-NOV-20
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20201112T161420
Piece Name: /u01/rmanbak/controlfile/c-1582243166-20201112-03
Control File Included: Ckp SCN: 2581400 Ckp time: 12-NOV-20
#4.恢復控制檔案到指定的路徑，（to 要恢復的控制檔案 from 已備份的控制檔案）
#查看控制檔案位置SQL>select name from v$datafile;，
RMAN>restore controlfile to '/u01/oracle/tools/oracle11g/oradata/orcl/control01.ctl' from '/u01/rmanbak/controlfile/c-1582243166-20201112-03';
RMAN>restore controlfile to '/u01/oracle/tools/oracle11g/flash_recovery_area/orcl/control02.ctl' from '/u01/rmanbak/controlfile/c-1582243166-20201112-03';
#5.在sql視窗，裝載資料庫
SQL>alter database mount;
Database altered.
#6.恢復資料庫
RMAN> recover database;
#7.打開資料庫
SQL> alter database open resetlogs;
database opened
五、定時任務添加
[root@OCR-DEVDB u01]crontab -e
#每天凌晨4點執行，腳本/u01/rman_backup.sh
00 04 * * * /u01/rman_backup.sh
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/220891.html
標籤：python
上一篇：MySQL練習