Understanding the Redo (6) log but the process and redo analysis of direct path Loading

Before the server process obtains the redo allocation latch for redo log Buffer Allocation, You need to sniff the redo log file to check whether there is sufficient space. If the space is insufficient, the SP will send a request for the switch log file and wait until the log file switch completion event is complete.

 

After a log request is sent, ckpt performs an incremental checkpoint event, while lgwr initiates a log change.

 

The specific process is as follows:

1) The lgwr process will find an available redo log file through the two-way linked list in the control file as the new current redo log.
The algorithm is as follows:

The log file is inactive and has been archived.
Prioritize unused Log File groups

 

2) flush the redo entries not written in the redo log buffer to the current online redo log file, the SCN of the last redo entries is recorded in the redo log header as the high SCN of the current log file, and the current online redo log file is closed.

 

3) conduct the second control file transaction and mark the closed redo log as active (this is an incremental checkpoint event, the reason why it is identified as active, this is because the dirty buffer it protects may not be written to the data file. If it has all been written to the disk, it can be identified as inactive.) mark the new redo log as current, if the database is in archive mode, you need to record the old log group to the Control File Archive list record and notify arcn to archive the log file.

 

4) lgwr opens all the members of the new log group and records the current log sequence # And the SCN (low SCN) of the First redo block in the log file header)

 

5) lgwr modifies the flag in the SGA to generate new redo log information.

 

 

To sum up, log switching is an expensive operation. During the change period, all transactions to the database will be blocked. However, increasing the size of the redo log file is irrelevant to the amount of data lost. Reason:

1) Redo entries is written in sequence, and one or more are written, which is the same for recovery.
2) Storage faults are certainly affected by all redo log files.

 

In archive mode, direct path Loading records redo. In non-archive log mode, direct path loading does not record Redo, But Oracle generates redo because of system changes that require maintenance of segments, partitions, and tablespaces.

 

The redo log file is dumped to dump.

At 09:29:36, sys @ orcl (^ ω ^) Conn HR/HR is connected. 09:34:57 HR @ orcl (^ ω ^) Create Table Test as select * From dba_objects where 1 = 2; the table has been created. 09:35:41 HR @ orcl (^ ω ^) Select region from dual; get_system_change_number ---------------------- 280423009: 36: 06 hr @ orcl (^ ω ^) select group #, status from V $ log; group # status ---------- ------------------------------ 1 current 2 inactive 3 inactive09: 36: 26 HR @ orcl (^ ω ^) COL member for a7209: 36 hr @ orcl (^ ω ^) select group #, member from V $ logfile; group # member ------- --- Release 3 D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ onlinelog \ Release 3 D: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ orcl \ onlinelog \ o1_mf_3_7tqz x11d_.log 2 D: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ onlinelog \ Release 2 D: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ orcl \ onlinelog \ o1_mf_2_7tqz wyph_.log 1 D: \ Orac Le \ product \ 10.2.0 \ oradata \ orcl \ onlinelog \ Release 1 D: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ orcl \ onlinelog \ o1_mf_1_7tqz wwj8_.log has selected 6 rows. 09:37:24 HR @ orcl (^ ω ^) Select dbms_flashback.get_system_change_number from dual; get_system_change_number -------------------- 280435309: 37: 59 HR @ orcl (^ ω ^) insert/* + append */into test select * From dba_objects; 50453 rows have been created. 09:39:15 HR @ orcl (^ ω ^) commit; submitted completely. 09:39:21 HR @ orcl (^ ω ^) Select dbms_flashback.get_system_change_number from dual; get_system_change_number ---------------------- 280452109: 39: 29 HR @ orcl (^ ω ^) alter system dump logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ onlinelog \ ow.mf_rj7tqzwvdd_.log '09: 40: 25 2 SCN min 2804353 SCN Max 2804521; the system has changed. 09:40:57 HR @ orcl (^ ω ^) Conn/As sysdba is connected. 09:41:09 sys @ orcl (^ ω ^) archive log list database log mode archive mode automatic archiving enable archiving end point use_db_recovery_file_dest oldest online log sequence 3 next archive log sequence 5 current log sequence 509: 41: 19 sys @ orcl (^ ω ^) alter system dump logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ onlinelog \ o1_mf_1_7tqzwvdd_.log '09: 43: 23 2 SCN min 2804230 SCN Max 2804521; the system has changed. At 09:44:03, sys @ orcl (^ ω ^) Conn HR/HR is connected. 09:45:58 HR @ orcl (^ ω ^) Select dbms_flashback.get_system_change_number from dual; get_system_change_number -------------------- 280480109: 46: 07 hr @ orcl (^ ω ^) insert/* + append */into text select * From dba_objects; insert/* + append */into text select * From dba_objects * row 1st error: ORA-00942: the table or view does not exist at 09:47:42 HR @ orcl (^ ω ^) insert/* + append */into test select * From dba_objects; 50453 rows have been created. 09:47:53 HR @ orcl (^ ω ^) commit; submitted completely. 09:47:58 HR @ orcl (^ ω ^) Select region from dual; get_system_change_number ---------------------- 280515509: 48: 10 hr @ orcl (^ ω ^) select group #, status from V $ log; group # status ---------- ------------------------------ 1 current 2 inactive 3 inactive09: 48: 50 HR @ orcl (^ ω ^) alter system dump logfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ onlinelog \ o1_mf_1_7tqzwvdd_.log '09: 49: 32 2 SCN min 2804801 SCN Max 2805155;

Extract Part 1 of OP as follows:

REDO RECORD - Thread:1 RBA: 0x000005.00003627.019c LEN: 0x2024 VLD: 0x01SCN: 0x0000.002ad2e0 SUBSCN:  1 08/19/2012 10:05:48CHANGE #1 TYP:1 CLS: 1 AFN:4 DBA:0x01006015 OBJ:53846 SCN:0x0000.002ad2e0 SEQ:  1 OP:19.1Direct Loader block redo entryBlock header dump:  0x08a90000 Object id on Block? Y seg/obj: 0xd256  csc: 0x00.2ad2df  itc: 3  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1006009 ver: 0x01 opc: 0     inc: 0  exflg: 0  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0007.01d.000002ae  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.000000000x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000 data_block_dump,data header at 0x3bae8288===============tsiz: 0x1f80hsiz: 0xc8pbl: 0x3bae8288bdba: 0x08a90000     76543210flag=--------ntab=1nrow=91frre=-1fsbo=0xc8fseo=0x432avsp=0x36atosp=0x36a0xe:pti[0]nrow=91offs=00x12:pri[0]offs=0x1f360x14:pri[1]offs=0x1eea0x16:pri[2]offs=0x1ea10x18:pri[3]offs=0x1e570x1a:pri[4]offs=0x1e090x1c:pri[5]offs=0x1dbe0x1e:pri[6]offs=0x1d690x20:pri[7]offs=0x1d1e0x22:pri[8]offs=0x1cd20x24:pri[9]offs=0x1c790x26:pri[10]offs=0x1c2f0x28:pri[11]offs=0x1be60x2a:pri[12]offs=0x1b930x2c:pri[13]offs=0x1b470x2e:pri[14]offs=0x1afc0x30:pri[15]offs=0x1ab10x32:pri[16]offs=0x1a670x34:pri[17]offs=0x1a1b0x36:pri[18]offs=0x19d20x38:pri[19]offs=0x19890x3a:pri[20]offs=0x193d0x3c:pri[21]offs=0x18f10x3e:pri[22]offs=0x18a80x40:pri[23]offs=0x185e0x42:pri[24]offs=0x18120x44:pri[25]offs=0x17c90x46:pri[26]offs=0x17790x48:pri[27]offs=0x17270x4a:pri[28]offs=0x16dc0x4c:pri[29]offs=0x16910x4e:pri[30]offs=0x16460x50:pri[31]offs=0x15fa0x52:pri[32]offs=0x15b20x54:pri[33]offs=0x155d0x56:pri[34]offs=0x150f0x58:pri[35]offs=0x14c30x5a:pri[36]offs=0x14740x5c:pri[37]offs=0x142b0x5e:pri[38]offs=0x13e00x60:pri[39]offs=0x13960x62:pri[40]offs=0x134c0x64:pri[41]offs=0x13010x66:pri[42]offs=0x12b50x68:pri[43]offs=0x126c0x6a:pri[44]offs=0x12210x6c:pri[45]offs=0x11d40x6e:pri[46]offs=0x118b0x70:pri[47]offs=0x11410x72:pri[48]offs=0x10f50x74:pri[49]offs=0x10a90x76:pri[50]offs=0x105d0x78:pri[51]offs=0x10110x7a:pri[52]offs=0xfc60x7c:pri[53]offs=0xf7a0x7e:pri[54]offs=0xf210x80:pri[55]offs=0xed40x82:pri[56]offs=0xe880x84:pri[57]offs=0xe3a0x86:pri[58]offs=0xdec0x88:pri[59]offs=0xda30x8a:pri[60]offs=0xd5a0x8c:pri[61]offs=0xd100x8e:pri[62]offs=0xcc00x90:pri[63]offs=0xc720x92:pri[64]offs=0xc220x94:pri[65]offs=0xbd20x96:pri[66]offs=0xb890x98:pri[67]offs=0xb3a0x9a:pri[68]offs=0xae70x9c:pri[69]offs=0xa990x9e:pri[70]offs=0xa4d0xa0:pri[71]offs=0xa000xa2:pri[72]offs=0x9b20xa4:pri[73]offs=0x9650xa6:pri[74]offs=0x9180xa8:pri[75]offs=0x8cf0xaa:pri[76]offs=0x8820xac:pri[77]offs=0x8370xae:pri[78]offs=0x7e90xb0:pri[79]offs=0x79c0xb2:pri[80]offs=0x74c0xb4:pri[81]offs=0x6fa0xb6:pri[82]offs=0x6a80xb8:pri[83]offs=0x6560xba:pri[84]offs=0x6040xbc:pri[85]offs=0x5b70xbe:pri[86]offs=0x56a0xc0:pri[87]offs=0x51d0xc2:pri[88]offs=0x4d00xc4:pri[89]offs=0x4820xc6:pri[90]offs=0x432block_row_dump:tab 0, row 0, @0x1f36tl: 74 fb: --H-FL-- lb: 0x0  cc: 13col  0: [ 3]  53 59 53col  1: [ 5]  49 43 4f 4c 24col  2: *NULL*col  3: [ 2]  c1 15col  4: [ 2]  c1 03col  5: [ 5]  54 41 42 4c 45col  6: [ 7]  78 69 08 1e 0e 33 19col  7: [ 7]  78 69 08 1e 0f 1b 13col  8: [19]  32 30 30 35 2d 30 38 2d 33 30 3a 31 33 3a 35 30 3a 32 34col  9: [ 5]  56 41 4c 49 44col 10: [ 1]  4ecol 11: [ 1]  4ecol 12: [ 1]  4etab 0, row 1, @0x1eeatl: 76 fb: --H-FL-- lb: 0x0  cc: 13col  0: [ 3]  53 59 53col  1: [ 7]  49 5f 55 53 45 52 31col  2: *NULL*col  3: [ 2]  c1 2dcol  4: [ 2]  c1 2dcol  5: [ 5]  49 4e 44 45 58col  6: [ 7]  78 69 08 1e 0e 33 1acol  7: [ 7]  78 69 08 1e 0e 33 1acol  8: [19]  32 30 30 35 2d 30 38 2d 33 30 3a 31 33 3a 35 30 3a 32 35col  9: [ 5]  56 41 4c 49 44col 10: [ 1]  4ecol 11: [ 1]  4e

Direct path loading only goes through PGA, But SGA, and the data is above hwm. From the preceding TRC file, we can see that in archive mode, the entire data block is put into the redo log during direct path load, which greatly reduces the redo generation, this ensures that no data will be lost.

 

The following is a comparison of redo entries generated by different operations under nologging and logging: