An Nvidia 6800 or better would probably be able to cope with the likes of Oblivion with fairly minor compromises. The best available card at the moment for AGP is the 7800GS, which is not far below a PCIE 7800GT and so should be able to run anything that's out today. Both of those (probably the top ATI models too, but I can't confirm that) support shader model 3 which is key for the beauty of games like Oblivion, and while other, older AGP cards may on the surface seem faster in benchmarks this is simply because they don't support all the features and thus have less work to do.
Such high end AGP cards need extra power though (not sure, but I think they draw it from a standard IDE power connector) so make sure your power supply has a free connector of the right type and enough spare juice for the job.
Sad truth is though, that while the top of the line AGP cards can certainly handle today's games, they'll be struggling within a year or two, and there's no more room for upgrades on AGP... it simply doesn't have the bandwidth needed to support even faster cards. Probably the PCIE 7800s will struggle too around the same time, it's just that those can still be upgraded as the slot has bandwidth to spare as well as the option for SLI.