oracle redo internal,Internal of Redo2

(四.六)Direct Load(直接加载)

*.If archiving is enabled then direct load blocks are written to redo

*.In Oracle 9.2 each block requires a 19.1 and 24.2 change

*.直接加载比传统的insert 产生的redo少很多,如下例是32条记录,但是ITL就3个就搞定,如果传统的话估计要32个itl(多个session)

eg:

SQLLDR直接加载file#4,block 3404

------------------------

alter system dump logfile '/opt/ora_log/FSXYBAK/onlinelog/o1_mf_2_54n7c6mn_.log' dba min 4 3404 dba max 4 3404';

..........

.........

REDO RECORD - Thread:1 RBA: 0x000080.00014791.0110 LEN: 0x2024 VLD: 0x01

SCN: 0x0000.0029a44b SUBSCN:  1 10/31/2009 15:46:56

CHANGE #1 TYP:1 CLS: 1 AFN:4 DBA:0x01000d4c OBJ:43812 SCN:0x0000.0029a44b SEQ:  1 OP:19.1

Direct Loader block redo entry                                                        --Eirect Loader

Block header dump:  0x00000000

Object id on Block? Y

seg/obj: 0xab24  csc: 0x00.29a44a  itc: 3  flg: E  typ: 1 - DATA

brn: 0  bdba: 0x1000d49 ver: 0x01 opc: 0

inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0029a44a

0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

----------------------------------

(四.七)Nologging of redo

1.Redo generation can be disabled for some operations using

*.UNRECOVERABLE (Oracle 7)

*.NOLOGGING (Oracle 8.0 and above)                                                                     --&gtNologging 可以防止redo产生

*.Objects created using NOLOGGING cannot be recovered                                                  --&gtNologging 创建的对象将不可恢复

*.Backup should be taken immediately affect using NOLOGGING

*.ALTER TABLESPACE /database FORCE LOGGING;                                           --&gt强制logging

--&gtNologging后得立即备份

2.Nologging可以在哪些场合使用

Can be enabled for

specific tables and indexes at object level or statement level                                         --&gt可以在表和索引的对象和语句级别使用nologging

eg:

create index/table xxx nologging;

The following redo log entry indicates a range of blocks which cannot be recovered:

这些范围的块不能恢复

-------------

REDO RECORD - Thread:1 RBA: 0x003674.00000006.01e0 LEN: 0x0028 VLD: 0x01

SCN: 0x0000.00ebaeec SUBSCN:  1 05/09/2003 11:16:07

CHANGE #1 INVLD AFN:5 DBA:0x0142ff03 BLKS:0x001f SCN:0x0000.00ebaeec SEQ:  1 OP:19.2

Direct Loader invalidate block range redo entry                                                        --&gt直接加载的无效块范围的 redo entry                                               --&gt

------------

Nologgin 的相关操作以及对照10000row for test

Redo size in bytes

Operation                        LOGGING NOLOGGING

CREATE TABLE AS SELECT          14238844 39548

ALTER TABLE MOVE                14227236 45340

INSERT /*+ APPEND */            14221904 42452

CREATE MATERIALIZED VIEW        20726784 3784532

CREATE INDEX                    2042532 24548

ALTER INDEX REBUILD              2056440 32192

ALTER INDEX REBUILD ONLINE      2083832 67840

SQL*Loader (Direct)              14248116 56712

Online Reorganization            21330788 7169472