在生产中，难免碰到各种场景。下面记录一下redo log被删除掉的处理方法。
数据库版本：
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
一、shutdow immediate情况下被删除redo
这种情况下最容易处理，数据完全不会丢失。
SQL> startup mount; ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size 2229464 bytes Variable Size 1207962408 bytes Database Buffers 671088640 bytes Redo Buffers 6070272 bytes Database mounted. SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.open的时候需要resetlogs，open之后，立刻做一次全库备份。
二、数据库open的情况下被删除redo
打开情况下分两种情况，即当前日志被删除和非当前日志被删除。
1、非当前日志被删除
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------------
1 1 1 52428800 512 1 YES INACTIVE 10452067 21-MAR-13 10454522 21-MAR-13
2 1 2 52428800 512 1 YES INACTIVE 10454522 21-MAR-13 10455156 21-MAR-13
3 1 3 52428800 512 1 NO CURRENT 10455156 21-MAR-13 2.8147E+14
SQL> alter database clear logfile group 2;
Database altered.
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 1887350784 bytes
Fixed Size 2229464 bytes
Variable Size 452987688 bytes
Database Buffers 1426063360 bytes
Redo Buffers 6070272 bytes
Database mounted.
Database opened.
上例中删除掉的redo是group2，是已经归档了，并且status为INACTIVE，才能clear成功。
如果是还没有归档，语句则不一样：
SQL> alter database clear unarchived logfile group 2;记得打开数据库后，马上做一次全库备份。
2、当前日志被删除
分实例可用和实例不可用两种情况
①数据库实例可用，直接用expdp导出数据
SQL> conn liujie/oracle Connected. SQL> insert into t6 select * from t6; 1209264 rows created. SQL> select count(*) from t6; COUNT(*) ---------- 2418528
SQL> commit; Commit complete.
打开另一个终端， SQL> conn / as sysdba; Connected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------------
1 1 7 52428800 512 1 NO CURRENT 10513263 22-MAR-13 2.8147E+14
2 1 5 52428800 512 1 YES INACTIVE 10510895 22-MAR-13 10511610 22-MAR-13
3 1 6 52428800 512 1 YES INACTIVE 10511610 22-MAR-13 10513263 22-MAR-13
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /data/oracle/oradata/uldw/redo03.log NO
2 ONLINE /data/oracle/oradata/uldw/redo02.log NO
1 ONLINE /data/oracle/oradata/uldw/redo01.log NO
删除掉所有日志
oracle@wls113:~>rm /data/oracle/oradata/uldw/redo01.log
oracle@wls113:~>rm /data/oracle/oradata/uldw/redo02.log
oracle@wls113:~>rm /data/oracle/oradata/uldw/redo03.log此时在数据库里再次插入数据
SQL> insert into t4 select * from t4;
75579 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t4;
COUNT(*)
----------
151158
此时如果实例可用，千万不要shutdown数据库，而是直接用expdp导出数据
oracle@wls113:/data/oracle/dump> expdp system directory=dump_dir dumpfile=liujie02.dmp schemas=liujie; Export: Release 11.2.0.3.0 - Production on Fri Mar 22 09:57:07 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_dir dumpfile=liujie02.dmp schemas=liujie Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 676.9 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW . . exported "LIUJIE"."T7" 317.8 MB 3378528 rows . . exported "LIUJIE"."T6" 233.9 MB 2418528 rows . . exported "LIUJIE"."T4" 14.62 MB 151158 rows . . exported "LIUJIE"."T1" 7.315 MB 75579 rows . . exported "LIUJIE"."T5" 7.315 MB 75579 rows . . exported "LIUJIE"."T3" 99.04 KB 1000 rows . . exported "LIUJIE"."MV1" 6.445 KB 7 rows . . exported "LIUJIE"."TEST1" 5.062 KB 3 rows . . exported "LIUJIE"."T2" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /data/oracle/dump/liujie02.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:57:56此时只要是commit的数据，在实例可用的情况下，都可以用expdp导出来。
这个时候数据库已经损坏掉，需要关闭数据库，然后重建数据库，把dump文件导入即可恢复数据。
②数据库实例不可用
SQL> conn liujie/oracle Connected. SQL> insert into t6 select * from t6; 604632 rows created. SQL> select count(*) from t6; COUNT(*) ---------- 1209264 //未提交 打开另一个终端， SQL> conn / as sysdba; Connected. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 3 ONLINE /data/oracle/oradata/uldw/redo03.log NO 2 ONLINE /data/oracle/oradata/uldw/redo02.log NO 1 ONLINE /data/oracle/oradata/uldw/redo01.log NO SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ --------------- 1 1 23 52428800 512 1 YES ACTIVE 10499875 21-MAR-13 10500288 21-MAR-13 2 1 22 52428800 512 1 YES ACTIVE 10497966 21-MAR-13 10499875 21-MAR-13 3 1 24 52428800 512 1 NO CURRENT 10500288 21-MAR-13 2.8147E+14然后删除掉当前日志
oracle@wls113:~>rm /data/oracle/oradata/uldw/redo03.log然后关闭数据库，open的时候出错
SQL> startup ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size 2229464 bytes Variable Size 452987688 bytes Database Buffers 1426063360 bytes Redo Buffers 6070272 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 32625 Session ID: 853 Serial number: 5
此时可以用数据库之前的备份来做不完全恢复，但是当前日志的数据会丢失掉。
RMAN> startup mount; RMAN> restore database; RMAN> recover database until sequence 24;因为是不完全恢复，open的时候，需要加上resetlogs
SQL> alter database open resetlogs; Database altered. SQL> conn liujie Enter password: Connected. SQL> select count(*) from t6; COUNT(*) ---------- 604632 //最后一个日志的内容丢失，造成数据丢失。