| Lesson 6 | Writing data to redo log files |
| Objective | Discuss writing data from the redo buffer to the redo log files. |
Writing Data to redo log Files
Assuming that we do not have a read only system, data will be modified. This data will be stored on the data buffer cache to be written to disk in the data files. When we modify data, the before and after image of the data object is written to the redo log buffer area. The
DBWR process is very busy reading and writing information to and from the data files. The LGWR process relieves DBWR from writing every modification to disk the moment it is made. Let us review how LGWR processes the redo log buffer information.
- Writing data to redo log files
Redo log files are used in a circular fashion. Let’s say we have two redo log files, log1orc1.ora and log2orc1.ora.LGWRwrites information first to log1orc1.ora until a log file switch occurs. ThenLGWRwrites data to log2orc1.ora until a log file switch occurs. ThenLGWRwrites data into log1orc1.ora, writing over any information currently stored in log1orc1.ora. Redo log file switches occur whenLGWRfills up a redo log file or if a DBA executes ALTERSYSTEMSWITCH LOGFILE. When a redo log file switch occurs, a checkpoint is generated. This will be covered later in this module.
Finding information about your redo log files
V$LOGprovides group information such as group number, log sequence number and status.V$LOGFILEprovides log file member information such as name and status.V$LOG_HISTORYcontains control file log history information.
Look at the following writing.sb.html to view information from
V$LOG and V$LOGFILE as well as V$LOG_HISTORY.
The following section contains information about V$LOG and V$LOGFILE.
V$LOG, V$LOGFILE, and V$LOG_HISTORY
SQL> select group#, SEQUENCE#, BYTES, MEMBERS, STATUS from V$LOG; GROUP# SEQUENCE# BYTES MEMBERS STATUS ------ --------- -------- ------- -------- 1 21 1048576 1 CURRENT 2 20 1048576 1 INACTIVE 3 19 1048576 1 INACTIVE 4 20 1048576 1 INACTIVE 4 rows selected. SQL> select * from V$LOGFILE; GROUP# STATUS MEMBER ------ ------ -------------------------------------- 1 C:\ORACLE8\DATABASE\LOG4ORC1.ORA 2 STALE C:\ORACLE8\DATABASE\LOG3ORC1.ORA 3 STALE C:\ORACLE8\DATABASE\LOG2ORC1.ORA 4 C:\ORACLE8\DATABASE\LOG1ORC1.ORA 4 rows selected.
Explanation:
-
V$LOG: Displays information about the redo log groups.
- Group 1 is the CURRENT group with sequence 21.
- Groups 2, 3, and 4 are INACTIVE.
- All redo log files are 1MB (
1048576bytes) in size and have one member.
-
V$LOGFILE: Displays the physical redo log file members.
- Group 1 and 4 log files are active (no status shown).
- Group 2 and 3 files have the STALE status, possibly due to a failure to update or an issue detected with those log members.
SQL> select * from V$LOG_HISTORY; RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME ----- ---------- ------- --------- ------------- ----------- 1 385899944 1 1 22290 03-JAN-00 2 385899951 1 2 22318 03-JAN-00 3 385973530 1 3 22349 04-JAN-00 4 385987148 1 4 42349 04-JAN-00 5 386119239 1 5 62377 05-JAN-00 6 386179889 1 6 82435 05-JAN-00 7 386179880 1 7 102403 05-JAN-00 8 386348273 1 8 122459 06-JAN-00 9 386454903 1 9 142526 07-JAN-00 10 386514301 1 10 162611 09-JAN-00 11 386521480 1 11 182669 09-JAN-00 12 386652185 1 12 202675 10-JAN-00 13 386652195 1 13 222695 10-JAN-00 14 386711463 1 14 242702 11-JAN-00 15 386711470 1 15 262745 11-JAN-00 16 386782119 1 16 282767 12-JAN-00 17 386843930 1 17 302794 13-JAN-00 18 386917918 1 18 322813 14-JAN-00 19 387048930 1 19 342891 15-JAN-00 20 387048964 1 20 362891 16-JAN-00 20 rows selected.The diagram above describes V$LOGHISTORY in Oracle.