Oracle retrieval data consistency and transaction recovery

To ensure data consistency, Oracle uses the UNDO record to retrieve database data, oracle always enables users to only view submitted data or data at a specific time point (select statement time point), and the UNDO record will be stored in the rollback segment, if the data is not submitted, the user retrieves the data from the UNDO record. (For example :)

 

1. ORACLE retrieval data consistency

First open a SecureCRT. (First session)

Create a table first

SQL> create table c(a int);  
Table created.  
SQL> alter table c add b number;  
Table altered.  
SQL> desc c   
 Name                                      Null?    Type  
 ----------------------------------------- -------- --------------------------------------------  
 A                                                  NUMBER(38)  
 B                                                  NUMBER  

Insert and submit data in the table

SQL> insert into c values(1,2);  
1 row created.  
SQL> insert into c values(3,4);  
1 row created.  
SQL> select * from c;  
         A          B  
---------- -----------------------------  
         1          2  
         3          4  
SQL> commit;  
Commit complete.  

Open another SecureCRT. (second session)

Query

 



SQL> select * from c;  
         A          B  
---------- --------------------------  
         1          2  
         3          4  

The first session changes the data in the table but does not submit

 

 

	
	
  
 SQL> update c set b=10 where a=1;   

	
  
 1 row updated.   


 

Second session query (modified but not submitted to retrieve data in UNDO)

 

 

	
	
  
 SQL> select * from c;   

	
  
          A          B   

	
  
 ---------- --------------------------   

	
  
          1          2   

	
  
          3          4   


 

Submit the first session

 

 

	
	
  
 SQL> commit;   

	
  
 Commit complete.   


 

Second session query (it can be seen that data in the data segment can be retrieved only after submission)

 

 

	
	
  
 SQL> select * from c;   

	
  
          A          B   

	
  
 ---------- -------------------------   

	
  
          1         10   

	
  
          3          4   


 

Conclusion: if the user modifies the data but does not submit the data, other users retrieve the UNDO data segment, which ensures data consistency.

2. rollback data transaction recovery)

1. When the user updata data has not been submitted

 

 

	
	
  
 SQL> select * from c;   

	
  
          A          B   

	
  
 ---------- -----------------------------   

	
  
          1          10   

	
  
          3          4   

	
  
 SQL> update c set b=2 where a=1;   

	
  
 SQL> select * from c;   

	
  
          A          B   

	
  
 ---------- -----------------------------   

	
  
          1          2   

	
  
          3          4   


 

Then the user suddenly regretted it and wanted to restore it to its original state.

 

 

	
	
  
 SQL> rollback;   

	
  
 Rollback complete.   

	
  
 SQL> commit;   

	
  
     

	
  
 SQL> select * from c;   

	
  
          A          B   

	
  
 ---------- -----------------------   

	
  
          1         10   

	
  
          3          4   


 

It can be seen that the rollback can be rolled back to the initial state when the user uses the rollback command.

2. When the user updata data has been submitted

After the user updata data has been submitted, the data can be returned to the Source Based on the SCN record.

View raw data first

 

 

	
	
  
 SQL> select * from c;   

	
  
          A          B   

	
  
 ---------- ----------   

	
  
          1         10   

	
  
          3          4   


 

Locate SCN

 

 

	
	
  
 SQL> select current_scn from v$database;   

	
  
 CURRENT_SCN   

	
  
 -----------   

	
  
      693636   


 

Delete the data in the table and submit it now.

 

SQL> delete from c;  
2 rows deleted.  
SQL> commit;         
Commit complete.
 

Query (no data in the table now)

 

SQL> select * from c;  
no rows selected  

Retrieve data of a specific SCN

 

SQL> select * from c as of scn 693636;  
         A          B  
---------- ----------  
         1         10  
         3          4 

Restore data

SQL> insert into c select * from c as of scn 693636;  
2 rows created.  
SQL> commit;  
Commit complete.  

Query now

 

SQL> select * from c;  
         A          B  
---------- ----------------------  
         1         10  
         3          4  

It can be seen that the data can be restored to a certain checkpoint Based on the SCN. If the SCN is converted to a time point, the data can be restored to a certain time point.

The preceding section describes the ORACLE Data Retrieval consistency and transaction recovery methods. This article is from the "pursuit" blog. to communicate with this blogger, click here.

1. Design Method of super large Oracle database application system
2. Three criteria for creating an Oracle database index
3. Oracle performance optimization achieved through Partitioning technology
4. A simple view on Oracle concurrent processing mechanism
5. Solution to Oracle locking