Home > Oracle > Resizing/recreating Online Redo Logs

Resizing/recreating Online Redo Logs

1. Find out the current online redo logs and their sizes

Set linesize 100

SQL> column member format a50

SQL> /

GROUP# MEMBER                                              BYTES

———- —————————————————–        —————-

1 /NROFPR/dbase/redo/set1/log1/redo01.log 104857600

2 /NROFPR/dbase/redo/set2/log2/redo02.log 104857600

3 /NROFPR/dbase/redo/set3/log3/redo03.log 104857600

 

      2. Backup the controlfile

      SQL> alter database backup controlfile to trace;

3. Make the last redo log group current

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

                 GROUP# STATUS
                  ———- —————-
                         1 CURRENT 
                         2 INACTIVE
                         3 INACTIVE

     SQL> alter system switch logfile ;

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

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

                 GROUP# STATUS
                  ———- —————-
                         1 INACTIVE 
                         2 CURRENT 
                         3 INACTIVE

4. Drop first redo log

     SQL> alter database drop logfile  group 1

     Database altered

     If you encounter the error message

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

    Make sure the group is not current and then issue the command

    SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

   and then drop the the group

5. Recreate Drop Redo Log groups

   SQL> alter database add logfile group 1 (
    2  ‘/u03/app/oradata/NESFPR/redo01a.log’, 
    3  ‘/u04/app/oradata/NESFPR/redo01b.log’,
    4  ‘/u05/app/oradata/NESFPR/redo01c.log’) size 250m reuse;

6. Force another log switch

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

                 GROUP# STATUS
                  ———- —————-
                         1 INACTIVE
                         2 INACTIVE
                         3 CURRENT
Repeat the process for rebuilding the remainder of the redo logs.

7. Once all the redo logs have been built , it can be checked with the following sql

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

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks 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: