Redo - > each operation is first recorded in the redo log. When an instance failure (such as power failure) causes the data to not be updated to the data file, the database redo is required when restarting to update the data to the data file again
Undo - > record a copy before the change, but when your system rollback, overwrite the copy to the original data
Redo - > records all the database transaction used for recovery
Undo - > record all previous impressions for rollback (Undo is used to store uncommitted) data infor used for rollback)
Redo - > the submitted transaction should be written to the data file during instance recovery
Undo - > uncommitted transactions
The reason for redo is that each time you commit, the modification of the data is immediately written to the online redo, but the modification of the data is not necessarily written to the data file at the same time. Because the data has been submitted, but only exists in the online log file, you need to find the data from the online log file and reapply it during recovery to change the changed data in the data file!
The reason for undo is: oracle During normal operation, in order to improve efficiency, if the user has not committed but there is not much free memory, the dbwr process will write the dirty block to the data file to free up valuable memory for other processes. This is why UNDO is needed. The commit statement has not been issued yet, but oracle's dbwr process has written the uncommitted data to the data file.
The meaning of Undo is to read consistency, roll back and let the database know its past state
Specific steps for read consistency
1. Confirm the SCN number of the read time
2. Search all data blocks associated with this table and row. The SCN number of the ILT transaction slot of the data block shall be less than the SCN number at the reading time.
3. If the SCN number less than the reading time is searched, it is read directly
4. If there is no SCN number less than the reading time, find the data before the change according to the undo information recorded by the ILT transaction slot in the data block. If the SCN number is still greater than the reading time, read all the data blocks associated with this transaction through recursion until the information of the undo block smaller than the SCN number at the reading time is found, and then read it.
5. If there is no undo information smaller than the SCN number at the reading time, the classic error ora-1555 snapshot too old will be reported. This is to avoid phantom reading, dirty reading and other phenomena and ensure the absolute characteristics of read consistency
The significance of Redo (online Redo log) is for recovery, so that the database can be recovered after crash without losing data
redo recovery needs to rely on undo to restore first
1. View current log group members
SQL> select member from v$logfile;
2. View the current log group status
SQL> select group#,members,bytes/1024/1024,status from v$log;
Horizontal and vertical comparison of switching times of redo log group in unit hour SQL statement
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'99') "00", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'99') "01", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'99') "02", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'99') "03", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'99') "04", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'99') "05", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'99') "06", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'99') "07", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'99') "0", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'99') "09", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'99') "10", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'99') "11", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'99') "12", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'99') "13", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'99') "14", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'99') "15", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'99') "16", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'99') "17", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'99') "18", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'99') "19", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'99') "20", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'99') "21", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'99') "22", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'99') "23" FROM V$LOG_HISTORY GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD') ORDER BY 1 DESC;