資料庫各種檔案丟失恢復大全。

SPFILE丟失:
startup nomount;
set dbid 3988862108;
restore spfile from autobackup;
shutdown immediate;
set dbid 3988862108;
startup;

 

模擬操作:
oracle> mv spfileora10g.ora spora10g.ora
oracle>rman target /;
rman> shutdown immediate;
rman> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/product/10.20/dbs/initora10g.ora'
rman>set dbid 3988862108;
rman>restore spfile from autobackup;

執行該命令，如果沒有找到的話，那可能是檔案的路徑發生錯誤.可以透過直接賦予它的檔案
rman>restore spfile from '/u01/oracle/flash_recovery_area/ORA10G/autobackup/2008_12_09/o1_mf_s_673025706_4mw7xc79_.bkp

在dbs/目錄下產生spfileora10g.ora檔案。證明spfile 已經恢復好

rman> shutdown immediate;
rman> startup ;(如果該命令不能夠啟動資料庫，那麼需要set dbid 3988862108)

controlfile 丟失:
startup nomount;
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open resetlogs;

注意:在做了alter database open resetlogs;會把online redelog file清空，資料檔案丟失.所以這個時候要做一個全備份。

oracle>rm *.ctl
oracle>rman target / ;//不能夠連線到rman ,因為controlfile丟失
oracle>sqlplus /nolog;

SQL>shutdown immediate; //因為controlfile丟失，不能夠正常shutdown
SQL>shutdown abort;

oracle>rman target /;

rman>startup nomount;
rman>restore controlfile from autobackup;
rman>alter database mount;
rman>alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/09/2008 16:21:13
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oradata/ora10g/system01.dbf

//出錯, redo log的scn記錄在controlfile裡面的，因為我們有新的controlfile,所以需要resetlogs;
 
/*
resetlogs命令表示一個資料庫邏輯生存期的結束和另一個資料庫邏輯生存期的開始，每次使用resetlogs命令的時候，SCN不會被重置，不過oracle會重置日誌序列號，而且會重置

聯機重做日誌內容.
這樣做是為了防止不完全恢復後日志序列會發生衝突（因為現有日誌和資料檔案間有了時間差）。
*/
rman>recover database;
rman>alter database open resetlogs;

Redolog file丟失:(下面的這些語句一定要在sqlplus中執行,不是在rman中執行)
 (sqlplus/nolog)
1.shutdown immediate;
2.startup mount;
3.recover database until cancel;(media recovery)
4.alter database resetlogs;

資料檔案丟失(在rman中執行sql語句，在sql後面用雙引號括起來):
1. sql "alter database datafile 3 offline";
2. restore datafile 3
3. recover datafile 3
4. sql "alter database datafile 3 online";

 

表空間丟失:
1. sql "alter tablespace users offline";//如果檔案不存在，則用 sql "alter tablespace users offline immeidate";
2. restore tablespace users;
3. recover tablespace users; //與online redolog file 資訊一致
4. sql "alter tablespace users online";

 

1.startup nomount;
2.restore controlfile from autobackup;
3.alter database mount;
4.restore database;
5.recover database;
6.alter database open resetlogs;

 

模擬操作:
oracle ora10g> rm *;
oracle ora10g> ls;
oracle ora10g>  //資料檔案，控制檔案全部刪除

oracle ora10g> rman target /; //因為controlfile 丟失，不能夠連線到rman
oracle ora10g> sqlplus /nolog;
oracle ora10g> connect / as sysdba;
oracle ora10g> shutdown abort;
oracle ora10g> rman target /

 

rman> startup nomount;
rman> restore controlfile from autabackup;
rman> alter database mount;
rman> restore database;
rman> recover database; //online redolog 不存在

SQL>recover database until cancel; //當redo log丟失，資料庫在預設的方式下，是不容許進行recover操作的,那麼如何在這種情況下操作呢
SQL>create pfile from spfile;

vi /u01/product/10.20/dbs/initora10g.ora，在這個檔案的最後一行新增
*.allow_resetlogs_corruption='TRUE'; //容許resetlog corruption

 
SQL>shutdown immediate;
SQL>startup pfile='/u01/product/10.20/dbs/initora10g.ora' mount;
SQL>alter database open resetlogs;

 

 基於時間點的恢復:
 run{
  set until time "to_date(07/01/02 15:00:00','mm/dd/yy hh24:mi:ss')";
  restore database;
  recover database;
  alter database open resetlogs;
}

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
1.startup mount;
2.restore database until time "to_date('2009-7-19 13:19:00','YYYY-MM-DD HH24:MI:SS')";
3.recover database until time "to_date('2009-7-19 13:19:00','YYYY-MM-DD HH24:MI:SS')";
4.alter database open resetlogs;

 

 

基於 SCN的恢復:
1.startup mount;
2.restore database until scn 10000;
3.recover database until scn 10000;
4.alter database open resetlogs;

基於日誌序列的恢復:
1.startup mount;
2.restore database until SEQUENCE 100 thread 1; //100是日誌序列
3.recover database until SEQUENCE 100 thread 1;
4.alter database open resetlogs;

日誌序列檢視命令： SQL>select * from v$log;其中有一個sequence欄位.resetlogs就會把sequence 置為1