In my previous article, I briefly introduced what RedoLog is and how to Dump binary logs from Oracle. Redo Log binary file formats: file headers, Redo Log headers, DML-INSERT, and DDL-create.

In the Redo Log binary file, the Log file is in small endian byte order.

The original link: www.chuonye.com/archives/or…

1. File Header

File header, occupying the first block. Many binary data and Log files in Oracle have a similar format, as do online Log Redo logs. Below is the first 80 bytes of the RedoLog file.

File Type

The first two bytes indicate the file type that distinguishes different Oracle files, for example, in the 10G version:

  • 0xA2Data File
  • 0xC2Control File
  • 0x22Log File: Redo Log File

An Oracle database is just a bunch of files.

Block Size

The offset 20, which is 2 bytes long, stores the block size, and its value is fixed and varies only by operating system. On Windows, Linux, and Solaris, the block size is 512 bytes – 0x0200, whereas on HP-UX it is 1024.

In addition, each block has a 16-byte bulk, which I’ll cover later.

Number of Blocks

Offset 24, length of 4 bytes, stores the number of blocks in the file, not including the block used by the file header itself, we can calculate the size of the entire file:

(0x00019000 + 1) * 512 = 52429312 (50MB)
Copy the code

Magic

Magic number is just a file identifier used to check if it is an Oracle file.

Block Header

Each block has a 16-byte header, which is important when parsing a Redo Record that spans multiple blocks.

The yellow in the figure above indicates an example of a header, each of which begins with the signature 0x0122, where:

  • The offset4Length,4 bytes, stores blocks in a fileSerial number
  • The offset8Length,4 bytesIs storedThe log sequence number
  • The offset12Length,1 byte, stores the Record in the blockByte offset

These three values happen to be the RBA contents of Record.

The final offset, 14, is 2 bytes long and stores the checksum, which is used to verify whether the data is complete. The logic for validation is not recorded here, but can be found in the PDF file described in the previous article.

2. Redo Log Header

Redo the log header to occupy the second block. This contains a lot of information, such as the database SID, the database version, and the time when the recording started.

3. Redo Record

The Redo Record contains all the operations in an SCN and consists of a header and one or more change vectors. For example, inserting a data entry into an indexed table creates the following:

  • Create them separately for the INSERT operationredo change 和 undo change
  • For index changes, created separatelyredo change 和 undo change
  • A transaction starts change and a transaction commits change

Each of these changes has an opcode to distinguish between common opcodes:

  • 5.1: Undo the modification -undo Record
  • 5.2: Transaction starts
  • 5.4: Transaction Commit – Commit
  • 11.2: Insert a row of data
  • 11.3: Deletes a row of data
  • 11.11: Insert multiple rows of data
  • 11.19: Update multi-row data
  • 10.2: INSERT an index – INSERT LEAF ROW
  • 10.4: Deleting an index -delete LEAF ROW
  • 13.1: Applying for space – After creating a TABLE
  • 24.1: DDL operation

4. DML-INSERT

Add, delete and change is the basic operation of the database, the figure below shows an insert operation Record into hexadecimal information.

The 12th byte of the Block header 0x10 indicates that the start byte of the Record is at an offset of 16 bytes;

The first 2 bytes of a Record indicate length, up to 65536 bytes, so it may require multiple block storage. Here the length is 0x01A8=424. One block is sufficient for storage. The fourth byte after the length of the Record header identifies the VLD, depending on the type of Record, where 0x0D indicates that the header length is 0x44=68;

Skip the 0x44 byte to find the first Change Vector with the opcode 0x0b02-11.2, the INSERT operation. Insert sys. SYSAUTH$; insert sys. SYSAUTH$; insert sys. SYSAUTH$; insert sys. SYSAUTH$; insert sys. SYSAUTH$; insert sys. SYSAUTH$; insert sys. SYSAUTH$;

Skipping 0x44+0x18 bytes, the first 2 bytes 0x000C=12 represent the length of the element length list for the first Change. The element length takes up 2 bytes, and 12 bytes represent a total of (12-2)/2=5 length elements excluding the first 2 bytes, which means that 3 field contents have been inserted.

  • 0x0014and0x0031: The two values are semi-fixed, indicating thatKTB 和 KDOThe length of the
  • 0x0002: indicates the number of data bytes inserted into the first column2 -
  • 0x0002: indicates that the number of data bytes inserted into the second column is also2 -
  • 0x0003: indicates the number of data bytes inserted into the third column3 bytes

The length above calculates the actual length, but 4-byte alignment is required to calculate the offset. Skipping the specified byte yields three fields with values of:

  • 0xC102: Indicates the contentThe number 1
  • 0xC105: Indicates the contentNumber four
  • 0xC20931: Indicates the contentThe number 848

SQL > select * from SYSAUTH$;

SQL> INSERT INTO SYS.SYSAUTH$ (GRANTEE#,PRIVILEGE#, SEQUENCE#) VALUES (1,4,848); 
Copy the code

There are two further changes, 0x0502 and 0x0501. 0x0502 can resolve the XID of this transaction; 0x0501 is an undo operation, and INSERT corresponds to DELETE.

5. DDL-CREATE

SQL > ALTER SYSTEM DUMP LOGFILE; SQL > ALTER SYSTEM DUMP LOGFILE; SQL > ALTER SYSTEM DUMP LOGFILE;

REDO RECORD - Thread:1 RBA: 0x000082.0000Febf.002C LEN: 0x00F4 VLD: 0x01 SCN: 0x00000.003a061F SUBSCN: 1 03/13/2007 13:55:41 CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:24.1Copy the code

The operation code 24.1 indicates a DDL operation. From RBA, we can see that the block number is 0x0000FEBF (65215) and the block size is 512, so the offset of this operation in binary is 512*65215=33390080, and the hexadecimal is 0x01FD7E00:

You can clearly see the DDL statement:

create user wiggywiggywiggy identified by VALUES '2FA1749D698AD874'
Copy the code

The corresponding binary format mapping information is:

6. Summary

This article briefly describes what the binary format is, the actual analysis is also the same, the binary file opened into a hexadecimal display, byte by byte analysis. If you have done network programming, especially TCP private protocol design and parsing, it should be easy to understand.

The next article will cover some of the parsing issues, such as how to calculate the length of the Record header, how to calculate the Rowid, and so on.