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

Oracle数据库联机日志文件丢失处理方法试验一：用命令清空日志组方法1、查看原来表中数据SQL>; conn test/test

Connected.

SQL>; select * from test;

TEL

----------

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,除此之外，还可以用下面的方法来做