Skip to content
Advertisements

Resizing/Recreating Online Redolog file

Step 1 :- Check your online redo log file group and members

sql> select  a.group#, a.member, b.bytes
  2  FROM v$logfile a, v$log b WHERE a.group# = b.group#;

group#                  member                                                                          bytes

1           D:\oracle\product\10.2.0\oradata\orcl\redo01.log           52428800

2          D:\oracle\product\10.2.0\oradata\orcl\redo02.log          52428800

3          D:\oracle\product\10.2.0\oradata\orcl\redo03.log          52428800

Step 2:

Forcing a log switch logfile

step 3:

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

group#       status

1                     inactive

2                   current

3                    inactive

sql> alter system switch logfile;

sql> alter system switch logfile;

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

group#       status

1                   active

2                   active

3                 current

Now we try to drop the group 1, we can face errors

 
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)
ORA-00312: online log 1 thread 1: ‘file_name’

sql>alter system checkpoint global;

system altered.

sql> alter database drop logfile group 1;

Database altered.

Step 4: Recreate the redolog file

SQL> alter database add logfile group 1 (‘D:\oracle\product\10.2.0\oradata\orcl\redo01.log’) size 100m reuse;

Database altered.

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

group#       status

1                     unused

2                  in active

3                    current

V$LOG

  • UNUSED Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
  • CURRENT Current redo log. This implies that the redo log is active. The redo log could be open or closed.
  • ACTIVE Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
  • CLEARING Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
  • CLEARING_CURRENT Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
  • INACTIVE Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
  • INVALIDATED Archived the current redo log without a log switch.

Why we are not altering Resize logfile

Refer :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55812348055

Advertisements
No comments yet

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: