Chapter 5 The Redo Log Files

1. Chapter 5 The Redo Log Files Oracle9i Database Administrator: Implementation and Administration

2. Objectives • Learn to describe redo log files, groups, and members • Manage redo log groups and members Oracle9i Database Administrator: Implementation and Administration

3. Introduction to Online Redo Log Files Online redo log files store details as the database is used including: • Checkpoints • Data Changes (DML) • Structural changes (DDL) • Data file changes Oracle9i Database Administrator: Implementation and Administration

4. Oracle9i Architecture: A Typical Server Oracle9i Database Administrator: Implementation and Administration

5. Introduction to Online Redo Log Files • Components: • Redo log group • Online redo log • file or member • Archived redo • log file • ARCn Oracle9i Database Administrator: Implementation and Administration

6. Introduction to Online Redo Log Files • Components: • Redo log buffer • LGWR • CKPT Oracle9i Database Administrator: Implementation and Administration

7. Introduction to Online Redo Log Files File 1 is written to File A by the ARCn process after the log switch is complete Oracle9i Database Administrator: Implementation and Administration

8. Introduction to Online Redo Log Files File 1 and File 3 are written to by the LGWR process simultaneously until both are full Oracle9i Database Administrator: Implementation and Administration

9. The Purpose of Redo Log Files Redo log files aid in recovery from short term data loss such as brief power outage: • Store changes that may not be written to the data files yet • Store checkpoints to allow recovery to resynchronize data files with correct changes • Cannot completely handle recovery from major loss such as an entire data file Oracle9i Database Administrator: Implementation and Administration

10. The Purpose of Redo Log Files Recovery procedures (overview): Prior to failure: • Perform full database backup regularly • Run database in ARCHIVELOG mode After failure: • Restore from full backup • Apply archived redo log files (oldest to newest) • Apply online redo log file Oracle9i Database Administrator: Implementation and Administration

11. The Structure of Redo Log Files Redo log buffer is flushed to the redo log file when: • A transaction COMMITS • The redo log buffer becomes 1/3 full • The redo log buffer contains >1 M of updated records • A checkpoint occurs Oracle9i Database Administrator: Implementation and Administration

12. The Structure of Redo Log Files Redo log file components: • Redo record (also called redo entry) • Relates to one data block • Made up of one or more change vectors Oracle9i Database Administrator: Implementation and Administration

13. Managing Redo Log Files The COMMIT command tells the database to log the changes to the redo log file Oracle9i Database Administrator: Implementation and Administration

14. Log Switches and Checkpoints • To manually signal a log switch: ALTER SYSTEM SWITCH LOGFILE; • A log switch triggers a checkpoint • Checkpoint is a signal to write all dirty buffers to the appropriate files • Checkpoint increments the System Change Number (SCN) Oracle9i Database Administrator: Implementation and Administration

15. Log Switches and Checkpoints How the SCN is used: • SCN is recorded in the redo log buffer • SCN is recorded in the header of each data file that was written to • During recovery, SCN of each datafile is compared to the SCN in the redo log file • If a match, the data file is up to date • If redo log file has higher SCN, changes are reapplied to the data file from the redo log file Oracle9i Database Administrator: Implementation and Administration

16. Multiplexing and Other Maintenance • Multiplexed redo log files requires multiple members in each group • A log group continues to function as long as it has at least one good member • If all members of the current group become damaged, the database forces a log switch • The database shuts down immediately if a log switch fails Oracle9i Database Administrator: Implementation and Administration

17. Adding a Member to a Group • Add members to groups while the database is running • Do not specify SIZE because new members are automatically assigned the same size as other members in the group • Command syntax: ALTER DATABASE ADD LOGFILE MEMBER '<X:\xxx>\<filename>' TO GROUP <n>; Oracle9i Database Administrator: Implementation and Administration

18. Adding a New Group • Add a new group while the database is running • Specify SIZE and group number • List all members to be created with the group (must be at least one) • Command syntax: ALTER DATABASE ADD LOGFILE GROUP <n> ( '<X:\xxx>\<filename>', '<X:\xxx>\<filename>') SIZE <n>; Oracle9i Database Administrator: Implementation and Administration

19. Renaming or Moving a Redo Log File • Must be done while the group is not ACTIVE • Steps: • Shut down the database: SHUTDOWN IMMEDIATE • Rename or relocate files using the operating system • Mount the database STARTUP MOUNT Oracle9i Database Administrator: Implementation and Administration

20. Renaming or Moving a Redo Log File • Steps (continued): • Inform database of changed file name: ALTER DATABASE RENAME FILE '<X:\xxx>\<oldfilename1>', '<X:\xxx>\<oldfilename2>' TO '<X:\xxx>\<newfilename1>', '<X:\xxx>\<newfilename2>'; • Open the database: ALTER DATABASE OPEN; Oracle9i Database Administrator: Implementation and Administration

21. Dropping Redo Log Members or Groups Reasons for dropping members or groups: • Bad disk, so must recreate member • Tuning recommendation calls for reducing members or groups • Corrupted members can be deleted and replaced later Oracle9i Database Administrator: Implementation and Administration

22. Dropping Redo Log Members or Groups • Rules when dropping redo log members: • Each group must have at least one member • The group affected must not be ACTIVE and (if in ARCHIVELOG mode) must be archived • Rule when dropping groups: • Database requires at least two groups • The group affected must not be ACTIVE and (if in ARCHIVELOG mode) must be archived Oracle9i Database Administrator: Implementation and Administration

23. Dropping Redo Log Members or Groups Steps to drop redo log member: • Query V$LOG to confirm that group is inactive • Drop redo log member: ALTER DATABASE DROP LOGFILE MEMBER '<X:\xxx>\<filename>'; • Delete associated file in the operating system Oracle9i Database Administrator: Implementation and Administration

24. Dropping Redo Log Members or Groups Steps to drop redo log group: • Query V$LOG to confirm that group is inactive: SELECT * FROM V$LOG; • Drop redo log group: ALTER DATABASE DROP LOGFILE GROUP <n>; • Delete associated file(s) in the operating system Oracle9i Database Administrator: Implementation and Administration

25. Dropping Redo Log Members or Groups Additional notes: • If group is ACTIVE, force log switch: ALTER SYSTEM SWITCH LOGFILE; • If group still active, force checkpoint: ALTER SYSTEM CHECKPOINT; • An alternative to dropping a corrupt group and recreating the members: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <n>; Oracle9i Database Administrator: Implementation and Administration

26. Archiving a Redo Log Group Advantages of archiving redo log groups: • Point-in-time recovery to a point earlier than online redo log files contain • The ability to query archived log files with LogMiner • The ability to set up and maintain a standby database Oracle9i Database Administrator: Implementation and Administration

27. Finding Redo Log Information in Data Dictionary Views States of a redo log group: • UNUSED • CURRENT • ACTIVE • CLEARING • CLEARING_CURRENT • INACTIVE Oracle9i Database Administrator: Implementation and Administration

28. Viewing Control File Data Oracle9i Database Administrator: Implementation and Administration

29. Chapter Summary • Redo log files primarily contain information on database changes • A database has at least two redo log groups • A redo log group has at least one member • Redo log files support automatic recovery from minor failures • A redo log group with multiple files is multiplexed Oracle9i Database Administrator: Implementation and Administration

30. Chapter Summary • The SCN is incremented at a checkpoint • Maintenance you can do with redo log files and groups: • Add a new file (member) to a group • Add a new group • Rename or relocate a member • Drop a member • Drop a group • Clear a group Oracle9i Database Administrator: Implementation and Administration

31. Chapter Summary • Placing a database in ARCHIVELOG mode causes redo log files to be archived after a log switch • Many initialization parameters set the behavior of the archiving function • The V$LOG and V$LOGFILES dynamic performance views display information about redo log groups and members Oracle9i Database Administrator: Implementation and Administration