8. Oracle 联机重做日志文件(ONLINE LOG FILE)
转载自：http://blog.csdn.net/leshami/article/details/5749556
一、Oracle中的几类日志文件
Redo log files -->联机重做日志 Archive log files -->归档日志 Alert log files -->告警日志 Trace files -->跟踪日志 user_dump_dest -->用户跟踪日志 backupground_dump_dest -->进程跟踪日志 --查看后台进程相关目录 sys@LEARN01> show parameter dump NAME TYPE VALUE ----------------------- ------- ------------------------------------------------- background_core_dump string partial background_dump_dest string /data/oracle/diag/rdbms/learn01/learn01/trace core_dump_dest string /data/oracle/diag/rdbms/learn01/learn01/cdump max_dump_file_size string unlimited shadow_core_dump string partial user_dump_dest string /data/oracle/diag/rdbms/learn01/learn01/trace 关于Oracle 常用目录及路径请参考：Oracle 常用目录结构(10g) 关于Oracle 体系结构请参考：Oracle实例和Oracle数据库(Oracle体系结构)
二、联机重做日志的规划管理
--1.联机重做日志
记录了数据的所有变化(DML,DDL或管理员对数据所作的结构性更改等)
提供恢复机制(对于意外删除或宕机利用日志文件实现数据恢复)
可以被分组管理
--2.联机重做日志组
由一个或多个相同的联机日志文件组成一个联机重做日志组
至少两个日志组，每组一个成员(建议每组两个成员，分散放开到不同的磁盘)
由LGWR后台进程同时将日志内容写入到一个组的所有成员
LGWR的触发条件
在事务提交的时候(COMMIT)
Redo Log Buffer 三分之一满
Redo Log Buffer 多于一兆的变化记录
在DBWn写入数据文件之前
--3.联机重做日志成员
重做日志组内的每一个联机日志文件称为一个成员
一个组内的每一个成员具有相同的日志序列号(log sequence number),且成员的大小相同
每次日志切换时，Oracle服务器分配一个新的LSN号给即将写入日志的日志文件组
LSN号用于唯一区分每一个联机日志组和归档日志
处于归档模式的联机日志，LSN号在归档时也被写入到归档日志之中
--4.日志文件的工作方式
日志文件采用按顺序循环写的方式
当一组联机日志组写满，LGWR则将日志写入到下一组，当最后一组写满则从第一组开始写入
写入下一组的过程称为日志切换
切换时发生检查点过程
检查点的信息同时写入到控制文件
--5.联机日志文件的规划
总原则
分散放开，多路复用
日志所在的磁盘应当具有较高的I/O
一般日志组大小应满足自动切换间隔至少15-20分钟左右业务需求
建议使用rdo结尾的日志文件名，避免误删日志文件。如redo1.rdo,redo2.rdo
规划样例
Redo Log Group1 Redo Log Group2 Redo Log Group3
Member1 Member1 Member1 -->Physical Disk 1
Member2 Member2 Member2 -->Physical Disk 2
Member3 Member3 Member3 -->Physical Disk 3
--6.日志切换和检查点切换
ALTER SYSTEM SWITCH LOGFILE; --强制手动切换
ALTER SYSTEM CHECKPOINT;
强制设置检查点间隔
ALTER SYSTEM SET FAST_START_MTTR_TARGET = n
--7.添加日志文件组
ALTER DATABASE ADD LOGFILE [GROUP n]
('$ORACLE_BASE/oradata/u01/logn1.rdo',
'$ORACLE_BASE/oradata/u01/logn2.rdo')
SIZE mM;
--8.添加日志成员
ALTER DATABASE ADD LOGFILE MEMBER
'$ORACLE_BASE/oradata/u01/logn1.rdo' TO GROUP 1,
'$ORACLE_BASE/oradata/u01/logn2.rdo' TO GROUP 2;
--9.删除日志成员
不能删除组内的唯一一个成员
不能删除处于active 和current 状态组内的成员
删除处于active 和current 状态组内的成员，应使用日志切换使其处于INACTIVE状态后再删除
对于组内如果一个成员为NULL 值，一个为INVALID，且组处入INACTIVE，仅能删除INVALID状态成员
删除日志成员，物理文件并没有真正删除，需要手动删除
删除日志文件后，控制文件被更新
对于处于归档模式下的数据库，删除成员时确保日志已被归档，查看v$log视图获得归档信息
ALTER DATABASE DROP LOGFILE MEMBER '$ORACLE_BASE/oradata/u01/logn1.rdo'
--10.删除日志组
一个实例至少需要两个联机日志文件组
活动或当前的日志组不能被删除
组内成员状态有NULL 值或INVALID状态并存，组不可删除
日志组被删除后，物理文件需要手动删除(对于非OMF)
ALTER DATABASE DROP LOGFILE GROUP n
--11.日志的重定位及重命名
所需权限
ALTER DATABASE 系统权限
复制文件到目的位置操作系统权限(写权限)
CURRENT状态组内的成员不能被重命名
建议该行为之前备份数据库
重命名或重定位之后建议立即备份控制文件
重定位及重命名的两种方法
添加一个新成员到日志组，然后删除一个旧的成员
使用ALTER DATABASE RENAME FILE 命令(不区分归档与非归档模式)
复制联机日志文件到新路径:ho cp <oldfile> <newfile>
执行ALTER DATABASE RENAME FILE '<oldfile>' TO '<newfile>'
对于处于CURRENT状态的需要改名且不切换的情况下
办法是切换到MOUNT状态下再执行上述操作
--12.清空日志文件组
ALTER DATABASE CLEAR LOGIFLE GROUP n
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP n --使用unarchived 避免归档
--13.日志周期循环及切换分析
Group 1 Group 2 Group 3
Current Inactive Inactive
---------- Log Switch -------------
Active Current Inactive
---------- Log Switch -------------
Active Active Current
---------- Log Switch -------------
Current Inactive Inactive
--Active 和Current 称之为在一个循环周期之内(按顺序写日志)
--Inactive 称为一个周期之外(一个新的循环)
--新一轮循环开始如在归档状态则先归档再清空，否则直接清空日志
--数据库启动时Active 和Current 状态的日志不能丢失，否则出错
--14.日志的监视
查看日志视图中的物理日志文件是否存在、位置、大小等
SELECT 'ho cp '||member FROM v$logfile;
查看日志文件所处的磁盘空间是否足够
SQL> ho df -h
查看组内是否存在多个成员，如为单一成员应考虑增加日志成员
日志切换的间隔时间，应满足15-20分钟业务需求，如果切换间隔很短，应当增加日志文件的大小
增加方法，先删除日志组，再重建该组(对于current和active的需要切换再做处理)
--查看切换时间间隔(下面的示例中为手工切换的时间，不作考虑)
SQL> SELECT TO_CHAR(first_time,'yyyy-mm-dd hh24:mi:ss'),group# FROM v$log;
TO_CHAR(FIRST_TIME, GROUP#
------------------- ----------
2010-07-20 09:43:18 1
2010-07-19 22:44:30 2
2010-07-19 22:44:32 3
--15.日志的异常处理(参照演示中9小节)
不一致的情况(启动时)
ALTER DATABASE CLEAR LOGFILE GROUP n
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP n
三、与日志有关的动态性能视图
V$LOG V$LOGFILE V$LOG中STATUS的状态值 UNUSED: 从未对该联机日志写入任何内容，一般为新增加联机日志文件或是使用resetlog后的状态 CURRENT：当前重做日志文件，表示该重做日志文件为活动状态，能够被打开和关闭 ACTIVE：处于活动状态，不属于当前日志，崩溃恢复需要该状态，可用于块恢复，可能归档，也可能未归档 CLEARING：表示在执行alter database clear logfile命令后正将该日志重建为一个空日志，重建后状态变为unused CLEARING_CURRENT:当前日志处于关闭线程的清除状态。如日志某些故障或写入新日志标头时发生I/O错误 INACTIVE:实例恢复不在需要联机重做文件日志组，可能归档也可能未归档 V$LOGFILE中STATUS的状态值 INVALID :表明该文件不可访问 STALE :表明文件内容不完全 DELETED : 表明该文件不再使用 NULL ：表明文件正在使用
四、演示
--1.查看当前数据库的日志
SQL> SELECT * FROM v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 52428800 1 NO CURRENT 2758062 19-JUL-10
2 1 3 52428800 2 YES INACTIVE 2695010 16-JUL-10
3 1 4 104857600 2 YES INACTIVE 2716552 18-JUL-10
SQL> SELECT * FROM v$logfile ORDER BY group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 STALE ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
2 STALE ONLINE /u01/app/oracle/oradata/orcl/redo2.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo3.log NO
--2.添加日志组
SQL> SELECT * FROM v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
2 STALE ONLINE /u01/app/oracle/oradata/orcl/redo2.log NO
2 STALE ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo3.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
4 ONLINE /u01/app/oracle/oradata/orcl/redo4.log NO
4 ONLINE /u01/app/oracle/oradata/orcl/redo04.log NO
--3.添加日志成员
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo1.log' TO GROUP 1;
Database altered.
SQL> SELECT * FROM v$logfile WHERE group# = 1 ;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo1.log NO
--4.删除日志成员
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo01.log'
*
ERROR at line 1: --redo01.log处于NULL状态且该日志组为current状态不能删除
ORA-00362: member is required to form a valid logfile in group 1
ORA-01517: log member: '/u01/app/oracle/oradata/orcl/redo01.log'
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo04.log';
Database altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo4.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo4.log'
*
ERROR at line 1: --最后一个日志成员不能被删除
ORA-00361: cannot remove last log member /u01/app/oracle/oradata/orcl/redo4.log for group 4
--5.日志切换
SQL> SELECT * FROM v$log; --当前的日志组处于CURRENT状态
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 52428800 2 NO CURRENT 2758062 19-JUL-10
2 1 3 52428800 2 YES INACTIVE 2695010 16-JUL-10
3 1 4 104857600 2 YES INACTIVE 2716552 18-JUL-10
4 1 0 31457280 1 YES UNUSED 0
SQL> ALTER SYSTEM SWITCH LOGFILE; --进行日志切换
System altered.
SQL> SELECT * FROM v$log; --原来的日志组4的unused状态变为current状态
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 52428800 2 YES ACTIVE 2758062 19-JUL-10
2 1 3 52428800 2 YES INACTIVE 2695010 16-JUL-10
3 1 4 104857600 2 YES INACTIVE 2716552 18-JUL-10
4 1 6 31457280 1 NO CURRENT 2759277 19-JUL-10
SQL> ALTER SYSTEM SWITCH LOGFILE; --再次进行日志切换
System altered.
SQL> SELECT * FROM v$log; --日志组1变为current且组4变为active 状态
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 52428800 2 YES ACTIVE 2758062 19-JUL-10
2 1 7 52428800 2 NO CURRENT 2759293 19-JUL-10
3 1 4 104857600 2 YES INACTIVE 2716552 18-JUL-10
4 1 6 31457280 1 YES ACTIVE 2759277 19-JUL-10
--由上可得知，在日志切换时对于unused组将优先作为下一组切换对象
--再次删除redo01.log还是收到错误提示
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo01.log'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 1
ORA-01517: log member: '/u01/app/oracle/oradata/orcl/redo01.log'
SQL> ALTER SYSTEM SWITCH LOGFILE; --再次进行日志切换
System altered.
SQL> SELECT * FROM v$log; --group1变为inactive
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 52428800 2 YES INACTIVE 2758062 19-JUL-10
2 1 7 52428800 2 YES ACTIVE 2759293 19-JUL-10
3 1 8 104857600 2 NO CURRENT 2759420 19-JUL-10
4 1 6 31457280 1 YES INACTIVE 2759277 19-JUL-10
--反复多切几次日志之后redo01.log被成功删除
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/redo01.log';
Database altered.
--6.删除日志组(CURRENT和ACTIVE状态的不能被删除)
SQL> SELECT * FROM v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 9 52428800 1 YES ACTIVE 2759487 19-JUL-10
2 1 11 52428800 2 NO CURRENT 2759502 19-JUL-10
3 1 8 104857600 2 YES ACTIVE 2759420 19-JUL-10
4 1 10 31457280 1 YES ACTIVE 2759499 19-JUL-10
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 4
*
ERROR at line 1: --处于活动状态的group4 用于灾难恢复，不能被删除
ORA-01624: log 4 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/redo4.log'
SQL> ALTER SYSTEM SWITCH LOGFILE; --进行日志切换
System altered.
SQL> /
System altered.
SQL> SELECT * FROM v$log; --group 4的状态变为inactvie
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 13 52428800 1 NO CURRENT 2759720 19-JUL-10
2 1 11 52428800 2 YES ACTIVE 2759502 19-JUL-10
3 1 12 104857600 2 YES ACTIVE 2759718 19-JUL-10
4 1 10 31457280 1 YES INACTIVE 2759499 19-JUL-10
SQL> ALTER DATABASE DROP LOGFILE GROUP 4; --成功删除group 4
Database altered.
SQL> ho ls /u01/app/oracle/oradata/orcl/redo*
/u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/orcl/redo1.log
/u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo2.log
/u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo3.log
/u01/app/oracle/oradata/orcl/redo04.log /u01/app/oracle/oradata/orcl/redo4.log
SQL> ho rm /u01/app/oracle/oradata/orcl/redo04.log --删除物理文件
SQL> ho rm /u01/app/oracle/oradata/orcl/redo4.log --删除物理文件
--7.日志的重定位及重命名(仅演示ALTER DATABASE RENAME FILE 命令)
SQL> SELECT name,log_mode FROM v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
SQL> SELECT * FROM v$logfile ORDER BY group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo2.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
3 STALE ONLINE /u01/app/oracle/oradata/orcl/redo3.log NO
SQL> ho cp /u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/redo01.rdo
SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/redo01.log'
2 TO '/u01/app/oracle/oradata/redo01.rdo';
Database altered.
SQL> SELECT * FROM v$logfile WHERE group# = 1;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/redo01.rdo NO
--8.清空日志文件组(只有非active 和非current状态的组才能被清空)
SQL> SELECT * FROM v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 13 52428800 1 YES ACTIVE 2759720 19-JUL-10
2 1 14 52428800 2 NO CURRENT 2761383 19-JUL-10
3 1 12 104857600 2 YES INACTIVE 2759718 19-JUL-10
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1: --active 状态不能被清空
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/redo1.rdo'
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 2
*
ERROR at line 1: --current 状态不能被清空
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo2.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Database altered.
SQL> SELECT * FROM v$log; --group 3被清空后状态变为unused
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 13 52428800 1 YES INACTIVE 2759720 19-JUL-10
2 1 14 52428800 2 NO CURRENT 2761383 19-JUL-10
3 1 0 104857600 2 YES UNUSED 2759718 19-JUL-10
--9.日志异常处理
--启动时提示日志不一致
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 83887892 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00341:log 1 of thread 1,wrong log # in header
ORA-00312:online log 1 thread 1:'/u01/app/oracle/oradata/orcl/redo1a.rdo'
ORA-00312:online log 1 thread 1:'/u01/app/oracle/oradata/orcl/redo1b.rdo'
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE OPEN;
Database opened.
--日志文件丢失(非current状态日志组)
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 88082196 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo1a.rdo'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo1b.rdo'
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
--日志文件丢失(current状态日志组)
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 83887892 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'
--查看告警日志
SQL> ho tail -n 30 /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Jul 20 10:45:58 2010
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_4112.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Jul 20 10:45:58 2010
ARC0: STARTING ARCH PROCESSES
Tue Jul 20 10:45:58 2010
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Tue Jul 20 10:45:58 2010
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=4137
Tue Jul 20 10:45:58 2010
ORA-313 signalled during: ALTER DATABASE OPEN...
--查看物理日志文件是否存在
SQL> ho ls /u01/app/oracle/oradata/orcl/redo3a.rdo
ls: /u01/app/oracle/oradata/orcl/redo3a.rdo: No such file or directory
SQL> ho ls /u01/app/oracle/oradata/orcl/redo3b.rdo
ls: /u01/app/oracle/oradata/orcl/redo3b.rdo: No such file or directory
--尝试使用清空日志组命令
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1: --系统处于非归档模式，且group 3状态为CURRENT
ORA-00350: log 3 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'
--尝试使用不归档清空日志
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--使用带控制文件的介质恢复
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 2835232 generated at 07/20/2010 10:40:23 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arc
ORA-00280: change 2835232 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--使用resetlogs选项打开数据库
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> SELECT * FROM v$log; --系统重建group 3
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 31457280 2 NO CURRENT 2835234 20-JUL-10
2 1 1 31457280 2 YES INACTIVE 2835233 20-JUL-10
3 1 0 31457280 2 YES UNUSED 0
SQL> SELECT * FROM v$logfile; --为group 3增加了两个成员redo3a.rdo ，redo3b.rdo
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/orcl/redo2a.rdo NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo2b.rdo NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo1a.rdo NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo3a.rdo NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo3b.rdo NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo1b.rdo NO
对于CURRENT组的也可以使用隐藏参数来解决
步骤：
alter system set "_allow_resetlogs_corruption" = true scope = spfile;
recover database using bakcup controlfile;
alter database open resetlogs;
shutdown immediate;
startup mount;
alter database open resetlogs;
alter system reset "_allow_resetlogs_corruption" scope = spfile sid = '*'
对于归档模式下的日志文件丢失，同样可以按上述步骤处理
