How To Move Oracle Online Redo Log Files

/0 Comments/in , /by

Issue

You have online redo log files in the wrong place and want to move them.

Solution

  1. Run the following SQL to locate the current redo log groups and their members:

    2. [sql]select l.group# group_number<br />
    , l.status group_status<br />
    , f.member group_member<br />
    , f.status file_status<br />
    from v$log l<br />
    , v$logfile f<br />
    where l.group# = f.group#<br />
    order by l.group#, f.member;[/sql]

  2. Find a location that can contain the new log files.
  3. Create the new log files using the following SQL. Note that there must be at least two logfile groups after dropping unnecessary log files.

    4. [sql]sqlplus /nolog<br />
    connect / as sysdba<br />
    alter database add logfile (‘/path/to/new/redo_01_01.log’,’/path/to/new/redo_01_02.log’;) size 150M;<br />
    alter database add logfile (‘/path/to/new/redo_02_01.log’,’/path/to/new/redo_02_02.log’;) size 150M;[/sql]

  4. Run the SQL from Step 1 to determine which logfile is CURRENT.
  5. Run the following command to switch log files. After running the SWITCH command, run the SQL from Step 1 again. Repeat the process until one of the newly created logfile groups has a CURRENT status.

    6. [sql]alter system switch logfile;[/sql]

  6. After one of the newly created log files is CURRENT, run the following SQL. You should only drop logfile groups that have an INACTIVE status.

    7. [sql]alter database drop logfile group GROUP#;[/sql]

  7. Repeat Step 6 for each undesired logfile group.
  8. Delete the old redo log files from the file system.

    9. References

    Oracle® Database Administrator’s Guide – Managing the Redo Log

    0 replies

    Leave a Reply

    Want to join the discussion?
    Feel free to contribute!

    Leave a Reply

    Your email address will not be published. Required fields are marked *