Redo Log File

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:

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

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

1. SQL>SHUTDOWN;

2. Copy the redo log file in new location.

3. SQL> STARTUP MOUNT;

4. 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';

1. SQL> alter database open;

Forcing Log Switch:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Forcing Checkpoint:

SQL> ALTER SYSTEM CHECKPOINT;