Oracle 10g:Create New/Update/Remove redo logs from existing database

Oracle 10g:Redo logs Operation

There are some condition when we need to recreate redo logs because of resize,rename or log corruption.

1.Check the current use of redo log group.

SELECT GROUP#, THREAD#,ARCHIVED, STATUS FROM V$LOG ;

Check the status 'INACTIVE' which can be dropped easily.

2.Drop INACTIVE Groups.

ALTER DATABASE DROP LOGFILE GROUP 1;

Create script based on below query.

select 'ALTER DATABASE DROP LOGFILE GROUP '||GROUP#||';' from V$LOG where status='INACTIVE';

 




3.Drop Redo log members.




ALTER DATABASE DROP LOGFILE MEMBER '/oracle1/oradata/pindb/redo1.log';

Alternatively remove old files by OS level commands.




4.Add New Redo Logs.




ALTER DATABASE ADD LOGFILE GROUP 1(
  '/oracle1/oradata/pindb/redo1.log',
  '/oracle2/oradata/pindb/redo1a.log')
    SIZE 100M;

ALTER DATABASE ADD LOGFILE GROUP 2(
  '/oracle1/oradata/pindb/redo2.log',
  '/oracle2/oradata/pindb/redo2a.log')
    SIZE 100M;




5.Switch current redo logs.




Now Drop that remain because of 'CURRENT' status;