Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In order to change the size of the Redolog files, it is necessary to create redolog files of the desired size and drop the existing redolog files. There is no process like Resize.
Let’s assume that there are 4 redo log groups. First, we create new files for 4 groups. If the database is RAC type, the thread counts will change.
1 2 3 4 | alter database add logfile thread 1 group 21 ('+DATA') size 500M; alter database add logfile thread 1 group 22 ('+DATA') size 500M; alter database add logfile thread 1 group 23 ('+DATA') size 500M; alter database add logfile thread 1 group 24 ('+DATA') size 500M; |
We can check the current status with the following queries.
1 2 3 | select * from v$instance_recovery; select * from v$log; select * from v$logfile; |
We ensure that new redolog files are used.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system checkpoint; System altered. |
We drop old redolog files.
1 2 3 4 | alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; alter database drop logfile group 4; |
We have given the group numbers of newly created files as 21-22-23-24.
In order to be like before, we will re-create the files with the group numbers 1-2-3-4 and drop 21-22-23-24.
Before you drop the files with the below script, you should make sure that the files you created are used with the help of above script.
1 2 3 4 5 6 7 8 9 10 | alter database add logfile thread 1 group 1 ('+DATA') size 500M; alter database add logfile thread 1 group 2 ('+DATA') size 500M; alter database add logfile thread 1 group 3 ('+DATA') size 500M; alter database add logfile thread 1 group 4 ('+DATA') size 500M; alter database drop logfile group 21; alter database drop logfile group 22; alter database drop logfile group 23; alter database drop logfile group 24; |
It is recommended that you have at least two redolog files in different directories. That’s why we create members in the FRA.
1 2 3 4 | alter database add logfile member '+FRA' to group 1; alter database add logfile member '+FRA' to group 2; alter database add logfile member '+FRA' to group 3; alter database add logfile member '+FRA' to group 4; |
Great instruction do not forget to clear the redo log file that in use and switch to another redo log file otherwise you’ll can’t drop it.