Here we are going to discuss about how to resize online redo log on production database. Once we had a recommendation and request of resizing online redo log on production database as we were having performance issue ( log file sync wait event). Following post involves doing same activity on different environment we are in. There is no other way till date except you have to add the group and drop the old groups. That is recreating the redo log.
1) Resize Redo Log on Standalone file system Database.
2) Resize Redo Log on Standalone ASM Database.
3) Resize Redo Log on RAC database.
It is a online activity. Though it is advisable to do it on minimum load on the system.
Pre-cautions :-
1. Make sure you have enough space available on the Redo log member mount point/drive.
2. Keep all the logs before proceeding with any activity.
3. Always make the commands on a notepad before typing it directly on sql prompt to avoid raising different issue we will discuss in different post :P
Resize Online Redo Log on Standalone Production Database.
Step 1 Check the database name, status and role just to make sure you logged in to desired db (if you have multiple database on that server) -
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CBSLIVE READ WRITE PRIMARY
Step 2 Now check the size of redo log member -
set linesize 1000
set pagesize 1000
set trim on
set lines 120
col group# format 999
col thread# format 999
col member format a80 wrap
col status format a10
col archived format a10
select lg.group#, lg.thread#,lgf.member,lg.archived,lg.status,(bytes/1024/1024) fsize from v$log lg, v$logfile lgf
where lgf.group# = lg.group# order by 1,2;
You will have a output as shown in following manner - In my environment, we had 5 redo log groups which were multiplexed in two different mount point. /u01 and /u02.
GROUP# THREAD# MEMBER ARCHIVED STATUS MB------ ------- ---------------------------------- -------- --------- --- 3 1 /u01/oradata/redologs/redo03a.log NO INACTIVE 100 3 1 /u02/oradata/redologs/redo03b.log NO INACTIVE 100 4 1 /u01/oradata/redologs/redo04a.log NO ACTIVE 100 4 1 /u02/oradata/redologs/redo04b.log NO ACTIVE 100 5 1 /u01/oradata/redologs/redo05a.log NO CURRENT 100 5 1 /u02/oradata/redologs/redo05b.log NO CURRENT 100 6 1 /u01/oradata/redologs/redo06a.log NO INACTIVE 100 6 1 /u02/oradata/redologs/redo06b.log NO INACTIVE 100 7 1 /u01/oradata/redologs/redo07a.log NO INACTIVE 100 7 1 /u02/oradata/redologs/redo07b.log NO INACTIVE 100
Step 3 Following is the command to create redo log group. Since we already had 3 to 7 redo group, we can't add a new redolog with same number, Hence redo log group got a new number that is 8. Since that redo log member were multiplexed hence there are two file name mentioned in below location. Please note for illustration purpose I have reduced the size to 10M, you have to keep whatever you want to set.
alter database add logfile group 8 ('/u01/oradata/redologs/redo03a.log','/u02/oradata/redologs/redo03b.log') size 10M;
Post addition always crosscheck with following command,
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 UNUSED
2 UNUSED
3 CURRENT
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 UNUSED
Step 4 Now you have to follow step 3 and add same number of online redo log group as we had earlier.Here we had 7 online redo log groups, so I have added 7 more online redo log groups following same step 3. ** repeat as necessary until group 8 is CURRENT **
Step 5 Here if you want to drop the groups you have to make sure it is INACTIVE . You can't drop a online redo log group if it is ACTIVE or CURRENT.
Step 6 To make CURRENT TO ACTIVE and then ACTIVE --> INACTIVE fire switch logfile command-
alter system switch logfile;
alter system checkpoint
Step 7 Now drop all the old redo log groups once they are INACTIVE.
SQL> alter database drop logfile group 4;
SQL> alter database drop logfile group 5;
SQL> alter database drop logfile group 6;
Now just crosscheck the size if it the one which you were looking for.
select group#, bytes, status from v$log;--------- --------- ----------------
8 10240 CURRENT
9 10240 UNUSED
10 10240 UNUSED
. . . .<output trimmed> . . . .
Step 8 Delete the files physically if OMF is not enabled.
rm /u01/oradata/redologs/redo03a.logrm /u02/oradata/redologs/redo03b.logrm /u01/oradata/redologs/redo04a.logrm /u02/oradata/redologs/redo04b.logrm /u01/oradata/redologs/redo05a.logrm /u02/oradata/redologs/redo05b.logrm /u01/oradata/redologs/redo06a.logrm /u02/oradata/redologs/redo06b.logrm /u01/oradata/redologs/redo07a.logrm /u02/oradata/redologs/redo07b.logResize Online Redo Log on Standalone ASM Database.
When it comes to asm, oracle recommends to enable OMF (Oracle Manage Files) . Here we are doing it with OMF. Make sure these parameters are set - DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n
Changes in OMF and ASM -
1) When you are adding a diskgroup, never miss the + before diskgroup name, else you will see the redo getting created in $ORACLE_HOME location.
2)You don't have to mention absolute path, while creating redo log group, instead we will mention diskgroup name. For example -
alter database add logfile group 8 ('+REDO1_CBS','+REDO2_CBS') size 10M;
Rest all steps will be same till 7 as step 8 will not be involved in OMF.
Resize Online Redo Log on RAC Database.
As you all know RAC database involves multiple threads hence your add command will change reset all process will be same. So while adding Online Redo Log (ORL) you have to use following command - Only Highlighted one is the difference.
alter database add logfile thread 1 group 8 ('+REDO1_CBS','+REDO2_CBS') size 10M;
For every instance we will add desired online redo log group.
No comments:
Post a Comment