Photos library missing after updating Sonoma

"There is a copy of Photos Library.photoslibrary in my iCloud Pictures folder. "

That is indeed the problem causing the error message. Photos 9 on Sonoma refuses to open a Photos Library, while it is in iCloud.

I decided to chance it and tried to copy the Photos Library (4.78 GB) from my iCloud Drive to my local Pictures folder.

It simply hangs saying "Preparing to move to "Pictures."

That is strange. Are you trying to drag the complete folder "Pictures" from iCloud Drive? Try to drag just the library out of the containing folder from iCloud Drive to your local Pictures folder.

When I move a Photos Library for testing to iCloud Drive, iCloud on Sonoma will not even upload and sync it. There will be crossed out cloud badge shown on iCloud Drive, indicating that the library will not be syncing with iCloud. And it is fairly quick to drag the library back to a local folder.

So your Photos Library has not been uploaded to iCloud Drive at all and it stuck in Limbo with a failed attempt to upload it.

You mentioned "One Drive". How are you using this? Why do you expect it to appear on iCloud Drive?

Do you have a backup of your Photos Library from the time before the Sonoma upgrade? It might be easier to restore the Photos Library from this backup.

To get a working Photos Library: It might be easier to restore your Photos Library directly from iCloud Photos, as you wrote "I can log in to iCloud Photos and see all of my photos including all of them synced from my iPhone after a recent trip. And I see them in the Photos app on my MacBook M1 Pro, just not on the Mac mini."

Unless you need to save projects like slideshows or books, it will be much faster to create a new, empty Photos Library in your local Pictures folder and to make his new library your System Photos Library in "Photos > Settings > General: Use as System Photos Library". Then enable iCloud Photos for this library and wait for your Photos and Videos to sync back from iCloud to this new library. See:

Create additional libraries

When you start with a new empty library on your Mac it will not wipe your iCloud Library. It will just upload the empty library and merge it into your iCloud Library, and this will not change the iCloud Photos Library, as there is nothing to merge into it. The iCloud Library will just download to your Mac into the empty library.

How have you searched on the Mac mini?

when I enter "photoslibrary " into the search field, I can select the option "Kind: Photos Library" and click the option "Search this Mac" to search everywhere.

Have you started the search on iCloud Drive?

Please check, where the local copy of your Photos Library is stored on your Mac mini.

The error message "Photos Library.photoslibrary is in iCloud Drive" is telling you, that the local Photos Library is in a place, where Photos cannot open it. Are you keeping it on your Desktop or in your Documents folder? If "Desktop and Ducuments" are syncing with iCloud Drive, the library must not be stored in Desktop or Documents. The iCloud syncing can damage the local copy of the Photos Library. Photos 9 on Sonoma is now checking, if the library is in a supported location and refuses to open it, if it is kept in an unsupported location.

Drag the Photos Library from iCloud Drive or Desktop or Documents to your Pictures folder. Then try again to launch Photos.