Shulou(Shulou.com)05/31 Report--

This article mainly introduces "ORACLE transaction and instance recovery process explanation". In daily operation, I believe that many people have doubts about the recovery process of ORACLE transaction and instance. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the doubts of "ORACLE transaction and instance recovery process explanation". Next, please follow the editor to study!

Let's start with a transaction in oracle:

For example, when we initiate a update statement to change a row of data, such as changing the table_name='ICOL$' of zabbix.cwdtest to 'aaxx'. The line is recorded in file 14, 1835491 yuan.

1. First of all, it will go through the parsing process of the sql statement in share pool, as long as it is for the sql syntax, execute the plan to deal with these, this part will not go into detail.

two。 Then, after the execution of sql, the database reads the corresponding data rows from the physical file to the buffer cache (assuming that there is no corresponding data block in memory and does not discuss the locking process), this process also involves the process of writing data blocks to dirty list and writing dirty blocks to find free space for newly read data blocks.

3. At the same time, a rollback segment is allocated and another modified block image is retained in the undo segment.

The following is viewed through DUMP UNDO-related information.

When you see that index is the transaction slot of 0x09, the state of 10 represents transaction activity, while the other slot 9 represents transaction inactivity.

Scn represents the SCN that the service starts, commits, and rolls back. The scn of the transaction slot 0x09 is 0x0009.01e25a30, which is 38686317104 after conversion.

Dba represents the uba: the undo block address of the first part, and this DBA is the starting point of the (rollback) rollback, that is, the address of the UNDO block where the last record of the transaction modification was recorded.

The dba in slot 0x19 in the things table is 0x0a400495, which is the 1173 block number of document 41 (consistent with the v$transaction view).

Let's take a look at what this front image is.

Dump data block

Here scn represents the SCN number recently written to disk, where the scn in the data block is 0x0009.01e25beb, and after conversion is 38686317547.

For DBWR, each time the dirty block is refreshed, the SCN number of the block is maintained, which represents the data version of the block.

Looking down, there are 51 records in this UNDO block:

We find that the object number of the record in line 51 is 130736, which is the table we changed in this transaction.

Here the field value is 49 43 4f 4c 24, which is ICOL$, by conversion, which is the value before update.

SQL > select utl_raw.cast_to_varchar2 (replace ('49 43 4f 4c 24)) from dual UTL_RAW.CAST_TO_VARCHAR2 (REPLACE ('49434F4C24')-ICOL$

And bdba: 0x039c01e3 is the address of the data block corresponding to the record, and the record is in file 14, 1835491 yuan.

SQL > select to_number ('039c01e3) file#, dbms_utility.data_block_address_block (60555747) block from dual) from dual;TO_NUMBER (' 039C01E3) FILE# BLOCK--14 1835491

There are several SCN related to UNDO. Let's reorganize them:

SCN in undo header: represents the SCN that the transaction starts, commits, and rolls back. The scn of the transaction slot 0x09 is 0x0009.01e25a30, and after conversion is 38686317104.

Scn in undo block: indicates the SCN number recently written to disk. In this case, the scn in the data block is 0x0009.01e25beb, and after conversion, it is 38686317547.

Undo block modifies the SCN of the record line:

Ctl max scn: after 0x0009.01e25b73 conversion is 38686317427

Prv tx scn: 0x0009.01e25b75, after conversion is 38686317429

Txn start scn: scn: 0x0000.00000000, this is 0.

At this point, the information recorded by redo is as follows:

Alter system dump logfile'/ opt/app/oracle/oradata/tlvdb/redo1.log'

4.。 The data block is modified for the transaction, and after the change is completed, the modification to this data block also generates redo information. Here, the rowid of these rows of data affected by the statement and read into db buffer, the original and new values to be updated and scn and other information are written from PGA to redo log buffer one by one.

The following alter system dump datafile 14 block 1835491; is executed before and after UPDATE respectively to dump the data block.

Before performing the UPDATE:

Tab 0, row 0, @ 0x1e87tl: 249 fb:-- Hmurf muri-lb: 0x0 cc: 55col 0: [3] 53 59 53col 1: [5] 49 43 4f 4c 24

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.