1 / 21

Chapter 5

Chapter 5. Managing Control Files and Online Redo Logs. Introduction. An Oracle database consists of three types of mandatory files: datafiles, control files, and online redo logs. Chapter 4 focused on tablespaces and datafiles.

agrata
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 Managing Control Files and Online Redo Logs

  2. Introduction • An Oracle database consists of three types of mandatory files: datafiles, control files, and online redo logs. • Chapter 4 focused on tablespaces and datafiles. • This chapter focuses on managing control files and online redo logs.

  3. Control File Contents • Database name • Names and locations of datafiles • Names and locations of online redo log files • Current online redo log sequence number • Checkpoint information • Names and locations of Oracle Recovery Manager (RMAN) backup files (if using)

  4. Displaying the Contents of a Control File SQL> oradebugsetmypid SQL> oradebugunlimit SQL> alter session set events 'immediate trace name controlf level 9'; SQL> oradebugtracefile_name

  5. Viewing Control File Names and Locations SQL> show parameter control_files SQL> select name from v$controlfile;

  6. Adding a Control File • Alter the initialization file CONTROL_FILES parameter to include the new location and name of the control file. • Shut down your database. • Use an OS command to copy an existing control file to the new location and name. • Restart your database.

  7. Moving a Control File • Determine the CONTROL_FILES parameter’s current value • Alter your CONTROL_FILES parameter to reflect that you’re moving a control file • Shut down your database: • At the OS prompt, move the control file to the new location. • Start up your database

  8. Removing a Control File • Identify which control file has experienced media failure by inspecting the alert.log for information. • Remove the unavailable control file name from the CONTROL_FILES parameter. If you’re using an init.ora file, modify the file directly with an OS editor (such as vi). If you’re using an spfile, modify the CONTROL_FILES parameter with the ALTER SYSTEM statement. • Stop and start your database.

  9. Online Redo Logs • Online redo logs store a record of transactions that have occurred in your database • These logs provide a mechanism for you to recover your database in the event of a failure • You’re required to have at least two online redo-log groups in your database • Each online redo log group must contain at least one online redo-log member • The member is the physical file that exists on disk • You can create multiple members in each redo log group, which is known as multiplexing your online redo log group

  10. Online Redo Log Group Considerations • Create at least three online redo log groups with two members in each group • Always create online redo log groups with the same physical size of file

  11. Online Redo Log Configuration

  12. Protecting Online Redo Logs • Multiplex groups to have multiple members. • Never allow two members of the same group to share the same controller. • Never put two members of the same group on the same physical disk. • Ensure that OS file permissions are set appropriately. • Use physical storage devices that are redundant (that is, RAID). • Appropriately size the log files so that they switch and are archived at regular intervals. • Set the ARCHIVE_LAG_TARGET initialization parameter to ensure that the online redo logs are switched at regular intervals.

  13. Displaying Online Redo-Log Information select a.group#,a.member,b.status,b.archived,bytes/1024/1024 mbytes from v$logfile a, v$log b where a.group# = b.group# order by 1,2;

  14. Determining the Optimal Size of Online Redo-Log Groups select count(*) ,to_char(first_time,'YYYY:MM:DD:HH24') from v$log_history group by to_char(first_time,'YYYY:MM:DD:HH24') order by 2; SQL> select optimal_logfile_size from v$instance_recovery;

  15. Avoiding Delays in Switching Online Redo Logs • Add more redo-log groups • Lower the value of FAST_START_MTTR_TARGET. Doing so causes the database-writer process to write older modified blocks to disk in a smaller timeframe • Tune the database-writer process (modify DB_WRITER_PROCESSES)

  16. Lower the value of FAST_START_MTTR_TARGET. Doing so causes the database-writer process to write older modified blocks to disk in a smaller timeframe. Adding Online Redo-Log Groups alter database add logfile group 5 ('/ora01/oraredo/O11R2/redo05a.rdo', '/ora02/oraredo/O11R2/redo05b.rdo') SIZE 500M;

  17. Resizing Online Redo-Log Groups • Add log groups with files sized appropriately • Drop the old log groups

  18. Adding Online Redo-Log Files to a Group SQL> alter database add logfile member '/ora02/oraredo/O11R2/redo04c.rdo' to group 4;

  19. Removing Online Redo-Log Files from a Group SELECT a.group#, a.member, b.status, b.archived, SUM(b.bytes)/1024/1024 mbytes FROM v$logfile a, v$log b WHERE a.group# = b.group# GROUP BY a.group#, a.member, b.status, b.archived ORDER BY 1, 2 SQL> alter database drop logfile member '/ora02/oraredo/O11R2/redo04a.rdo';

  20. Moving or Renaming Redo-Log Files • You can add new log files in the new location and drop the old log files or • Physically move the files and update the control file with the new location: • Shut down your database • From the OS prompt, move the files • Start up your database in mount mode • Update the control file with the new file locations and names via the ALTER DATABASE statement • Open the database

  21. Summary • This chapter describes how to configure and manage control files and online redo log files. • Control files and online redo logs are critical database files. • These files are critical because a normally operating database cannot function without these files. • Control files are small binary files that contain information regarding the structure of the database. • Online redo logs are crucial files that record the transaction history of the database.

More Related