Wednesday, July 31, 2013

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;



No comments:

Post a Comment

Dear User,

Thank you very much for your kind response