Oracle 11g archive log study _ 4, oracle11g ARCHIVE _ 4

Oracle 11g archive log study _ 4, oracle11g ARCHIVE _ 4

The change content is the core of converting oracle logs into SQL statements, and is also the most troublesome and most changing place.

The meaning of opcode is that there are a lot of searches on the Internet. What is really useful to me is addition, deletion, and modification. I don't care about any search or index operations at all.

5.1: contains a large amount of information. Each addition, deletion, and modification must correspond to a valid 5.1 value. This 5.1 value will contain raw data for use in undo rollback. In addition, there will be a large number of 5.1 operations. At present, I do not understand the meaning of the other 5.1 operations, and ignore these 5.1 operations that I think are "invalid. 5.1 of the data content is arranged in the following order:

Typedef struct ktudb {uint16_t siz; uint16_t spc; Trans flg; uint16_t unknown0; uint16_t xid0; uint16_t xid1; uint32_t xid2; uint16_t seq; uint8_t rec; uint8_t ufo; // 0x84: ktubu; 0x00: ktubl} Redo_ktudb;Redo_ktudb

This is the first piece of data after the change vector table of 5.1. The xid may be used as the id of txn. I don't know what other values are.

Typedef struct ktubl {struct objn; struct objd; struct tsn; // maybe uint32_t noname; // maybe region opc0; region opc1; region slt; uint32_t unknown1; unknown unknown0; // 00 00 00 00 uint32_t uba0; uint16_t uba1; uint8_t uba2; uint8_t unknown2; // 00 then max_scn1; then max_scn0; then unknown3; then tx_scn1; then success; uint16_t unknown4; uint32_t unknown5; // 00 00 00 00 uint32_t txn_scn1; // The position of uint16_t txn_scn0; uint16_t unknown6; // 00 00 uint32_t brb; uint32_t unknown7; // 00 00 00 00 uint8_t user; uint8_t bcl; // maybe uint8_t idx; // maybe uint8_t flg2; // maybe} Redo_ktubl;Redo_ktubl

This is the second paragraph, in which opc0.opc1 should be 11.1, corresponding to our addition, deletion, modification, and other values are ignored by me. Objd is treated as obj and searches for corresponding items in the data dictionary. Although the structure of ktubl is defined as this, the actual data is longer, and it may only have 24 bytes. Therefore, it is possible that only the values of the first few elements are actually used.

Typedef struct KTB {javasop0; javasunknown0 [7]; Primary xid0; Primary xid1; Primary xid2; Primary uba0; Primary uba1; Primary uba2; Primary unknown1; Primary unknown2; Primary scn0; uint32_t scn1;} Redo_KTB;Redo_KTB

This segment also gets longer. In fact, many operations have KTB segments, but their structures are different.

Typedef struct KDO {uint32_t bdba; uint32_t hdba; uint16_t maxfr; uint8_t unop0; uint8_t unop1; // maybe effecitli; uint8_t unknown1 [3]; region slot; uint8_t unknown2 [3];} Redo_KDO;Redo_KDO

This section also gets longer. Well, it seems that nothing in section 5.1 is fixed.

The data after these four segments belongs to the undo segment, and the data placement formats are similar. You can print and analyze the content according to the vector table.

 

5.2: it contains less information and may start as a transaction (txn), but it is ignored currently.

Typedef struct ktudh {javasxid1; // slt uint16_t unknown0; javasxid2; // sqn using uba0; using uba1; using uba2; using unknown1; uint16_t flg; using siz; uint8_t unknown3 [12];} Redo_ktudh;Redo_ktudh

This seems to be the only piece of data contained in 5.2, which is fixed to 32 bytes. It seems that 5.2 should contain xid as a txn identifier, but it only contains part of xid.

 

5.4: indicates the end of a transaction (txn). It should mean that the user has committed a commit and may correspond to 5.2.

Typedef struct ktucm {uint16_t slt; uint16_t unknown0; uint32_t sqn; uint32_t srt; // length 1-4 uint32_t sta; // length 1-4 uint32_t flg; // length 1-4} Redo_ktucm;Redo_ktucm

The first data segment of 5.4, fixed to 20 bytes.

Typedef struct ktucf {uint32_t uba0; uint16_t uba1; uint8_t uba2; uint8_t unknown0; // 00 uint16_t ext; uint16_t spc; uint16_t unknown1; uint16_t fbi; // maybe} forbidden;Redo_ktucf

The second data segment of 5.4, which is fixed to 16 bytes.

There may be other data in later 5.4. I don't know what it is, and it seems that the last section always ends with 4 bytes of data.

 

11.2: insert, corresponding to the insert statement.

The first data segment of 11.2 is KTB, and its structure can be 5.1, but the length is different, which may be shorter than 5.1.

Typedef struct detail {describdba; incluhdba; uint16_t maxfr; incluunknown0; incluitli; incluunknown1 [3]; incluunknown2; uint16_t cc; incluunknown3 [20]; inclusize_delt; uint8_t slot; uint8_t unknown4 [10];} Redo_KDO112;Redo_KDO112

The second segment is the KDO of 11.2.

The content of the insert statement starts from the third segment. Each segment corresponds to a field. For the insert statement, all fields are listed in 11.2.

 

11.3: delete, corresponding to the delete statement.

Typedef struct {primary op0; Primary unknown0 [7]; Primary xid0; Primary xid1; uint32_t xid2; Primary uba0; Primary uba1; Primary uba2; Primary unknown1; Primary zero0; Primary zero1; uint32_t zero2; uint32_t zero3; uint32_t zero4; uint32_t unknown2; uint32_t unknown3; uint32_t unknown4; uint16_t unknown5; uint16_t scn0; uint32_t scn1;} unknown;Redo_KTB113

In the first section, although the names are KTB, the content is different.

The second section is KDO. The 5.1 KDO structure can be used.

 

11.5: Modify the update statement.

Typedef struct destination {uint8_t op0; uint8_t unknown0 [7]; uint32_t uba0; uint16_t uba1; uint8_t uba2; uint8_t unknown1;} destination;Redo_KTB115

The first section is a unique KTB.

Typedef struct detail {describdba; uint32_t hdba; uint16_t maxfr; uint16_t unknown0; descriitli; descriunknown1 [3]; shortflag; shortlock; descriunknown2 [2]; Role slot; uint8_t size; // maybe uint8_t ncol; uint8_t nnew; uint8_t unknown4 [5];} Redo_KDO115;Redo_KDO115

And the unique KDO.

The start of the third section is the update data. The third part is an array similar to a vector table, but it stores the id-1 data field to be modified.

 

11.17: corresponding to the Add or modify operation of the LOB field, used to indicate the total length of the LOB field. Another type does not contain 11.17 of the LOB length. Its meaning is unknown and is ignored by me.

Typedef struct LOB_11_17 {unknown xid0; unknown xid1; unknown xid2; jsonobj; unknown unknown2; unknown unknown3; // 00 uint32_t unknown4; // 00 bytes lobsize; unknown unknown5; // 00} Redo_LOB_11_17;Redo_LOB_11_17

This is the third section of 11.17. I ignored the first two sections and I don't know what they are. Not all 11.17 have this structure, so we need to make a judgment. If the length of this segment is not 32 bytes, we think this 11.17 is not what we need. In this section, the lobsize is the total length of the LOB field in this operation.

 

19.1: the LOB field content is stored in the Add or modify operation corresponding to the LOB field. Currently, the oracle 11g, the LOB is cut into 8168-36 (the LOB header) = 8132 bytes, each of which is placed in a 19.1. The total length of the LOB needs to be obtained through 11.17, and then the LOB data is obtained through 19.1. LOB Header Format:

Typedef struct failed {unknown unknown0; unknown unknown1; uint16_t unknown2; uint32_t lob_set; unknown unknown3; uint32_t seq; // 2 or 4 byte, lob number, 1 to n unknown unknown5; // 00 uint32_t subseq; // 2 or 4 byte, lob segment number, 0 to n uint32_t unknown7; uint32_t unknown8; // 00} Redo_LOB_19_1;Redo_LOB_19_1

There are two numbers in the LOB header. These two numbers indicate the order in which LOB is arranged. The insert and update operations are different (java Script, sqldeveloper, etc ), LOB data may be duplicated (at this time, the number of LOB 1st will be + 1, and the number of 2nd will start from 0 ).

 

The preceding figure shows the basic data structure for addition, deletion, and modification. The following is a summary:

We perform the add, delete, and modify operations, that is, 11.2, 11.3, and 11.5 are what we actually operate on.

If our add or modify operations contain the LOB field, 11.17 and 19.1 are displayed.

Each operation (11.2, 11.3, 11.5) must have 5.1 corresponding to it, which contains the content required by undo.

11.3 is the simplest, because there is no data to be parsed, as long as the row is determined through the undo segment of 5.1, and usually only one primary key is needed.

11.2 moderate, the data to be inserted is in the Data Segment of 11.2, And the undo segment of 5.1 is empty, because the inserted undo is deleted.

11.5 is the most complex, and the data to be modified is in the Data Segment of 11.5. the row to be located must be parsed from the corresponding undo segment of 5.1, the undo segment actually stores the original value of the corresponding field in 11.5.

 

Program running example:

The Program specifies that the data dictionary is dictionary. ora, and the archive log file is o1_mf_1_3279_brn6w2fm _. arc. The program running results are huge and only a small part of them is pasted.

Parse the data dictionary to get the username, table name, and field name. Only the corresponding obj (id) can be obtained from the log file, and the name must be retrieved from the data dictionary. The field id in the data dictionary starts from 1 and starts from 0 in the log file. Therefore, the field id + 1 in the log file is not required for other IDs.

It starts with 5.2, 5.1, and ends with 5.4. The "11.2" in the middle indicates the insert operation. The ERR in the image is the DEBUG output. Ignore ~~

The parsing result is saved as an SQL statement.