How to change the Redo Log File size in Oracle Database

The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.

To View the current satus:

select * from v$logfile;

select * from v$log;

 

We need to make active groups to Inactive before proceeding ahead:

alter system checkpoint;

It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups. Drop inactive groups and recreate it:

alter database drop logfile group 1;

alter database add logfile group 1 ‘D:\APP\ADMINISTRATOR\ORADATA\ORCL11\RD01.LOG’ size 30M;

alter database drop logfile group 2;

alter database add logfile group 2 ‘D:\APP\ADMINISTRATOR\ORADATA\ORCL11\RD02.LOG’ size 30M;

Delete the dropped files  from File system also.

Now we have to drop the 3rd Redo Log Group. Before that, we need to change the status of this group:

alter system switch logfile;

Still the Grpoup is in Active status. Issue a checkpoint:

alter system checkpoint;

Drop inactive group and recreate it:

alter database drop logfile group 3;

alter database add logfile group 3 ‘D:\APP\ADMINISTRATOR\ORADATA\ORCL11\RD03.LOG’ size 30M;

Delete the dropped file  from File system also.

Then change the groups status to active:

alter system switch logfile;

‹ ORA-02062: distributed recovery received

Posted in DBA