How to resize online redo log file in Oracle
During one of the recovery processes we faced some complication due to the bigger size of archvie redo log file. After successful recovery we had to reduce the size of archvie redo log file. Earlier the size was 50GB and we had to reduce it to 25GB.
Note: Since there is no way to resize the archivelog file, to achvie this we have to resize our online redo log files.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
There are 4 states of online redo files:
1. Current: If the status is current that means the online redolog file is currently being used.
2. Active: If the status is active that means it was used earlier but redo contents are still there and we may need this redo log file during recovery.
3. Inactive: If the status is inactive that means it was used earlier also there is no redo content inside it. All the content have been archived our flushed out. We wont need this file for recovery. It is the state where we can drop the redo log group.
4. Unused: If the status is unused that means this file was never in use. It might be recently added group.
We have redo log files of size 50 GB , 5 Redo Log Groups and One member in each group.
SQL> select group#, members, bytes/1024/1024/1024 GB from v$log;
GROUP# MEMBERS GB
---------- --------------- ------------
1 1 50
2 1 50
3 1 50
4 1 50
5 1 50
And their status says:
SQL> select GROUP#, STATUS from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 INACTIVE
5 INACTIVE
We can see only group 2 is in current state and rest are in inactive state, so we can drop any of them. I will start dropping from gruop 3.
Use below command to drop a redo log group:
SQL>alter database drop logfile group 3;
Database altered.
Now when we will the total number of redolog group then we can see only 4 redo log group.
Since we have dropped group 3, we have to add group 3 with appropriate size.
Use below command to add a redo log group:
sql>alter database add logfile group 3 '+ASM_Diskgroupname' size 25GB;
Database altered.
like this we can dope and add all the redo log group and recreate with appropiate size.
Note:
1.To make a group 'INACTIVE' from 'ACTIVE' use below command:
SQL> alter system checkpoint;
2. For log switch use below command:
SQL>alter system swich logfile.
During one of the recovery processes we faced some complication due to the bigger size of archvie redo log file. After successful recovery we had to reduce the size of archvie redo log file. Earlier the size was 50GB and we had to reduce it to 25GB.
Note: Since there is no way to resize the archivelog file, to achvie this we have to resize our online redo log files.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
There are 4 states of online redo files:
1. Current: If the status is current that means the online redolog file is currently being used.
2. Active: If the status is active that means it was used earlier but redo contents are still there and we may need this redo log file during recovery.
3. Inactive: If the status is inactive that means it was used earlier also there is no redo content inside it. All the content have been archived our flushed out. We wont need this file for recovery. It is the state where we can drop the redo log group.
4. Unused: If the status is unused that means this file was never in use. It might be recently added group.
We have redo log files of size 50 GB , 5 Redo Log Groups and One member in each group.
SQL> select group#, members, bytes/1024/1024/1024 GB from v$log;
GROUP# MEMBERS GB
---------- --------------- ------------
1 1 50
2 1 50
3 1 50
4 1 50
5 1 50
And their status says:
SQL> select GROUP#, STATUS from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 INACTIVE
5 INACTIVE
We can see only group 2 is in current state and rest are in inactive state, so we can drop any of them. I will start dropping from gruop 3.
Use below command to drop a redo log group:
SQL>alter database drop logfile group 3;
Database altered.
Now when we will the total number of redolog group then we can see only 4 redo log group.
Since we have dropped group 3, we have to add group 3 with appropriate size.
Use below command to add a redo log group:
sql>alter database add logfile group 3 '+ASM_Diskgroupname' size 25GB;
Database altered.
like this we can dope and add all the redo log group and recreate with appropiate size.
Note:
1.To make a group 'INACTIVE' from 'ACTIVE' use below command:
SQL> alter system checkpoint;
2. For log switch use below command:
SQL>alter system swich logfile.
No comments:
Post a Comment