RMAN完全恢復丟失的資料檔案
前提條件:
丟失前做過RMAN備份,全庫的
控制檔案、歸檔、聯機日誌檔案完好無損
SYS@PROD>conn amy/amy Connected. AMY@PROD>create table t_amy02(b int);
Table created.
AMY@PROD>conn / as sysdba Connected. SYS@PROD>conn amy/amy Connected.
AMY@PROD>insert into t_amy02 values(11);
1 row created.
AMY@PROD>commit;
Commit complete.
AMY@PROD>select * from t_amy02;
B ---------- 11
-- 此時做rman的全庫備份 -- 下面再繼續插入資料
AMY@PROD>insert into t_amy02 values(12);
1 row created.
AMY@PROD>commit;
Commit complete.
AMY@PROD>select * from t_amy02;
B ---------- 11 12
AMY@PROD>conn / as sysdba Connected. SYS@PROD>alter system switch logfile;
System altered.
SYS@PROD>conn amy/amy Connected. AMY@PROD>insert into t_amy02 values(13);
1 row created.
AMY@PROD>commit;
Commit complete.
AMY@PROD>select * from t_amy02;
B ---------- 11 12 13
-- 以上三條記錄,11在rman備份裡,12在歸檔裡,13在聯機日誌裡 -- 接著刪除使用者AMY的預設表空間TBS_AMY的資料檔案'/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'
AMY@PROD>create table t_amy03(c int); create table t_amy03(c int) * ERROR at line 1: ORA-01116: error in opening database file 10 ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
--發現資料檔案丟失後,需要做RMAN恢復 --如果不關機直接做restore會失敗,報錯ORA-19573,需要shutdown immediate然後再做RMAN restore and recover RMAN> shutdown immediate
database closed database dismounted Oracle instance shut down
RMAN> startup mount
connected to target database (not started) Oracle instance started database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes Variable Size 79693200 bytes Database Buffers 230686720 bytes Redo Buffers 2973696 bytes
RMAN> restore database;
Starting restore at 23-JAN-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=287 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=285 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk1/example1.dbf restoring datafile 00007 to /u01/app/oracle/oradata/PROD/disk1/users1.dbf restoring datafile 00008 to /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf restoring datafile 00009 to /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/PROD_0jouptng_1_1 channel ORA_DISK_2: starting datafile backupset restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/PROD/disk1/system001.dbf restoring datafile 00003 to /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk1/indx1.dbf restoring datafile 00006 to /u01/app/oracle/oradata/PROD/disk1/tools1.dbf restoring datafile 00010 to /u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf channel ORA_DISK_2: reading from backup piece /home/oracle/backup/PROD_0iouptng_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/home/oracle/backup/PROD_0jouptng_1_1 tag=TAG20140123T142135 channel ORA_DISK_1: restore complete, elapsed time: 00:00:56 channel ORA_DISK_2: restored backup piece 1 piece handle=/home/oracle/backup/PROD_0iouptng_1_1 tag=TAG20140123T142135 channel ORA_DISK_2: restore complete, elapsed time: 00:01:04 Finished restore at 23-JAN-14
RMAN> recover database;
Starting recover at 23-JAN-14 using channel ORA_DISK_1 using channel ORA_DISK_2
starting media recovery media recovery complete, elapsed time: 00:00:03
Finished recover at 23-JAN-14
RMAN> alter database open;
database opened
AMY@PROD>conn / as sysdba Connected. SYS@PROD>conn amy/amy ERROR: ORA-03135: connection lost contact
ERROR: ORA-24315: illegal attribute type
Warning: You are no longer connected to ORACLE. @>conn / as sysdba ERROR: ORA-24313: user already authenticated
@>exit [oracle@odd-oelr4u8 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 23 14:41:14 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SYS@PROD>conn amy/amy Connected. AMY@PROD>select * from t_amy02; select * from t_amy02 * ERROR at line 1: ORA-00376: file 10 cannot be read at this time ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'
SYS@PROD>alter tablespace tbs_amy begin backup; alter tablespace tbs_amy begin backup * ERROR at line 1: ORA-01128: cannot start online backup - file 10 is offline ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'
SYS@PROD>alter database datafile 10 online;
Database altered.
SYS@PROD>alter tablespace tbs_amy begin backup;
Tablespace altered.
SYS@PROD>alter tablespace tbs_amy end backup;
Tablespace altered.
SYS@PROD>conn amy/amy Connected. AMY@PROD>select * from t_amy02;
B ---------- 11 12 13
-- 至此,完全恢復完成 |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26521853/viewspace-1083918/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- RMAN恢復案例:丟失全部資料檔案恢復
- 完全恢復之所有資料庫檔案丟失資料庫
- rman 恢復---歸檔丟失and資料檔案損壞
- RMAN恢復案例:丟失非系統資料檔案恢復
- 控制檔案丟失的RMAN恢復
- rman恢復--歸檔模式有備份,丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份,丟失資料檔案的恢復模式
- 資料檔案丟失的恢復
- 利用rman做資料檔案丟失的恢復實驗
- RMAN恢復案例:無恢復目錄,丟失全部資料檔案、控制檔案、日誌檔案恢復
- RMAN_部分資料檔案丟失或者損壞的恢復
- 資料檔案丟失如何恢復
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 全部控制檔案丟失後的完全恢復(轉)
- Oracle RMAN 不完全恢復(只有資料檔案備份,丟失歸檔日誌備份)Oracle
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- 普通資料檔案丟失的恢復方法
- 資料檔案丟失損壞的恢復--
- rman恢復:資料檔案丟失,控制檔案丟失,聯機日誌檔案丟失(非當前使用與當前使用)
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 資料庫所有檔案丟失後透過RMAN實現恢復資料庫
- 歸檔模式下資料檔案丟失的恢復模式
- 恢復案例:歸檔模式下丟失全部資料檔案的恢復模式
- 備份恢復之資料檔案丟失
- rman恢復--丟失控制檔案的恢復
- 只有rman備份集,控制檔案丟失的恢復
- 恢復丟失的控制檔案
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 引數檔案控制檔案和資料檔案丟失的恢復
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 資料檔案丟失的恢復(改變目錄)
- 撤消表空間資料檔案丟失的恢復.
- 資料庫各種檔案丟失恢復大全。資料庫
- RMAN - "丟失控制檔案的恢復"
- 探索ORACLE之RMAN_07 控制檔案丟失恢復Oracle
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 【恢復】Redo日誌檔案丟失的恢復