Your debug log shows Pi-hole to be fully operational on IPv4:
*** [ DIAGNOSING ]: Name resolution (IPv4) using a random blocked domain and a known ad-serving domain
[✓] hemoroxin-new.org is NOERROR on lo (127.0.0.1)
[✓] hemoroxin-new.org is NOERROR on eth0 (172.16.0.2)
[✓] hemoroxin-new.org is NOERROR on wlan0 (172.16.0.200)
[✓] doubleclick.com is 142.250.113.113 via a remote, public DNS server (8.8.8.8)
With regards to IPv6, your network has only link-local connectivity, so your network can't speak to public IPv6 addresses on the Internet.
This would sporadically hit your Pi-hole when it rounds up its upstream DNS servers to elect the fastest responding one every now and then, and it seems we were lucky enough to catch such an instance in your debug log:
Unchecking the IPv6 Name Server for Upstream resolution didn't resolve the issue. The revised debug log token is hoba6OmC Name resolution is still intermittent.
Understand the need to uncheck the IPv6 (don't know what I was thinking when it was checked a version or two earlier - typical end-user self-inflicted error).
What's your hardware? and how long did you wait since upgrade?
I did experience the same/similar behaviour on my Pi2B/1GB, see Hardware recommendation for v6? - it did normalize after about 6hours and now I'm back on the Pi2B pi-hole.
edit: I think it was, because my FTL database is about 4,5 GB in size. Perhaps check yours?
pi@pihole:~ $ ls -lah /etc/pihole/pihole-FTL.db
-rw-r----- 1 pihole pihole 4,3G 20. Feb 20:17 /etc/pihole/pihole-FTL.db
OS: Debian GNU/Linux 12 (bookworm) aarch64
Host: Raspberry Pi 3 Model B Plus Rev 1.3
Kernel: 6.1.69-v8+
It has been over 24 hours since the first upgrade. Went through the steps a second time shortly after, since the issue flared up immediately.
Some setting at my end is causing the intermittent behavior. But with my limited knowledge, I am struggling. Don't know why the name resolution is swinging like a pendulum.
You saved me from making few clicks on an already operational OPNsense instance (for DNS and AdGuard)!!
Also, many, many thanks for helping me to become productive in my own way. Following your guidance, the DNS resolution has now been stable for over TEN+ minutes and the earlier signs of intermittent connectivity are not visible. (Need to Read The Manual vis-a-vis PiHole).
Regards.
P.S.
Goes without saying that I'm indebted to the Community here and, of course, the PiHole Dev team. There is always an opportunity to refine the documentation in the spirit of stepwise continuous refinements - whatever that means .