noarchivelog 模式下的单文件恢复

非归档模式下，如果文件损坏当时的日志即current没有被覆盖，可以只恢复损坏的单个数据文件，并应用redo来recover。具体实验过程如下：
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            D:\log_dir_alt
Oldest online log sequence     31
Current log sequence           33
SQL> CREATE USER LIN IDENTIFIED BY LIN DEFAULT TABLESPACE TEST;
SQL>CREAET TABLE LIN.T AS SELECT * FROM DBA_OBJECTS;
SQL>SELECT COUNT(*) FROM LIN.T;

  COUNT(*)
----------
      6422
SQL>SELECT * FROM V$LOG;
查当前日志在33
SQL>SHUTDOWN IMMEDIAte;
cp test01.dbf test01.dbf.bak
SQL>STARTUP
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1         32   10485760          1 NO  INACTIVE
      7012229 08-OCT-10

         2          1         31   10485760          1 NO  INACTIVE
      6785006 28-SEP-10

         3          1         33   10485760          1 NO  CURRENT
      7012235 08-OCT-10


SQL> insert into lin.t select * from lin.t where rownum<=1;

1 row created.

SQL> select count(*) from lin.t;

  COUNT(*)
----------
      6423

SQL> commit;

Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
移除test01.dbf，讲test01.dbf.bak改名为test01.dbf。模拟数据文件故障，用旧备份还原。


SQL> startup
ORACLE instance started.

Total System Global Area  160504432 bytes
Fixed Size                   453232 bytes
Variable Size             117440512 bytes
Database Buffers           41943040 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: 'D:\ORACLE\ORADATA\FOX\TEST01.DBF'


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1         32   10485760          1 NO  INACTIVE
      7012229 08-OCT-10

         2          1         31   10485760          1 NO  INACTIVE
      6785006 28-SEP-10

         3          1         33   10485760          1 NO  CURRENT
      7012235 08-OCT-10

此处，当前日志还是在33，并没有被覆盖，故可以应用日志还原
SQL> recover datafile 7
Media recovery complete.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1         32   10485760          1 NO  INACTIVE
      7012229 08-OCT-10

         2          1         31   10485760          1 NO  INACTIVE
      6785006 28-SEP-10

         3          1         33   10485760          1 NO  CURRENT
      7012235 08-OCT-10


SQL> alter database open;

Database altered.

SQL> select count(*) from lin.t;

  COUNT(*)
----------
      6423
此处，还原后数据量还是6423，没有减少，恢复成功。
如处理过程中执行了1次alter system switch logfile，33号转为active或者inactive，仍然可以恢复成功。
做三次alter system switch logfile 直到33号日志被覆盖，重新执行上面过程，将会提示recover失败
同理，如果是将数据文件offline（非归档下只能做offline drop），则当前的日志如果没有被覆盖仍然可以通过recover datafile来重新online，如果current被覆盖了，则回天乏术。

另外，通过查看恢复提示的信息结合v$log的序列号也能确定是否可以正常恢复
在恢复的时候会提示
SQL> recover datafile 7
ORA-00279: change 7013449 generated at 10/08/2010 16:41:34 needed for thread 1
ORA-00289: suggestion : D:\LOG_DIRARC00044.001
ORA-00280: change 7013449 for thread 1 is in sequence #44

通过查询
SQL> select SEQUENCE#,FIRST_CHANGE# from v$log;

 SEQUENCE# FIRST_CHANGE#
---------- -------------
        46       7013477
        47       7013479
        45       7013473

可以看出恢复的起点7013449已经超出任何一个log file的范围，恢复肯定是无效的。