Sallah: Please, what does it always mean, this... this "Junior"? Professor Henry Jones: That's his name. (points to himself) Henry Jones (points to Indy) Junior. Indiana Jones: I like "Indiana". Professor Henry Jones: We named the dog Indiana.
When a character needs a name that tells the audience right away how awesome they are, this trope is one way to do it. The character will have a first name that's interesting and long - at least three syllables long - contrasted by short and unremarkable last name.
Similarly to Mister Strangenoun, the first name is sometimes a cool-sounding noun that isn't normally used as a name. It could also be a made-up word, or an actual name, but only one that's rare enough to still sound unique.
For the last name, "Smith" and "Jones" are the two most used, but any one-syllable name that sounds common and unremarkable also works.
The Discworld novels are prone to this, with characters such as Eskarina Smith and Legitimate First (can't blame a mother for being proud). Young witches also often go through a phase of reinventing themselves with elaborate first names.
TimeSplitters has an entire family of these spread across all three games and several centuries. The three that are introduced are Peekaboo Jones in 1965, his great-great-grandfather, Elijah Jones from the 1800's and Mordecai Jones from the Robot Wars in the 2200's.
Sci-fi author Robert A. Heinlein was very fond of this trope, most often with the surname "Smith". His characters included Valentine Michael Smith, Woodrow Wilson Smith, and Johann Sebastian Bach Smith.
Arabella Figg, Nymphadora Tonks (and Andromeda Tonks, nee Black), Broderick Bode, Caractacus Burke, Bartemius Crouch, Elphias Doge, Marvolo Gaunt, Augustus Pye, Pomona Sprout, Emeric Switch, Emmeline Vance, Romilda Vane, and pretty much all the other Blacks. Thank you, Wikipedia.
In L. Sprague de Camp's Solomon's Stone, the hero finds himself in a world populated by the figures people daydream of being. Everyone has a Sesquipedalian Smith name, indicating first the daydream and then the mundane reality.
Mahershalalhashbaz Ali, an actor who played Mombasa in Predators.
Sufjan Stevens, musician. Has a brother named Marzuki Stevens and a sister named Djohariah.
Nick Clegg has three sons, named Antonio, Alberto, and Miguel. Apparently, his Spanish wife, Miriam, insisted on this trope if all three were to have the surname "Clegg."
Vannevar Bush, 20th century American engineer.
Supermodel Elle MacPherson has played this straight and inverted. She was born Eleanor Gow. Her parents divorced when she was young and her mother married a man named MacPherson. Eleanor took the last name and shortened her first name for balance.
Common in the NFL, with strange names combined with average or short surnames, such as D'Brickashaw Ferguson, Ndamukong Suh and Laveranues Coles.