How to resize
redolog file in oracle
Once, i receive the e-mail regarding the resize of the redo log file. The
Sender want the easiest way to size the redo log file something like 'alter
database log file group 1 '? \redo01.log resize 100m ‘or using some other trick.
We cannot resize the redo log files. We must drop the redolog file and
recreate them .This is only method to resize the redo log files. A database
requires at least two groups of redo log files, regardless the number of the
members. We cannot the drop the redo log file if its status is current or active.
We have change the status to "inactive" then only we can drop it.
When a redo log
member is dropped from the database, the operating system file is not deleted
from disk. Rather, the control files of the associated database are updated to
drop the member from the database structure. After dropping a redo log file,
make sure that the drop completed successfully, and then use the appropriate
operating system command to delete the dropped redo log file. In my case i have
four redo log files and they are of 50MB in size .I will resize to 100 MB.
Below are steps to resize the redo log files.
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 log file 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:
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
Ref: http://neeraj-dba.blogspot.in
Thank You.
Comments
Post a Comment