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
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;