--=========================================
-- Oracle 联机重做日志文件 (ONLINE LOG FILE)
--=========================================
一、 Oracle 中的几类日志文件
Redo log files --> 联机重做日志
Archive log files --> 归档日志
Alert log files --> 告警日志
Trace files --> 跟踪日志
user_dump_dest --> 用户跟踪日志
backupground_dump_dest --> 进程跟踪日志
-- 查看后台进程相关目录
SQL > show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string / u01 / app / oracle / admin / orcl / bdump
core_dump_dest string / u01 / app / oracle / admin / orcl / cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string / u01 / app / oracle / admin / orcl / udump
关于 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 = '*'
对于归档模式下的日志文件丢失，同样可以按上述步骤处理
五、更多
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
