How loud a sound would need to be to wake someone up?
I think a SIREN sound with higher Decibel >110 will work. Because as a human being, a high pitch sound create alert signal to our brain. 
Still, the sound to wake up a person will vary from person to person. 
But a person will definitely react to eardrum piercing sound waves