数据库各种文件丢失恢复大全

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";

 

非catalog方式完全恢复

数据库出现问题:

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;

 

如果有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