Sometime DBA'S need to work on the task to increase the size of the redologs based on requirement of more data being pushed on. The procedure remain the same if you working on RAC environment for ODA'S and EXADATA machines the slightest difference you will be looking is some varied diskgroup names DATA, RECO and DBFS.
#####################
Script to check Redo logs
#####################
spool redologs.txtset echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
spool off
################
Output of the script
################
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------- ---------- ---------- ---------
1 1 +DG1/<DB Name>/ONLINELOG/group_1 YES INACTIVE 100
1 1 +DG2/<DB Name>/ONLINELOG/group_1 YES INACTIVE 100
2 1 +DG1/<DB Name>/ONLINELOG/group_2 YES INACTIVE 100
2 1 +DG2/<DB Name>/ONLINELOG/group_2 YES INACTIVE 100
3 2 +DG1/<DB Name>/ONLINELOG/group_3 NO CURRENT 100
3 2 +DG2/<DB Name>/ONLINELOG/group_3 NO CURRENT 100
4 2 +DG1/<DB Name>/ONLINELOG/group_4 YES UNUSED 100
4 2 +DG2/<DB Name>/ONLINELOG/group_4 YES UNUSED 100
5 3 +DG1/<DB Name>/ONLINELOG/group_5 YES UNUSED 100
5 3 +DG2/<DB Name>/ONLINELOG/group_5 YES UNUSED 100
6 3 +DG1/<DB Name>/ONLINELOG/group_6 YES UNUSED 100
6 3 +DG2/<DB Name>/ONLINELOG/group_6 YES UNUSED 100
#############
Add Redo Logs
#############
First Add or Resize the redo logs depending on your requirement . You can drop the redologs later, once the addition is done.
alter database add logfile thread 1 group 7 ('+DG1','+DG2') size 250M;
alter database add logfile thread 1 group 8 ('+DG1','+DG2') size 250M;
alter database add logfile thread 2 group 9 ('+DG1','+DG2') size 250M;
alter database add logfile thread 2 group 10 ('+DG1','+DG2') size 250M;
alter database add logfile thread 3 group 11 ('+DG1','+DG2') size 250M;
alter database add logfile thread 3 group 12 ('+DG1','+DG2') size 250M;
###########
Log Switch
###########
Do the log switch to and ensure the status is in INACTIVE state before you drop the redo logs. Use the below commannds to change the state of logfiles.
alter system switch logfile;
alter system checkpoint;
alter system checkpoint global;
Once you find the logfile in INACTIVE state drop them using the below command:
ex: alter database drop logfile group 1; =====> Whichever is in INACTIVE state.
#############
Desired Result
#############
Use the same script mentioned above to crosscheck your output
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------- ---------- ---------- ---------
7 1 +DG1/<DB Name>/ONLINELOG/group_7 YES INACTIVE 250
7 1 +DG2/<DB Name>/ONLINELOG/group_7 YES INACTIVE 250
8 1 +DG1/<DB Name>/ONLINELOG/group_8 YES INACTIVE 250
8 1 +DG2/<DB Name>/ONLINELOG/group_8 YES INACTIVE 250
9 2 +DG1/<DB Name>/ONLINELOG/group_9 NO CURRENT 250
9 2 +DG2/<DB Name>/ONLINELOG/group_9 NO CURRENT 250
10 2 +DG1/<DB Name>/ONLINELOG/group_10 YES UNUSED 250
10 2 +DG2/<DB Name>/ONLINELOG/group_10 YES UNUSED 250
11 3 +DG1/<DB Name>/ONLINELOG/group_11 YES UNUSED 250
11 3 +DG2/<DB Name>/ONLINELOG/group_11 YES UNUSED 250
12 3 +DG1/<DB Name>/ONLINELOG/group_12 YES UNUSED 250
12 3 +DG2/<DB Name>/ONLINELOG/group_12 YES UNUSED 250
You are DONE with Addition and resize of redolog groups.
#############
More Thoughts
#############
You may get through these errors when you are not properly executing the redo logs tasks. These can be ignored.
SYS@<DB Name>1>alter database drop logfile group 7;
alter database drop logfile group 7
*
ERROR at line 1:
ORA-01624: log 7 needed for crash recovery of Name <DB Name> (thread 2)
ORA-00312: online log 7 thread 2: '+DG1/<DB Name>/ONLINELOG/group_7.'
ORA-00312: online log 7 thread 2: '+DG2/<DB Name>/ONLINELOG/group_7.'
SYS@<DB Name>1>
alter database drop logfile group 12;SYS@<DB Name>1>
alter database drop logfile group 12
*
ERROR at line 1:
ORA-01567: dropping log 12 would leave less than 2 log files for <DB Name> <DB Name>3 (thread 3)
ORA-00312: online log 12 thread 3: '+DG1/<DB Name>/ONLINELOG/group_12.'
ORA-00312: online log 12 thread 3: '+DG2/<DB Name>/ONLINELOG/group_12.'
########################
Work Arounds for Errors
########################
PRCR-1079 : Failed to start resource ora.dbname.db
CRS-5017: The resource action "ora.dbname.db start" encountered the following error:
ORA-01618: redo thread 3 is not enabled - cannot mount
. For details refer to "(:CLSN00107:)" in crsd trace file
CRS-2674: Start of 'ora.dbaname.db' on 'node03' failed
CRS-5017: The resource action "ora.dbname.db start" encountered the following error:
ORA-01618: redo thread 4 is not enabled - cannot mount
. For details refer to "(:CLSN00107:)" in crsd trace
SQL> alter database enable public thread 3;
This will resolve the issue. Hurray!!!!!!!!!!
