Redo
All the Oracle changes made to the db are recorded in the redo log files, these files along with any archived redo logs enable a dba to recover the database to any point in the past. Oracle will write all commited changes to the redo logs first before applying them to the data files. The redo logs guarantee that no committed changes are ever lost. Redo log files consist of redo records which are group of change vectors each referring to specific changes made to a data block in the db. The changes are first kept in the redo buffer but are quickly written to the redo log files.
There are two types of redo log files online and archive. Oracle uses the concept of groups and a minimum of 2 groups are required, each group having at least one file. They are used in a circular fashion when one group fills up oracle will switch to the next log group.
The LGWR process writes redo information from the redo buffer to the online redo logs when
The log group can be in one of four states
Current | log group that is being actively being written too. |
Active | the files in the log group are required for instance recovery |
Inactive | the files in the log group are not required for instance recovery and can be over written |
Unused | log group has never been written too, a new group. |
A log file can be in one of four states
Invalid | the file is corrupt or missing |
Stale | the log file is new and never been used |
Deleted | the log file is no longer being used |
<blank> | the log file is currently being used |
Log group and log files commands
Configuration |
|
Creating new log group | alter database add logfile group 4 ('c:\oracle\redo3a.log','c:\oracle\redo3b.log') size 10M; |
Adding new log file to existing group | alter database add logfile member 'c:\oracle\redo3c.log' to group3; |
Renaming log file in existing group | shutdown database |
Drop log group | alter database drop logfile group 3; |
Drop log file from existing group | alter database drop logfile member 'c:\oracle\redoc.log' |
Maintaining |
|
Clearing Log groups | alter database clear logfile group 3; Note: used the unarchived option when a loggroup has not ben archived |
Logswitch and Checkpointing | alter system checkpoint; # Difference between them are Note: I have discussed checkpoints |
Display the redo usage | select le.leseq "Current log sequence No", 100*cp.cpodr_bno/le.lesiz "Percent Full", cp.cpodr_bno "Current Block No", le.lesiz "Size of Log in Blocks" from x$kcccp cp, x$kccle le where le.leseq =CP.cpodr_seq and bitand(le.leflg,24) = 8 / |
Useful Views |
|
V$LOG | displays log file information from the control file. |
V$LOGFILE | contains information about redo log files. |
Archived Logs
When a redo log file fills up and before it is used again the file is archived for safe keeping, this archive file with other redo log files can recover a database to any point in time. It is best practice to turn on ARCHIVELOG mode which performs the archiving automatically.
The log files can be written to a number of destinations (up to 10 locations), even to a standby database, using the parameters log_archive_dest_n and log_archive_min_succeed_dest you can control how Oracle writes its log files.
Configuration |
|
Enabling | alter system set log_archive_dest_1 = 'location=c:\oracle\archive' scope=spfile; shutdown database Archive format options |
Disabling | alter database noarchivelog; |
Displaying | archive log list; select name, log_mode from v$database; select archiver from v$instance; |
Maintainance |
|
Display system parameters | show parameter log_archive_dest show parameter log_archive_format show parameter log_archive_min_succeed_dest |
Useful Views |
|
V$ARCHIVED_LOG | Display the archived log files |
V$INSTANCE | Display if database is in archive mode |
V$DATABASE | Display if database is in archive mode |
I have a more detailed section on redo in my Data Guard section called Redo Processing.