Resize Redo log
If the redo logs need to be resized, the following steps can be taken.
1. Create new log group members with the following command:
ALTER DATABASE ADD LOGFILE
GROUP 4 ( '/ccbprod/oradata/ccbprod/REDO04A.LOG',
'/ccbprod/oradata/ccbprod/REDO04B.LOG') SIZE 1280M reuse,
GROUP 5 ( '/ccbprod/oradata/ccbprod/REDO05A.LOG',
'/ccbprod/oradata/ccbprod/REDO05B.LOG') SIZE 1280M reuse,
GROUP 6 ( '/ccbprod/oradata/ccbprod/REDO06A.LOG',
'/ccbprod/oradata/ccbprod/REDO06B.LOG') SIZE 1280M reuse;
2. Switch the logfiles by issuing: ALTER SYSTEM SWITCH LOGFILE until one of the new redo logs from step 1 is the current redo log. (Can check this by @datafiles).
(select group#, status from v$log;)
As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group. I have run into instances; however, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:
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>'
Easy problem to resolve. Simply perform a checkpoint on the database:
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
System altered.
3. Drop the original redo logs with:
ALTER DATABASE DROP LOGFILE GROUP 1, GROUP 2, GROUP 3;
4. Delete the operating system files from the old redo logs.
5. (Optional). Repeat steps 1-4 to create redo groups 1-3 and drop groups 4-6 to restore the redo group numbers starting from 1.
ALTER DATABASE ADD LOGFILE
GROUP 1 ( '/redoa/ccbprod/REDO01A.LOG',
'/redob/ccbprod/REDO01B.LOG') SIZE 1280M reuse,
GROUP 2 ( '/redoa/ccbprod/REDO02A.LOG',
'/redob/ccbprod/REDO02B.LOG') SIZE 1280M reuse,
GROUP 3 ( '/redoa/ccbprod/REDO03A.LOG',
'/redob/ccbprod/REDO03B.LOG') SIZE 1280M reuse;
6. ALTER DATABASE DROP LOGFILE GROUP 4, GROUP 5, GROUP 6;
7. double check redo members:
set lines 1000
select GROUP#,MEMBER from v$logfile;
No comments:
Post a Comment