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,除此之外,还可以用下面的方法来做
Oracle数据库联机日志文件丢失处理方法
最新推荐文章于 2024-10-17 18:58:27 发布