Maintaining Online Redo Log Files

Maintaining Online Redo Log Files

Forcing Log Switches and Checkpoints
# Forcing a log switch
ALTER SYSTEM SWITCH LOGFILE;
# forcing checkpoints in seconds
FAST_START_MTTR_TARGET = 600
ALTER SYSTEM CHECKPOINT;

Adding Online Redo Log File Groups
# GROUP n is optional
ALTER DATABASE ADD LOGFILE GROUP 3
('/ORADATA/u01/log3a.rdo', '/ORADATA/u02/log3b.rdo')
SIZE 1M;

Adding Online Redo Log File Members
ALTER DATABASE ADD LOGFILE MEMBER
'/ORADATA/u04/log1c.rdo' TO GROUP 1,
'/ORADATA/u04/log2c.rdo' TO GROUP 2,
'/ORADATA/u04/log3c.rdo' TO GROUP 3;

Dropping Online Redo Log File Groups
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Online Redo Log File Members
ALTER DATABASE DROP LOGFILE MEMBER '$HOME/ORADATA/u04/log3c.rdo';

Relocating and Renaming Redo Log Members
SHUTDOWN

Copy the redo log files to the new location.
STARTUP MOUNT
Page 77 Oracle DBA Code Examples
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
ALTER DATABASE OPEN;
Verifying Blocks in Redo Log Files
# it defaults to TURE
alter system set DB_BLOCK_CHECKSUM=true ;

Clearing a Redo Log File
# if DB stops becuase log file is corrupted
ALTER DATABASE CLEAR LOGFILE GROUP 2;
# the redo log file will be available even if not archived
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Viewing Redo Log Information
SELECT * FROM V$LOG;
# STATUS: INVALID (inaccessible), STALE (incomplete), DELETED, Blank (in use)
SELECT * FROM V$LOGFILE;
SELECT * FROM V$LOG G, V$LOGFILE M where G.GROUP#=M.GROUP# order by M.GROUP#