I know that the redo log entries are created when there is insert/update/delete/create/drop/alter occurs. What information gets stored in redo log ? In case of instance failure, redo log file is used to recover database, does it contain information on changes applied database buffer cache?
If one does redo log mining (to view exactly what is in the redo logs), there is a view that tells you what they store: V$LOGMNR_CONTENTS.
This typically shows:
- Operation: INSERT, UPDATE, DELETE, or DDL
- SCN - system change number - very important for recovery
- The transaction to which a change belongs
- The table and schema name of the modified object
- The name of the user who issued the DDL or DML
- the SQL needed to redo and/or undo your changes.
So yes, the redo logs contain changes made when dirty buffers are committed. They are used to reconstruct the database in the case of failure. They also protect rollback data, as the SQL both for redo and undo are stored and are played back during recovery.
V$LOG and V$LOGFILE show how your redo data files are allocated. You would generally want them in pairs, as you have a backup in case one is lost. You also want to have at least 3 groups (pairs) as some are active, some are current, and some are being written to the archive logs which are also critical for recovery.