1. What is redo log file
•
Oracle writes all statements except, SELECT
statement, to the log files.
•
If a user updates a row, Oracle will
change the row in db_buffer_cache and records the statement in
the logfile and give the message to the
user that row is updated. Actually the row is not yet
written back to the datafile but still it give the message to the user that row
is updated. After 3 seconds the row is actually written to the datafile. This
is known as deferred batch writes.
•
Since Oracle defers writing to the datafile
there is chance of power failure or system crash before the row is written to
the disk. That’s why Oracle writes the statement in redo log file so
that in case of power failure or system crash oracle can re-execute the
statements next time when you open the database.
•
Every Oracle database must have at least 2
redo logfile groups.
2. Redo log groups status
·
UNUSED - The Redo Log Group has never been used –
this status only occurs for a newly added Redo Log Group.
·
CURRENT -
Current redo log. This implies that the redo log is active. The redo log could
be open or closed.
·
ACTIVE - Log is active but is not the current log.
It is needed for crash recovery. It may be in use for block recovery. It may or
may not be archived.
·
CLEARING - Log is being re-created as an empty log
after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the
status changes to UNUSED.
·
INACTIVE - Log is no longer needed for instance
recovery. It may be in use for media recovery. It might or might not be
archived.
3. Create online Redo log
Groups
Online redo logs are created when
the database was created.
4. Adding a New
Redo Logfile Group
Ex:-
alter database add logfile group 4 ('/u02/oradata/orcl/redo04a.log‘,’
/u02/oradata/orcl/redo04a.log) size 50m;
Note: You can add groups to a database up to
the MAXLOGFILES setting you have specified at the time of creating the
database. If you want to change MAXLOGFILE setting you have to create a new controlfile
Select
'- MAXLOGFILES - '||records_total from
v$controlfile_record_section where type = 'REDO LOG';
5. Adding Members to an
existing group
Ex: alter
database add logfile member '/u02/oradata/orcl/redo01b.log' to group 1;
Note: You can add members to a group up to the MAXLOGMEMBERS
setting you have specified at the time of creating the database. If you want to
change MAXLOGMEMBERS setting you have create a new controlfile
Select 'MAXLOGMEMBERS ' || dimlm
from x$kccdi;
6. Dropping Members from a
group
•
You can drop member from a log group only if the
group is having more than one member and if it is not the current group.
•
If you want to drop members from the current
group, force a log switch or wait so that log switch occurs and another group
becomes current.
•
To force a log switch give the following command
à alter system switch
logfile
The following
command can be used to drop a logfile member:-
alter database drop logfile member
'/u02/oradata/GG/GGTEST/redo01b.log';
Note: When you
drop logfiles the files are not deleted from the disk. You have to
use O/S command to delete the files from disk.
7. Dropping Logfile Group
·
You can also drop logfile group only
if the database is having more than two groups and if it is not the current
group.
alter
database drop logfile group 4;
Note:-When you drop logfiles the
files are not deleted from the disk. You have to use O/S command to delete the
files from disk.
8. Resizing Logfiles
You cannot
resize logfiles. If you want to resize a logfile create a
new logfile group with the new size and subsequently drop the
old logfile group.
9. Renaming or
Relocating Logfiles
For Example, suppose you want to
move a logfile from ‘/u01/app/oracle/orcl/log1.ora’ to ‘/u02/oracle/ica/log1.ora’, then do
the following steps:
1.
Shutdown the database
SQL>shutdown immediate;
2.
Move the logfile from old to new
location using operating system command
$mv /u01/app/oracle/orcl/log1.ora /u02/oracle/ica/
3.
Start and mount the database
SQL>startup mount
4.
Now give the following command to change the
location in controlfile
SQL>alter database rename file ‘/u01/app/oracle/orcl/log1.ora’
to ‘/u02/oracle/ica/log2.ora’;
5.
Open the database
SQL>alter database open;
10.
Clearing REDO LOGFILES
·
A redo log file might become corrupted while the
database is open, and ultimately stop database activity because archiving
cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement
can be used to reinitialize the file without shutting down the database.
Ex: - Alter
database clear logfile group 3;
·
This statement overcomes two situations
where dropping redo logs is not possible:
i.
If there are only two log groups
ii.
The corrupt redo log file belongs to the
current group
·
If the corrupt redo log file has not been
archived, use the UNARCHIVED keyword in the statement.
ALTER
DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
11.
Viewing Information about Log files
• select
*from v$logfile;
• Select
*from v$log;
