oracle,RMAN恢複資料庫資料到指定時間,RMAN不完全恢複shell腳本
RMAN備份配置,shell腳本見:https://blog.csdn.net/weixin_43614067/article/details/109647639
su - oracle
sqlplus / as sysdba
#設定時間格式
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
#檢視資料庫原型(目前原型為2)
SQL> SELECT INCARNATION#,STATUS,RESETLOGS_TIME FROM V$DATABASE_INCARNATION;
INCARNATION# STATUS RESETLOGS_TIME
------------ ------- -------------------
1 PARENT 2009-08-15 00:16:43
2 CURRENT 2020-10-22 10:13:52
3 ORPHAN 2020-11-13 09:31:38
4 ORPHAN 2020-11-16 17:09:12
5 ORPHAN 2020-11-16 17:11:07
6 ORPHAN 2020-11-16 17:25:57
7 ORPHAN 2020-11-16 17:29:44
8 ORPHAN 2020-11-16 17:34:25
9 ORPHAN 2020-11-16 17:39:11
10 ORPHAN 2020-11-16 17:51:56
11 ORPHAN 2020-11-16 17:54:40
INCARNATION# STATUS RESETLOGS_TIME
------------ ------- -------------------
12 ORPHAN 2020-11-16 17:57:59
12 rows selected.
#另起會話視窗
su - oracle
rman target/
#切換資料庫原型至11(需在mount狀态)
#強制關閉資料庫
#SQL>shutdown abort;
#将資料庫以mount方式啟動
#SQL>startup mount;
RMAN> reset database to incarnation 11;
using target database control file instead of recovery catalog
database reset to incarnation 11
#在rman檢視目前的資料庫原型(目前資料庫原型為11)
#orcl為資料庫名
RMAN> list incarnation of database "orcl";
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1582243166 PARENT 1 15-AUG-09
2 2 ORCL 1582243166 PARENT 945184 22-OCT-20
3 3 ORCL 1582243166 PARENT 2659636 13-NOV-20
4 4 ORCL 1582243166 PARENT 2858078 16-NOV-20
5 5 ORCL 1582243166 ORPHAN 2858279 16-NOV-20
6 6 ORCL 1582243166 PARENT 2858279 16-NOV-20
7 7 ORCL 1582243166 ORPHAN 2858501 16-NOV-20
10 10 ORCL 1582243166 ORPHAN 2858501 16-NOV-20
8 8 ORCL 1582243166 ORPHAN 2858569 16-NOV-20
11 11 ORCL 1582243166 CURRENT 2858575 16-NOV-20
12 12 ORCL 1582243166 ORPHAN 2858575 16-NOV-20
9 9 ORCL 1582243166 ORPHAN 2878961 16-NOV-20
恢複資料庫資料至指定時間
#恢複資料庫資料至指定時間
#在第一步中查到資料庫原型11的具體日期 11 ORPHAN 2020-11-16 17:54:40
#切換資料庫原型至11後 11 CURRENT 2020-11-16 17:54:40;
#注意:下面指令中time='2020-11-16 17:55:00'隻能在目前資料庫原型時間之後。
#若在目前時間前,會報UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time。
#舉個栗子:time='2020-11-16 17:53:00'時間在11号原型時間2020-11-16 17:54:40之前,
#就會報UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
RMAN>run {
shutdown abort;
startup mount;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time='2020-11-16 17:55:00';
restore database;
recover database;
alter database open resetlogs;
}
rman不完全恢複shell腳本
#!/bin/bash
rmanbakDir=/u01/rmanbak
#日志輸出路徑
logDir=$rmanbakDir/log
recoverdata(){
echo "恢複資料到日期:${1}"
su - oracle -c "
rman target / <<EOF
run{
shutdown abort;
startup mount;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
sql 'alter session set nls_date_format=\"yyyy-mm-dd hh24:mi:ss\"';
set until time='${1}';
restore database;
recover database;
alter database open resetlogs;
}
exit;
EOF"
opendatabase
}
startRecover(){
read -p "請輸入恢複到的日期(如2020-11-16 17:55:00):" inputdate
if [ -z '$inputdate' ]
then
echo "輸入日期為空,請重新輸入!"
startRecover
else
echo "輸入參數為:$inputdate"
date -d "$inputdate" +%Y-%m-%d\ %H:%M:%S
if test $? -eq 0
then
echo "輸入的日期格式合法!"
recoverdata "${inputdate}" | log
else
echo "輸入的日期格式不合法,請重新輸入!"
startRecover
fi
fi
}
opendatabase(){
if test $? -ne 0
then
echo "rman恢複非正常運作,重新打開資料庫!"
su - oracle -c "
sqlplus / as sysdba <<EOF
alter database open;
exit;
EOF"
else
echo "rman恢複正常運作,資料庫已啟動!"
fi;
}
log(){
tee -a $logDir/rmanrecover_$backtime.log
}
isDirectory(){
if test -d $1
then
echo "日志目錄:$1目錄已存在!"
else
echo "日志目錄:$1目錄不存在!,建立目錄!"
mkdir -p $1
fi
}
isDirectory $logDir | log
startRecover | log