How To Resize Online Redo Logs In Oracle Database

In this tutorial, we are going to learn the steps for resize the online redo log files.

What is redo log in oracle database?

The redo log is the most crucial structure for recovery operations in oracle database which consist of two or more per-allocated files that store all changes made to the database as they occur. Every oracle database has associated online redo logs to protect the database in case of an instance failure.

Redo log files are filled with redo records that also called redo entry which is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

The redo log of a database consists of two or more redo log files and a database requires minimum of two files to guarantee that one is always available for writing and other is being used for archiving. Backgroup process Log Writter (LGWR) writes to redo log file in a circular fashion.

Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the oracle database background process Log Writer (LGWR). Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

Redo log resize steps are given below:

Step 1: First, see the size of the current logs:

RMAN> select group#,bytes/1024/1024 As MB,status from v$log;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        100 CURRENT
         2        100 INACTIVE
         3        100 INACTIVE

Step 2: From above query we see that all redo log size is 100 MB now we want to resize redo log 100 MB to 200 MB. Retrieve all the log member names for the groups

 SQL> select group#, member from v$logfile;

    GROUP#                                           MEMBER
--------------------------------------------------------------------------------         
E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO01.LOG 1

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO02.LOG 2

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO03.LOG 3

Step 3: Now we will create 3 new log groups which each group contain one log member and log groups name is 4, 5, 6 each of which size 200 MB

SQL> alter database add logfile group 4 'E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO04.LOG' size 200M;

Database altered.

SQL> alter database add logfile group 5 'E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO05.LOG' size 200M;

Database altered.

SQL> alter database add logfile group 6 'E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO06.LOG' size 200M;

Database altered.

Step 4: Run below status to check the log status

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 UNUSED
         5 UNUSED
         6 UNUSED

Step 5: From above query we can see log group 1 is current and switch log until into log group 4 and log group 1,2 and 3 will show INACTIVE

SQL>  alter system switch logfile;

System altered.

Check log status again

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 ACTIVE
         5 CURRENT
         6 INACTIVE

Step 6: Now drop log group 1,2 and 3 using below command

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.


Verify the groups were dropped, and the new groups’ sizes are correct.

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         4  209715200 INACTIVE
         5  209715200 CURRENT
         6  209715200 INACTIVE

Step 7: At this point, you consider taking a backup of the database. You can now go out to the operating system and delete the files associated with redo log groups 1, 2, and 3 in step 2 above as they are no longer needed:

E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO01.LOG
E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO02.LOG
E:\APP\MASUD\VIRTUAL\ORADATA\TEST\REDO03.LOG


Done!

Post a Comment

0 Comments

3