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

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