Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Log group switch sequence

Posted by Riyaj Shamsudeen on January 11, 2007

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 = 0x9, size = 72, max = 16, in-use = 8, last-recid= 15)
LOG FILE #1:
siz: 0xc8000 seq: 0x0000022b hws: 0x2 bsz: 512 nab: 0x9 flg: 0x1 dup: 1
LOG FILE #2:
siz: 0xc8000 seq: 0x0000022e hws: 0x2 bsz: 512 nab: 0x39 flg: 0x1 dup: 1
LOG FILE #3:
siz: 0xc8000 seq: 0x00000230 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
LOG FILE #4:
siz: 0x5000 seq: 0x00000228 hws: 0x2 bsz: 512 nab: 0xc flg: 0x1 dup: 1
LOG FILE #5:
siz: 0x5000 seq: 0x0000022a hws: 0x2 bsz: 512 nab: 0x4 flg: 0x1 dup: 1
LOG FILE #6:
siz: 0x5000 seq: 0x0000022c hws: 0x2 bsz: 512 nab: 0x98 flg: 0x1 dup: 1
LOG FILE #7:
siz: 0x5000 seq: 0x0000022f hws: 0x2 bsz: 512 nab: 0xe9 flg: 0x1 dup: 1
LOG FILE #8:
siz: 0x5000 seq: 0x0000022d hws: 0x2 bsz: 512 nab: 0x5 flg: 0x1 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 215 other followers

%d bloggers like this: