Redo Log File

Redo Log file contains any changes made to the data in database buffer cache. Every database should have at least two redolog files groups.

Check Redo Log file Status:

SQL> select group#,status from v$log;

GROUP# STATUS

---

     1 CURRENT

     2 INACTIVE

     3 INACTIVE

The log files have the following status values:

Name

Meaning

USED

Indicates either that a log has just been added but never used.

CURRENT

Indicates a valid log that is in use.

ACTIVE

Indicates a valid log file that is not currently in use.

CLEARING

Indicates a log is being re-created as an empty log due to DBA action.

CLEARING CURRENT

Means that a current log is being cleared of a closed thread. If a log stays in this status, it could indicate there is some failure in the log switch.

INACTIVE

Means that the log is no longer needed for instance recovery but may be needed for media recovery.

The v$logfile table has a status indicator that gives these additional codes:

Name

Meaning

INVALID

File is inaccessible.

STALE

File contents are incomplete (such as when an instance is shut down with SHUTDOWN ABORT or due to a system crash).

DELETED

File is no longer used.

Null

File in use.

Adding Redo Log Groups:

SQL> ALTER DATABASE ADD LOGFILE GROUP 4

                  'C:\oracle\product\10.2.0\oradata\dba12\REDO04.LOG'

                  SIZE 10M;

Adding Redo Log Members:

SQL> ALTER DATABASE ADD LOGFILE MEMBER

                  'C:\oracle\product\10.2.0\oradata\dba12\REDO04b.LOG' TO GROUP 4;

Check the file Location of redo log files:

SQL> select group#,member from v$logfile;

GROUP# MEMBER

---

3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA12\REDO03.LOG

2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA12\REDO02.LOG

1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA12\REDO01.LOG

4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA12\REDO04.LOG

4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA12\REDO04B.LOG

Dropping Online Redo Log Member:

SQL> ALTER DATABASE DROP LOGFILE MEMBER

                  'C:\oracle\product\10.2.0\oradata\dba12\REDO04B.LOG';

Dropping Online Redo Log Groups:

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;

Move Redo Log File Destinations

SQL>SHUTDOWN;
Copy the redo log file in new location.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME
FILE ‘C:\oracle\product\10.2.0\oradata\dba12\REDO01.LOG’

TO ‘C:\oracle\product\10.2.0\oradata\dba12\redologfile\REDO01.LOG’;

SQL> alter database open;

Forcing Log Switch:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Forcing Checkpoint:

SQL> ALTER SYSTEM CHECKPOINT;

Share this post: on Twitter on Facebook on Google+