Resize redolog file in oracle


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\anardb\oradata\orcl\redo01.log' size 100m;
alter database add logfile group 1 'C:\app\anardb\oradata\orcl\redo01.log' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file 'C:\app\anardb\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\anardb\oradata\orcl\redo01.log' size 100m;
Database altered.
SQL> alter database add logfile group 2 'C:\app\anardb\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\anardb\oradata\orcl\redo03.log' size 100m;
Database altered.
SQL> alter database add logfile group 4 'C:\app\anardb\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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: