Log group switch sequence

Q: How does Oracle determines which log group to use next,
at the onset of log switch ?
Q: How does adding a log group alters the sequence ?

Log group to switch, is determined from controlfile.
Let’s review the controlfile for pertinent information
Dumping the control file using following commands reveals more information:

oradebug setmypid
oradebug dump controlf 12

Above commands generated a trace file in
user_dump_dest directory and following is the excerpt from the file.
Only few lines shown to improve readability.

**********************************************************
LOG FILE RECORDS
**********************************************************

(blkno = 0×9, size = 72, max = 16, in-use = 8, last-recid= 15)
LOG FILE #1:
siz: 0xc8000 seq: 0x0000022b hws: 0×2 bsz: 512 nab: 0×9 flg: 0×1 dup: 1
LOG FILE #2:
siz: 0xc8000 seq: 0x0000022e hws: 0×2 bsz: 512 nab: 0×39 flg: 0×1 dup: 1
LOG FILE #3:
siz: 0xc8000 seq: 0×00000230 hws: 0×1 bsz: 512 nab: 0xffffffff flg: 0×8 dup: 1
LOG FILE #4:
siz: 0×5000 seq: 0×00000228 hws: 0×2 bsz: 512 nab: 0xc flg: 0×1 dup: 1
LOG FILE #5:
siz: 0×5000 seq: 0x0000022a hws: 0×2 bsz: 512 nab: 0×4 flg: 0×1 dup: 1
LOG FILE #6:
siz: 0×5000 seq: 0x0000022c hws: 0×2 bsz: 512 nab: 0×98 flg: 0×1 dup: 1
LOG FILE #7:
siz: 0×5000 seq: 0x0000022f hws: 0×2 bsz: 512 nab: 0xe9 flg: 0×1 dup: 1
LOG FILE #8:
siz: 0×5000 seq: 0x0000022d hws: 0×2 bsz: 512 nab: 0×5 flg: 0×1 dup: 1

Sequence# above is log sequence # in hexadecimal.

Oracle determines the next log group to switch:
i) searching for the log group with lowest sequence# that is archived.
ii) If a new log group is added, then that new log group will have log sequence# as zero and so that group will be selected
iii) If there are two new log groups, then first one encountered in the above array is selected.

Third point is quite important, since if we drop a log group, then that log group is simply marked as deleted, not completely removed from the array. So, if the same log group is readded, then deleted entry is reused.

Luckily, above controlfile array is externalized as x$kccle table. Instead of dumping the controlfile, we could use x$kccle to test our understanding:

Following SQL, can predict which log group will be selected by Oracle, during next log switch:

SELECT indx, lenum group#, leseq sequence#, archived,
DECODE (leflg, 8, ‘CURRENT’) curstatus,
CASE WHEN leseq = minseq AND indx = minind THEN ‘NEXT’
END NEXT
FROM (SELECT indx, lenum, leseq, leflg,
DECODE (BITAND (leflg, 1), 0, ‘NO’, ‘YES’) archived,
FIRST_VALUE (leseq) OVER (ORDER BY leseq, indx,BITAND (leflg, 1) DESC) minseq,
FIRST_VALUE (indx) OVER (ORDER BY leseq, indx, BITAND (leflg, 1) DESC) minind
FROM x$kccle
WHERE lesiz != 0 AND inst_id = USERENV (‘instance’))
ORDER BY indx

For RAC:

SELECT lethr, indx, lenum group#, leseq sequence#, archived,
DECODE (leflg, 8, ‘CURRENT’) curstatus,
CASE WHEN leseq = minseq AND indx = minind THEN ‘NEXT’
END NEXT
FROM (SELECT lethr, indx, lenum, leseq, leflg,
DECODE (BITAND (leflg, 1), 0, ‘NO’, ‘YES’) archived,
FIRST_VALUE (leseq) OVER (partition by lethr ORDER BY leseq, indx,BITAND (leflg, 1) DESC) minseq,
FIRST_VALUE (indx) OVER ( partition by lethr ORDER BY leseq, indx, BITAND (leflg, 1) DESC) minind
FROM x$kccle
WHERE lesiz != 0 AND inst_id = USERENV (‘instance’))
ORDER BY indx

See Oracle log sequence for complete test case.