what's the name of the sound cars and motorcycles makes during redlining?

That is the sound of the engine hitting the rev limiter and it prevents excessive rpm which can eventually destroy an engine,

Some engines, well the early ones, had rev limiters in the distributors and were easily removed / altered. I had to remove one for a customer as the "road" version was limited to 6000rpm, while the "rally" version of the same engine - which was identical - was up to 11500 rpm. We did warn the owner, but he got what he wanted.

Now, the rev limiters are not so easily altered, as the settings need to be changed in software and that means being able to write to the ecu.