Size redolog

[Former Member]

Hello,

I need to resize the redolog because I'm gettting the message Checkpoint not complete.

I've read this notes but I don´t know what is the best option or less "dangerous"

309526 - Enlarging redo log files

1259767 - Management of online redo log files using BRSPACE

With the brtools I dont know what I need to fill in on Redolog group number and Redolog member number.

D:\ORACLE\SID\ORIGLOGA\LOG_G11M1.DBF

C:\ORACLE\SID\MIRRLOGA\LOG_G11M2.DBF

D:\ORACLE\SID\ORIGLOGB\LOG_G12M1.DBF

C:\ORACLE\SID\MIRRLOGB\LOG_G12M2.DBF

D:\ORACLE\SID\ORIGLOGA\LOG_G13M1.DBF

C:\ORACLE\SID\MIRRLOGA\LOG_G13M2.DBF

D:\ORACLE\SID\ORIGLOGB\LOG_G14M1.DBF

C:\ORACLE\SID\MIRRLOGB\LOG_G14M2.DBF

Thanks

[Brindavan_M · Accepted solution]

Hi Ruben,

You can do it online redlog szie when the database is up and no user can affected at the time.

Add new bigger redo log groups and then drop small group when it is inactive.

Repeat  the steps until you replace all groups  for that you can use

"Alter system switch logfile"; inbetween add/drop to rotate logs faster.

For example: find the below steps how its work.

Step 1 : Check the Status of Redo Logfile

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

    GROUP#  SEQUENCE#      BYTES    ARC    STATUS

----------    ----------    ----------      -----       -------------

         1          5   52428800      YES          INACTIVE

         2          6   52428800      YES          ACTIVE

         3          7   52428800      NO          CURRENT

         4          4   52428800     YES          INACTIVE

Here,we cannot drop the current and active redo log file .

Step  2 :  Forcing a Checkpoint  :

The SQL statement alter system checkpoint explicitly forces Oracle to perform a checkpoint for either the current instance or all instances. Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles on disk .A global checkpoint is not finished until all instances that require recovery have been recovered.

SQL> alter system checkpoint global ;

system altered.

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

    GROUP#    SEQUENCE#        BYTES    ARC       STATUS

----------    ----------    ----------    -----     ----------------

         1          5       52428800     YES      INACTIVE

         2          6      52428800     YES       INACTIVE

         3          7      52428800     NO       CURRENT

         4          4      52428800    YES       INACTIVE

Since the status of group 1,2,4 are inactive .so we will drop the group 1 and group 2 redo log file.

Step  3  :  Drop Redo Log File :

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

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

    GROUP#  SEQUENCE#      BYTES    ARC    STATUS

----------    ----------    ----------    ---     ----------------

         3          7               52428800      NO       CURRENT

         4          4             52428800       YES      INACTIVE

Step  4  : Create new redo log file

If we don't delete the old redo logfile by OS command when creating the log file with same name then face the below error . Therefore to solve it delete the file by using OS command .

SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;

alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m

*

ERROR at line 1:

ORA-00301: error in adding log file 'C:\app\neerajs\oradata\orcl\redo01.log' - file cannot be created

ORA-27038: created file already exists

OSD-04010: <create> option specified, file already exists

SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;

Database altered.

SQL> alter database add logfile group 2 'C:\app\neerajs\oradata\orcl\redo02.log' size 100m;

Database altered.

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

    GROUP#      SEQUENCE#      BYTES     ARC       STATUS

----------    ----------     ----------       ---      ----------------

         1          0      104857600       YES     UNUSED

         2          0      104857600       YES     UNUSED

         3          7       52428800        NO      CURRENT

         4          4       52428800       YES      INACTIVE

Step 5 :  Now drop the remaining two old redo log file

SQL> alter system switch logfile ;

System altered.

SQL> alter system switch logfile ;

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS

---------- ---------- ---------- --- ----------------

         1          8  104857600     YES     ACTIVE

         2          9  104857600     NO      CURRENT

         3          7   52428800     YES     ACTIVE

         4          4   52428800     YES     INACTIVE

SQL> alter system checkpoint global;

System altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS

---------- ---------- ---------- --- ----------------

         1          8    104857600     YES     INACTIVE

         2          9    104857600     NO     CURRENT

         3          7     52428800     YES     INACTIVE

         4          4     52428800    YES      INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS

---------- ---------- ---------- --- ----------------

         1          8  104857600      YES      INACTIVE

         2          9  104857600      NO       CURRENT

Step 6 : Create the redo log file

SQL> alter database add logfile group 3 'C:\app\neerajs\oradata\orcl\redo03.log' size 100m;

Database altered.

SQL> alter database add logfile group 4 'C:\app\neerajs\oradata\orcl\redo04.log' size 100m;

Database altered.

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

    GROUP#  SEQUENCE#      BYTES ARC STATUS

---------- ---------- ---------- --- ----------------

         1          8        104857600      YES       INACTIVE

         2          9        104857600      NO        CURRENT

         3          0        104857600     YES        UNUSED

         4          0        104857600     YES        UNUSED

Thanks,

Brindavan M

[fidel_vales]

hi,

I do recomend you to read the Oracle documentation (the concept guide  is quite good starting point)

starting playing around with things you have no clue is very dangerous, and if those things are the online redo logs, even more