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 恢復表空間】 實驗Oracle
- Oracle 12C新特性-RMAN恢復表Oracle
- Oracle RMAN恢復測試Oracle
- 表空間TSPITR恢復-實驗
- Oracle 不完全恢復Oracle
- G017-ORACLE-MIGRATION-01 RMAN備份異機不完全恢復Oracle
- SYSTEM 表空間管理及備份恢復
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- Oracle 備份恢復篇之RMAN catalogOracle
- ORACLE DG從庫 Rman備份恢復Oracle
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- Oracle RMAN備份為什麼會大量使用temp表空間？Oracle
- Oracle表空間Oracle
- oracle 表空間Oracle
- RMAN深入解析之--Incarnation應用（不完全恢復）
- oracle表空間的整理Oracle
- 12C PDB使用RMAN的4種完全恢復場景
- Oracle 12C RMAN備份佔用大量臨時表空間Oracle
- RMAN增量恢復
- 增加oracle表空間Oracle
- oracle temp 表空間Oracle
- Oracle單個資料檔案損壞，在Rman命令裡設定表空間、資料檔案offline方式來恢復最方便Oracle
- 【RMAN】Oracle12c以後rman 備份恢復命令參考Oracle
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- [20210527]rman與undo表空間備份.txt
- Oracle9i RMAN 的優缺點及RMAN 備份及恢復步驟Oracle
- RMAN恢復實踐
- Oracle 批量建表空間Oracle
- Oracle清理SYSAUX表空間OracleUX
- Oracle的表空間quota詳解Oracle
- Oracle OCP(47)：表空間的建立Oracle
- oracle ORA-01180 ORA-01110（rman恢復問題）Oracle
- ORACLE 11.2.0.4 RAC RMAN異機恢復之ORA-15001Oracle
- NBU恢復Oracle通道完成後RMAN沒有進度Oracle
- [20190718]12c rman新特性 表恢復.txt
- RMAN恢復之RMAN-06555處理
- Oracle 不完全恢復遇到的ORA-600錯誤Oracle