AHUBFILE files in ASM in Oracle 23ai – what is it?

I really love our customers. Many people are so super-smart. A few weeks ago I delivered a full-day hands-on workshop for ora2know in Munich and some (positively) crazy people fly in very early morning from the other end of Germany to attend. Others catch a train at night time (which is the bigger adventure in Germany these days). I feel really grateful since this was also a pay-for-it workshop. It was a great day, I’ve had lots of fun – and the attendees hopefully as well. Dagmar sent me an email a few days later with the subject similar to the title of this blog post: AHUBFILE files in ASM in Oracle 23ai – what is it?

Photo by Bernice Tong on Unsplash

AHUBFILE in ASM?

Dagmar explained that she has a small test environment on their Exadata with a primary and a physical standby with 23.8.0. She spotted an error message in the alert_+ASMx.log pointing to a new file type in ASM: AHUBFILE.

Actually, she found 17 files of this type on the primary ASM, and 91 on the standby site.

The only hint she could find was point to ORA-13586. But this didn’t help her much.

ASMCMD [+DATAXXX/abc/AHUBFILE] > ls -ls

Type      Redund  Striped  Time             Sys  Block_Size  Blocks    Bytes    Space  Name
AHUBFILE  MIRROR  COARSE   JUL 31 06:00:00  Y          4096       1     4096  8388608  ahubfile.1234.1207097041
AHUBFILE  MIRROR  COARSE   JUL 31 06:00:00  Y          4096     522  2138112  8388608  ahubfile.1357.1207097041
AHUBFILE  MIRROR  COARSE   JUL 30 23:00:00  Y          4096     421  1724416  8388608  ahubfile.1482.1207071981
...

And further:

+ASM1> select ... from  v$asm_file where TYPE='AHUBFILE'; 

FILE_NUMBER   BLOCKS      BYTES   TYPE       CREATION_   MODIFICAT R PERMISSIONS  
------------- ----------  ------  ---------- ----------- --------- - -----------
        1357        1       4096  AHUBFILE     30-JUL-25 31-JUL-25 U rw-rw-rw- 
        1482      255    1044480  AHUBFILE     19-JUN-25 19-JUN-25 U rw-rw-rw-  
        1010        1       4096  AHUBFILE     19-JUN-25 19-JUN-25 U rw-rw-rw-   
        1111      381    1560576  AHUBFILE     19-JUN-25 19-JUN-25 U rw-rw-rw-
...

What are these files for, and where are they coming from?

Google and AI are no help either

Well, you certainly would ask your preferred search engine – or you check on MOS – as Dagmar did. But this wasn’t helpful.

Your preferred AI agent can just give information it stole from somewhere else – and when there is no information anywhere, you won’t get anything useful out of it. Tried it out but instead saying “I have no idea it makes a guess – which is totally wrong. Anyway, this blog post is not meant to rant about LLMs. Try it in a few weeks when the bots borrowed the content from this blog post here, and they’ll give you a proper answer then.

BugDB to the rescue

My search started with the BugDB – and I found a helpful bug which allowed me to get further to the topic.

Actually, this is related to AWR snapshots on a standby database. Since your physical standby database is read-only, and typically applying redo, you need some tricks to generate proper AWR information about your standby. In the past – if I remember correctly – we were using database links between primary and standby. The whole process is nicely described in MOS Note: 2409808.1. – How to Generate AWRs in Active Data Guard Standby Databases.

Still, the bug I found pointed me to something different: a new feature in Oracle Database 23ai. It allows to capture AWR data from the ADG Standby, and store these stats inside the SQL tables at the ADG Primary. Since it isn’t in the license guide right now as far as I can see, I can’t even tell you whether this is Actice Data Guard (ADG) only, or not. But I’d guess so. Therefore, in case you don’t have an ADG license, be careful when using it.

Interestingly enough, in Dagmar’s case it got enabled by default since she didn’t even know that there is an API call for it:

BEGIN
  dbms_workload_repository.enable_snapshot_service(
    dbid        =>  NULL,
    snap_type   => 'WORKLOAD',
    snap_action => 'CREATE');
END;
/

In parallel, I did check the New Features App – but it didn’t help me further.

Maybe this query is more helpful?

select * from awrhub_registration;

We’ll see.

Further Investigation

We keep praising Oracle DBAs Run The World. And it is so true. With my hints, Dagmar did some further investigations and send me the outcome of her search.

She found:

select * from awrhub_registration;
 
SOURCE_NAME       SOURCE_AWRID    SERVER_AWRID REGISTRATION_STATE
______________ _______________ _______________ _____________________
abfal                600000087      1234567890 ACTIVE
abfar                600000087      5432109876 ACTIVE

And there are a number of related views:

select distinct (VIEW_NAME)  from cdb_views where view_name like '%AWRHUB%' order by view_name; 

VIEW_NAME
______________________________
AWRHUB_CONFIG
AWRHUB_DOWNLOAD_MAILPKG
AWRHUB_REGISTRATION
AWRHUB_SERVER_CONFIG
AWRHUB_SOURCE_SETTINGS
AWRHUB_SUBSCRIBED_SERVICE
AWRHUB_SUBSCRIPTION
AWRHUB_UPLOAD_MAILPKG
AWRHUB_UPLOAD_MAILPKG_HIST
AWRHUB_UPLOAD_SNAPSHOT_HIST

None of these views is – as far as I can see – documented right now. But even more interesting, I checked with oradiff.oracle.com – and these views are in 19c as well from 19.24 onward, and in 21c, too.

This is just one example:

oradiff.oracle.com => Search Objects => VIEW => “AWRHUB”

Maybe I am using the wrong search terms for the documentation? If somebody has a pointer to this feature, please enlighten me. I found the AWR Hub as a tool for cloud ops. But I don’t think that this is what we are seeing here.

I’m not even sure whether the above views are really related to this standby feature, or whether those are used generally for the AWR Hub for cloud ops.

Conclusion

There is a new feature in Oracle 23ai which allows you easily to generate AWR snapshots on a physical standby database. Dagmar checked her Cloud Control, and she liked what she saw:

AWR Snapshot taken from the physical standby – EM CC

That’s quite nice. And I confess, I don’t know why I couldn’t find anything in the documentation. Maybe it had slipped through the cracks. But in case you find AHUBFILE files, you know now where they are coming from. AHUBFILE stands for AWR Hub data file. And my interpretation is that – since we can’t write into the standby database itself (it is read-only), we write those files to disk or into ASM or ExaScale vaults, and then read them once a report needs to be taken. 

Many thanks to Dagmar for pinging me, and for investigating further.

Further Links and Information

• ORA-13586
• MOS Note: 2409808.1. – How to Generate AWRs in Active Data Guard Standby Databases
• I will update this blog post once there is official documentation available
• Oracle New Features App
• AWR Hub for cloud ops

–Mike