“Abort!!! Abort!!!”

"For years she ruled as a wise and beloved monarch, each morning at sunrise
releasing the day's new sounds, to be borne by the winds throughout the kingdom,
and each night at moonset gathering in the old sounds, to be catalogued and filed in
the vast storage vaults below."
-- Norton Juster,"The Phantom Tollbooth"

In the early days of Oracle database administration using the ABORT option to the SHUTDOWN command was met with many comments:

“Insane!”

“Irresponsible!”

“Foolhardy!”

“Stupid!”

“It’s your funeral…”

“Better get your CV updated.”

Such exclamations (and their accompanying expletives) were due, in part, from not understanding that Oracle is smart enough to realize an aborted shutdown and initiate the steps necessary to recover any and all available committed transactions, ensuring a consistent database by the time it’s opened. We have come far since then and, for the most part, no longer fear a “shutdowon abort”. Until 11.2 and later releases. 11.2.0.1 introduced the EMON {event monitor} process, which uses the SYS.REG$ table to monitor event queues. Unfortunately a “shutdown abort” can leave the REG$ table in a state unusable by the EMMON process resulting in the process failing to start at database startup. Let’s look into that to get a better understanding of why this can occur.

Oracle is written to perform a number of “housekeeping” tasks when a shutdown is normal, immediate or transactional; one set of these tasks cleans out various queue tables so that at the next startup no orphan queues exist. During a normal or transactional shutdown Oracle will wait for pending transactions to complete before terminating the associated user processes. A shutdown immediate terminates user proesses “immediately”, with networked conenctions taking the most time to stop cleanly. In all three cases Oracle then terminates any advanced queuing proesses, removing associated entries from the tables those processes use. By the time the DBA sees “Oracle shut down” all of the maintenance tasks related to process management are complete and nothing is left “hanging”; the next startup is clean and smooth and, barring any physical problems (disk, memory, CPU) the startup proceeds in an orderly fashion and the database is ready for use.

One of these housekeeping tasks is to clean up the queue tables to remove entries generated by runnning processes that have ben registered for notification. EMON is the process whereby registered events are monitored; changes are recorded, generating notifications. SYS.REG$ contains the information EMON uses to connect to and monitor events:

SQL> desc reg$
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 REG_ID                                                                                       NUMBER
 SUBSCRIPTION_NAME                                                                   NOT NULL VARCHAR2(128)
 LOCATION_NAME                                                                       NOT NULL VARCHAR2(256)
 USER#                                                                               NOT NULL NUMBER
 USER_CONTEXT                                                                                 RAW(128)
 CONTEXT_SIZE                                                                                 NUMBER
 NAMESPACE                                                                                    NUMBER
 PRESENTATION                                                                                 NUMBER
 VERSION                                                                                      NUMBER
 STATUS                                                                                       NUMBER
 ANY_CONTEXT                                                                                  ANYDATA
 CONTEXT_TYPE                                                                                 NUMBER
 QOSFLAGS                                                                                     NUMBER
 PAYLOAD_CALLBACK                                                                             VARCHAR2(4000)
 TIMEOUT                                                                                      TIMESTAMP(6)
 REG_TIME                                                                                     TIMESTAMP(6) WITH TIME ZONE
 NTFN_GROUPING_CLASS                                                                          NUMBER
 NTFN_GROUPING_VALUE                                                                          NUMBER
 NTFN_GROUPING_TYPE                                                                           NUMBER
 NTFN_GROUPING_START_TIME                                                                     TIMESTAMP(6) WITH TIME ZONE
 NTFN_GROUPING_REPEAT_COUNT                                                                   NUMBER
 GROUPING_INST_ID                                                                             NUMBER
 STATE                                                                                        NUMBER
 SESSION_KEY                                                                                  VARCHAR2(1024)

SQL> 

Because of this dependency orphan entries in SYS.REG$ will cause EMON startup to fail, which is why, in most shutdown scenarios, Oracle “cleans house” to remove any entries that could create issues if they are left behind. Notice that says “in most shutdown scenarios”; there is one shutdown option that doesn’t follow those rules.

With a “shutdown abort” the situation is different — processes are terminated abruptly without benefit of any pre-shutdown maintenance. Up until version 11.2 this really wasn’t an issue as background processes started and the resulting “mess” left by the “shutdown abort” could be addressed by performing those same cleanup tasks at the next database startup. Enter the EMON process — it reads the SYS.REG$ table for queues and queuing procoesses to discover which processes to monitor. A “shutdown abort” won’t clear old queue entries from SYS.REG$ and, as a result, the EMON process can fail to start, leaving notice in the alert log of the failure. This really doesn’t affect mounting and opening the database but it is a problem for the DBA to reckon with since notifications will be essentially disabled. If this isn’t quite clear think of it as though the process moved, didn’t leave a forwarding address and tore dowm the old location; mail can’t be delivered because nothing exists where the process used to live.

Finding orphan entries in SYS.REG$ is fairly straightforward as they will not have a lccation_name found in SYS.LOC$:

select reg_id, eubscription_name, count(*) from sys.reg$
where subscription_name like 'CHNF%' and
location_name not in (select location_name from sys.loc$) group by reg_id, subscription_name;

This will provide a list of orphaned registrations that should be able to be deleted from SYS.REG$:

SUBSCRIPTION_NAME                                                                                                                    REG_ID   COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
CHNF130232                                                                                                                           130232          1
CHNF130219                                                                                                                           130219          1
CHNF130513                                                                                                                           130513          1
CHNF130228                                                                                                                           130228          1
CHNF130220                                                                                                                           130220          1
CHNF130514                                                                                                                           130514          1
CHNF130504                                                                                                                           130504          1
CHNF130215                                                                                                                           130215          1
CHNF130510                                                                                                                           130510          1
CHNF130509                                                                                                                           130509          1
CHNF130227                                                                                                                           130227          1
CHNF130507                                                                                                                           130507          1
CHNF130218                                                                                                                           130218          1
CHNF130230                                                                                                                           130230          1
CHNF130229                                                                                                                           130229          1
CHNF130231                                                                                                                           130231          1
CHNF130506                                                                                                                           130506          1
CHNF130505                                                                                                                           130505          1
CHNF130503                                                                                                                           130503          1
CHNF130217                                                                                                                           130217          1
CHNF130512                                                                                                                           130512          1
CHNF130508                                                                                                                           130508          1
CHNF130511                                                                                                                           130511          1
CHNF130216                                                                                                                           130216          1

Modifying the query can let Oracle write the delete statements for you:

select 'delete from sys.reg$ where reg_id = '||reg_id||';'
from sys.reg$
where subscription_name like 'CHNF%' and
location_name not in (select location_name from sys.loc$) group by reg_id, subscription_name;

The query results can be spooled to a file for execution; the output of the above query is shown below:

delete from sys.reg$ where reg_id = 130217;
delete from sys.reg$ where reg_id = 130512;
delete from sys.reg$ where reg_id = 130509;
delete from sys.reg$ where reg_id = 130506;
delete from sys.reg$ where reg_id = 130513;
delete from sys.reg$ where reg_id = 130510;
delete from sys.reg$ where reg_id = 130232;
delete from sys.reg$ where reg_id = 130229;
delete from sys.reg$ where reg_id = 130228;
delete from sys.reg$ where reg_id = 130508;
delete from sys.reg$ where reg_id = 130503;
delete from sys.reg$ where reg_id = 130219;
delete from sys.reg$ where reg_id = 130231;
delete from sys.reg$ where reg_id = 130504;
delete from sys.reg$ where reg_id = 130511;
delete from sys.reg$ where reg_id = 130230;
delete from sys.reg$ where reg_id = 130507;
delete from sys.reg$ where reg_id = 130216;
delete from sys.reg$ where reg_id = 130514;
delete from sys.reg$ where reg_id = 130227;
delete from sys.reg$ where reg_id = 130505;
delete from sys.reg$ where reg_id = 130220;
delete from sys.reg$ where reg_id = 130218;
delete from sys.reg$ where reg_id = 130215;

Once these entries are gone the database can be shutdown and started the EMON process will also successfully start, enabling registered notifications and eliminating the EMON errors previously found in the alert log.

Just remember to file this away for future use.

Digging Through The Archives-

"Nothing can possibly go wrong now," cried the Humbug happily, and as soon
as he'd said it he leaped from the car, as if stuck by a pin, and sailed all the way to
the little island.
-- Norton Juster,"The Phantom Tollbooth"

Starting with Oracle version 6 the database offered the possibility of “hot”, or online, database backups. This relied, in part, on the ability of Oracle to archive, or copy, redo data into a separate location to be read later for recovery purposes. [Oracle also introduced the redo change vector in this same release, a key part of the ability to peform “hot” backups. A discussion on the redo change vector will have to wait for another post.] To provide that functionality the database must be running in archivelog mode, and until recently required that the log_archive_start parameter be set to true; Oracle now automatically sets this if the database is running in archivelog mode. [Of course such parameters as log_archive_dest_n need to be set to let Oracle know where to write the archived redo.] What may not be common knowledge is how much of the configured size of the redo log is actually copied during archiving. Let’s look into that and see where it leads.

Redo logs are configured with a fixed size at creation which cannot be altered, meaning there is no ‘alter redo log … resize …’ command; to increase the size of redo logs it is necessary to create larger logs then delete the smaller logs once they have been archived. Left to its own devices Oracle will write redo data to the current log until it is either completely filled or until the next redo record won’t fit in the current log; Oracle executes a log switch which begins the process of archiving the current redo records. [For this post the redo log size will be 4 GB, using a block size of 512 bytes, producing 8,388,608 blocks.] One would therefore expect that the archivelogs will be very close in size to the redo logs from which they were generated but that may not always be true.

When Oracle copies redo records it only copies what was currently written which may not completely fill the redo log. Given the impossible situation of unlimited storage space Oracle would only switch logs when they are full and the size of the archive would be very close to the size of the redo log. Of course that can’t happen so it’s necessary to backup the archivelog destination on a regular basis to provide both recoverability and space. Such procexses invariably execute a manual log switch prior to backing up the contents of the archivelog destination, making smaller archivelogs a reality. On top of that only the current redo records in a given redo log are copied; the entire file is not simply duplicated with an operating-system level command so even the largest archivelog will likely be smaller than the redo log it was generated from. Depending on the frequency of the archivelog backups some archives may be exceptionally small in comparison.

This brings us to the topic of redo log switch time. Since this is the real world many things can affect archivelog generation — frequency of backups, size of the redo records, transactional volume and how archive_lag_target is set all affect the frequency and size of the archived logs. In an ideal world the switch time would only depend upon the redo log size and the transaction volume; only ‘full’ logs would be archived. That, of course, isn’t true so the log switch time will vary due to the conditions stated at the beginning of this section. Reporting that time is fairly straightforward, given some limitations on the data used to calculate the value. Without any maintenance activity on the redo logs (backups, resizing) any range of days will provide a reasonably accurate switch time indicative of the size of the redo log and the database activity experienced during the day. Looking at a basic script to calculate such a value the query is not a complicated one:

select round(avg(60/count(*)),0) "Log switch time - minutes"
from v$log_history
where first_time >= sysdate - 30
group by to_Char(first_time, 'DD-MON-RRRR HH24')

The output is the average redo log switch time, in minutes, over a period of 30 days. Recommended switch times range from 10 to 15 minutes but some systems may not be able to satisfy that recommendation — transaction volume and file size restrictions may prevent sizing the redo logs to provide that log switch frequency. So the DBA may need to work within such limitations to provide the best compromise between log size and switch frequency.

Looking at log switch history can provide a window into the overall level of activity; the following query can be used to generate a table of hourly log switches (I take no credit for writing it, although I cannot remember where it may have come from):

set verify off pagesize 60
col date format a12
col h0 form 999
col h1 form 999
col h2 form 999
col h3 form 999
col h4 form 999
col h5 form 999
col h6 form 999
col h7 form 999
col h8 form 999
col h9 form 999
col h10 form 999
col h11 form 999
col h12 form 999
col h13 form 999
col h14 form 999
col h15 form 999
col h16 form 999
col h17 form 999
col h18 form 999
col h19 form 999
col h20 form 999
col h21 form 999
col h22 form 999
col h23 form 999
col "Day" format a4
col "Total" format 99999

set linesize 145

SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
    count(*) "Total",
    SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
    SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
    SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
    SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
    SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
    SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
    SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
    SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
    SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
    SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
    SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
    SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
    SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
    SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
    SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
    SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
    SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
    SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
    SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
    SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
    SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
    SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
    SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
    SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
FROM    V$log_history
where first_time > trunc(sysdate - &&1)
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;

Providing the number of days allows for generating output ranging from 1 day to the limit of the data found in V$LOG_HISTORY; using 30 days as an example the script generates the following table:

Date         Day   Total   h0   h1   h2   h3   h4   h5   h6   h7   h8   h9  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23
------------ ---- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
09-NOV-20    Mon     566   21    22   20   28   20   27   25   26   25   24   25   23   26   25   26   25   23   24   22   22   22   23   19   23
10-NOV-20    Tue     520   24    25   25   23   22   24   26   24   23   22   21   21   22   22   21   21   20   20   20   19   21   20   15   19
11-NOV-20    Wed     505   26    20   18   24   20   24   24   21   23   24   22   22   23   22   21   21   19   18   19   19   18   21   17   19
12-NOV-20    Thu     535   20    22   19   24   31   31   25   24   25   22   22   24   23   22   23   21   20   21   19   18   20   20   20   19
13-NOV-20    Fri     550   19    21   17   24   26   25   26   22   24   22   23   27   25   25   28   27   25   24   22   19   19   21   20   19
14-NOV-20    Sat     505   19    21   19   23   26   22   24   23   24   21   22   21   22   20   22   19   21   20   19   19   19   20   20   19
15-NOV-20    Sun     536   21    22   21   29   53   25   25   22   21   20   20   21   21   20   20   21   19   20   18   20   19   20   19   19
16-NOV-20    Mon     537   20    22   21   30   21   24   27   25   25   24   27   31   21   23   21   22   21   21   20   20   18   19   16   18
17-NOV-20    Tue     605   21    20   19   22   22   34   25   25   23   27   28   31   28   30   29   29   28   26   25   20   20   25   24   24
18-NOV-20    Wed     587   25    19   23   32   29   24   25   23   24   24   23   21   21   21   22   23   26   33   31   21   20   27   24   26
19-NOV-20    Thu     520   24    19   20   25   27   24   27   24   24   26   25   21   22   20   22   21   20   18   18   19   19   20   18   17
20-NOV-20    Fri     511   19    20   19   25   25   22   26   25   23   24   22   22   21   22   22   20   20   19   19   19   19   20   19   19
21-NOV-20    Sat     524   19    22   19   24   27   22   26   25   25   22   23   23   22   22   21   20   20   21   20   21   21   20   19   20
22-NOV-20    Sun     542   18    21   18   25   58   25   28   21   22   22   21   21   20   21   20   20   22   21   20   20   20   20   20   18
23-NOV-20    Mon     522   19    22   20   24   28   23   25   24   24   22   25   22   21   22   21   22   23   21   19   19   19   19   20   18
24-NOV-20    Tue     519   18    22   18   24   26   24   25   23   26   22   22   22   22   23   24   22   20   21   19   20   20   19   19   18
25-NOV-20    Wed     521   19    19   21   26   25   24   26   22   24   24   22   23   21   22   23   21   20   22   20   21   20   20   18   18
26-NOV-20    Thu     484   19    22   20   21   20   22   24   19   22   21   20   21   19   20   19   20   19   19   19   20   20   20   19   19
27-NOV-20    Fri     519   20    21   21   27   31   22   24   24   23   21   21   23   21   20   21   21   22   20   19   19   20   20   19   19
28-NOV-20    Sat     521   19    21   19   26   31   23   27   23   23   23   21   22   22   21   21   20   20   22   19   19   20   21   19   19
29-NOV-20    Sun     535   21    21   19   23   60   22   25   22   21   21   19   20   19   22   20   21   20   19   20   20   21   20   19   20
30-NOV-20    Mon     592   19    19   20   22   27   23   28   24   25   21   24   25   22   28   29   29   28   26   30   20   19   28   28   28
01-DEC-20    Tue     535   19    18   19   27   33   24   28   25   26   25   24   22   22   23   23   21   22   20   19   21   18   19   18   19
02-DEC-20    Wed     529   18    21   18   27   33   25   25   23   23   22   22   23   21   23   23   22   21   20   20   21   20   20   19   19
03-DEC-20    Thu     559   20    22   21   29   33   24   27   27   24   25   26   25   22   25   25   23   22   20   20   21   20   20   19   19
04-DEC-20    Fri     529   18    21   20   24   31   26   27   25   24   23   23   24   19   22   24   22   20   19   19   20   19   20   19   20
05-DEC-20    Sat     520   19    21   19   23   29   25   25   24   23   23   21   21   21   21   21   21   22   20   21   21   20   21   19   19
06-DEC-20    Sun     531   20    20   19   20   59   26   24   23   23   20   21   19   20   21   20   19   19   21   18   19   19   22   18   21
07-DEC-20    Mon     525   20    22   20   24   27   24   23   22   26   21   24   24   22   23   23   21   21   21   20   19   21   20   19   18
08-DEC-20    Tue     552   19    23   20   25   26   24   26   24   24   22   22   24   20   23   23   23   20   20   19   21   20   19   17   48
09-DEC-20    Wed      98   10    10    9    8   10   10    9    8    9    9    6    0    0    0    0    0    0    0    0    0    0    0    0    0

Using such a query can help spot redo logs that need to be made larger to accomodate the transaction volume so log switch frequency can be reduced; remember that these totals will be affected by archivelog backup frequency and the value of archive_lag_target.

In tandem with the log switch frequency is the actual time required to archive the current redo log; this should be a fairly quick operation barring any storage or configuration issues. The archive write time can be calculated from data found in V$ARCHIVED_LOG, and write times for individual archives as well as an overall average can be computed. To calculate the write time for individual archivelogs:

select (completion_time - next_time)*86400 from v$archived_log;

NEXT_TIME is the time recorded for the first entry in the next redo log or redo log group to be written to; it matches the FIRST_TIME entry for the next log record in the view. COMPLETION_TIME is the date/time when the actual data copy to the archivelog finishes. NEXT_TIME is therefore the date/time of the log switch; switching logs triggers the write mechanism to the archivelog destination so it’s also the start time of the redo data copy. Depending on the size of the redo data copied the copy time can fluctuate, and that may be confusing to those who expect the archivelogs to be the size of the redo logs. V$ARCHIVED_LOG also records the blocks copied so it’s easy to determine the size of each archived copy of a redo log:

connect / as sysdba
set linesize 180 pagesize 100 trimspool on
column name format a60
column pct_arch format 990.90
column sysdate new_value sys_dt noprint

select sysdate from dual;

alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

with rblocks as (
select distinct bytes/blocksize rbl from v$log
)
select name, first_time, next_time, blocks, round((blocks/rbl)*100, 2) pct_arch, completion_time, (completion_time - next_time)*86400 write_time
from v$archived_log, rblocks
where first_time >= trunc(sysdate)

spool archlog_rpt_&sys_dt..log
/
spool off

undefine 1

A portion of the report generated is shown below:

NAME                                 FIRST_TIME       NEXT_TIME        BLOCKS PCT_ARCH COMPLETION_TIME  WRITE_TIME
------------------------------------------------------------ -------------------- -------------------- ---------- -------- -------------------- ----------
                                 11-DEC-2020 00:00:22 11-DEC-2020 00:01:08    7395689    88.16 11-DEC-2020 00:01:17      9
                                 11-DEC-2020 00:01:08 11-DEC-2020 00:02:06    7642144    91.10 11-DEC-2020 00:02:14      8
                                 11-DEC-2020 00:02:06 11-DEC-2020 00:03:27    7426709    88.53 11-DEC-2020 00:03:38     11
                                 11-DEC-2020 00:03:27 11-DEC-2020 00:15:24    3594910    42.85 11-DEC-2020 00:15:29      5
                                 11-DEC-2020 00:15:24 11-DEC-2020 00:30:18      98727     1.18 11-DEC-2020 00:30:18      0
                                 11-DEC-2020 00:30:18 11-DEC-2020 00:45:16      91018     1.09 11-DEC-2020 00:45:16      0
                                 11-DEC-2020 00:45:16 11-DEC-2020 00:45:50        699     0.01 11-DEC-2020 00:45:50      0
                                 11-DEC-2020 00:45:50 11-DEC-2020 01:00:41    5510730    65.69 11-DEC-2020 01:00:50      9
                                 11-DEC-2020 01:00:41 11-DEC-2020 01:00:54    1382449    16.48 11-DEC-2020 01:00:57      3
                                 11-DEC-2020 01:00:54 11-DEC-2020 01:01:41    8097166    96.53 11-DEC-2020 01:01:55     14
                                 11-DEC-2020 01:01:41 11-DEC-2020 01:02:44    7558944    90.11 11-DEC-2020 01:02:55     11
                                 11-DEC-2020 01:02:44 11-DEC-2020 01:04:02    7429076    88.56 11-DEC-2020 01:04:15     13
                                 11-DEC-2020 01:04:02 11-DEC-2020 01:15:26    2023945    24.13 11-DEC-2020 01:15:29      3
                                 11-DEC-2020 01:15:26 11-DEC-2020 01:30:18    1779189    21.21 11-DEC-2020 01:30:21      3
                                 11-DEC-2020 01:30:18 11-DEC-2020 01:45:14    1732877    20.66 11-DEC-2020 01:45:16      2
                                 11-DEC-2020 01:45:14 11-DEC-2020 01:45:40      39409     0.47 11-DEC-2020 01:45:41      1
                                 11-DEC-2020 01:45:40 11-DEC-2020 02:00:24    4620762    55.08 11-DEC-2020 02:00:32      8
                                 11-DEC-2020 02:00:24 11-DEC-2020 02:01:22    7640768    91.09 11-DEC-2020 02:01:32     10
                                 11-DEC-2020 02:01:22 11-DEC-2020 02:02:25    7465826    89.00 11-DEC-2020 02:02:33      8
                                 11-DEC-2020 02:02:25 11-DEC-2020 02:03:58    7328193    87.36 11-DEC-2020 02:04:07      9
                                 11-DEC-2020 02:03:58 11-DEC-2020 02:15:16    1618757    19.30 11-DEC-2020 02:15:19      3
                                 11-DEC-2020 02:15:16 11-DEC-2020 02:30:14     786809     9.38 11-DEC-2020 02:30:15      1
                                 11-DEC-2020 02:30:14 11-DEC-2020 02:30:16         86     0.00 11-DEC-2020 02:30:16      0
                                 11-DEC-2020 02:30:16 11-DEC-2020 02:45:16     775505     9.24 11-DEC-2020 02:45:17      1
                                 11-DEC-2020 02:45:16 11-DEC-2020 02:45:39      36017     0.43 11-DEC-2020 02:45:39      0
                                 11-DEC-2020 02:45:39 11-DEC-2020 03:00:29    4719624    56.26 11-DEC-2020 03:00:40     11
                                 11-DEC-2020 03:00:29 11-DEC-2020 03:01:20    7616245    90.79 11-DEC-2020 03:01:31     11
                                 11-DEC-2020 03:01:20 11-DEC-2020 03:02:11    7445839    88.76 11-DEC-2020 03:02:19      8
...

The BLOCKS column provides the number of redo blocks copied from the redo log to the archive; notice that it is less than the total number of blocks for the redo log (8388608). [The NAME is blank because the archivelog has been removed from the system as a result of a scheduled archivelog backup.] As mentioned previously the redo record size governs how full the redo log will become before a “standard” log switch occurs; very rarely will the archivelog contain the total number of blocks allocated to the redo log, that’s the nature of redo records. As long as the archived block count is reasonably close to the total number of blocks in the redo log there should be no reason for concern. If archive_lag_target is set then log switches should occur with regularity and at the specified interval — this does not prevent redo log switches from occurring “naturally”, and the report above illustrates that for activity at the top of each hour. Notice that those will be very close to 90+% of the total redo blocks allocated. Other reasons log switches can occur are a privileged user executed a manual log switch after redo log additioms and RMAN archivelog backups so the blocks written can sometimes be a smaller number. Using the log switch history query provided above will report the number of log switches per hour so hourly comparisons can be made from several days data; an increase in an hourly number could be indicative of manual intervention and a starting point for investigation. A report similar to that provided above should cause no concern.

As long as you don’t jump to conclusions.