備份與恢復系列 八 丟失所有資料檔案的還原與恢復
RMAN執行塊中用於恢復的set選項
為資料檔案設定新名稱
為特定的資料檔案制定新的檔名和路徑。如果沒有定義該項，則RMAN將檔案還原到其當前的路徑和檔名。
設定歸檔日誌路徑
為還原和恢復命令的歸檔日誌檔案定義不同的路徑。如果沒有定義該項，RMAN使用目標init.ora檔案定義的第一個歸檔儲存的路徑。
set until
為恢復定義停止點。不完全恢復可以完成到指定時間、日誌序列號或者SCN。如果沒有定義該項，RMAN將還原基準備份，並用增量備份和重做將所有的資料檔案恢復到當前狀態
環境變數對還原和恢復起著重要的作用。有時候需要設定環境變數使回話的字符集與資料庫的字符集保持一致。設定NLS_LANG環境變數使它與目標資料庫的字符集匹配。如果在RMAN的LIST, REPORT, SET UNTIL等命令中要使用特定的時間，則應該設定NLS_DATE環境變數與命令的日期字串匹配。也可以在指令碼中使用TO_DATE函式指向特定的日期。
export NLS_LANG=AMERICA_AMERICA.WE8ISO8859P1
export NLS_DATE_FORMAT=YYYY-MM-DD:HH24:DD:SS
校準當前的作業系統日期
date -s "2014-08-27 09:26:00"
練習1 還原整個資料庫
假設場景如下：
全部資料檔案丟失
控制檔案存在
聯機重做日誌存在
實驗步驟如下：
執行整個資料庫備份
模擬PRACTICE資料檔案丟失
還原與恢復資料庫
驗證還原與恢復的資料庫
1，執行整個資料庫備份
export ORACLE_SID=PRACTICE
rman target /
run{
allocate channel c1 type disk maxpiecesize 1000m;
allocate channel c2 type disk maxpiecesize 1000m;
backup incremental level 0
tag = 'whole_in0'
format = '/backup/%d_in0_%s_%p_%t' database;
sql 'alter system archive log current';
backup archivelog all
tag 'arch_bak'
format = '/backup/%d_arch_%t_%U.bak';
backup current controlfile
tag 'auto_ctl'
format ='/backup/%d_auto_ctl_%t_%U.bak';
release channel c1;
release channel c2;
}
sqlplus / as sysdba
conn snow/snow
insert into date_log values(sysdate+10,'Mini');
commit;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level 1
format '/backup/db_in1_%d_%s_%p_%t'
tag='whole_inc1'
database;
release channel c1;
release channel c2;
}
確認date_log表所在表空間
conn snow/snow
select TABLE_NAME,TABLESPACE_NAME from user_tables where table_name='DATE_LOG';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DATE_LOG TOOLS
確認資料檔案位置
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/PRACTICE/system01.dbf
/oradata/PRACTICE/sysaux01.dbf
/oradata/PRACTICE/undotbs01.dbf
/oradata/PRACTICE/users01.dbf
/oradata/PRACTICE/example01.dbf
/oradata/tools01.dbf
/oradata/indx01.dbf
2 模擬資料庫失效
RMAN> shutdown abort
RMAN> host;
[oracle@practice3 ~]$ rm /oradata/PRACTICE/*.dbf
[oracle@practice3 ~]$ ls -l /oradata/PRACTICE/
total 173436
drwxr-xr-x 2 oracle oinstall 4096 Jul 30 12:00 archive
drwxr-xr-x 2 oracle oinstall 4096 Jul 30 12:00 backup
-rw-r----- 1 oracle oinstall 10043392 Aug 27 09:36 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Aug 17 02:28 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 27 09:36 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 17 02:28 redo03.log
-rw-r----- 1 oracle oinstall 10043392 Aug 27 09:30 snap_PRACTICE.ctl
[oracle@practice3 ~]$ exit
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/27/2014 09:38:51
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oradata/PRACTICE/system01.dbf'
3,還原與恢復資料庫
RMAN>shutdown abort;
RMAN>startup mount;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
recover database;
}
allocated channel: c1
channel c1: SID=133 device type=DISK
allocated channel: c2
channel c2: SID=10 device type=DISK
Starting restore at 2014/08/27 09:40:25
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /oradata/PRACTICE/sysaux01.dbf
channel c1: restoring datafile 00003 to /oradata/PRACTICE/undotbs01.dbf
channel c1: restoring datafile 00006 to /oradata/tools01.dbf
channel c1: restoring datafile 00007 to /oradata/indx01.dbf
channel c1: reading from backup piece /backup/PRACTICE_in0_48_1_855800878
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /oradata/PRACTICE/system01.dbf
channel c2: restoring datafile 00004 to /oradata/PRACTICE/users01.dbf
channel c2: restoring datafile 00005 to /oradata/PRACTICE/example01.dbf
channel c2: reading from backup piece /backup/PRACTICE_in0_47_1_855800878
channel c1: piece handle=/backup/PRACTICE_in0_48_1_855800878 tag=WHOLE_IN0
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
channel c2: piece handle=/backup/PRACTICE_in0_47_1_855800878 tag=WHOLE_IN0
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:15
Finished restore at 2014/08/27 09:40:41
Starting recover at 2014/08/27 09:40:41
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oradata/PRACTICE/sysaux01.dbf
destination for restore of datafile 00003: /oradata/PRACTICE/undotbs01.dbf
destination for restore of datafile 00006: /oradata/tools01.dbf
destination for restore of datafile 00007: /oradata/indx01.dbf
channel c1: reading from backup piece /backup/db_in1_PRACTICE_56_1_856690214 <==
channel c2: starting incremental datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/PRACTICE/system01.dbf
destination for restore of datafile 00004: /oradata/PRACTICE/users01.dbf
destination for restore of datafile 00005: /oradata/PRACTICE/example01.dbf
channel c2: reading from backup piece /backup/db_in1_PRACTICE_55_1_856690214
channel c1: piece handle=/backup/db_in1_PRACTICE_56_1_856690214 tag=WHOLE_INC1
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel c2: piece handle=/backup/db_in1_PRACTICE_55_1_856690214 tag=WHOLE_INC1
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2014/08/27 09:40:43
released channel: c1
released channel: c2
RMAN> alter database open;
RMAN> host;
4，驗證還原與恢復的資料庫
[oracle@practice3 ~]$ sqlplus snow/snow
SNOW@PRACTICE >select * from date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/08/27 09:44:54 --
2014/08/27 09:45:54 --
2014/08/27 09:46:54 --
2014/08/27 09:47:54 --
2014/09/06 09:29:26 Mini
驗證結果
--未完待續--
為資料檔案設定新名稱
為特定的資料檔案制定新的檔名和路徑。如果沒有定義該項，則RMAN將檔案還原到其當前的路徑和檔名。
設定歸檔日誌路徑
為還原和恢復命令的歸檔日誌檔案定義不同的路徑。如果沒有定義該項，RMAN使用目標init.ora檔案定義的第一個歸檔儲存的路徑。
set until
為恢復定義停止點。不完全恢復可以完成到指定時間、日誌序列號或者SCN。如果沒有定義該項，RMAN將還原基準備份，並用增量備份和重做將所有的資料檔案恢復到當前狀態
環境變數對還原和恢復起著重要的作用。有時候需要設定環境變數使回話的字符集與資料庫的字符集保持一致。設定NLS_LANG環境變數使它與目標資料庫的字符集匹配。如果在RMAN的LIST, REPORT, SET UNTIL等命令中要使用特定的時間，則應該設定NLS_DATE環境變數與命令的日期字串匹配。也可以在指令碼中使用TO_DATE函式指向特定的日期。
export NLS_LANG=AMERICA_AMERICA.WE8ISO8859P1
export NLS_DATE_FORMAT=YYYY-MM-DD:HH24:DD:SS
校準當前的作業系統日期
date -s "2014-08-27 09:26:00"
練習1 還原整個資料庫
假設場景如下：
全部資料檔案丟失
控制檔案存在
聯機重做日誌存在
實驗步驟如下：
執行整個資料庫備份
模擬PRACTICE資料檔案丟失
還原與恢復資料庫
驗證還原與恢復的資料庫
1，執行整個資料庫備份
export ORACLE_SID=PRACTICE
rman target /
run{
allocate channel c1 type disk maxpiecesize 1000m;
allocate channel c2 type disk maxpiecesize 1000m;
backup incremental level 0
tag = 'whole_in0'
format = '/backup/%d_in0_%s_%p_%t' database;
sql 'alter system archive log current';
backup archivelog all
tag 'arch_bak'
format = '/backup/%d_arch_%t_%U.bak';
backup current controlfile
tag 'auto_ctl'
format ='/backup/%d_auto_ctl_%t_%U.bak';
release channel c1;
release channel c2;
}
sqlplus / as sysdba
conn snow/snow
insert into date_log values(sysdate+10,'Mini');
commit;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level 1
format '/backup/db_in1_%d_%s_%p_%t'
tag='whole_inc1'
database;
release channel c1;
release channel c2;
}
確認date_log表所在表空間
conn snow/snow
select TABLE_NAME,TABLESPACE_NAME from user_tables where table_name='DATE_LOG';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DATE_LOG TOOLS
確認資料檔案位置
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/PRACTICE/system01.dbf
/oradata/PRACTICE/sysaux01.dbf
/oradata/PRACTICE/undotbs01.dbf
/oradata/PRACTICE/users01.dbf
/oradata/PRACTICE/example01.dbf
/oradata/tools01.dbf
/oradata/indx01.dbf
2 模擬資料庫失效
RMAN> shutdown abort
RMAN> host;
[oracle@practice3 ~]$ rm /oradata/PRACTICE/*.dbf
[oracle@practice3 ~]$ ls -l /oradata/PRACTICE/
total 173436
drwxr-xr-x 2 oracle oinstall 4096 Jul 30 12:00 archive
drwxr-xr-x 2 oracle oinstall 4096 Jul 30 12:00 backup
-rw-r----- 1 oracle oinstall 10043392 Aug 27 09:36 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Aug 17 02:28 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 27 09:36 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 17 02:28 redo03.log
-rw-r----- 1 oracle oinstall 10043392 Aug 27 09:30 snap_PRACTICE.ctl
[oracle@practice3 ~]$ exit
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/27/2014 09:38:51
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oradata/PRACTICE/system01.dbf'
3,還原與恢復資料庫
RMAN>shutdown abort;
RMAN>startup mount;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
recover database;
}
allocated channel: c1
channel c1: SID=133 device type=DISK
allocated channel: c2
channel c2: SID=10 device type=DISK
Starting restore at 2014/08/27 09:40:25
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /oradata/PRACTICE/sysaux01.dbf
channel c1: restoring datafile 00003 to /oradata/PRACTICE/undotbs01.dbf
channel c1: restoring datafile 00006 to /oradata/tools01.dbf
channel c1: restoring datafile 00007 to /oradata/indx01.dbf
channel c1: reading from backup piece /backup/PRACTICE_in0_48_1_855800878
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /oradata/PRACTICE/system01.dbf
channel c2: restoring datafile 00004 to /oradata/PRACTICE/users01.dbf
channel c2: restoring datafile 00005 to /oradata/PRACTICE/example01.dbf
channel c2: reading from backup piece /backup/PRACTICE_in0_47_1_855800878
channel c1: piece handle=/backup/PRACTICE_in0_48_1_855800878 tag=WHOLE_IN0
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
channel c2: piece handle=/backup/PRACTICE_in0_47_1_855800878 tag=WHOLE_IN0
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:15
Finished restore at 2014/08/27 09:40:41
Starting recover at 2014/08/27 09:40:41
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oradata/PRACTICE/sysaux01.dbf
destination for restore of datafile 00003: /oradata/PRACTICE/undotbs01.dbf
destination for restore of datafile 00006: /oradata/tools01.dbf
destination for restore of datafile 00007: /oradata/indx01.dbf
channel c1: reading from backup piece /backup/db_in1_PRACTICE_56_1_856690214 <==
channel c2: starting incremental datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/PRACTICE/system01.dbf
destination for restore of datafile 00004: /oradata/PRACTICE/users01.dbf
destination for restore of datafile 00005: /oradata/PRACTICE/example01.dbf
channel c2: reading from backup piece /backup/db_in1_PRACTICE_55_1_856690214
channel c1: piece handle=/backup/db_in1_PRACTICE_56_1_856690214 tag=WHOLE_INC1
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel c2: piece handle=/backup/db_in1_PRACTICE_55_1_856690214 tag=WHOLE_INC1
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2014/08/27 09:40:43
released channel: c1
released channel: c2
RMAN> alter database open;
RMAN> host;
4，驗證還原與恢復的資料庫
[oracle@practice3 ~]$ sqlplus snow/snow
SNOW@PRACTICE >select * from date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014/08/27 09:44:54 --
2014/08/27 09:45:54 --
2014/08/27 09:46:54 --
2014/08/27 09:47:54 --
2014/09/06 09:29:26 Mini
驗證結果
--未完待續--
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1258611/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復系列 九 丟失表空間資料檔案的還原與恢復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- 備份與恢復--資料檔案損壞或丟失
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 備份恢復之資料檔案丟失
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 備份與恢復系列 十 引數檔案spfile的備份與恢復
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- 備份與恢復--利用備份的控制檔案恢復
- 恢復之利用備份在所有控制檔案丟失情況下恢復（四）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（三）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（二）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（一）
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- 備份與恢復（Parameter 檔案恢復篇）
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 完全恢復之所有資料庫檔案丟失資料庫
- 歸檔模式無備份丟失資料檔案後恢復模式
- 歸檔模式有備份丟失資料檔案後恢復模式
- 資料檔案丟失的恢復
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- RMAN恢復案例:丟失全部資料檔案恢復
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 【備份與恢復】恢復受損的口令檔案
- 【備份與恢復】archivelog模式中資料檔案的恢復Hive模式
- 【備份與恢復】noarchivelog模式中資料檔案的恢復Hive模式
- 【備份恢復】丟失所有控制檔案，利用RMAN進行恢復操作
- 備份＆恢復之八：RMAN備份歸檔模式下損壞（丟失）多個資料檔案，進行整個資料庫的恢復模式資料庫
- 備份與恢復--從備份的歸檔日誌中恢復資料
- 資料檔案丟失如何恢復
- 無備份丟失部分資料檔案和控制檔案恢復 [轉]