联机日志 redofile
联机日志 存在的目的是为了数据完整性 通过时间向量和日志条目来维持完整性
Oracle服务器维护联机重做日志文件以使数据库中的数据丢失减到最小。
重做日志文件记录了对数据库缓冲区高速缓存内数据所做的所有更改,存放着SQL条目(记录)就是改变向量
重做日志
按group来存放的.叫日志组
一个数据库至少有2个日志组
每个日志组至少有一个成员,组内成员与成员之间是镜像关系
顺序切 循环写
一个时间内只能打开一组日志
3秒写一次
满1／3
文件达到1M
commit
dbwr-优先写lgwr
vim /etc/logrotate.conf控制message 日志
日志断档，覆盖的日志无法恢复
所以要开启归档模式
切换的时候触发归档
archive log
在被覆盖之前CP到指定的文件夹
业务交易会不断生成REDO，使得REDO LOG文件越来越大，显然不利于ORACLE管理,所以设计切换模式
当前组日志写满,就会触发日志切换.手动也可以触发
alter system switch logfile;
手动触发日志切换
重做日志文件是写多读少，要存放在写性能最好的磁盘上.而且要多元化管理(物理隔离).
重做日志文件的读写特征
写 → 重做日志主要是写操作，由LGWR完成。
读 → 重做在实例恢复或者介质恢复的时候才会去读,日志挖掘也能用到。
SQL> desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# 单机 NUMBER
SEQUENCE# 序列 NUMBER
BYTES 大小 NUMBER
MEMBERS 成员数 NUMBER
ARCHIVED 归档 VARCHAR2(3)
STATUS 状态 VARCHAR2(16)
FIRST_CHANGE# 第一条redo条目所在的SCN号 NUMBER
FIRST_TIME DATE
联机日志的工作状态
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 706870 22-FEB-11
status:
CURRENT 当前 当前日志非常重要 若当前日志组丢失 系统崩溃恢复时将会丢失数据
UNUSED 未使用
ACTIVE 活动的 说明这组redo记录的条目里关联的脏块没写盘 或者说这组日志在实例恢复时需要
INACTIVE 表明日志条目里关联的脏块已经全部写盘 或者说这组日志实例恢复已经不需要了
查询日志的存储信息
idle> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora10g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora10g/redo01.log NO
idle>
手工切换日志
idle> alter system switch logfile;
System altered.
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 708058 22-FEB-11
2 1 0 52428800 1 YES UNUSED 0日志的切换 20分切一次
3 1 1 52428800 1 NO ACTIVE 706870 22-FEB-11
idle>
跟踪日志切换频率
idle> select to_char(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') FIRST_TIME FROM V$LOG_history;
FIRST_TIME
-------------------
2011-02-22 01:11:08
2011-02-22 01:52:55
idle>
idle> alter system switch logfile;
多次切换
idle> select to_char(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') FROM V$LOG_history;
TO_CHAR(FIRST_TIME,
-------------------
2011-02-22 01:11:08
2011-02-22 01:52:55
2011-02-22 01:54:11
2011-02-22 02:02:02
2011-02-22 02:02:10
2011-02-22 02:02:20
2011-02-22 02:02:30
7 rows selected.
idle>
日志切换太快 就会造成切换延迟
日志切换原理: 把当前组冻结 下一组初始化
如果太快就会导致下一组初始化未完成 在这个切换的间隙 就会导致redo log buffer不能写 越积越多 可能积满
积满时再执行update语句 无法产生redo条目时 挂起 (触发了日志等待事件 等待事件后期会学)
解决方法: 增加日志组的数量
增大日志文件
使切换的周长时间增加
日志的切换 20分切一次
日志文件不宜过大
触发完全检查点 将ACTIVE变成INACTIVE
alter system checkpoint;
增加日志组
idle> alter database add logfile group 4 '/home/oracle/oradata/ora10g/redo04.log' size 50M;
Database altered.
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
1 1 8 52428800 1 NO CURRENT 708333 22-FEB-11
2 1 6 52428800 1 NO INACTIVE 708329 22-FEB-11
3 1 7 52428800 1 NO INACTIVE 708331 22-FEB-11
4 1 0 52428800 1 YES UNUSED 0
idle> alter system switch logfile;
System altered.
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
1 1 8 52428800 1 NO ACTIVE 708333 22-FEB-11
2 1 6 52428800 1 NO INACTIVE 708329 22-FEB-11
3 1 7 52428800 1 NO INACTIVE 708331 22-FEB-11
4 1 9 52428800 1 NO CURRENT 708576 22-FEB-11
idle>
切换日志的顺序以 SEQUENCE# 最小值作为下一初始化组
group 1: 708333 ==> 708328
group 2: 708329 ==> 708330
group 3: 708331 ==> 708575
group 4: 708576 ==>
日志的多路复用
一组日志中一个成员 这个成员损坏 就会导致日志断档 将来恢复时不是顺序的日志 恢复无法进行
为了增加日志的安全性 顾多元化管理日志成员
新添加的日志组成员刚开始是无效的，只有在切换回来的时候才会生效；
为现有的组增加成员
ALTER DATABASE ADD LOGFILE MEMBER 'FILE' [REUSE] TO GROUP INTEGER[,'FILE' [REUSE] TO GROUP INTEGER...];
alter database add logfile member '/home/oracle/oradata/ora10g/redo01a.log' to group 1;
alter database add logfile member '/home/oracle/oradata/ora10g/redo02a.log' to group 2;
alter database add logfile member '/home/oracle/oradata/ora10g/redo03a.log' to group 3;
alter database add logfile member '/home/oracle/oradata/ora10g/redo04a.log' to group 4;
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------
1 1 8 52428800 2 NO ACTIVE 708333 22-FEB-11
2 1 6 52428800 2 NO INACTIVE 708329 22-FEB-11
3 1 7 52428800 2 NO INACTIVE 708331 22-FEB-11
4 1 9 52428800 2 NO CURRENT 708576 22-FEB-11
idle>
删除日志组
删除日志组有如下限制:
一个数据库至少2个日志组，不能删除至少于2个日志组
活动或当前日志组不允许删除
如果数据库运行在 ARCHIVELOG（归档） 模式下，并且未将日志文件组归档，那么无法丢弃该组
当删除日志组时，文件没有正真的删除，需要人工删除（OS下去删）
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 12 52428800 2 NO CURRENT 514093 28-MAR-11
2 1 11 52428800 2 NO INACTIVE 514091 28-MAR-11
3 1 10 52428800 2 NO INACTIVE 514088 28-MAR-11
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance ora10g (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01a.log'
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ora10g (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01a.log'
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL>
删除日志成员
删除日志组成员有如下限制:
current不能删,active可以删
最后一个有效成员不能删
在删除联机重做日志成员时并未删除操作系统文件
删除组：
1、current状态不能删除
2、 active状态也不能删除
3、inactive 可以删除
4、unused 可以删
磁盘的文件不会删除，需要手动执行rm
至少保持两组日志
SQL> select * from v$Logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO
3 ONLINE /u01/oracle/oradata/ora10g/redo03a.log NO
2 ONLINE /u01/oracle/oradata/ora10g/redo02a.log NO
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
2 1 14 52428800 2 NO CURRENT 514130 28-MAR-11
3 1 13 52428800 2 NO ACTIVE 514105 28-MAR-11
SQL> alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02a.log';
alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02a.log'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/ora10g/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/ora10g/redo02a.log'
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02a.log';
Database altered.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
2 1 14 52428800 1 NO ACTIVE 514130 28-MAR-11
3 1 15 52428800 2 NO CURRENT 514153 28-MAR-11
SQL> select * from v$Logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO
3 ONLINE /u01/oracle/oradata/ora10g/redo03a.log NO
SQL> alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02.log';
alter database drop logfile member '/u01/oracle/oradata/ora10g/redo02.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /u01/oracle/oradata/ora10g/redo02.log for group 2
SQL>
移动联机日志文件和改名 为了分散IO 要每个成员IO独立
可以不停库在open模式做,但要保证被移动的文件不是活跃的.
1.停库
SYS@beijing> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@beijing>
2.启动到mount
SYS@beijing> startup mount
ORACLE instance started.
Total System Global Area 230686720 bytes
Fixed Size 1218676 bytes
Variable Size 75499404 bytes
Database Buffers 150994944 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@beijing>
3.移动磁盘文件
SYS@beijing> ! mv /home/oracle/oradata/ora10g/redo01.log /home/oracle/oradata/ora10g/redo01abc.log
SYS@beijing>
4.移动控制文件中的指针 指向新文件
SYS@beijing> alter database rename file '/home/oracle/oradata/ora10g/redo01.log' to '/home/oracle/oradata/ora10g/redo01abc.log';
Database altered.
SYS@beijing>
5.正常启库
SYS@beijing> alter database open;
Database altered.
什么是前滚?
检查点位置是是个rba,他指向着重做日志文件中的某个重做记录.
在此位置前的重做记录,其对应的脏块已经被写进了数据文件,
在此位置后的重做记录,所对应的脏块有可能还在内存中。
如果发生了实例崩溃,只需要在日志文件中找到检查点位置,从此处开始应用所有的重做日志文件,就完成了前滚操作.
实例崩溃后,再次启动数据库,oracle会到控制文件中读取low cache rba,这就是检查点位置.
从此处开始应用重做信息,应用到on disk rba处.
on disk rba是磁盘中重做日志文件的最后一条重做记录的rba.
如果某条命令的重做记录的rba高于on disk rba，那说明此重做记录还没有被写进日志文件中
崩溃恢复发生时，他是不可能被恢复的. 所以oracle中的commit要保证日志都写到redo才算提交完成
on disk rba是oracle前滚操作的终点。就是'在磁盘上'的意思,是和log_buffer在内存中的相对而言.
比on disk rba更高的rba,都在log buffer中,
还没有来的急被写进磁盘中的日志文件.所以是不能被用于恢复的.
从检查点位置处，应用重做记录到On disk rba处，这个过程就是前滚。
日志损坏的恢复
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 4 52428800 1 NO INACTIVE 515008 28-MAR-11
2 1 3 52428800 1 NO INACTIVE 514969 28-MAR-11
3 1 5 52428800 1 NO CURRENT 515011 28-MAR-11
SQL> select * from v$Logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO
1 ONLINE /u01/oracle/oradata/ora10g/redo01.log NO
SQL>
如果多成员中一个成员被删除 数据库不受影响
如果删除的是INACTIVE组
SQL> ! rm /u01/oracle/oradata/ora10g/redo02.log
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/ora10g/redo02.log'
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
如果删除的是ACTIVE组 只要是一致停库 就和INACTIVE没区别 因为一致停库会做完全检查点
如果删除的CURRENT组 要使用基于cancel的不完全恢复(目的是为了要使用resetlogs)
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 4 52428800 1 NO INACTIVE 515008 28-MAR-11
2 1 6 52428800 1 NO CURRENT 515178 28-MAR-11
3 1 5 52428800 1 NO INACTIVE 515011 28-MAR-11
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /u01/oracle/oradata/ora10g/redo03.log NO
2 ONLINE /u01/oracle/oradata/ora10g/redo02.log NO
1 ONLINE /u01/oracle/oradata/ora10g/redo01.log NO
SQL> ! rm /u01/oracle/oradata/ora10g/redo02.log
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/ora10g/redo02.log'
SQL> alter database clear logfile group 2; 无法清洗
alter database clear 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: '/u01/oracle/oradata/ora10g/redo02.log'
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 515398 generated at 03/28/2011 04:24:13 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/ORA10G/archivelog/2011_03_28/o1_mf_1_6_%u_.arc
ORA-00280: change 515398 for thread 1 is in sequence #6
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
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;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oracle/oradata/ora10g/system01.dbf'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using BACKUP CONTROLFILE until cancel; 日志不要了.(不完全恢复)放弃了curren日志.直接启库
ORA-00279: change 515398 generated at 03/28/2011 04:24:13 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/ORA10G/archivelog/2011_03_28/o1_mf_1_6_%u_.arc
ORA-00280: change 515398 for thread 1 is in sequence #6
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL>
改变日志成员大小：
成员大小不可以直接控制
只能控制组的大小 组的大小就代表组内每个成员大小
组的大小在创建时指明 后期不能该
解决的办法：建立新的需求大小的日志组
在删除原来的
损坏：日志损坏，要看损坏的是那种状态的
不同的状态组损坏 有不一样的修复方法
生产库 日志的成员实现多元化管理
INACTIVE损坏 直接清洗
ACTIVE损坏 是否归档 归档没问题
未归档 又没多元化 数据丢失
CURRENT 损坏 和归档不归档无关
归档也完就不了CURRENT损坏
解决的以为方法 多元化
如果没多元化 数据丢失
.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756465/viewspace-717777/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24756465/viewspace-717777/
扫一扫
2482
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
