Oracle RMAN 表空間的完全恢復
如果資料庫開啟，有部分資料檔案損壞，可以在不影響其他資料檔案的情況下，對損壞的資料檔案進行恢復。
--對資料庫進行備份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
Starting backup at 2016-01-18 23:33:13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=4 STAMP=901495908
input archived log thread=1 sequence=9 RECID=5 STAMP=901495932
input archived log thread=1 sequence=10 RECID=6 STAMP=901495947
input archived log thread=1 sequence=11 RECID=7 STAMP=901496002
channel ORA_DISK_1: starting piece 1 at 2016-01-18 23:33:25
channel ORA_DISK_1: finished piece 1 at 2016-01-18 23:33:40
piece handle=/u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_annnn_TAG20160118T233324_c9t1g580_.bkp tag=TAG20160118T233324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_8_c9t1c26j_.arc RECID=4 STAMP=901495908
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_9_c9t1crdx_.arc RECID=5 STAMP=901495932
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_10_c9t1d6ym_.arc RECID=6 STAMP=901495947
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_11_c9t1g2w2_.arc RECID=7 STAMP=901496002
Finished backup at 2016-01-18 23:33:40
--檢視資料檔案
[oracle@localhost neo]$ ls -trl
總計 1728264
-rw-r----- 1 oracle oinstall 10493952 01-18 22:30 users02.dbf_bk
-rw-r----- 1 oracle oinstall 30416896 01-18 23:23 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 01-18 23:33 redo02.log
-rw-r----- 1 oracle oinstall 52429312 01-18 23:35 redo03.log
-rw-r----- 1 oracle oinstall 10493952 01-18 23:35 users02.dbf
-rw-r----- 1 oracle oinstall 145498112 01-18 23:35 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 01-18 23:35 example01.dbf
-rw-r----- 1 oracle oinstall 89137152 01-19 00:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 713039872 01-19 00:05 system01.dbf
-rw-r----- 1 oracle oinstall 503324672 01-19 00:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 01-19 00:07 redo01.log
-rw-r----- 1 oracle oinstall 9748480 01-19 00:07 control01.ctl
--將 USERS 表空間中的一個資料檔案改名
[oracle@localhost neo]$ mv users02.dbf users02.dbf_bk
--驗證資料檔案
RMAN> validate datafile 6;
Starting validate at 2016-01-19 00:09:47
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 01/19/2016 00:09:47
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/ORADATA/neo/users02.dbf'
ORA-01565: error in identifying file '/ORADATA/neo/users02.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--將損壞的表空間離線
RMAN> SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";
sql statement: ALTER TABLESPACE users OFFLINE IMMEDIATE
--檢視損壞的資料檔案
select name,status from v$datafile;
----------------------------------------------------------------- -------
/ORADATA/neo/system01.dbf SYSTEM
/ORADATA/neo/sysaux01.dbf ONLINE
/ORADATA/neo/undotbs01.dbf ONLINE
/ORADATA/neo/users01.dbf RECOVER
/ORADATA/neo/example01.dbf ONLINE
/ORADATA/neo/users02.dbf RECOVER
--進行表空間恢復
RMAN> RESTORE TABLESPACE users;
Starting restore at 2016-01-19 00:11:23
using channel ORA_DISK_1
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 00004 to /ORADATA/neo/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /ORADATA/neo/users02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T233340_c9t1gsmg_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T233340_c9t1gsmg_.bkp tag=TAG20160118T233340
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 2016-01-19 00:11:48
RMAN> RECOVER TABLESPACE users;
Starting recover at 2016-01-19 00:12:03
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2016-01-19 00:12:04
--將表空間置為線上狀態
RMAN> SQL "ALTER TABLESPACE users ONLINE";
sql statement: ALTER TABLESPACE users ONLINE
--檢視資料檔案的狀態
select name,status from v$datafile;
NAME STATUS
----------------------------------------------------------------- -------
/ORADATA/neo/system01.dbf SYSTEM
/ORADATA/neo/sysaux01.dbf ONLINE
/ORADATA/neo/undotbs01.dbf ONLINE
/ORADATA/neo/users01.dbf ONLINE
/ORADATA/neo/example01.dbf ONLINE
/ORADATA/neo/users02.dbf ONLINE
--下面是告警日誌中的資訊
[oracle@localhost trace]$ tailf alert_neo.log
Mon Jan 18 23:23:06 2016
Errors in file /u01/app/oracle/diag/rdbms/neo/neo/trace/neo_m001_3398.trc:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/ORADATA/neo/users02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jan 18 23:25:09 2016
ALTER TABLESPACE users OFFLINE IMMEDIATE
Completed: ALTER TABLESPACE users OFFLINE IMMEDIATE
Mon Jan 18 23:27:17 2016
Checker run found 2 new persistent data failures
Mon Jan 18 23:27:18 2016
Full restore complete of datafile 6 /ORADATA/neo/users02.dbf. Elapsed time: 0:00:00
checkpoint is 846683
Full restore complete of datafile 4 /ORADATA/neo/users01.dbf. Elapsed time: 0:00:01
checkpoint is 846683
Mon Jan 18 23:27:46 2016
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace USERS
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
Mem# 0: /ORADATA/neo/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
Mem# 0: /ORADATA/neo/redo02.log
Media Recovery Complete (neo)
Completed: alter database recover if needed
tablespace USERS
Mon Jan 18 23:28:42 2016
ALTER TABLESPACE users ONLINE
Completed: ALTER TABLESPACE users ONLINE
--對資料庫進行備份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
Starting backup at 2016-01-18 23:33:13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=4 STAMP=901495908
input archived log thread=1 sequence=9 RECID=5 STAMP=901495932
input archived log thread=1 sequence=10 RECID=6 STAMP=901495947
input archived log thread=1 sequence=11 RECID=7 STAMP=901496002
channel ORA_DISK_1: starting piece 1 at 2016-01-18 23:33:25
channel ORA_DISK_1: finished piece 1 at 2016-01-18 23:33:40
piece handle=/u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_annnn_TAG20160118T233324_c9t1g580_.bkp tag=TAG20160118T233324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_8_c9t1c26j_.arc RECID=4 STAMP=901495908
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_9_c9t1crdx_.arc RECID=5 STAMP=901495932
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_10_c9t1d6ym_.arc RECID=6 STAMP=901495947
archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_11_c9t1g2w2_.arc RECID=7 STAMP=901496002
Finished backup at 2016-01-18 23:33:40
--檢視資料檔案
[oracle@localhost neo]$ ls -trl
總計 1728264
-rw-r----- 1 oracle oinstall 10493952 01-18 22:30 users02.dbf_bk
-rw-r----- 1 oracle oinstall 30416896 01-18 23:23 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 01-18 23:33 redo02.log
-rw-r----- 1 oracle oinstall 52429312 01-18 23:35 redo03.log
-rw-r----- 1 oracle oinstall 10493952 01-18 23:35 users02.dbf
-rw-r----- 1 oracle oinstall 145498112 01-18 23:35 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 01-18 23:35 example01.dbf
-rw-r----- 1 oracle oinstall 89137152 01-19 00:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 713039872 01-19 00:05 system01.dbf
-rw-r----- 1 oracle oinstall 503324672 01-19 00:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 01-19 00:07 redo01.log
-rw-r----- 1 oracle oinstall 9748480 01-19 00:07 control01.ctl
--將 USERS 表空間中的一個資料檔案改名
[oracle@localhost neo]$ mv users02.dbf users02.dbf_bk
--驗證資料檔案
RMAN> validate datafile 6;
Starting validate at 2016-01-19 00:09:47
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 01/19/2016 00:09:47
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/ORADATA/neo/users02.dbf'
ORA-01565: error in identifying file '/ORADATA/neo/users02.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--將損壞的表空間離線
RMAN> SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";
sql statement: ALTER TABLESPACE users OFFLINE IMMEDIATE
--檢視損壞的資料檔案
----------------------------------------------------------------- -------
/ORADATA/neo/system01.dbf SYSTEM
/ORADATA/neo/sysaux01.dbf ONLINE
/ORADATA/neo/undotbs01.dbf ONLINE
/ORADATA/neo/users01.dbf RECOVER
/ORADATA/neo/example01.dbf ONLINE
/ORADATA/neo/users02.dbf RECOVER
RMAN> RESTORE TABLESPACE users;
Starting restore at 2016-01-19 00:11:23
using channel ORA_DISK_1
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 00004 to /ORADATA/neo/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /ORADATA/neo/users02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T233340_c9t1gsmg_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T233340_c9t1gsmg_.bkp tag=TAG20160118T233340
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 2016-01-19 00:11:48
RMAN> RECOVER TABLESPACE users;
Starting recover at 2016-01-19 00:12:03
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2016-01-19 00:12:04
RMAN> SQL "ALTER TABLESPACE users ONLINE";
sql statement: ALTER TABLESPACE users ONLINE
select name,status from v$datafile;
NAME STATUS
----------------------------------------------------------------- -------
/ORADATA/neo/system01.dbf SYSTEM
/ORADATA/neo/sysaux01.dbf ONLINE
/ORADATA/neo/undotbs01.dbf ONLINE
/ORADATA/neo/users01.dbf ONLINE
/ORADATA/neo/example01.dbf ONLINE
/ORADATA/neo/users02.dbf ONLINE
[oracle@localhost trace]$ tailf alert_neo.log
Mon Jan 18 23:23:06 2016
Errors in file /u01/app/oracle/diag/rdbms/neo/neo/trace/neo_m001_3398.trc:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/ORADATA/neo/users02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jan 18 23:25:09 2016
ALTER TABLESPACE users OFFLINE IMMEDIATE
Completed: ALTER TABLESPACE users OFFLINE IMMEDIATE
Mon Jan 18 23:27:17 2016
Checker run found 2 new persistent data failures
Mon Jan 18 23:27:18 2016
Full restore complete of datafile 6 /ORADATA/neo/users02.dbf. Elapsed time: 0:00:00
checkpoint is 846683
Full restore complete of datafile 4 /ORADATA/neo/users01.dbf. Elapsed time: 0:00:01
checkpoint is 846683
Mon Jan 18 23:27:46 2016
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace USERS
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
Mem# 0: /ORADATA/neo/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
Mem# 0: /ORADATA/neo/redo02.log
Media Recovery Complete (neo)
Completed: alter database recover if needed
tablespace USERS
Mon Jan 18 23:28:42 2016
ALTER TABLESPACE users ONLINE
Completed: ALTER TABLESPACE users ONLINE
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-1978740/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle RMAN 表空間恢復Oracle
- Oracle 11g RMAN恢復-只讀表空間的恢復Oracle
- rman恢復資料檔案 恢復表空間
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- 恢復Oracle表空間的方法Oracle
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- system表空間檔案損壞----完全恢復
- 【Oracle 恢復表空間】 實驗Oracle
- Oracle 11g RMAN恢復-只讀表空間的恢復（備份是在表空間只讀狀態下做的)Oracle
- 使用RMAN對PDB中的表空間或資料檔案執行完全恢復
- RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表delete
- Oracle 11g RMAN恢復-使用者誤刪除表空間Oracle
- 【TSPITR】RMAN表空間基於時間點的自動恢復
- 非系統表空間損壞，rman備份恢復
- Backup And Recovery User's Guide-執行完全資料庫恢復-執行表空間的完全恢復GUIIDE資料庫
- Oracle Rman 資料庫的不完全恢復Oracle資料庫
- Oracle rman 備份與恢復 臨時表空間的檔案問題解決Oracle
- Oracle 基於 RMAN 的不完全恢復(incomplete recovery by RMAN)Oracle
- RMAN全庫【完全恢復/不完全恢復brief version】
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- Oracle表空間時間點恢復技術TSPITROracle
- Oracle 傳輸表空間-RmanOracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(手動)）Oracle
- 用RMAN執行表空間及時點恢復——RMAN使用者手冊
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- SYSAUX表空間管理及恢復UX
- 刪除表空間,有rman全備的恢復(使用dbms_backup_restore來進行恢復)REST
- 根據表空間的TSPITR恢復
- Backup And Recovery User's Guide-從RMAN開始-恢復表空間GUIIDE
- 表空間TSPITR恢復-實驗
- 執行RMAN表空間及時點恢復——使用者管理備份和恢復手冊
- ORACLE 11g TSPITR恢復被刪除的表空間Oracle
- Backup And Recovery User's Guide-執行RMAN表空間時間點恢復（TSPIRT）GUIIDE
- 表空間不完全恢復（全備--備份控制檔案--刪除表空間andy--日誌檔案）
- flashback database 恢復誤刪除的表空間。Database
- UNDO 表空間檔案損壞的恢復
- 恢復表空間到不同的ASM磁碟組ASM