Oracle数据库联机日志文件丢失处理方法-CSDN博客

Oracle数据库联机日志文件丢失处理方法

试验一：用命令清空日志组方法
1、   查看原来表中数据
SQL>; conn test/test
Connected.
SQL>; select * from test;

   TEL
----------
      1
      2
      3
2、插入新数据
SQL>; insert into test values(4);
1 row created.
SQL>; commit;
Commit complete.
SQL>;
3、   正常关闭数据库
4、   利用os command删除所有redo文件
5、   启动数据库
SQL>; startup
ORACLE instance started.
Total System Global Area  353862792 bytes
Fixed Size               730248 bytes
Variable Size          285212672bytes
Database Buffers          67108864bytes
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'
6、   查看当前日志状态
SQL>; select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
      1       1       2  104857600       1 YES INACTIVE
   487837 01-9月 -05

      2       1       4  104857600       1 NO  CURRENT
   487955 01-9月 -05

      3       1       3  104857600       1 YES INACTIVE
   487839 01-9月 -05
看来redo01.log不是当前日志，对于这类非当前日志可以直接clear,系统会重新自动生成一个redo文件

7、SQL>;alter database clear logfile group 1;
Database altered.
7、   继续启动db
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'
8、   看来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'
尝试clearunarchived logfile group ,报错：
SQL>; alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
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
看来他是因为找不到这个文件，从有效的备份中cp一个过来看看
SQL>; host cp /T3/ORACLE/oradatabak/redo02* /T3/ORACLE/oradata/ORA9

SQL>; alter database clear unarchived logfile group 2;

Database altered.
搞定……….

9、   按照oracle的某些做法也是可以的
SQL>; alter database clear unarchived logfile group 1 unrecoverabledatafile;

Database altered.

10、但是对于非当前日志就都可以，下面看看redo03
SQL>;  alter database clear logfile group 3;

Database altered.

结论：
如果数据库是正常shutdown,非当前日志都可以直接clear来重新生成，而且不丢失数据，因为正常关闭db,数据已经写入dbf文件了。唯独当前日志不可以，当前日志必须首先从有效的备份中拷贝一个日志文件过来，然后用
alter database clear unarchived logfile group n 或alter database clear unarchived logfile group n,除此之外，还可以用下面的方法来做