210 likes | 365 Views
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.
E N D
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. • This chapter focuses on managing control files and online redo logs.
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)
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
Viewing Control File Names and Locations SQL> show parameter control_files SQL> select name from v$controlfile;
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.
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
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.
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
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
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.
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;
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;
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)
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;
Resizing Online Redo-Log Groups • Add log groups with files sized appropriately • Drop the old log groups
Adding Online Redo-Log Files to a Group SQL> alter database add logfile member '/ora02/oraredo/O11R2/redo04c.rdo' to group 4;
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';
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
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.