Resize Online Redo Logs (10g) - Rough Notes
Jump to navigation
Jump to search
To resize redo log files, we have to drop and recreate them. The status of these files is critical.
Files with a status of:
- CURRENT - cannot/must not be dropped. To change the status of a current file, we have to initiate a log switch (ALTER SYSTEM SWITCH LOGFILE;).
- ACTIVE - Is likely to return an error; ORA-01624: log 1 needed for crash recovery of instance. This can be overcome by generating a checkpoint (ALTER SYSTEM CHECKPOINT GLOBAL;).
- INACTIVE - May be dropped.
- UNUSED - May be dropped.
Once again, the status of these files can be changed by initiating a log switch (ALTER SYSTEM SWITCH LOGFILE;).
The following SQLs assist with the task of:
Checking pertinent redo log file details
SELECT a.group# , a.member , b.bytes / 1024 / 1024 MB , b.status FROM v$logfile a , v$log b WHERE a.group# = b.group# ORDER BY a.group#;
Dropping redo log file groups
ALTER DATABASE DROP LOGFILE GROUP n;
Resizing redo log file
ALTER DATABASE ADD LOGFILE GROUP n ('/member_path/redo_logfile_name1', '/member_path/redo_logfile_name2') SIZE nM REUSE;
For a standby database the following additional steps are needed before dropping a online redo log group ...
Stop Standby Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Set Standby File Management to Manual
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
Clear Logfile Group
If the STATUS of the logfile group is CLEARING or CLEARING_CURRENT, you can set it to unused by running command
ALTER DATABASE CLEAR LOGFILE GROUP n;