Track Current Redo Log Usage with X$KCCCP

calendar Oct 18, 2026 / · 11 min read · Oracle DBA Redo Log Internals X$ Fixed Tables ARCH Process v$log v$logfile v$log_history v$archive_processes x$kcccp  ·
Share on: linkedin copy

Track Current Redo Log Usage with X$KCCCP

Purpose

V$LOG reports a redo log group's allocated size and its status, but it does not report how much of the CURRENT group has actually filled. On a busy instance, that is the number a DBA wants: is the current log close to a switch, or has it barely started filling? V$LOG cannot say.

Oracle's own archiver already answers a version of that question every time it runs. A long-standing third-party reference on the ARCH background process states that when a log switch is forced, ARCH "copies the online redo log a bit more intelligently than how the operating system command cp or copy would do: if a log switch is forced, only the used space of the online log is copied, not the entire log file." That same source names where that count is exposed: "the amount of bytes that have been written to the current online redo log can be determined with x$kcccp." Unlike V$LOG, V$LOGFILE, and V$LOG_HISTORY, X$KCCCP has no published column reference anywhere in either the current 26ai or the 21c Oracle Database documentation on redo log management — it is one of the undocumented fixed tables that sit underneath the documented V$ layer.

This post works outward from that one fact. It covers the documented views first — current group status, log switch history, and the archiver's own process count — because those answer most of what a redo log health check needs, and then shows where X$KCCCP fits as the specific answer to "how full is the log LGWR is writing to, right now."

Code

 1-- Query 1: every redo log group -- status, allocated size, and member file paths
 2SELECT a.group#, a.status, a.bytes/1024/1024 AS size_mb, b.member
 3FROM   v$log a, v$logfile b
 4WHERE  a.group# = b.group#
 5ORDER  BY a.group#;
 6
 7-- Query 2: isolate the one group LGWR is actively writing to right now
 8SELECT group#, status
 9FROM   v$log
10WHERE  status = 'CURRENT';
11
12-- Query 3: log switch frequency by hour, over the last 7 days
13SELECT TRUNC(first_time, 'HH') AS switch_hour, COUNT(*) AS switches
14FROM   v$log_history
15WHERE  first_time > SYSDATE - 7
16GROUP  BY TRUNC(first_time, 'HH')
17ORDER  BY 1;
18
19-- Query 4: how many ARCH background processes are currently running
20-- (the view lists every archiver slot, idle ones included, so filter on STATUS)
21SELECT COUNT(*) AS active_arch
22FROM   v$archive_processes
23WHERE  status = 'ACTIVE';
24
25-- Query 5: force a manual log switch (useful before re-checking status/history)
26ALTER SYSTEM SWITCH LOGFILE;
27
28-- Query 6: the one figure the documented views above do not expose --
29-- bytes already written to the CURRENT redo log, read from the fixed table
30SELECT *
31FROM   x$kcccp;

Code Breakdown

Query 1: group status, size, and member files together

Joining V$LOG to V$LOGFILE on GROUP# turns two separate views into one row per redo log group: its status, its allocated size in megabytes, and the physical file path (or paths, if the group is multiplexed) backing it. This is the query to run first on any instance, because it's the only one of the six that shows every group at once rather than filtering down to one.

Query 2: filtering to CURRENT

STATUS = 'CURRENT' narrows the same view to the single group LGWR is writing to at this moment. A long-standing redo log reference describes a CURRENT log as "active, that is, needed for instance recovery," and the log the database is writing to now. Only one group per redo thread carries this status at a time.

Query 3: switch frequency from history

V$LOG_HISTORY keeps a row for every log switch that has already happened, with FIRST_TIME recording when each one started. Truncating that timestamp to the hour and counting rows turns a flat history into a per-hour switch count — the number that tells a DBA whether switches are happening on a reasonable cadence or far too often.

Query 4: counting ARCH processes

V$ARCHIVE_PROCESSES has one row per archiver slot, including slots whose STATUS is STOPPED, so the count of running archivers is the rows with STATUS ACTIVE. That is the way to confirm how many archiver background processes are actually running, rather than assuming the setting that starts them is doing its job. This matters because the archiver is the process that has to keep up with log switches — if a switch happens and no ARCH process is available to copy the filled group, the database can stall waiting for a group to become available again.

Query 5: forcing a switch

ALTER SYSTEM SWITCH LOGFILE ends the current group early rather than waiting for it to fill naturally. It's useful immediately before re-running Queries 1 through 3, so the "before" and "after" state of a specific group is visible without waiting for real traffic to fill it.

Query 6: X$KCCCP, with no columns assumed

There is no published reference for what columns X$KCCCP carries — it doesn't appear anywhere in the redo log management documentation that covers V$LOG, V$LOGFILE, and V$LOG_HISTORY in detail. SELECT * FROM x$kcccp; is the safe way to query it: it returns whatever the fixed table actually exposes on the release in question, rather than a query built around column names nobody has documented. The one fact the third-party reference above gives — that this table is where the bytes written to the current online redo log can be found — is the whole reason to look at it at all.

Key Points

  • V$LOG.BYTES is size, not fill. It reports the group's allocated capacity. Nothing in the documented view shows how much of the CURRENT group has actually been written to.
  • A log switch is a stop-and-start event, not a resize. Across both the 26ai and 21c Oracle Database references, a log switch is described the same way: the point where the database stops writing to one redo log file and starts writing to another.
  • Status carries more values than the four most DBAs check. CURRENT, ACTIVE, INACTIVE, and UNUSED cover the common cases, but a reference covering GV$LOG also lists CLEARING and CLEARING_CURRENT — states tied to an ALTER DATABASE CLEAR LOGFILE operation in progress.
  • ARCH doesn't copy blindly. A forced log switch triggers a copy of only the used portion of the group, not the full preallocated file size.
  • X$KCCCP sits below the documented layer. It's absent from the same Oracle documentation that fully documents V$LOG, V$LOGFILE, and V$LOG_HISTORY — a different class of object than the three views the rest of this post's queries run against.

Insights and Best Practices

Separate "how big" from "how often"

Guidance on log switch frequency doesn't agree on a single number, but it agrees on direction. The same reference recommends a switch every 5 to 15 minutes — 4 to 12 switches per hour. Its reasons: fewer archive log records and files to track, fewer backup records for RMAN to maintain, and less channel-allocation overhead on backup appliances that need one file per backup set. A separate source, a vendor blog, sets the target slightly wider, at 15 to 30 minutes per switch during peak load, and states plainly that anything more frequent "is a clear sign your logs are undersized." Both sources point the same direction: a redo log health check needs the switch-frequency count from Query 3 alongside the raw size from Query 1, because a log that looks adequately sized can still be switching far too often under real load.

Size for the peak, not the average

The same vendor post gives one example: a database running 200 MB redo logs hit 456 switches in a single hour during a nightly batch load, which showed up as a log file switch (checkpoint incomplete) wait event. Increasing the log size to 22 GB made that wait event disappear from the AWR top-waits list entirely, and the batch window itself shrank by 40%. Size redo logs for the heaviest batch window, not the daily average. A log that looks fine most of the day can still cause a nightly bottleneck.

When switches are too far apart

The opposite problem has a parameter. A well-known write-up on log file switches notes that when switches are too short you add larger log files and drop the old ones, and when they are too long or irregular you can set ARCHIVE_LAG_TARGET (added in 9i) to force a switch after a set number of seconds. A forced switch archives only the used portion of the log, which is why archived log files on such systems vary in size during the day.

Confirm ARCH is actually running, not just configured

A database can be correctly set to ARCHIVELOG mode and still have nothing archiving filled logs if the archiver background process itself isn't up. V$ARCHIVE_PROCESSES is the documented way to check the actual running count rather than inferring it from a configuration setting. It's a cheap check worth running before assuming a backlog of unarchived logs means a storage problem rather than a stalled process.

Reach for X$KCCCP only after the documented views have been checked

Because X$KCCCP carries no column documentation in Oracle's own reference material, it's a diagnostic of last resort rather than something to build a routine monitoring query around. The documented path — V$LOG for current status and size, V$LOG_HISTORY for switch cadence, V$ARCHIVE_PROCESSES for archiver health — answers most redo log questions a DBA actually has day to day. X$KCCCP is only needed for the narrower question none of those three views answer: the exact byte count already written to the group that's current right now.

When to Use This

  • Confirming how close LGWR is to forcing the next log switch, before deciding whether to force one manually.
  • Building a redo log health check that covers size, current status, and switch cadence as one view instead of three separate ad hoc queries.
  • Diagnosing a log file switch (checkpoint incomplete) wait by checking whether log size, switch frequency, or both are out of line with the workload.
  • Verifying ARCH processes are actually running before troubleshooting what looks like an archiving backlog.
  • Investigating exactly how far the current redo log has filled when neither V$LOG nor an AWR snapshot answers the question directly.

Troubleshooting Common Issues

Log switches are happening far more often than the 4-12-per-hour or 15-30-minute guidance. Run Query 1 to check whether the group size matches the workload, and Query 3 to confirm the pattern is sustained rather than a one-off spike. Both sizing references above name the same cause: logs sized for average redo generation, not peak.

All members of the next group become inaccessible to LGWR at a log switch because of media failure. Oracle's own documentation on responding to redo log failure states this returns an error and shuts the instance down. Media recovery may be needed; if the database checkpoint has already moved beyond the lost redo log, Oracle's guidance is that recovery isn't necessary and the inaccessible group only needs to be dropped.

ARCHIVELOG mode is set, but filled logs seem to be piling up unarchived. Check the actual running ARCH process count with Query 4 before assuming a storage or configuration problem — a correctly configured mode with no running archiver process will still leave filled groups waiting.

A query against x$kcccp returns nothing useful or errors out. Because the table carries no published column reference, confirm first that the connecting account has the access an undocumented fixed table typically requires, and treat any column layout observed as specific to the Oracle release being queried rather than a guaranteed constant across versions.

References

Posts in this series