The redo log records all changes made to an Oracle database. In the process of researching how to extract DML operations in Redolog, there are very few details about the structure of Redolog, but finally we can sort out its structure and develop a log-based synchronization software.

This series of articles will document the problems encountered during the research process and the analysis commands and tools used.

1. What is Redo Log

The Redo Log is a set of files that record all operations to a database.

  • allDMLOperation,INSERT\UPDATE\DELETE\SELECT FOR UPDATE
  • allDDLOperation,CREATE TABLE\ALTER TABLE 等
  • All because ofRecursive SQLOracle implicitly performs other SQL modifications to the data dictionary when executing DDL statements

Before the COMMIT, Oracle writes changes to both the Rodo Log file and the original data — UNDO Segments. Therefore, Redo logs are not only used to recover data, but also to protect data from rollback.

How do I write Redo logs to an Oracle database

Each instance of an Oracle database has a redo thread (LGWR, short for LoG WRiter) that writes to logs. There are at least two Redo Log files. LGWR writes in a loop: when one file is full, it writes to the next file, and when the last file is full, it returns to the first file, and so on.

Does this write result in data loss? By default, it does.

There are two modes of database logging: archived and non-archived, and the non-archived mode has overwriting problems. In archive mode, when a switchover occurs when a set of ReodLog files is full, Oracle guarantees that the set of files will not be overwritten until the archive is complete.

You can use the following command to manually trigger log switching:

SQL> alter system switch logfile
Copy the code

Redo Log basic structure

The Redo Log is a series of Redo records, each of which is composed of a set of change vectors, each of which records changes made to a single block of data.

Redo Log files are stored in blocks. By default, the block size is the size of the disk sector, usually 512 bytes. The format depends on the operating system and database version. The analysis here is based on Windows Server 2008 R2 and Oracle 11G.

Redo logs are written sequentially in the following format:

The first two blocks record meta information, respectively:

  • The first block records information about the file itself, such as the file type, block size, and number of blocksFile Header – The file header
  • The second block records information about the database instance, such as the database SID, database version, which is calledRedo Log Header – Redo log headers

In combination with Log writing, the entire set of Redo Log files is treated as a block-by-block circular buffer in memory. The process of parsing is to read Record after Record.

3.1 Redo Record

A Redo Record may occupy one block, several blocks, or only a portion of the block, depending on its length. The length field is stored in the Record Header and is structured as follows:

The system dump command is used to export the header of the Record file. The system dump command is used to export the header of the Record file. The system dump command is used to export the header of the Record file.

REDO RECORD - Thread:1 RBA: 0x000009.0000029e.0010 LEN: 0x02ac VLD: 0x0d
SCN: 0x0000.0010c5e6 SUBSCN:  1 01/02/2021 21:09:41
Copy the code

The meaning of the main fields:

  • RBA: Redo Byte Address, consisting of three parts: log number (0x9), block number (0x29e), mid-byte offset (0x10)
  • LEN: Record length, including the length of the header
  • VLD: Head length identifier, according to a certain logic to calculate the length of the head,0x0dThat means the length of the head is zero68 bytes
  • SCN: System Change Number, also known as System Commit Number. When a transaction commits, LOWR writes the buffer contents to a file and assigns an identifier, SCN, to each committed transaction. That is, SCN allows you to track database changes and decide where to start restoring data.

In addition, you can have DML statements that run against dump with the help of SCN, and there are related commands at the end of this article.

3.2 Change the Vector

Each Change represents a database operation, such as add, delete, Change, transaction start, transaction rollback, transaction commit, etc. It has the following format:

Among them:

  • Change Headerfixed24 bytesThe length of the
  • Length VectorThat’s how many there areChange Record, each2 -Represents a length, which you need to calculate4 Byte Alignment
  • Change RecordThere are specific variations. Different operations have different formats.

Use system dump to look at the Change Header:

CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x01000085 OBJ:73194 SCN:0x0000.000e606a SEQ:1 OP: 11.2ENc :0 RBL:0Copy the code

The meaning of the main fields:

  • TYP: Change Type
  • CLS: Class = X$BH
  • DBA: Database Block Address, 4 bytes long,High 10Represents the relative file number,Low 22Said the block
  • OP: Opcodes distinguish operation types. Each opcode consists of two parts:Layer Code 和 Sub Code, such as11.2

Here are some common operations

3.3 the Transactions transaction

To start a DML operation, a Change OP:5.2 is created to mark the start of the transaction:

CHANGE #2 TYP:0 CLS:19 AFN:3 DBA: 0x00C00090 OBJ:4294967295 SCN:0x0000.0010c5bb SEQ:3 OP:5.2 ENC:0 RBL:0 ktudh redo: SLT: 0x0018 SQN: 0x0000033a FLG: 0x0012 SIZ: 108 FBI: 0 UBA: 0x00C007A0.009b. 40 PxID: 0x0000.000.00000000Copy the code

When a transaction is committed or rolled back, a Change of OP:5.4 is created to mark the end of the transaction:

CHANGE #4 TYP:0 CLS:19 AFN:3 DBA: 0x00C00090 OBJ:4294967295 SCN:0x0000.0010c5e6 SEQ:1 OP:5.4 ENC:0 RBL:0 ktucm redo: SLT: 0x0018 SQN: 0x0000033A SRT: 0 STA: 9 FLG: 0x2 kTUCf Redo: uba: 0x00C007A0.009b. 41 ext: 2 SPC: 640 FBI: 0Copy the code

A complete transaction has a unique identifier, which is represented in the log as an XID:

Xid: 0 x0002. 018.0000033 aCopy the code

The XID length is 8 bytes and consists of three parts:

  • USN: Undo segment number (0x0002
  • slt: Undo segment header Transaction table slot (0x018)ktudh/ktucmIn theslt
  • sqn: 0x0000033A corresponds toktudh/ktucmIn thesqn

In ktudh/ ktucM there is a UBA field with the content uBA: 0x00C007A0.009b.41, which represents the address of this Change in the undo block. It is 7 bytes long and also consists of three parts:

  • Undo block DBA (0x00C007A0)
  • Serial number (0 x009b)
  • Record number in block (0x41)

Here is an example of a complete transaction:

Update (c1=1); update (c1=1); update (c1=1); C2, where c1 is equal to 2, was 200, but now it’s 201;

4. Related commands

4.1 Archiving and non-archiving Logs

Query the current logging mode of the database:

SQL> archive log list;
or
SQL> select log_mode from v$database;
Copy the code

View online logs:

SQL> select l.STATUS, lf.MEMBER from v$log l, v$logfile lf where l.GROUP# = lf.GROUP#;
Copy the code

Viewing archived logs:

SQL> select recid, stamp, thread#, sequence#, name from v$archived_log;
Copy the code

View the default archive path

SQL> show parameter db_recovery_file_dest;
Copy the code

Enable archive mode for logs:

SQL> shutdown immediate;
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Copy the code

Log off archive mode:

SQL> shutdown immediate;
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
Copy the code

4.2 Redo Log Dump

The ALTER SYSTEM command dumps a binary Redo Log file to an ASCII file readable by any text editor to help us understand binary structure. The syntax for this command is as follows:

alter system dump logfile 'FileName' scn min MinimumSCN scn max MaximumSCN time min MinimumTime (s) time max MaximumTime  (s) layer Layer opcode Opcode dba min FileNumber BlockNumber dba max FileNumber BlockNumber rba min LogFileSequenceNumber BlockNumber rba max LogFileSequenceNumber BlockNumber objno ObjectNumber xid UndoSegmentNumber UndoSlotNumber UndoSequenceNumber;Copy the code

Use the SCN:

SQL> alter system dump logfile '/u01/app/oradata/orcl/redo03.log' scn min 1099234 scn max 1099246;
Copy the code

Using the RBA:

SQL> select cpodr_seq,cpodr_bno from x$kcccp where rownum=1;
 CPODR_SEQ  CPODR_BNO
---------- ----------
         9       1514
SQL> DML (insert/update/delete)
SQL> select cpodr_seq,cpodr_bno from x$kcccp where rownum=1;
 CPODR_SEQ  CPODR_BNO
---------- ----------
         9       1518

SQL> alter system dump logfile '/u01/app/oradata/orcl/redo03.log' rba min 9 1514 rba max 9 1518;
Copy the code

Note: After each dump, you need to exit the session and log in again before dumping. Otherwise, only one file is stored.

4.3 Querying the Dump Path

There are two ways to query the dump path.

First, use the following command to view the default path:

SQL> show parameter user_dump_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      c:\database\oracle\administrat
                                                 or\diag\rdbms\orcl\orcl\trace
Copy the code

Second, use the following command before and after the dump command to display:

SQL> oradebug setmypid;
SQL> alter system dump logfile xxxxxxx
SQL> oradebug tracefile_name;
c:\database\oracle\xxxxxxx\xxxxxxx.trc
Copy the code

4.4 Dump an INSERT operation

First, check which online log is currently in use, i.e. the file in the CURRENT state:

SQL> select l.STATUS, lf.MEMBER from v$log l, v$logfile lf where l.GROUP# = lf.GROUP#;
Copy the code

Insert a row into the DEPT table of Scott and check the result of dump as shown below:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1099234
SQL> insert into scott.dept values(50, 'a', 'a');
SQL> commit;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1099246
SQL> alter system dump logfile '/u01/app/oradata/orcl/redo03.log' scn min 1099234 scn max 1099246;
Copy the code

Finally, the binary Redo log dump results, here is only the insert section, too long, I believe that no one read ~~ :

CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x01000085 OBJ:73194 SCN:0x0000.000e606a SEQ:1 OP: 11.2enc :0 RBL:0 KTB Redo OP: 0x01 ver: 0x01 Compat bit: 4 (post-11) PADDING: 0 op: F xID: 0x0002.018.0000033a uba: 0x00C007A0.009b.40 KDO op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01000085 hdba: 0x01000082 itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) size/delt: 10 fb: --H-FL-- lb: 0x1 cc: 3 null: --- col 0: [ 2] c1 33 col 1: [ 1] 61 col 2: [1], 61Copy the code

Briefly, OP:11.2 means that this is an INSERT operation; OBJ:73194 indicates that the operation table is scott.dept; The last three lines of COL represent the field data of the operation. The values displayed are all hexadecimal, where C1 33 will be converted to 50 according to certain operation logic, and 61 is the ASCII code of character A.

5. To summarize

It is suggested that the above commands are manually executed, after all, others do not sum up their own experience, to be impressed.

The main references in this series are:

  • Julian Dyke’s powerpoint analysis of RedoLog
  • David Litchfield’s PDF analysis of Redo Logs binaries
  • Zhoubihui’s redo_log_Calculate_analysis research article posted on GitHub

The above information can be searched online, of course, you can also pay attention to the WX public number, “small creative programming” reply keyword “redolog” to obtain.