Yeah, you just have to get a radical cam to make it sound like that. In a 307 though, it is kind of pointless. You would have to change so much other stuff to make the engine last, let alone get any real power. More or less you could get a lazy 350 Chevy (non-performance) in good shape and have the same, if not more power, than the best 307 Olds from the factory.
Your best bet if you want to keep the 307, but increase the power a little bit, is to just upgrade it with VIN 9/H.O. parts. The H.O. engine is supposed to have a more "muscular" exhaust note because of the different cam (as opposed to the VIN Y). This would be a different cam, which would then require different valve springs. There is a few other things that could be changed too, but I haven't found any real info pertaining to their effectiveness. I'm actually trying to piece all of this information together right now, in the link I gave you previously. I too, have a 1986 car with a VIN Y 307.