How can I create a custom alert sound in Mountain Lion?
You need add the sound file to /System/Library/Sounds/, or ~/Library/Sounds. (If you don't know how to locate that easily, you can search for "sosumi", and follow the breadcrumbs at the bottom of the finder window.)

The sound file needs to be converted to AIFF or WAV format. Macworld has a good writeup on how to do this using iTunes or garageband.

Then, in System Preferences-&gt;Sound-&gt;Sound Effects, it will show up as an option for your system alerts.