本帖最后由 guoyJoe 于 2013-3-20 12:09 编辑
一、什么是REDO LOG
REDOLOG文件是十分重要的文件,它记录了Oracle的所有变化,是数据库实例恢复机制中最为关键的组成部分。
sys@OCM> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ------------ --------------
1
1 49 52428800512
1 YES INACTIVE
2701394 09-3?? -13 2711001 09-3?? -13
2
1 50 52428800512
1 YES INACTIVE
2711001 09-3?? -13 2732482 10-3?? -13
3
1 51 52428800512
1 NOCURRENT
2732482 10-3?? -13 2.8147E+14
sys@OCM> col member for a50
sys@OCM> select * from v$logfile;
GROUP# STATUSTYPEMEMBER
IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE/u01/app/oracle/oradata/ocm/redo03.log
NO
2 ONLINE/u01/app/oracle/oradata/ocm/redo02.log
NO
1 ONLINE/u01/app/oracle/oradata/ocm/redo01.log
NO
二、REDO LOG的作用
1、记录ORACLE数据库的变化
2、可以避免数据提交后直接写入数据文件
3、实例恢复和介质恢复
三、REDO LOG的块
1、块的大小
(1)dbfsize redo01.log
(2)SELECT DISTINCT BLOCK_SIZE FROM V$ARCHIVED_LOG;
(3)SELECT MAX(LEBSZ) FROM X$KCCLE;
(4)日志文件头的内容 ALTER SESSION SET EVENTS 'immediate trace name redohdr level 10';
2、REDO的内容
(1)改变矢量(Change Vector)
(2)重做记录(Redo Record)
(3)一条插入的产生的日志
create table t5(id int,name varchar2(100));
select max(ktuxescnw * power(2,32)+ktuxescnb) from x$ktuxe;--[K]ernel [T]ransaction [U]ndo Transa[x]tion Entry
insert into t5 values(1,'AAAAAA');
commit;
select max(ktuxescnw * power(2,32)+ktuxescnb) from x$ktuxe;
alter system dump logfile '/u01/app/oracle/oradata/ocp/redo02.log' scn min 1694394 scn max 1693357;
四、和REDO LOG性能相关的组件
1、记录实例中的数据库变化
2、顺序存取
3、环状的缓冲区
4、LOG_BUFFER定义了缓冲区的大小 (1)9i以前,一般是3M
(2)在10g中ORACLE会自动调整它的值,他遵循这样一个原则,'Fixed SGA Size'+ 'Redo Buffers'是granule size 的整数倍
select * from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers','Granule Size');
--在10.2.0.3 中Log Buffer 默认值是14M,在10.2.0.4中,默认值是15M
select * from v$version where rownumlogfile sync等待开始--->lgwr开始-->lgwr完成(IO)-->logfile sync等待结束--commit完成
(2)解决方法:
①IO性能来解决这个问题:RAID 5--->RAID 1+0
②加大LOG BUFFER
③减少提交的次数
④把部分经常提交的事务设置为异步提交:ALTER SESSION SET COMMIT_WRITE = NOWAIT;
(3)nologging使用
表空间:1.nologgin,2.force logging
alter tablespace test3 nologging;
create table a(id int) tablespace test3; //test3-->nologging
alter tablespace test3 no force logging;
表:1.no force logging start2.表本身no logging(alter table a nologging;)3.使用append插入
insert /*+ append */ into test3 select * from t3;
*************supplemental
select supplemental_log_data_min,supplemental_log_data_pk from v$databae;
alter database add supplemental log data; --rowid
alter database add supplemental log data(primary key) columns; --primary
六、开启归档模式
SELECT log_mode FROM v$database;
ARCHIVE LOG LIST;
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog3';
shutdown immediate;
startup mount;
alter database archivelog;
select dest_name,status,archiver,destination,log_sequence,reopen_secs,transmit_mode,process
from v$archive_dest;--归档路径及状态
select name,sequence#,registrar,standby_dest,archived,status from v$archived_log;--从控制文件中获得归档的相关信息
v$log_history
--控制文件中日志的历史信息
v$archive_processes--归档相关的后台进程信息
select member,bytes/1024/1024 from v$log a,v$logfile b where a.group#=b.group#;
|