Using Redo Log Files

1
Using Redo Log Files
Redo logs record all changes to the database from
the database buffer cache. They are used to
recover committed data that has not been written
to the data file in the event of a failure.
They can be set up with multiple copies
(multiplexed) on different volumes, that to avoid
single point failure. On Line Redo Log
Groups A minimum of 2 groups are required LGWR
writes the same information to redo log group
members concurrently. Note Direct writes are
written directly to datafiles ( not written to
redo logs)
2
Using Redo Log Files
Online Redo Log Members Group members have the
same size. A log sequence number is assigned to
the group each time Oracle starts writing to the
group. The log sequence number identify each
group uniquely. The current sequence number is
stored in the control file and in the header of
the data file. You can see the present log
sequence number by querying dynamic performance
view VLOG Creating Redo Log files CREATE
DATABASE clause LOGFILE creates the initial redo
log files for use by the database. The Database
Configuration wizard does it for you. This
parameter has a default value for Oracle Versions
7 and 8. The parameter must be set manually for
Oracle 9. MAXLOGMEMBERS Sets the max number of
logs per group. The initialization parameter
LOG_FILES MAXLOGFILES MAXLOGMEMBERS which sets
the max number of log groups that can open at the
start of the database. This is an optional
parameters and may be omitted.
3
LGWR, Log Switches, and Checkpoints
Redo Log Buffer and LGWR Oracle sequentially
records changes to the database in the redo log
buffer, which is used in circular manner. LGWR
writes entries from the log buffer to the redo
log groups in these situations -On a
COMMIT. -When redo log buffer is 1/3 full -When
LGWR timeout occurs (3 secs.) -Before DBWR writes
modified blocks from the database buffer to the
data file.
Circular Redo Log Buffer
4
LGWR, Log Switches, and Checkpoints
Log Switches Writes to redo logs is circular
where when the curtrent group is filled, LGWR
begins writing to the next group, and when the
last group files are full, LGWR returns to the
first group. A Log Switch happens when when
LGWR stops writing on one group and starts
writing to the next group. Log Switch can also
be forced using ALTER SYSTEM command. With each
log switch, a new Log Sequence Number is assigned
to the group. This number is used to identify the
entries in the group. With every log switch a
checkpoint event is initiated.
5
LGWR, Log Switches, and Checkpoints
Checkpoints During a checkpoint the following
happens -All dirty buffers in the database
covered by the log being checkpointed are
written to the database by DBWR. -The Checkpoint
background CKPT process updates the headers of
all data files, control files indicating
successfully completed write. A checkpoint occurs
with every log switch, on on instance shutdown
(normal, transitional, immediate) and when forced
by one of the parameters LOG_CHECKPOINT_INTERVAL
-- blocks LOG_CHECKPOINT_TIMEOUT --
seconds A checkpoint can be requested manually.
If LOG_CHECKPOINTS_TO_ALERT parameter in the
init file is set to TRUE, checkpoint information
is recorded in the alert file.
6
LGWR, Log Switches, and Checkpoints Parameters
Time and block based checkpoints Forcing a
checkpoint based on quantity and time.
LOG_CHECKPOINT_INTERVAL looks at the number of
blocks and forces a checkpoint when the number of
blocks specified is reached. On the other hand,
if you want to force a checkpoint based on time,
you can set LOG_CHECKPOINT_TIMEOUT to signify
that no buffer will remain dirty (in the cache)
for more than the number of seconds it is set at
where a checkpoint will occur. Time starts at the
previous checkpoint and a new checkpoint occurs
after the amount of time specified by this
parameter.
7
Archiving Redo Logs
Why Archiving redo logs Archiving redo logs
insures that a copy of the online red log is
available in the case of a failure causing the
loss of one or all log members. In
NOARCHIVELOG mode, the data in the redo log is
overwritten when the log is full and a log switch
occurs. In ARCHIVELOG mode, INACTIVE(not being
written onto currently) members of filled redo
log group are archived onto a specified
location. Automatic Archival Vs. Manual
Archival Automatic archival requires no user
intervention to accomplish the task of archiving
redo logs, which makes it the mode of choice for
efficient operation. In addition in manual mode,
logs must be archived in a timely fashion,
otherwise LGWR will not reuse any inactive groups
which can render the suspension of the database
operation. Furthermore, redo logs can be archived
manually at any time even if automatic archival
is enabled. NOTE In order to manually archive
redo logs the database must in ARCHIVE
mode NOTE In archive mode both the physical
nightly backup and the archived redo logs can be
used to recover all committed transactions in the
event of a loss of a redo log member with
committed transaction loss.
8
Automatic Archiving of Redo Logs
Setting up for Automatic Redo Logs Archival I)
Parameter File Configuration The following
parameter must be set up to put the database in
automatic archive mode LOG_ARCHIVE_STARTTRUE LOG_
ARCHIVE_DESTc\oracle\oradata\db1\archive LOG_ARC
HIVE_FORMATarchs.arc LOG_ARCHIVE_START ? Sets
database in archive log mode on initialization of
database LOG_ARCHIVE_DEST ? Sets the path to
where archived logs will copied LOG_ARCHIVE_FORMAT
? Sets the format of the archived file name on
the OS level where s is the log
sequence number generated by oracle.
There are other parameters
available depending on what versio
n of oracle you are using. II) Database
Configuration Start the database in MOUNT mode
and run ALTER DATABASE ARCHIVELOG
statement. Open the database by running the
statement ALTER DATABASE OPEN
9
Automatic Archiving of Redo Logs
To verify that the database is now in Archive log
mode, connect to Server Manager as user INTERNAL
(Oracle 8) or to SQL as SYS / AS SYSDBA (Oracle
9) and execute the following statement ARCHIVE
LOG LIST SQLgt archive log list Database log
mode Archive Mode Automatic
archival Enabled Archive destination
c\oracle\oradata\db1\archive Oldest
online log sequence 23 Next log sequence to
archive 25 Current log sequence
25 This command can be run while the database
is in MOUNT mode, and when the database is open.
10
Automatic Archiving of Redo Log
You can stop Automatic archival by altering the
database system while in open mode ALTER SYSTEM
ARCHIVE LOG STOP SQLgt ALTER SYSTEM ARCHIVE LOG
STOP System altered. Listing the archive log
status SQLgt ARCHIVE LOG LIST Database log mode
Archive Mode Automatic archival
Disabled Archive destination
c\oracle\oradata\db1\archive Oldest online log
sequence 23 Next log sequence to archive
25 Current log sequence 25 Note In
MOUNT mode you can switch the database to
NOARCHIVE mode. ALTER DATABASE NOARCHIVELOG
11
Manual Archiving
At any point and while the database is open you
can manually archive redo logs. ALTER SYSTEM
ARCHIVE LOG ALL SQLgt ALTER SYSTEM ARCHIVE LOG
ALL alter system archive log all ERROR at line
1 ORA-00271 there are no logs that need
archiving SQLgt ALTER SYSTEM SWITCH
LOGFILE System altered. SQLgt ALTER SYSTEM
ARCHIVE LOG ALL System altered.
12
Archive Information in Dynamic Views
Dynamic performance views VDATABASE, VINSTANCE,
and VTHREAD provide information on archiving
status. VDATABASE. LOG_MODE Archive /
Noarchive log mode VDATABASE. CHECKPOINT_CHANGE
Last SCN check-pointed VDATABASE.
ARCHIVE_CHANGE Last SCN Archived VINSTANCE,
ARCHIVE_CHANGE Last SCN Archived VTHREAD.GROU
PS VTHREAD.CURRENT_GROOUP VTHREAD.SEQUENCE_NUMBE
R Example SQLgt SELECT groups, current_group,
sequence 2gt FROM vthread GROUPS
CURRENT_GR SEQUENCE ----------
-------------------
----------------- 3 2
1090 This information is
valuable when you need to resize or change path
redo log group or groups. NOTE If you have
enabled archiving, Oracle cannot re-use or
overwrite an active online log file until ARCn
has archived its contents. If archiving is
disabled,when the last online redo log file
fills, writing continues by overwriting the
first available active file.
13
Archive Group Information
SQLPLUS in 9i (Server Manager in 8i) also
provide a command to show the status of the log
archival mode using ARCHIVE LOG command ARCHIVE
LOG START STOP LIST NEXT ALL 'path to
log file' ARCHIVE LOG LIST Database log mode
Archive Mode Automatic archival
Enabled Archive destination
C\Oracle\oradata\db1\archive Oldest
online log sequence 1088 Next log sequence
to archive 1090 Current log sequence
1090 Note Server Manager is no longer
supported in 9i. SQL Plus can replace Server
Manager function. So, in effect user INTERNAL is
no longer a valid user In 9i. User SYS or
SYSTEM ( AS SYSDBA ) is user instead.
14
Group Members Information VLOG
Vlog and VLOGFILE dynamic performance views are
useful source for information on redo log group
members SELECT group, sequence, bytes,
members, status FROM vlog GROUP SEQUENCE
BYTES MEMBERS STATUS ----------
----------------- ---------- ----------
---------------- 1
1089 1048576 2
INACTIVE 2 1090
1048576 2 CURRENT
3 1088 1048576
2 INACTIVE The column
STATUS can have on of the following
values UNUSED For newly created
files. CURRENT Group being written to by LGWR.
This indicates the file is active. ACTIVE
It is not the current groups but is needed for
recovery. INACTIVE Is no longer needed for
recovery. It may or may not be archived. has
transactions that have not been written to the
database by DBWR.
15
Group Members Information VLOGFILE
SELECT FROM vlogfile GROUP STATUS
MEMBER ---------- -----------
------------------------------------ 1
C\ORACLE\ORADATA\DB1\REDO01_A.LOG
2 C\ORACLE\ORADATA\DB1\REDO02_A.LO
G 3 C\ORACLE\ORADATA\DB1\REDO0
3_A.LOG 2 C\ORACLE\ORADATA\DB1
\REDO02_B.LOG 1
C\ORACLE\ORADATA\DB1\REDO01_B.LOG 3
C\ORACLE\ORADATA\DB1\REDO03_B.LOG Stat
us column can have one of the following
values INVALID Indicated that the file (newly
created files) is not accessible, it
will change to active when the file gets
used STALE Oracle may not be able to write
to this file. Deleted Deleted. NULL File
is in use. New redo log files (created or new
installs) may take this value. Before you take
any corrective (drop /recreate) actions to remove
STALE status, execute ALTER SYSTEM to switch log
files.
16
Manually Archiving Redo Logs
ALTER SYSTEM ARCHIVE LOG ALL ALTER SYSTEM
ARCHIVE LOG LOGFILE 'c\oracle\oradata\db1\redo03.
log'
ALTER SYSTEM
17
Adding / Dropping / Changing Log Groups
Adding 1- ALTER DATABASE ADD LOGFILE 2-
GROUP 2 3- ('C\Oracle\oradata\db1\REDO02_a.LOG
','C\Oracle\oradata\db1\REDO02_b.LOG') 4- SIZE
1M 1- ALTER DATABASE statement is used. 2- Group
the number of the group you wish to add. 3- The
path to where the group will be stored. 4- The
size of each file in the group.
Dropping ALTER DATABASE DROP LOGFILE GROUP
3 You may not drop active groups and you many
not drop groups leaving less than 2 groups for a
running instance. Un-archived groups may not be
dropped in archive mode instance. Physical files
are deleted manually on the OS level. Changing
To rename a file ALTER DATABASE . RENAME FILE
.is used. To relocate a group or a log file
within a group, the database needs to be
shutdown, relocate the file on the OS level
then execute the command ALTER DATABASE
.RENAME FILE
18
Example Dropping a Group
SQLgt SELECT FROM vlog GROUP THREAD
SEQUENCE BYTES MEMBERS ARC
STATUS ---------- ---------- ---------------
---------- ---------- ---
---------------- 1 1 39
51200 2 NO CURRENT 2 1
36 51200 2 YES INACTIVE
3 1 37 51200 2
YES INACTIVE 4 1 38
102400 2 YES ACTIVE SQLgt ALTER DATABASE
DROP LOGFILE GROUP 4 ALTER DATABASE DROP LOGFILE
GROUP 4 ERROR at line 1 ORA-01624 log 4
needed for crash recovery of thread 1 ORA-00312
online log 4 thread 1 'C\ORACLE\ORADATA\DB1\REDO
04_A.LOG' ORA-00312 online log 4 thread 1
'C\ORACLE\ORADATA\DB1\REDO04_B.LOG My Notes
Groups of 2 members Different sizes of
groups Current not archived Can not drop ACTIVE
group has data that has not been written to
data file.
19
Tuning and Planning Issues

• Plan symmetrical configuration for your groups
  with the same number of files and the same size.
  Oracle allows other wise.
• If LGWR has to wait for check points to complete
  archiving (noted in ALERT file) build additional
  groups.
• Locate multiplexed members of a group on
  different volumes. Oracle would write on the
  available member if one is not available in the
  event of volume failure.
• Place archive logs and redo logs on different
  volumes to reduce LGWR and ARCH contention.
• You may use different size redo log groups in
  special situations for faster processing.
• Planning comes with experience and with trial
  and error.