Jump to Navigation

233 - Resize or Recreate Online Redo log

One of the best ways to resize or recreate online redo log files and keep the current sequence is to perform it online. In this example,
we will resize all online redo logs from 100MB to 300MB while the database is running and use SQL*Plus to drop/recreate them in stages.

1. Check the logs information, files and status.

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

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

2. Switch the current log.

SQL> alter system switch logfile;

3. Drop a log group which will be changed.

SQL> alter database drop logfile group 2;

4. Do check point, if need.

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

or

SQL> alter system checkpoint;

Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with
the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles.

The latest SCN is written (updated) into the datafile header.
The latest SCN is also written to the controlfiles.

Oracle suggests that you might consider setting archive_lag_target to zero to reduce "checkpoint not complete" messages :

SQL> alter system set archive_lag_target=0 scope=both;

5. Add log files and group.

SQL> alter database add logfile group 2 ('/d01/app/oracle/oradata/prod/redo2a.log' ) size 300m reuse;

 

 



Main menu 2

Story | by Dr. Radut