The name, often also spelled as unobtanium, combines unobtainable with the -ium suffix that marks the names of chemical elements.
It goes back several decades. This is how it was defined in one of its earliest appearances:
A substance having the exact high test properties required for a piece of hardware or other item of use, but not obtainable whether because it theoretically cannot exist or because technology is insufficiently advanced to produce it.
Interim Glossary, Aero-Space Terms, by Woodford Heflin, Feb. 1958, reproduced in Paul Dickson’s A Dictionary of the Space Age, 2009. The glossary was issued by the Air University of the US Air Force. Heflin tagged the word as “humorous or ironical”.
The word had obviously been around for a while in aeronautics circles. The first known reference is in a newspaper in Michigan in February 1956, but it came to wider notice as the result of this syndicated news article 18 months later:
A officer at Wright-Patterson Air Force Base has disclosed specifications of the ultimate material sought by the Air Force. He is Maj. Gen. William O. Senter, chief of procurement and production, who describes it this way: It must be practically weightless, infinitely strong, resist any degree of heat, mill, machine, cast, and stand forging with great ease and be available at negligible cost. “We haven't found this material yet,” Gen. Senter says, “but we do have a name for it. We call it ‘unobtainium.’”
The Zanesville Signal (Ohio), 9 Nov. 1957.
You can see how engineers in aeronautics and the space program, who were forced to invent novel materials to withstand extreme stresses and temperatures, would have needed such a concept and term to help keep them sane. Unobtainium has long since become a common term in engineering circles for a material that would instantly solve a tricky design problem, if only it existed.
It can also be some substance that does exist but is too difficult to make or get hold of or would cost too much to be practical; another recent term for such stuff is unaffordium. It also turns up a lot in this sense in sports such as motor racing and mountain biking for highly desirable products that are at the most expensive end of their range:
Besides the state-of-the-art 249cc two-stroke motor that delivered the explosive power that motocross required, the bike was loaded with unobtainium parts, including magnesium hubs, electronic ignition and reed-valve induction.
American Motorcyclist, Oct. 2005.
The term has become much better known among non-specialists following two recent SF films. In The Core of 2003, a mad scientist named Edward Brazzleton invents a metal by that name to withstand the extraordinary temperatures and pressures at the Earth’s core. James Cameron’s Avatar (2009) is set on the distant world Pandora, home to the Na’vi, where humans are mining for the rare mineral unobtainium, a room-temperature superconductor.
Imaginatively exotic materials whose properties usefully disregard the boring laws of physics have long been features of SF writing, although authors usually prefer to create their own names for them, such as cavorite, kryptonite, scrith, dilithium and carbonite. The term is so well known as an umbrella tag for such materials (along with its relatives impossibilium, handwavium and fictionite) that it’s usually employed knowingly when it does appear:
The design challenges involved in making a spacecraft the size, shape, and complexity of Nike able to survive atmospheric braking were something to give even modern computers major, major headaches. Dr. Gupta didn't think it could be done at all, in the absence of science-fictional deflector shields or unobtainium hulls.
Boundary, by Eric Flint & Ryk Spoor, 2006.