Redo log files | Oracle DBA Tutorial pdf

Redo log files

Redo entries record data changes that can be used to reconstruct all changes made to database. Whenever you do any change to database (DML or DDL), it is recorded in redo logs.
To operate successfully, an Oracle instance requires at least 2 redo log groups. Each group must have at least 1 redo log file.
Usually in production databases, there are at least 3 redo log groups and each group has at least 2 redo log members. Note that, all member files under same group are identical. Members are multiple copies to protect against data loss in case of disk failure.

If LGWR can write to at least 1 member of the group, database functions normally, but otherwise Oracle shuts down the instance.

Creating new groups and members

ALTER DATABASE ADD LOGFILE GROUP 3
(‘/oracle/oradata/log/redo31.log’, ‘/oracle/oradata/log/redo32.log’) SIZE 10M
ALTER DATABASE ADD LOGFILE MEMBER
‘/oracle/oradata/log/redo31.log’ TO GROUP 3
For OMF
ALTER DATABASE ADD LOGFILE

Renaming log members

Follow these steps
=> Shutdown database
=> Copy/rename redo log file member to new location
=> SQL> STARTUP MOUNT
=> ALTER DATABASE RENAME FILE old redo log file TO new redo log file
=> ALTER DATABASE OPEN
=> Backup control file

Dropping redo log file

First, make the log file inactive, if necessary, issue ALTER SYSTEM SWITCH LOGFILE
ALTER DATABASE DROP LOGFILE GROUP 3
ALTER DATABASE DROP LOGFILE MEMBER
‘/oracle/oradata/log/redo31.log’

Running database in archive log mode

Specify LOG_ARCHIVE_DEST_n parameters in initialization file.
E.g. 

LOG_ARCHIVE_DEST_1 = ((LOCATION = ‘/oradata/mydb/archive1’)
MANDATORY REOPEN = 60)
In 11g, to enable database archive log mode, define archive location like this
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle11g\oradata\MYDB\archivelog'
SCOPE=SPFILE; (in mount state)
LOG_ARCHIVE_FORMAT = ‘arch_%t_%s’
%s – log sequence number
%S – log sequence number, zero filled
%t – thread number
%T – thread number, zero filled
To enable/disable archive log mode, follow these steps
=> Shutdown database (log in via conn / as sysdba)
=> Startup and mount database
=> SQL> ALTER DATABASE ARCHIVELOG (use NOARCHIVELOG to
disable)
=> SQL> ALTER DATABASE OPEN
Till 9i, in the initialization file, set LOG_ARCHIVE_START = TRUE (if it is not set, once a redo log file is full, Oracle hangs until redo log file is archived).
From 10g, this parameter is deprecated.
To manually initiate automatic archiving, issue ALTER SYSTEM ARCHIVE LOG START and ALTER SYSTEM SWITCH LOGFILE commands.
To see whether database is in archive log mode, use
SQL> ARCHIVE LOG LIST

Redo log related data dictionary views

V$LOG – shows redo log status
V$LOGFILE – shows redo log files’ location
V$THREAD
V$LOG_HISTORY
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_PROCESSES