A few examples will suffice, although there are indeed plenty to go around. And I mean no disrespect to any of the great opera composers not mentioned here. Let's take as exhibit one, a piece of music so familiar that as kids, who knew nothing of classical music, let alone opera, we even bowdlerized its common name: Handel's Largo which we irreverently referred to as Handel's lager. I've only recently come to really know this piece which more properly is referred to as Ombra mai fu from Serse, the story of Xerxes I of Persia, successor to Darius the Great. Xerxes sings this aria in praise of the plane tree and the wonderful shade it provides:
of any plant,
dearer and more lovely,
or more sweet.
There's a wonderful video and recording on YouTube with Cecilia Bartoli [recall that Handel wrote many of his lead male parts for castrato voices which are generally sung these days by women].
My next example is from Mozart and you might quibble with my interpretation of the text as mundane as it tells of lovely fresh breezes in a romantic setting. Yet, the purpose of the letter which the Countess is dictating is to expose her husband's infidelity, it is not an outpouring of some noble emotion. She's not even particularly upset -- she just wants to teach him a lesson. But I don't think you can disagree with my assessment of the sublimity of the duettino from The Marriage of Figaro: Sull'aria che soave zeffiretto. You may even recall one of my all-time favorite movies scenes, from the Shawshank Redemption, when Andy (Tim Robbins) broadcasts this over the prison P.A. system to the consternation of the warden and the wonder of Red (Morgan Freeman).
What a gentle little Zephyr
This evening will sigh
Under the pines in the little grove.
And the rest he'll understand.
Easily the best sound quality that I found on YouTube is the recording (without video) of Jessye Norman and Mirella Freni. The balance, and playing, of the oboe and bassoon are just perfect. Incidentally, there's a clip of Dame Kiri which should be the best but the sound quality isn't good. In particular, the bassoon overpowers the oboe! [I'll bet you never thought I'd say that!]
My final example is by Puccini from that intrinsically mundane seeming story about the canal people: Il tabarro. Even the title refers to something seemingly (though not in fact) insignificant: a cloak. But don't be fooled. This is verismo opera at its absolute best. What appears ordinary is loaded with passion, repression and violence. This rather long aria and duet is known as E' ben altro il mio sogno (I have a different dream) and tells of very ordinary life in the suburbs of Paris (in particular Belleville). Here are Teresa Stratas and Placido Domingo. Giorgetta's aria starts at about 0:40 and the duet, some of the most powerful music Puccini ever wrote, starts at 4:12. Here are the words of that section (my own translation):
And whoever returns cannot leave.
Over there Paris is crying out to us,
with a thousand happy voices,
about its eternal enchantment.
I hope I've convinced you that ordinariness or banality is no bar to glorious opera when at the hands of a real master.