Oracle备份恢复中，redo的恢复相对来说还是比较简单的，只要保证每组的成员不止一个，出现问题的几率就相当小，即使出了问题我们也可按照不同的方法将他们恢复，所以如果碰到日志文件损坏，完全不必紧张！按照下面的方法来做，基本上都能搞定！
sql> conn test/test
Connected.
sql> select * from test;
@H_403_6@ TEL
----------
@H_403_6@ 1
@H_403_6@ 2
@H_403_6@ 3
@H_403_6@2 、插入新数据
sql> insert into test values(4);
1 row created.
sql> commit;
Commit complete.
sql>
@H_403_6@4、 @H_403_6@利用 os command 删除所有 redo 文件
sql> startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open Failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
sql> select * from v$log;
@H_403_6@ GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
@H_403_6@ 1 1 2 104857600 1 YES INACTIVE
@H_403_6@ 487837 01-9 月 -05
@H_403_6@ 2 1 4 104857600 1 NO CURRENT
@H_403_6@ 487955 01-9 月 -05
@H_403_6@ 3 1 3 104857600 1 YES INACTIVE
@H_403_6@ 487839 01-9 月 -05
@H_403_6@看来 redo01.log 不是当前日志，对于这类非当前日志可以直接 clear, 系统会重新自动生成一个 redo 文件
@H_403_6@7 、 sql> alter database clear logfile group 1;
Database altered.
sql> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open Failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
@H_403_6@8、 @H_403_6@看来 redo 也得恢复，但是 redo02 是当前 redo, 直接 clear 是不行的
sql> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of thread 1 needs to be archived
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
@H_403_6@9、 @H_403_6@按照 oracle 的某些做法也是不行的
sql> alter database clear unarchived logfile group 2 unrecoverable datafile;
alter database clear unarchived logfile group 2 unrecoverable datafile
*
ERROR at line 1:
ORA-00313: open Failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
@H_403_6@10、 @H_403_6@尝试从其他冷被分 cp 过来一个，再作 clear, 还是不行
sql> host cp /T3/ORACLE/oradata2/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/
sql> alter database clear unarchived logfile group 2 unrecoverable datafile;
alter database clear unarchived logfile group 2 unrecoverable datafile
*
ERROR at line 1:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
sql>
@H_403_6@11、 @H_403_6@但是对于非当前日志就都可以，下面看看 redo03
sql> alter database clear logfile group 3;
Database altered.
@H_403_6@结论：
@H_403_6@如果数据库是 正常 shutdown, 非当前日志都可以直接 clear 来重新生成，而且不丢失数据，因为正常关闭 db, 数据已经写入 dbf 文件了。唯独当前日志不可以，当前日志必须用其他方法恢复，不管是不是正常关闭，
@H_403_6@前面的出错提示，步骤都一样，唯独恢复的方法不一样
sql> startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open Failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
@H_403_6@看看丢失了哪些 redo
sql> host ls /T3/ORACLE/oradarta/ORA9/redo*
/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory
@H_403_6@看来 redo 都丢了
@H_403_6@直接 recover
@H_403_6@sql> recover database until cancel;
Media recovery complete.
sql> host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
sql> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
sql> alter database open resetlogs;
Database altered.
@H_403_6@（注意，这里必须用 resetlogs, 否则会错误的
@H_403_6@sql> alter database open noresetlogs;
@H_403_6@alter database open noresetlogs
@H_403_6@*
@H_403_6@ERROR at line 1:
@H_403_6@ORA-00313: open Failed for members of log group 1 of thread 1
@H_403_6@ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
@H_403_6@Resetlogs 其实就是根据控制文件让系统自动重新生成 redo, 如果 noresetlog 的话，就不会重新生成 redo, 缺少了文件， db 自然无法启动）
sql> host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo01.log /T3/ORACLE/oradata/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/redo03.log
sql>
@H_403_6@检验
sql> select * from test.test;
@H_403_6@ TEL
----------
@H_403_6@ 1
@H_403_6@ 2
@H_403_6@ 3
@H_403_6@ 4
sql>
@H_403_6@数据一点儿都没有丢失
@H_403_6@结论：
@H_403_6@如果数据库是正常关闭的，用 recover database until cancel 可以轻松恢复或者说重新建立所有的 redo ，不再区分是否是当前日志，而且由于正常关闭，不会丢失任何数据，唯一可能丢失的情况就是如果日志还没有归档
@H_403_6@这种恢复方法 由于要 resetlogs, 所以在恢复完成后，日志清零，以前的备份不再起作用，所以建议立即备份
@H_403_6@sql> archive log list;
@H_403_6@Database log mode Archive Mode
@H_403_6@Automatic archival Enabled
@H_403_6@Archive destination /T3/ORACLE/arch
@H_403_6@Oldest online log sequence 0
@H_403_6@Next log sequence to archive 1
@H_403_6@Current log sequence 1
@H_403_6@实验三：通过重新生成控制文件来恢复 redo
@H_403_6@ORACLE instance started.
@H_403_6@Total System Global Area 353862792 bytes
@H_403_6@Fixed Size 730248 bytes
@H_403_6@Variable Size 285212672 bytes
@H_403_6@Database Buffers 67108864 bytes
@H_403_6@Redo Buffers 811008 bytes
@H_403_6@Database mounted.
@H_403_6@ORA-00313: open Failed for members of log group 1 of thread 1
@H_403_6@ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
@H_403_6@sql> alter database backup controlfile to trace;
@H_403_6@Database altered.
@H_403_6@sql> shutdown immediate
@H_403_6@ORA-01109: database not open
@H_403_6@Database dismounted.
@H_403_6@ORACLE instance shut down.
@H_403_6@2、 @H_403_6@修改一下刚才生成的那个文件
CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
@H_403_6@ MAXLOGFILES 50
@H_403_6@ MAXLOGMEMBERS 5
@H_403_6@ MAXDATAFILES 100
@H_403_6@ MAXINSTANCES 1
@H_403_6@ MAXLOGHISTORY 226
LOGFILE
@H_403_6@ GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log' SIZE 100M,
@H_403_6@ GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log' SIZE 100M,
@H_403_6@ GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
@H_403_6@ '/T3/ORACLE/oradata/ORA9/system01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/undotbs01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/cwmlite01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/drsys01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/example01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/indx01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/odm01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/tools01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/users01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/xdb01.dbf',
@H_403_6@ '/T3/ORACLE/oradata/ORA9/test01.dbf'
CHARACTER SET ZHS16GBK
;
@H_403_6@另存为一个脚本，运行他
Control file created.
sql> alter database open resetlogs;
Database altered.
sql>
@H_403_6@搞定 ……………
@H_403_6@结论：这种方法的关键是重新创建控制文件，后面的步骤和前面的道理一样的
@H_403_6@前面的三种方法都是假设 db 是正常关闭的，数据已经写入数据库文件中，所以不会由数据存在 redo 种，所以 clear 的话也不会有数据丢失
@H_403_6@本文来自http://www.cublog.cn/u/11765/showart_427753.html
