Parameter, Control and Redo Log Files

1
Parameter, Control and Redo Log Files

• CIS 410

2
Parameter, Control, and Redo Log Files
3
Back-up of key files

• The password file, the parameter file, the
  control file and the set of redo log files all
  represent potential single points of failure for
  the entire database.
• Thus multiple copies of each of these files must
  be maintained

4
Parameter file - PFILE

• Is a Text file
• Is modified with an operating system editor
  (notepad, VI, etc.)
• Modifications are made manually
• Changes take effect on the next startup
• Only opened during instance startup
• Default location is ORACLE_HOME/dbs
• Default filename initSID.ora
• Backup by copying file while database is shut
  down. Must modify copies each time a parameter is
  changed.
• SPFILE is now available as an alternative
• It is a system file (not readable editable
  externally) allows changes to parameters while
  database is up and posts changes automatically.

5
PFILE Example

• Initialization Parameter File initdba01.ora
• db_name dba01
• instance_name dba01
• control_files ( home/dba01/ORADATA/u01/
  control01dba01.ctl,
• home/dba01/ORADATA/u02/control01dba02.ctl)
• db_block_size 4096
• db_cache_size 4M
• shared_pool_size 50000000
• java_pool_size 50000000
• max_dump_file_size 10240
• background_dump_dest /home/dba01/ADMIN/BDUMP
• user_dump_dest /home/dba01/ADMIN/UDUMP
• core_dump_dest /home/dba01/ADMIN/CDUMP
• undo_management AUTO
• undo_tablespace UNDOTBS
• . . .

6
Starting Up a Database NOMOUNT
OPEN
STARTUP
MOUNT
NOMOUNT
PFILE Read -Instance Started
SHUTDOWN
SHUTDOWN
7
Starting Up a DatabaseMOUNT
OPEN
STARTUP
MOUNT
Control file opened for this instance
NOMOUNT
Instance started
SHUTDOWN
SHUTDOWN
8
Starting Up a DatabaseOPEN
OPEN
STARTUP
All files opened as described by the control file
for this instance
MOUNT
Control file opened for this instance
NOMOUNT
Instance started
SHUTDOWN
SHUTDOWN
9
STARTUP Command

• Start up the instance and open the database
• Using non-default Parameter file name/location

STARTUP
STARTUP PFILEORACLE_HOME/dbs/initdb01.ora
10
Shutting Down the Database
A No No No No
T No No Yes Yes
I No No No Yes
Shutdown Mode Allow new connections Wait until
current sessions end Wait until current
transactions end Force a checkpoint and close
files
N No Yes Yes Yes

• Shutdown mode
• A ABORT
• I IMMEDIATE
• T TRANSACTIONAL
• N NORMAL

11
Control File

• A small binary file
• Defines current state of physical database
• Maintains integrity of database
• Required
• At MOUNT state during database startup
• To operate the database
• Linked to a single database
• Loss may require recovery
• Sized initially by
  CREATE DATABASE Statement

12
Control File Contents

• A control file contains the following entries
• Database name and identifier
• Time stamp of database creation
• Tablespace names
• Names and locations of datafiles and redo log
  files
• Current redo log file sequence number
• Checkpoint information
• Begin and end of undo segments
• Redo log archive information
• Backup information

13
Multiplexing the Control File
CODE THIS LINE IN THE PARAMETER
FILE CONTROL_FILES HOME/ORADATA/u01/ctrl01.ctl,
HOME/ORADATA/u02/ctrl02.ctl
14
Multiplexing the Control File When Using PFILE

• Shut down the database
• Create additional control files
• Add control file names to PFILE
• Start the database
• Use VCONTROLFILE to monitor control file
  contents

shutdown immediate
cp HOME/ORADATA/u01/ctrl01.ctl
HOME/ORADATA/u02/ctrl02.ctl
CONTROL_FILES (/DISK1/control01.ctl,
/DISK3/control02.ctl)
startup
15
Control Files and System Failures

• Loss of all copies of control file is a failure
  that may cause data loss
• At startup, we must be able to connect to each
  copy of the control file listed in the Parameter
  file
• If 1 copy of a multiplexed control file fails
  while the database is up and running,
• processing continues
• The failure of the copy is recorded in the system
  log (CDUMP)
• The valid control file must be re-multiplexed and
  the parameter file modified as needed the next
  time the database goes down and is restarted.

16
Using Redo Log Files

• Redo log files have the following
  characteristics
• Record all changes made to data
• Written is sequential fashion
• Provide a recovery mechanism
• Can be organized into groups
• At least two groups required

Redo Log 1
Redo Log 2
Redo Log 3
17
Structure of Redo Log Files
Example with 3 log groups and 2 members (copies)
of each group
Group 2
Group 3
Group 1
Disk 1
Member
Disk 2
Member
18
How Redo Log Files Work

• Redo log files are used in a cyclic fashion.
• When a redo log file is full, LGWR will move to
  the next log group.
• This is called a log switch
• A checkpoint operation also occurs
• Information about the log switch is written to
  the control file

19
Forcing Log Switches and Checkpoints

• Forcing a log switch
• ALTER SYSTEM CHECKPOINT command

ALTER SYSTEM SWITCH LOGFILE
ALTER SYSTEM CHECKPOINT
20
Adding Online Redo Log File Groups
ALTER DATABASE ADD LOGFILE GROUP 3
('HOME/ORADATA/u01/log3a.rdo',
'HOME/ORADATA/u02/log3b.rdo') SIZE 1M
log3a.rdo
log3b.rdo
Group 1
Group 2
Group 3
21
Adding Online Redo Log File Members
ALTER DATABASE ADD LOGFILE MEMBER 'HOME/ORADATA/u
04/log1c.rdo' TO GROUP 1, 'HOME/ORADATA/u04/log2c
.rdo' TO GROUP 2, 'HOME/ORADATA/u04/log3c.rdo'
TO GROUP 3
Group 3
Group 1
Group 2
22
Online Redo Log File Configuration

• Should always have
• Equal number of members in each group
• Equal size for each group

Group 2
Group 3
Group 1
?
Disk 3
Disk 2
Disk 1
23
Obtaining Group and Member Information

• Information about a group and its members can be
• obtained by querying the following views
• VLOG
• VLOGFILE

24
Problems at log switches

• As long as one member of each logfile group is
  working properly, database will continue
  operating
• A warning about the bad member file will be
  written to the system log file (BDUMP)
• Database will hang (stop accepting transactions)
  if
• no members of the next log group can be written
  to at a log switch
• Checkpoint showing that all entries from previous
  use have been posted to permanent tablespaces has
  not been completed
• Previous contents have not been fully written to
  archived redo log files (IF ARCHIVING HAS BEEN
  ENABLED)