断电导致oracle的ORA-01207错误完全解决办法（重做日志文件（redo log files）管理（增，删，改，查，切））

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了断电导致oracle的ORA-01207错误完全解决办法（重做日志文件（redo log files）管理（增，删，改，查，切）），前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

硬件维护的时候有人不小心把生产库的电源断掉了,重新启动盘柜和服务器后,oracle 10g 10.2.0.4 startup时出现错误提示:

代码如下
1 数据库装载完毕。
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01207: 文件比控制文件更新 - 旧的控制文件 (file is more recent than controlfile - old controlfile)

处理办法：

重做日志文件。

汇总整理一下有关重做日志文件（redologfiles）管理相关的操作（增，删，改，查，切）。供参考。

1.当前日志相关信息
sys@ora11g> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09

sys@ora11g> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO

2.添加重做日志组
sys@ora11g>alter database add logfile group 4 ('/oracle/u02/oradata/ora11g/redo04_01.log','/oracle/u02/oradata/ora11g/redo04_02.log') size 50m;

Database altered.

sys@ora11g> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 1 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 1 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 1 YES INACTIVE 432636 04-MAR-09
4 1 0 52428800 2 YES UNUSED 0

sys@ora11g> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO


3.添加日志文件
sys@ora11g>alter database add logfile member
'/oracle/u02/oradata/ora11g/redo01_02.log' to group 1,
'/oracle/u02/oradata/ora11g/redo02_02.log' to group 2,
'/oracle/u02/oradata/ora11g/redo03_02.log' to group 3;

Database altered.

sys@ora11g> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
1 1 10 209715200 2 YES INACTIVE 461938 09-MAR-09
2 1 11 209715200 2 NO CURRENT 485885 09-MAR-09
3 1 9 209715200 2 YES INACTIVE 432636 04-MAR-09
4 1 0 52428800 2 YES UNUSED 0

sys@ora11g> select * from v$logfile order by 1;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ---------------------------------------- ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
1 INVALID ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 INVALID ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
3 INVALID ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO

8 rows selected.

4.重命名日志成员
在重命名日志组成员之前新的目标必须已经存在。Oracle的sql命令只是把控制文件中的内部指针指向新的日志文件。
1）关闭数据库
sys@ora11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2）使用操作系统命令重命名或移动日志文件
ora11g@RHEL53 /oracle/u02/oradata/ora11g$mv redo01.log redo01_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$mv redo02.log redo02_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$mv redo03.log redo03_01.log

3）启动数据库实例到mount状态，重命名控制文件中的日志文件成员。
NotConnected@> select * from v$logfile order by 1,4;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO

8 rows selected.

NotConnected@>alter database rename file '/oracle/u02/oradata/ora11g/redo01.log' to '/oracle/u02/oradata/ora11g/redo01_01.log';

Database altered.

NotConnected@>alter database rename file '/oracle/u02/oradata/ora11g/redo02.log' to '/oracle/u02/oradata/ora11g/redo02_01.log';

Database altered.

NotConnected@>alter database rename file '/oracle/u02/oradata/ora11g/redo03.log' to '/oracle/u02/oradata/ora11g/redo03_01.log';

Database altered.

4）open数据库，验证结果
NotConnected@>alter database open;

Database altered.

sys@ora11g> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09
2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09
3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09
4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09

sys@ora11g> select * from v$logfile order by 1,4;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_02.log NO

8 rows selected.

5）最后，不要忘记备份控制文件
sys@ora11g>alter database backup controlfile to trace;

Database altered.

5.删除一个非活动的重做日志组的成员
sys@ora11g> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
1 1 18 209715200 2 YES INACTIVE 486960 09-MAR-09
2 1 19 209715200 2 YES INACTIVE 486964 09-MAR-09
3 1 21 209715200 2 NO CURRENT 486973 09-MAR-09
4 1 20 52428800 2 YES INACTIVE 486968 09-MAR-09

sys@ora11g>alter database drop logfile member '/oracle/u02/oradata/ora11g/redo04_02.log';

Database altered.

sys@ora11g> !ls -l /oracle/u02/oradata/ora11g/redo04_02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 9 16:28 /oracle/u02/oradata/ora11g/redo04_02.log

sys@ora11g>!rm -f /oracle/u02/oradata/ora11g/redo04_02.log

sys@ora11g> select * from v$logfile order by 1,4;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO
4 ONLINE /oracle/u02/oradata/ora11g/redo04_01.log NO

7 rows selected.

6.删除一个非活动的重做日志组
sys@ora11g>alter database drop logfile group 4;

Database altered.

sys@ora11g>!rm -f /oracle/u02/oradata/ora11g/redo04_01.log

sys@ora11g> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- -------------------- ------------------------------------------ ---
1 ONLINE /oracle/u02/oradata/ora11g/redo01_01.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_01.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_01.log NO
1 ONLINE /oracle/u02/oradata/ora11g/redo01_02.log NO
2 ONLINE /oracle/u02/oradata/ora11g/redo02_02.log NO
3 ONLINE /oracle/u02/oradata/ora11g/redo03_02.log NO

6 rows selected.

7.强制切换日志
sys@ora11g>alter system switch logfile;

System altered.

sys@ora11g>alter system archive log current;

System altered.

8.小结
1）日志文件非常重要，当多路复用重做日志文件时，应该把一个组的成员保存在不同的磁盘上。
2）在完成日志文件维护后一定要记得备份最新的控制文件！
3以上试验是在11g环境下完成的，在10g环境中一样适用。

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问