--=========================================-- 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
TYPEVALUE------------------------------------ ----------- ------------------------------background_core_dump
stringpartialbackground_dump_dest
string/u01/app/oracle/admin/orcl/bdumpcore_dump_dest
string/u01/app/oracle/admin/orcl/cdumpmax_dump_file_size
stringUNLIMITEDshadow_core_dump
stringpartialuser_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
执行ALTER DATABASE RENAME FILE '' TO ''
对于处于CURRENT状态的需要改名且不切换的情况下
办法是切换到MOUNT状态下再执行上述操作
12.清空日志文件组ALTER DATABASE CLEAR LOGIFLE GROUP nALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP n --使用unarchived 避免归档
13.日志周期循环及切换分析
Group 1Group 2Group 3
CurrentInactive Inactive---------- Log Switch -------------Active CurrentInactive---------- Log Switch -------------Active Active Current---------- Log Switch -------------CurrentInactive 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
12010-07-19 22:44:30
22010-07-19 22:44:32
3
15.日志的异常处理(参照演示中9小节)不一致的情况(启动时)
ALTER DATABASE CLEAR LOGFILE GROUP n
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP n
三、与日志有关的动态性能视图V$LOGV$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#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
5 52428800
1 NOCURRENT
2758062 19-JUL-10
2
1
3 52428800
2 YES INACTIVE
2695010 16-JUL-10
3
1
4104857600
2 YES INACTIVE
2716552 18-JUL-10
SQL> SELECT * FROM v$logfile ORDER BY group#;
GROUP# STATUSTYPEMEMBER
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# STATUSTYPEMEMBER
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# STATUSTYPEMEMBER
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 1ORA-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#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
5 52428800
2 NOCURRENT
2758062 19-JUL-10
2
1
3 52428800
2 YES INACTIVE
2695010 16-JUL-10
3
1
4104857600
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#BYTESMEMBERS 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
4104857600
2 YES INACTIVE
2716552 18-JUL-10
4
1
6 31457280
1 NOCURRENT
2759277 19-JUL-10 SQL> ALTER SYSTEM SWITCH LOGFILE; --再次进行日志切换 System altered. SQL> SELECT * FROM v$log;
--日志组1变为current且组4变为active 状态
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
5 52428800
2 YES ACTIVE
2758062 19-JUL-10
2
1
7 52428800
2 NOCURRENT
2759293 19-JUL-10
3
1
4104857600
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 1ORA-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#BYTESMEMBERS 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
8104857600
2 NOCURRENT
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#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
9 52428800
1 YES ACTIVE
2759487 19-JUL-10
2
1 11 52428800
2 NOCURRENT
2759502 19-JUL-10
3
1
8104857600
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#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1 13 52428800
1 NOCURRENT
2759720 19-JUL-10
2
1 11 52428800
2 YES ACTIVE
2759502 19-JUL-10
3
1 12104857600
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;
NAMELOG_MODE--------- ------------ORCLARCHIVELOG SQL> SELECT * FROM v$logfile ORDER BY group#;
GROUP# STATUSTYPEMEMBER
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'
2TO '/u01/app/oracle/oradata/redo01.rdo'; Database altered. SQL> SELECT * FROM v$logfile WHERE group# = 1;
GROUP# STATUSTYPEMEMBER
IS_---------- ------- ------- ------------------------------------------------------- ---
1 ONLINE/u01/app/oracle/oradata/redo01.rdo
NO
--8.清空日志文件组(只有非active 和非current状态的组才能被清空)SQL> SELECT * FROM v$log;
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1 13 52428800
1 YES ACTIVE
2759720 19-JUL-10
2
1 14 52428800
2 NOCURRENT
2761383 19-JUL-10
3
1 12104857600
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#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1 13 52428800
1 YES INACTIVE
2759720 19-JUL-10
2
1 14 52428800
2 NOCURRENT
2761383 19-JUL-10
3
1
0104857600
2 YES UNUSED
2759718 19-JUL-10
--9.日志异常处理--启动时提示日志不一致SQL> startupORACLE instance started. Total System Global Area251658240 bytesFixed Size
1218796 bytesVariable Size
83887892 bytesDatabase Buffers
163577856 bytesRedo Buffers
2973696 bytesDatabase mounted.ORA-00341:log 1 of thread 1,wrong log # in headerORA-00312
nline log 1 thread 1:'/u01/app/oracle/oradata/orcl/redo1a.rdo' ORA-00312
nline 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> startupORACLE instance started. Total System Global Area251658240 bytesFixed Size
1218796 bytesVariable Size
88082196 bytesDatabase Buffers
159383552 bytesRedo Buffers
2973696 bytesDatabase mounted.ORA-00313: open failed for members of log group 1 of thread 1ORA-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> startupORACLE instance started. Total System Global Area251658240 bytesFixed Size
1218796 bytesVariable Size
83887892 bytesDatabase Buffers
163577856 bytesRedo Buffers
2973696 bytesDatabase mounted.ORA-00313: open failed for members of log group 3 of thread 1ORA-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.logORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Tue Jul 20 10:45:58 2010Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_4112.trc:ORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Tue Jul 20 10:45:58 2010ARC0: STARTING ARCH PROCESSESTue Jul 20 10:45:58 2010ARC1: Becoming the 'no FAL' ARCHARC1: Becoming the 'no SRL' ARCHTue Jul 20 10:45:58 2010ARC2: Archival startedARC0: STARTING ARCH PROCESSES COMPLETEARC0: Becoming the heartbeat ARCHARC2 started with pid=18, OS id=4137Tue Jul 20 10:45:58 2010ORA-313 signalled during: ALTER DATABASE OPEN...
--查看物理日志文件是否存在SQL> ho ls /u01/app/oracle/oradata/orcl/redo3a.rdols: /u01/app/oracle/oradata/orcl/redo3a.rdo: No such file or directory SQL> ho ls /u01/app/oracle/oradata/orcl/redo3b.rdols: /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状态为CURRENTORA-00350: log 3 of instance orcl (thread 1) needs to be archivedORA-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 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3 --使用带控制文件的介质恢复SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;ORA-00279: change 2835232 generated at 07/20/2010 10:40:23 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arcORA-00280: change 2835232 for thread 1 is in sequence #39
Specify log: {=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 statusLinux Error: 2: No such file or directoryAdditional information: 3 --使用resetlogs选项打开数据库SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.
SQL> SELECT * FROM v$log; --系统重建group 3
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1
1
2 31457280
2 NOCURRENT
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# STATUSTYPEMEMBER
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 = '*'
对于归档模式下的日志文件丢失，同样可以按上述步骤处理
|
