Transcript Chapter 1
CHAPTER 5
Managing Control Files, Online Redo Logs,
and Archiving
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, online
redo logs, and archiving
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> oradebug setmypid
SQL> oradebug unlimit
SQL> alter session set events 'immediate trace name
controlf level 9';
SQL> oradebug tracefile_name
Viewing Control File Names and
Locations
SQL> show parameter control_files
SQL> select name from v$controlfile;
Adding a Control File
1. Alter the initialization file CONTROL_FILES parameter
to include the new location and name of the control file.
2. Shut down your database.
3. Use an OS command to copy an existing control file to
the new location and name.
4. Restart your database.
Moving a Control File
1. Determine the CONTROL_FILES parameter’s current
2.
3.
4.
5.
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
1. Identify which control file has experienced media failure
by inspecting the alert.log for information.
2. 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.
3. 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
Online Redo Log Configuration
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:
1. Shut down your database
2. From the OS prompt, move the files
3. Start up your database in mount mode
4. Update the control file with the new file locations and
names via the ALTER DATABASE statement
5. Open the database
Archive Redo Log Architectural Decisions
• Where to place the archive redo logs and whether to use the
•
•
•
•
•
•
•
fast recovery area to store them
How to name the archive redo logs
How much space to allocate to the archive redo log location
How often to back up the archive redo logs
When it’s okay to permanently remove archive redo logs from
disk
How to remove archive redo logs (e.g., have RMAN remove
the logs, based on a retention policy)
Whether multiple archive redo log locations should be enabled
(When to schedule the small amount of downtime that’s
required (if a production database)
Enabling Archive Log Mode
$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
Note: You can confirm archivelog mode with this query:
SQL> archive log list;
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.