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 285212672 bytes
Database Buffers 67108864 bytes
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'
尝试clear unarchived 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 unrecoverable datafile; 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,除此之外,还可以用下面的方法来做
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 285212672 bytes
Database Buffers 67108864 bytes
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'
尝试clear unarchived 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 unrecoverable datafile; 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,除此之外,还可以用下面的方法来做
解决方案 »
- dual表的二次利用问题?
- dbms_job.remove()到底有什么用处?什么时候使用?
- 请较如何导出数据库中某一时间段的数据
- 新手 关于存储过程一些问题
- 请问:在Oracle9i中如何设置使数据库只能本机连接?
- oracle中如何取得时间的月份?
- 为什么我的Oracle9i的服务中没有OracleServiceORACLE呀?
- 我想把不同服务器上的一个表中数据
- 菜鸟问:取2000-01-01到2000-12-31之间的数据怎么写?
- 高手很容易解决的问题,asp调用存储过程,代码很短的。解决立码给100分
- 请教怎样交换分区,即怎样使用exchange命令?
- 如何将schema保存成.sql文件,以后可以用来恢复数据库。
前面的出错提示,步骤都一样,唯独恢复的方法不一样
SQL>; startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
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'
看看丢失了哪些redo
SQL>; host ls /T3/ORACLE/oradarta/ORA9/redo*
/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory
看来redo都丢了
直接recover
SQL>; recover database until cancel;
Media recovery complete.
这个时候redo还没有生成
SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
启动数据库
SQL>; alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL>; alter database open resetlogs;
Database altered.
(注意,这里必须用resetlogs,否则会错误的
SQL>; alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
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'
SQL>;
Resetlogs其实就是根据控制文件让系统自动重新生成redo,如果noresetlog的话,就不会重新生成redo,缺少了文件,db自然无法启动)
SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo01.log /T3/ORACLE/oradata/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/redo03.log
SQL>;
检验
SQL>; select * from test.test; TEL
----------
1
2
3
4
SQL>;
数据一点儿都没有丢失 结论:
如果数据库是正常关闭的,用recover database until cancel可以轻松恢复或者说重新建立所有的redo,不再区分是否是当前日志,而且由于正常关闭,不会丢失任何数据,唯一可能丢失的情况就是如果日志还没有归档
这种恢复方法 由于要resetlogs,所以在恢复完成后,日志清零,以前的备份不再起作用,所以建议立即备份
SQL>; archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /T3/ORACLE/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>;
前面的都一样,只是处理方法不一样
SQL>; startup
ORACLE instance started. Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
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' SQL>; alter database backup controlfile to trace; Database altered. SQL>; shutdown immediate
ORA-01109: database not open Database dismounted.
ORACLE instance shut down.
SQL>;
2、 修改一下刚才生成的那个文件
CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log' SIZE 100M,
GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log' SIZE 100M,
GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/T3/ORACLE/oradata/ORA9/system01.dbf',
'/T3/ORACLE/oradata/ORA9/undotbs01.dbf',
'/T3/ORACLE/oradata/ORA9/cwmlite01.dbf',
'/T3/ORACLE/oradata/ORA9/drsys01.dbf',
'/T3/ORACLE/oradata/ORA9/example01.dbf',
'/T3/ORACLE/oradata/ORA9/indx01.dbf',
'/T3/ORACLE/oradata/ORA9/odm01.dbf',
'/T3/ORACLE/oradata/ORA9/tools01.dbf',
'/T3/ORACLE/oradata/ORA9/users01.dbf',
'/T3/ORACLE/oradata/ORA9/xdb01.dbf',
'/T3/ORACLE/oradata/ORA9/test01.dbf'
CHARACTER SET ZHS16GBK
;
另存为一个脚本,运行他
SQL>; @clone.sql
Control file created.
SQL>; alter database open resetlogs;
Database altered.
SQL>;
搞定…………… 结论:这种方法的关键是重新创建控制文件,后面的步骤和前面的道理一样的 前面的三种方法都是假设db是正常关闭的,数据已经写入数据库文件中,所以不会由数据存在redo种,所以clear的话也不会有数据丢失
方法四:修改系统参数方法
1、 插入数据
SQL>; select * from test; TEL
----------
1
2
3
4 SQL>; insert into test values(5); 1 row created. SQL>; commit; Commit complete. SQL>;
2、 强行关闭
SQL>; shutdown abort
ORACLE instance shut down.
SQL>;
3、 手工模拟删除redo
4、 启动db
SQL>; startup
ORACLE instance started. Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/T3/ORACLE/oradata/ORA9/redo03.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
5、 尝试使用前3中方法中最简单的
SQL>; recover database until cancel;
ORA-00279: change 550174 generated at 09/02/2005 16:00:19 needed for thread 1
ORA-00289: suggestion : /T3/ORACLE/arch/1_1.dbf
ORA-00280: change 550174 for thread 1 is in sequence #1
Specify log: {<RET>;=suggested | filename | AUTO | CANCEL}
看来不行
6、 修改init.ora,加入一行
_allow_resetlogs_corruption=true
7、 启动with pfile
SQL>; startup
ORACLE instance started.
Total System Global Area 320308312 bytes
Fixed Size 730200 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
SQL>; alter database open resetlogs;
Database altered. SQL>; host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo01.log /T3/ORACLE/oradata/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/redo03.log
8、 检验数据
SQL>; select * from test.test; TEL
----------
1
2
3
4 SQL>;
看到了吧,我们前面由于执行了SHUTDOWN ABORT,这时候对数据的修改还没有保存到数据文件中,虽然执行了COMMIT,这个时候还在联机日志中,等待CKPT触发DBWR写入DATAFILE,但是这个时候执行了SHUTDOWN ABORT,redo被删除后,里面的信息也就丢了,造成数据丢失
9、 备份,去掉那个参数
现在的问题是OnlineRedoLog所在的磁盘坏了,我必须更改存储的磁盘. (比如在windows下D盘坏了,我想换存到E盘上去)在你例子里
GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log' SIZE 100M,
GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log' SIZE 100M,
GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log' SIZE 100M
就是说 /T3所在的磁盘坏了, 我想换/T4甚至其他什么目录. (不太懂LINUX,UNIX...)所以你给出的实验1实验2方法,我觉得都不可行.他们都建立在原路径还是可用的情况下.
我想知道实验3的方法是否可以解决这个问题呢?
GROUP 1 'e:\ORACLE\oradata\ORA9\redo01.log' SIZE 100M, 原文链接:http://bbs.chinaunix.net/viewthread.php?tid=613223
还附送了个学习UNIX的好网站~