第七章： REDO 日志(1)
1、redo （重做） log 的功能：数据recovery
2、redo log 特征： 1）记录数据块的变化（DML、DDL) 2) 用于数据块的recover 3）以组的方式管理redo file ，最少两组redo ，循环使用 4）和数据文件存放到不同的磁盘上，需读写速度快的磁盘(比如采用RAID10) 日志切换： 1）归档模式：将历史日志进行保存 2）非归档： 历史日志被覆盖 3）并产生checkpoint，通知redo log 所对应的 dirty block 从data buffer写入到datafile，并且更新控制文件 3、redo 日志组 1） 最少两组，最好每组有两个成员，并存放到不同的磁盘上，大小形同，互相镜像 2）日志在组写满时发生切换，或手工切换： alter system switch logfile ； 3）在归档模式，日志进行归档，并把相关的信息写入controlfile 4、添加日志组 04:33:02 SQL> startup ORACLE instance started.Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes Variable Size 134219516 bytes Database Buffers 113246208 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. 04:33:24 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 2 1 7 10485760 1 NO CURRENT 381102 02-AUG-11 1 1 6 10485760 1 NO INACTIVE 357157 01-AUG-1104:34:30 SQL> col member for a50
04:34:38 SQL> select group#, member from v$logfile;GROUP# MEMBER
---------- -------------------------------------------------- 2 /u01/app/oracle/oradata/lx02/redo02a.log 1 /u01/app/oracle/oradata/lx02/redo01a.log04:34:39 SQL> alter database add logfile
04:34:53 2 '/u01/app/oracle/oradata/lx02/redo03a.log' size 10m;Database altered.
04:35:18 SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------- 2 /u01/app/oracle/oradata/lx02/redo02a.log 1 /u01/app/oracle/oradata/lx02/redo01a.log 3 /u01/app/oracle/oradata/lx02/redo03a.log04:35:24 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 6 10485760 1 NO INACTIVE 357157 01-AUG-11 3 1 0 10485760 1 YES UNUSED 0 2 1 7 10485760 1 NO CURRENT 381102 02-AUG-1104:35:31 SQL>
5、添加日志组的成员
04:36:54 SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------- 2 /u01/app/oracle/oradata/lx02/redo02a.log 1 /u01/app/oracle/oradata/lx02/redo01a.log 3 /u01/app/oracle/oradata/lx02/redo03a.log 04:38:30 SQL> alter database add logfile member 2 '/disk1/lx02/oradata/redo01b.log' to group 1, 3 '/disk1/lx02/oradata/redo02b.log' to group 2, 4* '/disk1/lx02/oradata/redo03b.log' to group 3； 04:38:31 SQL>Database altered.
04:38:36 SQL> select group#, member from v$logfile order by 1;
GROUP# MEMBER
---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/lx02/redo01a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 2 /u01/app/oracle/oradata/lx02/redo02a.log 3 /disk1/lx02/oradata/redo03b.log 3 /u01/app/oracle/oradata/lx02/redo03a.log6 rows selected.
04:38:47 SQL>
6、查看日志信息
04:38:47 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 6 10485760 2 NO INACTIVE 357157 01-AUG-11 3 1 0 10485760 2 YES UNUSED 0 2 1 7 10485760 2 NO CURRENT 381102 02-AUG-1104:40:19 SQL> alter system switch logfile;
System altered.
04:40:42 SQL> /
System altered.
04:40:43 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 9 10485760 2 NO CURRENT 384007 02-AUG-11 3 1 8 10485760 2 NO ACTIVE 384005 02-AUG-11 2 1 7 10485760 2 NO ACTIVE 381102 02-AUG-1104:40:45 SQL>
status： unused 新添加的日志组，还没有使用
inactive 日志组对应的脏块已经从data buffer写入到data file ，可以删除 active 日志组对应的脏块还没有从data buffer写入到data file，不能被删除 current 当前日志组，日志组对应的脏块还没有从data buffer写入到data file，不能被删除 在删除日志组之前，进行日志手工切换，将被删除的日志切换到inactive状态。 thread ：线程（通过后台进程lgwr 启动），在单实例的环境下，thread# 永远是1 sequence ：日志序列号。在日志切换时会递增。 FIRST_CHANGE# ：在当前日志中记录的首个数据块的scn。（当事务完成的时候会在数据块上写入一个scn，代表数据块的变化）。 ----------查看生成的日志量 14:51:12 SQL> insert into scott.emp1 select * from scott.emp1;28 rows created.
14:51:33 SQL> insert into scott.emp1 select * from scott.emp1;
56 rows created.
14:51:35 SQL> select a.name,b.value from v$statname a,v$mystat b
14:51:39 2 where b.statistic#=a.statistic# and a.name like '%redo size%';NAME VALUE
---------------------------------------------------------------- ---------- redo size 454414:51:42 SQL>
7、redo 日志成员重命名或迁移 05:07:37 SQL> select group#,member from v$logfile order by 1;GROUP# MEMBER
---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/lx02/redo01a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 2 /u01/app/oracle/oradata/lx02/redo02a.log 3 /disk1/lx02/oradata/redo03b.log 3 /u01/app/oracle/oradata/lx02/redo03a.log6 rows selected.
05:07:43 SQL> shutdown immediate
Database closed. Database dismounted. ORACLE instance shut down. 05:08:01 SQL> ! [oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/redo01a.log /disk2/lx02/oradata/ [oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/redo02a.log /disk2/lx02/oradata [oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/redo03a.log /disk2/lx02/oradata [oracle@oracle ~]$ !sql sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:08:35 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:08:35 SQL> startup mount
ORACLE instance started.Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes Variable Size 125830908 bytes Database Buffers 121634816 bytes Redo Buffers 2973696 bytes Database mounted. 05:08:43 SQL> col member for a50 05:08:49 SQL> select group# ,member from v$logfile order by 1;GROUP# MEMBER
---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/lx02/redo01a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 2 /u01/app/oracle/oradata/lx02/redo02a.log 3 /disk1/lx02/oradata/redo03b.log 3 /u01/app/oracle/oradata/lx02/redo03a.log6 rows selected.
05:08:58 SQL> alter database rename file
05:09:05 2 '/u01/app/oracle/oradata/lx02/redo01a.log' to '/disk2/lx02/oradata/redo01a.log';Database altered.
05:09:22 SQL> alter database rename file
05:09:26 2 '/u01/app/oracle/oradata/lx02/redo02a.log' to '/disk2/lx02/oradata/redo02a.log';Database altered.
05:09:34 SQL> alter database rename file
05:09:35 2 '/u01/app/oracle/oradata/lx02/redo03a.log' to '/disk2/lx02/oradata/redo03a.log';Database altered.
05:09:42 SQL> select group# ,member from v$logfile order by 1;
GROUP# MEMBER
---------- -------------------------------------------------- 1 /disk2/lx02/oradata/redo01a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 2 /disk2/lx02/oradata/redo02a.log 3 /disk1/lx02/oradata/redo03b.log 3 /disk2/lx02/oradata/redo03a.log6 rows selected.
05:09:53 SQL> alter database open;Database altered.
05:10:01 SQL> select group# ,member from v$logfile order by 1;
GROUP# MEMBER
---------- -------------------------------------------------- 1 /disk2/lx02/oradata/redo01a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 2 /disk2/lx02/oradata/redo02a.log 3 /disk1/lx02/oradata/redo03b.log 3 /disk2/lx02/oradata/redo03a.log6 rows selected.
05:10:06 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 9 10485760 2 NO INACTIVE 384007 02-AUG-11 3 1 8 10485760 2 NO INACTIVE 384005 02-AUG-11 2 1 10 10485760 2 NO CURRENT 385481 02-AUG-1105:10:12 SQL>